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_passwordport=3306socket=/var/run/mysqld/mysqld.sock# Specifically for MySQL services# MySQL server[mysqld]port=3306socket=/var/run/mysqld/mysqld.sockskip-locking384key_buffer=192Mkey_buffer_size=384Mmax_allowed_packet=1Mtable_cache=192sort_buffer_size=1Mread_buffer_size=1Mread_rnd_buffer_size=4Mmyisam_sort_buffer_size=64Mthread_cache_size=8query_cache_size=384Mtmp_table_size=192Mmax_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]quickmax_allowed_packet=16M[mysql]no-auto-rehash# Remove #, only if you know what you are doing.# safe-updates[isamchk]key_buffer=128Msort_buffer_size=128Mread_buffer=2Mwrite_buffer=2M[myisamchk]key_buffer=128Msort_buffer_size=128Mread_buffer=2Mwrite_buffer=2M[mysqlhotcopy]interactive-timeout
After my.cnf modification, please restart MySQL server:
service mysqld restart
Useful links: