MySQL database optimization (on) Single-host MySQL database Optimization

Source: Internet
Author: User

BKJIA exclusive Article]The company's website traffic is getting bigger and bigger, and the pressure on MySQL is getting bigger and bigger. The first step is to naturally consider the optimization of MySQL system parameters, we cannot expect the default system parameters of MySQL to make MySQL run smoothly. In the architecture of apacheng.pdf), PHP, and MySQL, MySQL has the greatest impact on performance and is also a key core part. MySQL is also the most difficult part of optimization. It not only needs to understand some MySQL professional knowledge, but also requires a long period of observation statistics and judgment based on experience, and then set reasonable parameters.

Next, let's take a look at some suggestions for MySQL optimization. Single-host MySQL optimization is divided into three parts: one is the optimization of server physical hardware, and the other is the compilation optimization during MySQL installation, third, my. cnf optimization. If the optimization of a single machine cannot solve the pressure on your database, we only need to consider using the cluster architecture. This will be highlighted later.

Optimization of standalone MySQL database

I. Impact of server hardware on MySQL Performance

Ii. Online installation of MySQL

I recommend that you use the compile and install method to improve the performance. For the server system, I suggest using 64-bit Centos5.5. The compilation parameters of the source code package will generate binary code in Debgu mode by default, however, the Debug mode causes a high performance loss for MySQL. Therefore, when compiling and installing the product code, do not forget to use it."-Without-debugThe Debug mode is disabled.

However-With-mysqld-ldflagsAnd-With-client-ldflagsSet the two compilation parameters-All-staticIt tells the compiler to compile and compile the result code in static mode to achieve the highest performance. Compared with code that uses static compilation and dynamic compilation, the performance gap may reach 5% to 10%. I have referred to the compilation parameters of Mr. Jian Chaoyang. The special columns are as follows for your reference.

 
 
  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,  
  2. 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    

After solving the preceding server hardware constraints, let's see how MySQL's optimization works. The optimization of MySQL is mainly to optimize and adjust the parameters in its configuration file my. cnf. The following describes some parameters that have a great impact on performance.

Next, we will describe the optimized my. cnf based on the above hardware configuration:

# Vim/etc/my. cnf only lists the content in the [mysqld] section in the my. cnf file. The content in other sections has little impact on MySQL running performance, so ignore it.

 
 
  1. [mysqld]    
  2. port = 3306    
  3. serverid = 1     
  4. socket = /tmp/mysql.sock       


 

 
 
  1. skip-locking 

# Avoid external locks of MySQL to reduce the chance of errors and enhance stability.

 
 
  1. skip-name-resolve 

# Prohibit MySQL from performing DNS resolution on external connections. Using this option can eliminate the time for MySQL to perform DNS resolution. However, if this option is enabled, IP addresses are required for all remote host connection authorizations. Otherwise, MySQL cannot process connection requests normally!

 
 
  1. back_log = 384 

# The value of the back_log parameter indicates how many requests can be stored in the stack within a short time before MySQL temporarily stops responding to a new request. 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 listener queue for the incoming TCP/IP connection. Different operating systems have their own limits on the queue size. Trying to set back_log to be higher than your operating system limit will be invalid. The default value is 50. We recommend that you set the value to an integer smaller than 512 in Linux.

 
 
  1. key_buffer_size = 384M 

# Key_buffer_size specifies the buffer size used for the index. Increasing the size can improve the index processing performance. This parameter can be set to 384 M or M for servers with around 4 GB of memory. Note: If this parameter value is set too large, the overall efficiency of the server will be reduced!

 
 
  1. max_allowed_packet = 4M  
  2. thread_stack = 256K  
  3. table_cache = 614K  
  4. sort_buffer_size = 6M 

# The buffer size that can be used for sorting. Note: The allocated memory for this parameter is exclusive to each connection. If there are 100 connections, the total size of the actually allocated sort buffer is 100 × 6 = 600 MB. Therefore, we recommend that you set the size of a server with around 4 GB to 6-8 Mb.

 
 
  1. read_buffer_size = 4M 

# The buffer size that can be used by the read query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is also exclusive to each connection.

 
 
  1. join_buffer_size = 8M 

# The size of the buffer that can be used by the Joint query operation. Like sort_buffer_size, the allocated memory corresponding to this parameter is exclusive to each connection.

 
 
  1. myisam_sort_buffer_size = 64M  
  2. table_cache = 512  
  3. thread_cache_size = 64  
  4. query_cache_size = 64M 

# Specify the size of the MySQL Query Buffer. You can observe on the MySQL console that if the Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient. If the Qcache_hits value is very large, it indicates that the query buffer is frequently used, if this value is small, it will affect the efficiency, you can consider not to query the buffer; Qcache_free_blocks, if this value is very large, it indicates that there are many fragments in the buffer.

 
 
  1. tmp_table_size = 256M  
  2. max_connections = 768 

# Specify the maximum number of connection processes allowed by MySQL. If the Too connector Connections error is frequently reported during Forum access, you need to increase the value of this parameter.

 
 
  1. max_connect_errors = 1000  
  2. wait_timeout = 10 

# Specify the maximum connection time of a request. For servers with around 4 GB of memory, you can set it to 5-10.

 
 
  1. thread_concurrency = 8 

# The value of this parameter is the number of logical CPUs of the server x 2. In this example, the server has two physical CPUs, and each physical CPU supports H.T hyper-threading, therefore, the actual value is 4*2 = 8. This is also the mainstream dual-quad-core server configuration.

 
 
  1. skip-networking 

# Enabling this option can completely disable the MySQL TCP/IP connection mode. If the WEB server accesses the MySQL database server remotely, do not enable this option! Otherwise, the connection will fail!

 
 
  1. table_cache=1024    

# The larger the physical memory is, the larger the setting will be. The default value is 2402, and the optimal value is adjusted.

 
 
  1. innodb_additional_mem_pool_size=4M 

# The default value is 2 MB.

 
 
  1. innodb_flush_log_at_trx_commit=1 

# If it is set to 0, innodb_log_buffer_size is stored after the queue is full. The default value is 1.

 
 
  1. innodb_log_buffer_size=2M     

# The default value is 1 MB.

 
 
  1. innodb_thread_concurrency=8       

# Set the number of CPUs on your server to 8 by default.

 
 
  1. key_buffer_size=256M    

# The default value is 218. The optimal value is 128.

 
 
  1. tmp_table_size=64M 

# The default value is 16 MB, and the maximum value is adjusted to 64-256.

 
 
  1. read_buffer_size=4M 

# The default value is 64 KB.

 
 
  1. read_rnd_buffer_size=16M 

#256 kb by default

 
 
  1. sort_buffer_size=32M 

#256 kb by default

 
 
  1. thread_cache_size=120 

# The default value is 60.

 
 
  1. query_cache_size=32M  

※Note:

In many cases, specific analysis is required.

1. If Key_reads is too large, increase Key_buffer_size in my. cnf to keep Key_reads/Key_read_requests above 1/100. The smaller the value, the better.

2. If Qcache_lowmem_prunes is large, you need to increase the value of Query_cache_size.

Many times we find that the performance improvement brought about by optimizing performance through parameter settings may not be as great as many people think, unless there is a serious unreasonable situation in the previous settings. We cannot optimize the performance by relying solely on the parameter adjustment made by DBA after the database goes online. Instead, we should minimize the performance issue during system design and development.

The above is the optimization method proposed by the author for the standalone MySQL database. If the optimization of a single MySQL is still under pressure, we must consider the MySQL cluster solution at this time, how should we optimize it? Please look forward to the next articleMySQLHigh Availability design scheme.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.