Optimize MySQL server


In this article, we will cover 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

Useful links:

Was this answer helpful?

 Print this Article

Also Read

Install Plesk

Use the One-Click Installer utility to perform a clean installation of the Plesk Panel in one...

Manage Storage Server

All Storage Servers is activated with  pre-installed special OS template - "Filer (CentOS 6,...

Install cPanel/WHM

cPanel/WHM installation is easy and straightforward. Login to the client area; At the top menu...

Execute cPanel scripts from SSH

Here are the scripts provided by cPanel that you can use to automate, configure, fix some issues...

Install rsync for transferring files

Use any one of the following commands to install rsync. If you are using Debian or Ubuntu Linux,...