1

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
Improve this question
5
  • 1
    Do you use some MyISAM tables or InnoDB only? One more thing is that this value is probably computed from global status counters which reset on restart together with the caches and buffers, so after a restart all data have to be reload from disk and not from cache, so this might lower the computed ratio until you get enough cache reads to make that cold reads negligible.
    – jkavalik
    Commented Oct 2, 2015 at 14:11
  • ahh, this will be the explanation. I'm using innoDB only on this server. The server restarted half an hour ago, so yes, might take some more time. @jkavalik
    – baao
    Commented Oct 2, 2015 at 14:12
  • Then it depends on how Workbench computes that number, key_buffer is MyISAM only but WB might "combine" it with some relevant status counter for InnoDB too.. If not then system tables (mysql database) are MyISAM by default so it would only show statistics for these.
    – jkavalik
    Commented Oct 2, 2015 at 14:16
  • Do you think the metrics as they stand now are acceptable/scaleable enough? @jkavalik
    – baao
    Commented Oct 2, 2015 at 14:29
  • I am not well-versed in Workbench stats.. The number of connections seems quite big, but only if it means concurrent ones, if it is total for history then thats ok. InnoDB buffer seems empty so those 6GB might be too much, but you should check how big your tables are, if they are more or at least close to 6GB then keep the setting, but if you had say 2GB of InnoDB data then you could lower it.
    – jkavalik
    Commented Oct 2, 2015 at 14:38

2 Answers 2

Reset to default
1

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.

Improve this answer
0

Have a check with mysqltuner. It will inform you of what is needed depending on your data and index volume.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.