MySQL performance optimization

Company website access is increasing, MySQL database server pressure is getting bigger, the first step naturally thought is the MySQL system parameter optimization, we can not expect MySQL default system parameters can make MySQL run very smoothly. In the Apache (Nginx), PHP, MySQL architecture, MySQL has the greatest impact on performance and is a key core part. MySQL is also the most difficult part of optimization, not only need to understand some of the MySQL expertise, but also need a long time to observe the statistics and based on experience to judge, and then set reasonable parameters (sometimes set too large does not get a better effect, but it will also affect the performance of MySQL). Below we understand some of the MySQL optimization recommendations, single-machine MySQL optimization I divided into three parts, one is the optimization of the server physical hardware, the second is the MySQL installation of the compilation optimization, the third is the optimization of its own configuration file my.cnf; If the optimization of a single machine can not solve the pressure of your database, This is the only time to consider a cluster architecture approach, which I'll focus on later.

optimization of single-machine MySQL database
First, the impact of server hardware on MySQL performance
① disk seek capability (disk I/O), we are now on the SAS15000 drive. MySQL every second in a large number of complex query operations, the amount of read and write disk can be imagined. Therefore, the disk I/O is generally considered to be one of the biggest constraints on MySQL performance, for the average daily access to more than 1 million PV discuz! forum, due to disk I/O constraints, MySQL performance will be very low! To address this constraint, consider the following solutions: Using the raid1+0 disk array, it is important not to try to use Raid-5,mysql on the RAID-5 disk array as quickly as you might expect.
②CPU for MySQL applications, it is recommended to use Dell r710,e5620 @2.40ghz (4 Core) * 2, I now prefer the Dell R710, also used as a LINUXAKG virtualization application;
③ physical memory for a database server using MySQL, the server memory is recommended not less than 2GB, recommended to use more than 4GB of physical memory, but memory for the current server can be said to be a negligible problem, At work encountered high-end servers basically more than 32G of memory.
The database servers we work with are HP Dl580g5 and Dell R710, and the stability and performance are good, especially with Dell R710, I find many of my peers are servers that use it as a database, so it's recommended.

Second, the installation of MySQL online I recommend the method of compiling the installation, so that there is a greater performance improvement, Server system I suggest using 64bit Centos5.5, the source code package compiler parameters will default in DEBGU mode to generate binary code, and debug mode to MySQL performance loss is relatively large, so when we compile ready to install the product code, we must not forget to use the "- Without-debug "parameter disables debug mode. If you set the-with-mysqld-ldflags and-with-client-ldflags two compilation parameters to-all-static, you can tell the compiler to compile and compile the result code statically to get the highest performance. The performance gap can be as much as 5% to 10% by using static compilation and using dynamically compiled code. I refer to Mr. Jenzhiang's compilation parameters, which are listed below for your reference, and the compilation parameters are as follows:

1

/configure --prefix=/usr/local/mysql --without-debug--without-bench--enable-thread-safe-client-- Enable-assembler--enable-profiling--with-mysqld-ldflags=-all-static--with-client-ldflags=-all-static-- With-charset=latin1--WITH-EXTRA-CHARSET=UTF8,GBK--with-innodb--with-csv-storage-engine-- With-federated-storage-engine--with-mysqld-user=mysql--without-embedded-server--with-server-suffix=-community- -with-unix-socket-path=/usr/local/mysql/sock/mysql.sock

third, MySQL self-factors when the above server hardware constraints are resolved, let's look at how MySQL's own optimizations are being manipulated. The optimization of MySQL itself is mainly to optimize and adjust the parameters in the configuration file MY.CNF. Let's introduce some parameters that have a greater impact on performance.

Below, we describe the above hardware configuration in conjunction with an optimized MY.CNF:
#vim/etc/my.cnf

