MYSQL Optimization
Basic guide to tweak MYSQL. CentOS 7 with cPanel.
– top to see CPU usage.
– Process list
mysqladmin processlist|wc -l
– Current maximum potential MySQL memory usage with your my.cnf file:
grep -E ‘key_buffer_size|innodb_buffer_pool_size|max_connections|innodb_log_buffer_size’ /etc/my.cnf
https://www.mysqlcalculator.com
– Memory limits:
mysql
SHOW GLOBAL VARIABLES LIKE 'max_allowed_packet';
– Mem/RAM usage
ps faux | grep -i ^mysql
Disk I/O wait. If you are not seeing high IO WAIT or swap usage, then DISK IO is more than likely not an issue.
sar -s
Total Index Sizes by Engine
Set innodb_buffer_pool_size based on the sum of ‘ibd’ and ‘ibdata1’, and set key_buffer_size based on the total listed for ‘MYI’.
mysql -Bse 'show variables like "datadir";'|awk '{print $2}'|xargs -I{} find {} -type f -printf "%s %f\n"|awk -F'[ ,.]' '{print $1, $NF}'|awk '{array[$2]+=$1} END {for (i in array) {printf("%-15s %s\n", sprintf("%.3f MB", array[i]/1048576), i)}}' | egrep '(MYI|ibd)'
0.143 MB MYI
37.375 MB ibd
76.000 MB ibdata1
A good value for key_buffer_size would be 4MB and for innodb_buffer_pool_size 128MB. For better values:
-MYISAM
mysql -e "show global status like '%key_read%';"
A sign of excellent key_buffer performance will be a ratio of 1:100 for key_reads:key_read_requests. If you find this ration lower than 1:100, this is an indicator that MySQL performance could be increased by raising the value of the key_buffer setting. RATIO: a:b – a/a:b/a (to get ratio relative to 100).
## Good starting points are 1GB RAM = 64M, 2GB = 128M, 4GB = 256M. Should not be set higher than 4GB. (IF THERE IS A LOT OF MYISAM DATA).
-INNODB
mysql -e "show global status like '%innodb_buffer_pool_read%';"
An indicator or good innodb_buffer_pool performance is a ration of at least 1:100 for Innodb_buffer_pool_reads:Innodb_buffer_pool_read_requests. RATIO: a:b – a/a:b/a (to get ratio relative to 100).
## Good starting points are 64M, then double this value as RAM doubles. 1GB RAM = 64M, 2GB = 128M, 4GB = 256M, etc.
– Another my.cnf changes you SHOULD make
*innodb_buffer_pool_instances
“For best efficiency, specify a combination of innodb_buffer_pool_instances and innodb_buffer_pool_size so that each buffer pool instance is at least 1GB.” 1/G of buffer_pool_size.
*table_open_cache
If you see numerous queries in a “Opening Table” or “Closing Table” state this might be raised to check run
mysqladmin proc
To check opens and open tables:
mysqladmin stat
Opens = open tables since start of service. Open table = open right now. Opens is usually really high, but can be set to increase slowly.
To correctly set table_open_cache search for number of tables on the server:
find /var/lib/mysql -name '*.frm' | wc -l
30% of that number. If after a few minutes “Opens” continues to increase rapidly, you likely need to raise the table_open_cache value further.
*open_files_limit should be set to at least 3x the number for table_open_cache.
That is basically it. Other settings are more complex and don’t represent a MAJOR improvement such as these ones.
*Enable Slow Query Log
On MySQL 5.0 and below
log-slow-queries= /var/lib/mysql/slow.log
long_query_time = 1
On MySQL 5.1 or 5.5
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
On MySQL 5.6+
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/slow.log
long_query_time = 1
Restart mysql
To check
mysql> SELECT SLEEP(2);
All set!
*tmpdir in ramdisk
If server has a lot of RAM and mysql is being an issue, you can set MySQL to use a ramdisk for tmpdir. Add in my.cnf
tmpdir=/dev/shm
Restart mysql
InnoDB Tuning
*innodb_buffer_pool_size = 70%-80% of available memory. If you have RAM bigger than your dataset setting it bit larger is the way to go.
*innodb_buffer_pool_instances = 1/G in the innodb_buffer_pool_size (this is ignored if buffer pool size is less than 1G).
*innodb_log_file_size = Usually innodb_log_file_size is 25% of innodb_buffer_pool_size
That’s all folks, see you later!