Optimize MySQL server

Clara - VPS Hosting Team

In this article, we will explore the fundamentals of optimizing a MySQL server. For our example, we'll use a VPS plan with the following specs: 4 GHz CPU, 4 GB RAM, 50 GB storage, and 4 TB bandwidth. We’ll configure MySQL to maximize resource efficiency.

Variables by Formula

To tune MySQL, start by opening the my.cnf file:

nano /etc/my.cnf

Here are examples of variable settings for a VPS with a 4 GHz CPU, 4 GB RAM, 50 GB storage, and 4 TB bandwidth:

query_cache_size=12.5% of 4096M = 512M
key_buffer_size=12.5% of 4096M = 512M
tmp_table_size=6.5% of 4096M = 256M
max_heap_table_size=6.5% of 4096M = 256M

Example of Full MySQL Optimization

Below is a complete my.cnf configuration example for a VPS with a 4 GHz CPU, 4 GB RAM, 50 GB storage, and 4 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 connections to MySQL, 
# disable this option as shown 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/

# Set buffer_pool_size to 50 – 80% of overall VPS memory size.
# innodb_buffer_pool_size=256M
# innodb_additional_mem_pool_size=20M

# Set log_file_size to 25% of buffer_pool_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're 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 modifying my.cnf, restart the MySQL server with:

service mysqld restart

If tmp_table_size and max_heap_table_size are not present in your default configuration, add them manually at the end of the [mysqld] section.

Useful Links