Sun

Optimize MySQL server

  1. Knowledge Base
  2. Hosting
  3. VPS Hosting
  4. VPS Performance And Security
  5. Optimize MySQL server

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

Was this article helpful?

Related Articles