Mysql – inno_db_buffer_pool_size and key efficiency

innodbMySQL

I have the following metrics from mysql workbench

enter image description here

After I have set innodb-buffer-pool-size to 6G, according to this answer's query on how to determine the recommended size.

While the used buffer usage was between 87% and 93% before I did the change in my.cnf (running with default values) it dramatically decreased now, so I'm wondering if 6G was too much. Especially because the key efficiency was between 97% and 99% before the change, which has lost some percents now.

Should I bother more about key efficiency or buffer usage? Should I decrease the buffer usage again?

The server specs are

Intel® Xeon® E5-1650 v3 
Hexa-Core Haswell Hyper-Threading-Technologie
128 GB DDR4 ECC RAM
2 x 480 GB SATA 6 Gb/s Data Center Series SSD

Best Answer

If your tables are InnoDB (not MyISAM), innodb_buffer_pool_size should be about 70% of available RAM. However it does not need to be much bigger than the total Data_length and Index_length for all your tables.

It does not hurt to make that tunable "too big" unless you need the RAM for something else.

If you would like further analysis, please provide SHOW VARIABLES and SHOW GLOBAL STATUS. From that, we can sort out which engine the vague "key efficiency" refers to, plus check other things. (I have automated about 200 checks.)

Also, it is unclear whether "InnoDB writes per second" is INSERTs or disk writes. Since you have SSDs, it could be either.

Related Question