Optimize MySQL server

JustinJustin Moderator
edited March 2016 in Performance and Security
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:

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

Comments

  • dodinihdodinih Member
    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
  • RomanRoman Moderator
    Hello,

    Please launch MySQL tuner, it will help you to optimize your MySQL server.
Sign In or Register to comment.