In this article, we will cover the basics of MySQL server optimization using a VPS plan with 4 GHz CPU, 4 GB RAM, 50 GB storage, and 4 TB bandwidth as an example. To optimize MySQL, you should adjust variables in the my.cnf file:
Open the file with:
nano /etc/my.cnf
Here are example configurations for a VPS with 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 are complete my.cnf example for VPS with 4 GHz CPU | 4 GB RAM | 50 GB storage | 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 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 modifying the my.cnf file, 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:
