As an important part of the LAMP
stack, MySQL plays the critical role of data processing in website architecture. As the most widely used open-source database today, there are already quite a large number of optimization tutorials available online. This article will try to start with improving
MySQL configuration in order to further enhance MySQL performance.
Optimizing database structure and SQL statements is outside the scope of this discussion.
I plan to divide MySQL performance optimization into three parts: first, optimization of physical hardware; second,
compile-time optimization during MySQL installation; and third, optimization of MySQL’s configuration file, my.cnf.
1. Physical Hardware Optimization
Disk I/O is one of the biggest factors limiting MySQL performance.
A server using SSDs will definitely perform better than one using ordinary HDDs; using RAID10
will definitely perform better than using a single disk.
So-called physical hardware optimization is really just about upgrading server (VPS) hardware. More memory, faster disks, and a more powerful
CPU are undoubtedly the best choices.
2. Compile-Time Optimization During MySQL Installation
Under normal circumstances, installing MySQL directly with yum is not recommended.
First, you cannot customize features, and second, the version is usually relatively old. Therefore, I generally choose to install it by compiling from source.
Prerequisites (dependency packages) for source compilation and installation:
1. CMake. Official website: http://www.cmake.org/
2. GCC, A working ANSI C++ compiler. GCC 4.2.1 or
later. Official website: http://www.gnu.org/software/gcc/
3. bison, 2.1 or newer. Official website: http://www.gnu.org/software/bison/
4. m4. Official website: http://www.gnu.org/software/m4/
5. tar. Official website: http://www.gnu.org/software/tar/
Compilation options:
MySQL 5.5.x
http://dev.mysql.com/doc/refman/5.5/en/source-configuration-options.html
MySQL 5.6.x
http://dev.mysql.com/doc/refman/5.6/en/source-configuration-options.html
The MySQL compilation parameters in the LAMP one-click installation script are as follows:
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql
-DMYSQL_UNIX_ADDR=/tmp/mysql.sock
-DDEFAULT_CHARSET=utf8
-DDEFAULT_COLLATION=utf8_general_ci
-DWITH_EXTRA_CHARSETS=complex
-DWITH_INNOBASE_STORAGE_ENGINE=1
-DWITH_READLINE=1
-DENABLED_LOCAL_INFILE=1
-DWITH_PARTITION_STORAGE_ENGINE=1
-DWITH_FEDERATED_STORAGE_ENGINE=1
-DWITH_BLACKHOLE_STORAGE_ENGINE=1
-DWITH_MYISAM_STORAGE_ENGINE=1
-DWITH_EMBEDDED_SERVER=1
Since -DWITH_DEBUG is OFF
by default, there is no need to specify this parameter separately.
3. Optimizing MySQL’s configuration file, my.cnf
Configuration files:
MySQL 5.5.x
https://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html
MySQL 5.6.x
https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html
Using the my.cnf
file from the LAMP one-click installation script, only the contents of the [mysqld] section are listed here. The contents of the other sections have very little impact on MySQL
runtime performance, so they will be ignored for now.
Here are some optimization parameters.
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-external-locking
#Avoid MySQL external locking to reduce the chance of errors and improve stability.
key_buffer_size = 16M
#Specifies the size of the buffer used for indexes. Increasing it can improve index processing performance. 16M is suitable for
512MB of memory; for servers with around 4GB of memory, this parameter can be set to 256M, and so on. Note: setting this value too high will actually reduce the server’s overall efficiency!
max_allowed_packet = 1M
#Based on this setting, MySQL will limit the size of data packets accepted by the server.
table_open_cache = 64
#Specifies the size of the table cache. Each time MySQL accesses a table, if there is still space in the table buffer, the table is opened and placed there so its contents can be accessed more quickly. Note that you should not blindly set table_open_cache to a very large value. If it is set too high, it may cause a shortage of file descriptors, resulting in unstable performance or failed connections.
64 is suitable for 512MB of memory; for 1GB of memory, it can be set to
128, and so on.
sort_buffer_size = 512K
#The size of the buffer that can be used for query sorting. Note: the memory allocated for this parameter is exclusive to each connection. If there are 100 connections, then the total actually allocated sort buffer size will be 100
× 512K ＝ 50MB.
512K is suitable for 512MB of memory; for 1GB of memory, it can be set to
1M, and so on.
net_buffer_length = 8K
#The initial size of the data packet received by the server, which can then grow as needed under the control of
max_allowed_packet.
Note: the value range for this parameter can only be 1 – 1024K.
read_buffer_size = 256K
#The size of the buffer that can be used for read query operations. Like sort_buffer_size,
the memory allocated for this parameter is also exclusive to each connection.
256K is suitable for 512MB of memory; for 1GB of memory, it can be set to
512K, and so on.
read_rnd_buffer_size = 512K
#The size of the buffer that can be used for multi-table query operations. Setting a larger value can effectively improve
ORDER BY performance. Like sort_buffer_size,
the memory allocated for this parameter is also exclusive to each connection.
512K is suitable for 512MB of memory; for 1GB of memory, it can be set to
1M, and so on.
myisam_sort_buffer_size = 8M
#The size of the buffer that can be used for MyISAM sorting.
8M is suitable for 512MB of memory; for 1GB of memory, it can be set to
16M, and so on.
max_connections = 256
#Specifies the maximum number of connection processes allowed by MySQL. If you often encounter the error message Too
Many Connections during access, then you need to increase this parameter value.
Note: the default value of this parameter is 151, and the maximum can be set to 100000
It is recommended here to set it to half of the memory size; for example, with 512MB of memory, set it to
256, and so on.
[Final thoughts]
I found that most so-called MySQL optimization advice comes from explanations in the official documentation.
Domestic tutorials are either very old or reposted everywhere, with almost no real reference value.
There is no optimal configuration file, only one that suits your own needs. So you need to adjust it according to actual conditions, such as memory size and disk
I/O status.
LAMP one-click script‘s default configuration (which is intended by default for a
VPS with
512MB of memory) is definitely not suitable for you (it is suitable for me).
And above I have only listed a few relatively important parameters; for more parameters, please refer to the official website.