I have used mysqltuner script for a couple of years on and off. While doing some MySQL Performance Tuning research this week I came across the MySQL Tuning Primer which I found to be also useful for some clarity on configuration settings that could be readily updated. However, that one has not been updated for some time and reports the slow_query_log as not running when I know it is, states that there is no InnoDB support, which is odd as that is the default storage engine, and having another look I found a more up-to-date version at https://github.com/BMDan/tuning-primer.sh. That said it is still not up-to-date as it is offering this counter-intuitive advice:
You are using MySQL 8.0.27, no query cache is supported. I recommend an upgrade to MySQL 4.1 or better
Which suggests that it could use some work, but it still provides some good pointers, and as always YMMV aka ‘your mileage may vary’.
So back to my server, and mysqltuner script and covering some of the issues raised on the test server.
Using mysqltuner gave me the following commentary on the first pass:
General recommendations:
Check warning line(s) in /var/log/mysqld.log file
Check error line(s) in /var/log/mysqld.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
(specially the conclusions at the bottom of the page).
Performance schema should be activated for better diagnostics
Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
join_buffer_size (> 256.0K, or always use indexes with JOINs)
table_definition_cache(4000) > 7166 or -1 (autosizing if supported)
performance_schema = ON enable PFS
innodb_buffer_pool_size (>= 1.5G) if possible.
innodb_log_file_size should be (=16M) if possible, so InnoDB total log files size equals to 25% of buffer pool size.
I also grabbed a suggested config for my.cnf from https://gist.github.com/fevangelou/fb72f36bbe333e059b66 and used that as a basis for my next test run.
After a few tweaks, mysqltuner reports a better starting position:
General recommendations:
Check warning line(s) in /var/lib/mysql/mysql_error.log file
MySQL was started within the last 24 hours - recommendations may be inaccurate
Reduce your overall MySQL memory footprint for system stability
Dedicate this server to your database for highest performance.
Reduce or eliminate unclosed connections and network issues
Given that there are no warnings relating to memory size settings, other than the recommendation to reduce the overall footprint, I will wait for 24 hours to see what activity occurs and get a comparable report.
Just in case you are looking at your mysqltuner output and wondering what these lines refer to:
-------- Aria Metrics ------------------------------------------------------------------------------ [--] Aria Storage Engine not available. -------- TokuDB Metrics ---------------------------------------------------------------------------- [--] TokuDB is disabled. -------- XtraDB Metrics ---------------------------------------------------------------------------- [--] XtraDB is disabled. -------- Galera Metrics ---------------------------------------------------------------------------- [--] Galera is disabled.
The answer is relatively simple, unless you want to delve really deeply into it.
Galera is a clustering solution for MySQL and only applies if you are setting up a high-availability solution. My guess is, if you are reading this, then like me, you do not need it!
The other 3 are all alternative database storage engines, like ISAM and INNODB.
Aria is an alternative to ISAM. TokuDB is deprecated while XtraDB is current as InnoDB alternative from Percona for MySQL and MariaDB although MariaDB have stopped using it and reverted to InnoDB as their default storage engine.
For the moment I am sticking with the default InnoDB on MySQL 8.x for this server as I am not looking to ‘buy trouble’.
The main issue that I spent time reviewing, understanding (somewhat) and addressing was the Max MySQL Memory setting.
If Max Memory is greater than physical memory it only may be an issue IF MySQL actually tries to use it all. A more detailed reference is at MySQL is https://dev.mysql.com/doc/refman/5.7/en/memory-use.html
How to calculate MySQL Max Memory? Using my initial test examples, start with server_buffers, which are buffers that apply at the server level, i.e. only one buffer instance per server.
server_buffers = key_buffer_size 24M + innodb_buffer_pool_size 4G + innodb_log_buffer_size 16M + query_cache_size - Not Applicable with MySQL v8.x
As a side note, you may read other calculators which include “innodb_additional_mem_pool_size” in this calculation but that parameter was deprecated in MySQL 5.6 and removed in v5.7. See https://dev.mysql.com/doc/refman/5.7/en/innodb-performance-use_sys_malloc.html for more information.
server_buffers = 24M +4096M (4G) + 16M = 4136M
Then look at the total_per_thread_buffers which are the buffers used per connection. The total of these buffers is then multiplied by the max_connections value to get the total potential memory allocated that might be used.
total_per_thread_buffers = read_buffer_size 3M + read_rnd_buffer_size 4M + sort_buffer_size 4M + thread_stack 192K + max_allowed_packet 256M + join_buffer_size 4M
total_per_thread_buffers = 3M + 4M + 4M + 192K + 256M + 4M = 271M (+192K) use 272M.
Then multiply total_per_thread_buffers by the max_connections value and finally add the server_buffers.
4136M + (272M * 100) = 4136M + 27,200M = 31,336M
Which is only marginally inconsistent with the mysqltuner calculation at 34,600M (~34G) . Note that I also tweaked the server buffers to 8G during the various tests.
[–] Physical Memory : 31.4G
[–] Max MySQL memory : 34.6G
[–] Other process memory: 0B
[–] Total buffers: 8.2G global + 271.2M per thread (100 max threads)
So I reduced the max_allowed_packet parameter from 256M to 128M to try and resolve that max memory issue but Cpanel/WHM is overwriting that value back to 256M when it restarts. I then dropped the value of max_connections from 100 to 60 as the usage over 24 hours indicated only 27 max connections had been used.
Both of these values need more research, acknowledging that I have no idea at this time how frequently there are queries approaching that packet size or exceeding it, or how many peak connections may be needed, but monitoring will address that in time.
What I do know now, is that I can more effectively monitor what should be a more efficient MySQL server than it was 6 hours ago.