MySQL Forums
Forum List  »  Performance

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
2782
January 28, 2014 06:46PM
Re: Slow insert rate in mysql innodb
1783
January 30, 2014 08:31PM
2244
January 31, 2014 08:31PM
1504
February 07, 2014 11:00AM
1704
February 12, 2014 07:30PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.