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:

MySQL Tuner
MySQL documentation
MySQL memory calculator
my.cnf file analyzer