Re: Slow insert rate in mysql innodb

[Rick James]

Analysis of INSERTs:
Given:
* 4000/sec desired
* 100 disk hits/sec (assuming ordinary drive)
* PRIMARY KEY starts with AUTO_INCREMENT
* INDEX(timestamp)
* INDEX(host, ...)
* ~1000 distinct hosts
* buffer_pool: 24G
* Assuming errorname and message average 100 bytes.

The data (and PK) will have a new block after every ~25ms.
INDEX(timestamp) will need a new block after every ~100ms.
INDEX(host, ...) will have ~1000 "hot spots". Some hot spot will need to store a new block every ~100ms.
I/O: So, no problem with overloading disk I/O.
Cache: 1+1+1000 blocks need to be cached. That's ~16MB. So, no thrashing.

What's the _average_ size of errorname and message? If the average is, say, 1000 bytes, then the above computations are in trouble. In particular, a block can hold only about 14 records if errname+message is 1KB. This would lead to writing a new block every 3ms. The disk won't keep up. If the text is long (like that), then here are some options:

* RAID striping
* Compress the text fields
* Normalize the text fields -- but only if there is a lot of repetition.