The following lists only the contents of the [MYSQLD] paragraph in the my.cnf file, while the rest of the paragraphs have little impact on MySQL performance, so ignore them.
[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
Skip-locking
#避免MySQL的外部锁定, reducing the chance of error increases stability.
Skip-name-resolve
#禁止MySQL对外部连接进行DNS解析, this option can be used to eliminate the time for DNS resolution for MySQL. However, it is important to note that if this option is turned on, all remote host connection authorizations will use IP address mode, otherwise MySQL will not be able to handle the connection request properly!
Back_log = 384
#back_log参数的值指出在MySQL暂时停止响应新请求之前的短时间内多少个请求可以被存在堆栈中. If the system has many connections in a short period of time, you need to increase the value of this parameter, which specifies the size of the listening queue for incoming TCP/IP connections. Different operating systems have its own limitations on this queue size. Attempting to set a limit of back_log above your operating system will be invalid. The default value is 50. For Linux systems, the recommended setting is an integer less than 512.
Key_buffer_size = 384M
#key_buffer_size指定用于索引的缓冲区大小, increase it to get better index processing performance. The parameter can be set to 256M or 384M for a server that has around 4GB. Note: This parameter value setting is too large to be the overall efficiency of the server down!
Max_allowed_packet = 4M
Thread_stack = 256K
Table_cache = 614K
Sort_buffer_size = 6M
#查询排序时所能使用的缓冲区大小. Note: The allocated memory for this parameter is per-connection exclusive, and if there are 100 connections, then the actual allocated total sort buffer size is 100x6 = 600MB. Therefore, the recommended setting for a server that has around 4GB is 6-8m.
Read_buffer_size = 4M
#读查询操作所能使用的缓冲区大小. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.
Join_buffer_size = 8M
#联合查询操作所能使用的缓冲区大小, like Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection.
Myisam_sort_buffer_size = 64M
Table_cache = 512
Thread_cache_size = 64
Query_cache_size = 64M
#指定MySQL查询缓冲区的大小. It can be observed in the MySQL console that if the value of the qcache_lowmem_prunes is very large, there is often insufficient buffering, and if the value of qcache_hits is very large, the query buffer is used very frequently, and if the value is small it can affect efficiency. Then consider not querying the buffer; qcache_free_blocks, if the value is very large, it indicates that there is a lot of fragmentation in the buffer.
Tmp_table_size = 256M
Max_connections = 768
#指定MySQL允许的最大连接进程数. If the too many connections error is frequently encountered when accessing the forum, you need to increase the parameter value.
max_connect_errors = 1000
Wait_timeout = 10
#指定一个请求的最大连接时间, the server with about 4GB of memory can be set to 5-10.
Thread_concurrency = 8
#该参数取值为服务器逻辑CPU数量 * *, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4*2=8; This is currently a dual quad core server configuration.
Skip-networking
#开启该选项可以彻底关闭MySQL的TCP/IP connection, do not turn on this option if the Web server accesses the MySQL database server as a remote connection! Otherwise it will not connect properly!
table_cache=1024
#物理内存越大, the larger the setting. Default is 2402, set to 512-1024 best
innodb_additional_mem_pool_size=4m
#默认为2M
Innodb_flush_log_at_trx_commit=1
#设置为0就是等到innodb_log_buffer_size列队满后再统一储存, default is 1
Innodb_log_buffer_size=2m
#默认为1M
Innodb_thread_concurrency=8
#你的服务器CPU有几个就设置为几, the recommended default is typically 8
key_buffer_size=256m
#默认为218, tune to 128 best
tmp_table_size=64m
#默认为16M, tune to 64-256 most hanging
read_buffer_size=4m
#默认为64K
read_rnd_buffer_size=16m
#默认为256K
sort_buffer_size=32m
#默认为256K
thread_cache_size=120
#默认为60
query_cache_size=32m

※ It is worth noting that:
Many situations require specific analysis of specific circumstances
First, if the key_reads is too large, it should be my.cnf in the key_buffer_size to become larger, keep key_reads/key_read_requests at least 1/100, the smaller the better.
Second, if the qcache_lowmem_prunes is large, it is necessary to increase the value of query_cache_size.

Many times we find that performance improvements through parameter settings may not be as qualitative a leap as many might imagine, unless there is a serious unreasonable situation with the previous setup. We cannot rely entirely on tuning the parameters of the DBA after the database is online, but should minimize performance issues during the system design and development phases.

" 51CTO Exclusive Feature "If the single MySQL optimization is still not up to the pressure, this time we have to consider MySQL's high-availability architecture (many students also love to say that the MySQL cluster), the current feasible scheme is:
first, MySQL Cluster
Advantage: Very high availability and excellent performance. Each piece of data can have at least one copy stored on different hosts, and redundant copies of the data are synchronized in real time. But its maintenance is very complex, there are some bugs, is not suitable for comparison of the core of the online system, so I do not recommend this.
Second, DRBD Disk network mirroring scheme
Benefits: Software is powerful, data can be mirrored across physical hosts at the underlying fast device level, and different levels of synchronization can be configured based on performance and reliability requirements. IO operations are kept in order to meet the database's demanding data consistency. However, the non-Distributed file system environment cannot support the mirrored data, and the performance and reliability are contradictory, which is not suitable for the environment with the more demanding performance and reliability requirements, and the maintenance cost is higher than MySQL Replication. In addition, DRBD is officially recommended as one of the highly available MySQL scenarios, so you can consider whether or not to deploy according to the actual environment.
Third, MySQL Replication
In practical scenarios, MySQL Replication is the most widely used design tool to improve system extensibility. Many MySQL users through the replication function to enhance the expansion of the system, through the simple increase in inexpensive hardware equipment multiplied even to improve the performance of the original system, is the majority of MySQL low-end users like one of the features, It is also the most important reason for many MySQL users to choose MySQL.

there are several common MySQL replication architectures, which are explained in a nutshell
MySQL replication Schema One: The General replication Architecture--master-slaves, is a Master replication to one or more salve schema mode, mainly for the application of large-scale reading pressure solution, read-write separation, Master is primarily responsible for writing stress.
MySQL replication Schema Two: Cascade replication architecture, that is, master-slaves-slaves, this is to prevent slaves reading pressure is too large, and the configuration of a level two slaves, It is easy to solve the master side because of the slave too much to become the risk of bottle strength.
MySQL Replication Architecture III: Dual Master and Cascade replication in conjunction with the architecture, that is, master-master-slaves, the greatest benefit is to avoid the primary master's write operations are affected by the replication of slave cluster, It also guarantees a single point of failure for the Master master.
The above is the more common MySQL replication architecture scheme, we can according to their own company's specific environment to design, Mysql load balancer can consider using LVS or haproxy to do, ha software high availability I recommend heartbeat.
The lack of MySQL replication: If master host hardware failure cannot be recovered, it may result in some data loss that is not delivered to the slave end. So everyone should be based on their current network planning, choose their own reasonable MySQL schema, with their MySQL DBA and programmer multi-ditch, multi-backup (backup I will at least do local and offsite dual backup), multi-test, data is the biggest thing, not a bit of error, remember to remember.

MySQL performance optimization