Optimize MySQL server

Justin

In this article, we will cover the basics of MySQL server optimization. For server example, we will take VPS plan with 4 GHz CPU | 4 GB RAM | 50 GB storage | 4 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 4 GHz CPU | 4 GB RAM | 50 GB storage | 4 TB bandwidth:

• query_cache_size=12.5% from 4096M=512M;
• key_buffer_size=12.5% from 4096M=512M;
• tmp_table_size=6.5% from 4096M=256M;
• max_heap_table_size=6.5% from 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 my.cnf modification, please restart MySQL server:

service mysqld restart

If the tmp_table_size and max_heap_table_size variables do not exist in your default configuation, you can insert them manually at the end of [mysqld] section.

Useful links:

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

dodinih

Hi, how about optimize with low specification server

• CPU: 1 x 2.40 GHz
• RAM: 1024 MB
• HDD: 40 GB (100 IOPS)
• Bandwidth: 2 TB

that running CentOS7 could You please share the configuration for best performance

Roman

Hello,

Please launch MySQL tuner, it will help you to optimize your MySQL server.