Re: Slow insert rate in mysql innodb

[Dhinakaran Arumugam]

Thanks Rick for your reply. Please find my answers for your questions.

mysql> SHOW TABLE STATUS LIKE 'syslog_data';
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
| syslog_data | InnoDB | 10 | Compact | 83383285 | 267 | 22310567936 | 0 | 9293611008 | 82812338176 | 78494913 | NULL | NULL | NULL | latin1_swedish_ci | NULL | partitioned | |
+-------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-------------+----------------+-------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)



create table script

| syslog_data | CREATE TABLE `syslog_data` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`timestamp` datetime DEFAULT NULL,
`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(38) DEFAULT NULL,
KEY `sys_msg_supp_flag_index` (`suppression_flag`),
KEY `sys_id` (`id`),
KEY `sys_supp_notification_index` (`notification_type`),
KEY `sys_host_index` (`host`),
KEY `timestamp` (`timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=78494913 DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE ( to_days(timestamp))
(PARTITION P_2014_01_31 VALUES LESS THAN (735629) ENGINE = InnoDB,
PARTITION new_partition VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */ |



How long will you save the data before purging it? I assume you will be doing DROP PARTITION?
>>Will store 10 days before drop, yes, will drop the partition.

It sounds like you will be having a huge table (terabytes); shouldn't you consider a standalone mysql server?
>>In a day it may grow till 100GB.


Do you mean to say in standalone mysql we can insert only 100 records per second with my configurations?