In this article, we will cover the basics of MySQL server optimization for the server plan and configure MySQL for optimal resource usage.
Variables by formula
For MySQL tuning, please open my.cnf file:
Example of some variables for VPS with 2 x 2.6 GHz CPU | 4 GB RAM | 40 GB Storage | 8 TB Bandwidth:
• query_cache_size=12.5% from 4096M=512M;
• key_buffer_size=12.5% from 4096M=512M;
• tmp_table_size=6.5% from 4096M=256M;
• max_heap_table_size=6.5% from 4096M=256M.
Example of complete MySQL optimization
Below are complete my.cnf example for VPS with 2 x 2.6 GHz CPU | 4 GB RAM | 40 GB Storage | 8 TB Bandwidth:
# Client side variables [client]
#password=mysql_root_password
port=3306
socket=/var/run/mysqld/mysqld.sock #
#Specifically for MySQL services
# MySQL server
[mysqld]
port=3306
socket=/var/run/mysqld/mysqld.sock
skip-locking
key_buffer=256M
key_buffer_size=512M
max_allowed_packet=1M
table_cache=256
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=4M
myisam_sort_buffer_size=64M
thread_cache_size=8
query_cache_size=512M
tmp_table_size=256M
max_heap_table_size=256M
# Thread concurrency depends on your CPU count.
thread_concurrency=4
# If you do not use remote connection to MySQL,
# disable this option as example below (remove #).
# skip-networking
# Using DBD? Remove #.
#bdb_cache_size=64M
#bdb_max_lock=100000
# Using InnoDB? Remove #.
#innodb_data_home_dir=/var/lib/mysql/ #innodb_data_file_path=ibdata1:10M:autoextend
#innodb_log_group_home_dir=/var/lib/mysql/
#innodb_log_arch_dir=/var/lib/mysql/
# Change session variable buffer_pool_size to 50 – 80 %
# of overall VPS memory size.
#innodb_buffer_pool_size=256M
#innodb_additional_mem_pool_size=20M
# Change session variable log_file_size to 25 % of
# buffer_pool_size size.
#innodb_log_file_size=64M
#innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_lock_wait_timeout=50
[mysqldump]
quick
max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove #, only if you know what you are doing.
# safe-updates
[isamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M
[myisamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M
[mysqlhotcopy]
interactive-timeout
After my.cnf modification, please restart the MySQL server.
If the tmp_table_size and max_heap_table_size variables do not exist in your default configuration, you can insert them manually at the end of the [mysqld] section.
Useful links