frame

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Optimize MySQL server

JustinJustin Member
edited February 2020 in Performance and Security
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

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 Member
    Hello,

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

Time4VPS

Learn how to install a web and database server, email, FTP client or other applications. Discover and share information on server security or optimization recommendations.
Feel free to join our constantly expanding community, participate in discussions, strengthen your knowledge on Linux and Windows server management!
© 2013 - 2024 Time4VPS. All rights reserved.

Get In Touch