In this article, we will cover basics of MySQL server optimization. For server example, we will take VPS plan with 1 GHz CPU | 3 GB RAM | 60 GB storage | 3 TB bandwidth and configure MySQL for optimal resource usage.
Variables by formula
For MySQL tune, please open my.cnf file:
nano /etc/my.cnf
Example of some variables for VPS with 1 GHz CPU | 3 GB RAM | 60 GB storage | 3 TB bandwidth:
• query_cache_size=12.5% from 3072M=384M;
• key_buffer_size=12.5% from 3072M=384M;
• tmp_table_size=6.25% from 3072M=192M;
• max_heap_table_size=6.25% from 3072M=192M.
Example of full MySQL optimization
Below are complete my.cnf example for VPS with 1 GHz CPU | 3 GB RAM | 60 GB storage | 3 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-locking384
key_buffer=192M
key_buffer_size=384M
max_allowed_packet=1M
table_cache=192
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=384M
tmp_table_size=192M
max_heap_table_size=192M
# Thread concurrency depends on your CPU count.
hread_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=192M
#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 MySQL server:
service mysqld restart
Useful links: