Slow inserts For Large Innodb table

MySQL & MariaDB Other MySQL® Questions
1

Hi,
I have a very large Innodb. It is already 85 G + and growing really fast. The problem we are facing is that our inserts have become really slow at this point. As the table has increased in size the inserts have become slower by the day. I took the same schema and worked on it on development machine which is Pentium D ,2.8 ghz with 2 Gb of Ram. I have a primary key in the table and a unique index which is stores crc32 value of a large string which identifies each row in the summary table uniquely. In my dev benchmarking i noticed that the insert performance degraded significantly as the size of the table increases. The time for inserting 200000 rows keeps increasing linearly 23 ,53 ,86 etc. I used the load data infile method to test the performance.

The time taken is some what constant when the unique key is hit with the replace option in the Load data infile.

In our Prod database it simply takes way to long to insert into this summary table and also select from it.

Has any one noticed such quick performance degrades.

2

It sounds as if you are using the crc32 string as your primary key and then I can definitely understand it.

Since InnoDB stores data at the leaves of the primary index the primary key should be kept as short as possible.
And if the primary key is not a int that just increments linearly each insert of data can’t be made at the end of the table but in the middle which forces some data shuffling.

If that is the case then go with a int for the relation between the tables instead.

Good luck