Re: Slow insert rate in mysql innodb
Posted by:
Rick James
Date: January 30, 2014 08:31PM
> Do you mean to say in standalone mysql we can insert only 100 records per second with my configurations?
Maybe. It depends on the indexes, disk subsystem, etc.
Why is there 82GB of free space in the table? Or is in ibdata1? Are you using innodb_file_per_table = ON? If not, you should be.
Only 2 partitions??? Please explain the logic.
> `suppression_flag` int(1) DEFAULT NULL,
> `notification_type` int(38) DEFAULT NULL,
INT is always 4 bytes. INT SIGNED (the default) has a range of +/- 2 billion. INT UNSIGNED has a range of 0 to 4 billion. A flag does not need billions of options. Consider using TINYINT UNSIGNED. The (38) means nothing.
> >>Will store 10 days before drop, yes, will drop the partition.
I would expect 12 PARTITIONs, DROP one each day, plus REORGANIZE PARTITION to turn the future partition (normally empty) into tomorrow plus a new future.
> KEY `sys_msg_supp_flag_index` (`suppression_flag`),
It is almost always useless to index a flag.
You have no PRIMARY KEY -- Is there some column (or combination of columns) that provides a 'natural' PK? (It must be UNIQUE.) If so, add that. (And remove any secondary KEY that is then redundant.) It really, really, looks like `id` is such a key.
How random are these?
> KEY `sys_supp_notification_index` (`notification_type`),
> KEY `sys_host_index` (`host`),
They could be slowing you down.
Let's see the SELECTs you use on the table. We may be able to recommend different INDEXes, especially including a 'compound' index to speed up both the SELECTs and the INSERTs.