MYSQL Optimization

– 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