Re: Slow insert rate in mysql innodb
Thanks Rick.
After making below change in indexes, able go insert rate of 2500 inserts for more than 12hrs. But after 12hrs the insert rates are slowing down.
After 12 hours of insert, the select query is not responding.
New table creation script:
CREATE TABLE `syslog_data` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`timestamp` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
`host` varchar(38) DEFAULT NULL,
`hostname` varchar(38) DEFAULT NULL,
`fullhost` varchar(38) DEFAULT NULL,
`facility` varchar(10) DEFAULT NULL,
`priority` varchar(10) DEFAULT NULL,
`errorname` varchar(4000) DEFAULT NULL,
`message` varchar(4000) DEFAULT NULL,
`suppression_flag` int(1) DEFAULT NULL,
`notification_type` int(1) DEFAULT NULL,
PRIMARY KEY (`id`,`timestamp`),
KEY `timestamp` (`timestamp`,`notification_type`),
KEY `host` (`host`,`priority`,`suppression_flag`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( to_days(timestamp))
(PARTITION P_2014_02_07 VALUES LESS THAN (735636) ENGINE = InnoDB,
PARTITION new_partition VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */
select query which is not responding:
select id,timestamp,host,facility,priority,errorname,message,case suppression_flag when 0 then 'A' when 2 then 'F' else 'D' end,case notification_type when 1 then 'T' else 'S' end from syslog_data where timestamp between str_to_date('02/06/2014 12:25:31','%m/%d/%Y %k:%i:%s') and str_to_date('02/06/2014 12:55:31','%m/%d/%Y %k:%i:%s') limit 200
could you please provide your input.
Edited 2 time(s). Last edit at 02/06/2014 07:31AM by Dhinakaran Arumugam.