Will increase in Innodb_buffer_pool_size increases insert query performance?

Question

We have 3 identical database boxes(M1<->M2->S1) with Mysql 5.7.17 installed and having memory 96GB RAM. In these boxes nearly 42GB occupied by Index and data length of Innodb Tables and 38G occupied by Myisam index tables. All at a sudden we observed there is increase in insert query execution time in one of the box "M2" causing replication delays. Upon checking Innodb_buffer_pool_size is defined as 10G in M2 but in remaining boxes the value is 128M. So is this impact with increase in the innodb_buffer_pool_size only? why ?

Answer 1

128M is pathetically low.

If you are running a mixture of InnoDB and MyISAM on a MySQL-only box, I recommend

innodb_buffer_pool_size = 1/3 of RAM (32G for your 96GB machines)
key_buffer_size = 1/10 of RAM (10G)

That leaves some room for data caching for MyISAM since it leaves that up to the OS.

Some of the "Why"...

InnoDB uses 16KB blocks for data and indexes; it caches these blocks in (roughly) a "least-recently-used" fashion in the buffer_pool. How big should the buffer_pool be? There are several rules:

• It does not have to be much bigger than the actual data+index lengths.
• If the "working set" is small, then small is good enough. After all, that is what "caching" is all about.

MyISAM uses its "key_buffer" for caching index blocks, which are 1KB each. It depends on the OS for buffering/caching the data, which is not "blocked" (from MyISAM's point of view -- it is just a stream).

Since you are using both engines, and the entire dataset is pushing the amount of RAM, you have a juggling game to play. You might try 50G and 6G for those two settings.

If you are using UUIDs, you are screwed -- InnoDB and MyISAM are likely to blow out their caches and become I/O-bound as the dataset grows.

But... You said that the machine with the bigger buffer_pool became slower than the other boxes? This does not make sense. Unless some query (eg, ALTER TABLE) was hogging resources on it.