MySQL Configuration and performance optimizations

MySQL configuration file my.cnf A detailed explanation of Chinese, attached MySQL performance Optimization method sharing

=================================================================================================

MySQL parameter optimization for beginners, is more difficult to understand things, in fact, this parameter optimization, is a very complex thing, for different sites, and its online volume, access, number of posts, network conditions, as well as the machine hardware configuration has a relationship, optimization can not be done at once, need to constantly observe and debug, The best results are possible.

Let's talk about my server hardware and the forum,
Cpu:2 Quad Core Intelxeon 2.00GHz
Memory: 4GB DDR
HDD: SCSI 146GB
Forum: Online members are generally around 5000 people – the highest record is 13264.
Below, we are based on the above hardware configuration combined with a MY.CNF has been optimized to analyze the explanation: Some parameters may also be based on the changes in the forum and the Programmer's program to re-adjust.
[Mysqld]
Port = 3306
ServerID = 1
Socket =/tmp/mysql.sock
skip-locking # avoid MySQL external lock, reduce the chance of error increase stability.

Skip-name-resolve

Disable MySQL for DNS resolution of external connections, and Use this option 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 =
The number of connections required for MySQL to be available. When the primary MySQL thread gets very many connection requests in a very short time, this works, and then the main thread takes some time (albeit very short) to check the connection and start a new thread.
The Back_log value indicates how many requests can be present in the stack for a short period of time before MySQL temporarily stops answering a new request. Only if you expect to have a lot of connections in a short period of time, you need to increase it, in other words, the size of the listening queue for incoming TCP/IP connections. Your operating system has its own limits on this queue size. Attempting to set a limit of back_log above your operating system will be invalid. When you look at your host process list, you find a lot of 264084 | Unauthenticated User |xxx.xxx.xxx.xxx | NULL | Connect | NULL | Login | NULL to connect the process, it is necessary to increase the value of Back_log. The default value is 50, I change it to 500.
key_buffer_size= 384M
# KEY_BUFFER_SIZE Specifies the buffer size to be used for the index, increasing the index (for all reads and multiple writes) that can be better processed, to the extent that you can afford it. If you make it too big, the system will start to change pages and really become slow. The parameter can be set to 384M or 512M for a server that has around 4GB. By checking the status values key_read_requests and Key_reads, you can see if the key_buffer_size settings are reasonable. The proportional key_reads/key_read_requests should be as low as possible, at least 1:100,1:1000 better (the above status values can be obtained using the show status like ' key_read% '). Note: This parameter value setting is too large to be the overall efficiency of the server down!
Max_allowed_packet= 32M
It is safe to increase the value of this variable because additional memory is allocated only when needed. For example, MYSQLD will allocate more memory only if you issue a long query or if mysqld must return a large result row. This variable takes a smaller default value as a precaution to capture the error message packets between the client and the server and to ensure that memory overflows are not caused by accidental use of large packets.
Table_cache=512 (after 5.1 is called Table_open_cache)
Table_cache Specifies the size of the table cache. Whenever MySQL accesses a table, if there is room in the table buffer, the table is opened and put into it, which allows for faster access to the table contents. By checking the status values of peak time open_tables and Opened_tables, you can determine whether you need to increase the value of Table_cache. If you find that open_tables equals Table_cache, and opened_tables is growing, you need to increase the value of Table_cache (the above status values can use Show status like ' Open%tables ' obtained). Note that you cannot blindly set the Table_cache to a very large value. If set too high, it may cause insufficient file descriptors, resulting in performance instability or connection failures.
sort_buffer_size= 4M
The size of the buffer that can be used when the query is sorted. Note: This parameter corresponds to the allocation of memory per connection exclusive! If there are 100 connections, the actual allocated total sort buffer size is 100x4 = 400MB. Therefore, the recommended setting for a server that has around 4GB is 4-8m.
read_buffer_size= 4M
The size of the buffer that can be used by the read query operation. As with Sort_buffer_size, the allocated memory for this parameter is exclusive to each connection!
join_buffer_size= 8M
The size of the buffer that the Federated query operation can use, like sort_buffer_size, which allocates memory for each connection alone!
myisam_sort_buffer_size= 64M
MyISAM the buffer required for reordering when the table is changed
query_cache_size= 64M
Specifies the size of the MySQL query buffer. You can observe this by executing the following commands in the MySQL console:
# > Showvariables like '%query_cache% '; # > SHOW STATUS like ' qcache% '; # If the value of the qcache_lowmem_prunes is very large, it indicates that there is often insufficient buffering;
If the value of qcache_hits is very large, it indicates that the query buffer is used very frequently, and if the value is smaller it will affect efficiency, then you can 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.
thread_cache_size= 64
The number of threads that can be reused for saving in. If there is, a new thread is obtained from the cache, and if there is space when disconnected, the customer's line is placed in the cache. If there are many new threads, this variable value can be improved in order to improve performance. By comparing the variables of the Connections and threads_created states, you can see the effect of this variable
tmp_table_size=256m
max_connections= 1000
Specifies the maximum number of connection processes allowed by MySQL. If the too many connections error is frequently encountered when accessing the forum, you need to increase the parameter value.
max_connect_errors= 10000000
For the same host, if there is an interrupt error connection that exceeds the number of values for this parameter, the host will be blocked from connecting. If you need to unblock the host, execute: FLUSH host;.
Wait_timeout=10
Specifies the maximum connection time for a request, which can be set to 5-10 for a server with about 4GB of memory.
thread_concurrency=8
The parameter value is the number of server logical CPUs x2, in this case, the server has 2 physical CPUs, and each physical CPU supports H.T Hyper-threading, so the actual value is 4x2 = 8
skip-networking
Turn on this option to completely turn off MySQL's TCP/IP connection, and do not turn on this option if the Web server is accessing the MySQL database server remotely. Otherwise it will not connect properly!
Long_query_time= 10
log-slow-queries=
log-queries-not-using-indexes
Turn on slow query log (slow query log)
The slow query log is useful for tracking problematic queries. It records all queries that have exceeded long_query_time and, if necessary, records that do not use the index. Here is an example of a slow query log:
To open the slow query log, you need to set parameters log_slow_queries, Long_query_times, log-queries-not-using-indexes.
log_slow_queries Specifies the log file, and if the file name is not provided, MySQL will generate its own default file name. LONG_QUERY_TIMES Specifies the threshold for a slow query, which is 10 seconds by default. Log-queries-not-using-indexes is a parameter introduced after 4.1.0, which indicates that a query that does not use an index is logged. Set long_query_time=10
External attach use the show status command to view MySQL status-related values and their meanings:
UseShow StatusCommand
The meaning is as follows:
Aborted_clients Client Illegal interrupt connection count
Aborted_connects number of MySQL failures connected
com_xxx xxx Command execution times, there are many bars
Connections number of connections to MySQL
Created_tmp_disk_tables temporary tables created on disk
Created_tmp_tables temporary tables created in memory
Created_tmp_files number of temporary files
Key_read_requeststhe number of requests to read a key block from the cache
Key_reads thenumber of physical reads of a key block from disk
Max_used_connections the number of simultaneous connections used
Open_tables Open Table
Open_files Open files
Opened_tables Open a table
Questions the number of queries submitted to the server
Sort_merge_passes If this value is large, you should increase the Sort_buffer value in the MY.CNF
Number of seconds that the Uptime server has been working
recommendations for improving performance:
1. IfOpened_tablesIt's too big to make the Table_cache in the my.cnf bigger.

2. If the key_reads is too large, the my.cnf should be key_buffer_size larger. The cache failure rate can be calculated with key_reads/key_read_requests

3. If the handler_read_rnd is too large, then you write a lot of SQL statement query is to scan the entire table, and do not play the role of the index key

4. If the threads_created is too large, increase the value of thread_cache_size in the my.cnf. Cache hit rate can be calculated with Threads_created/connections

5. If the created_tmp_disk_tables is too large, increase the value of tmp_table_size in MY.CNF and replace the disk-based temporary table with a memory-based

==========================================================================================================
What is the storage engine? The data in MySQL is stored in a file with a variety of different technologies (or the correct compilation method is important, but it is only part of the effort to improve the performance of the MySQL server. Many of the parameters of the MySQL server affect the performance of the server, and we can save these parameters to the configuration file, so that each time the MySQL server starts, these parameters are automatically functioning. This configuration file is my.cnf.
MySQL server provides several examples of my.cnf files, which can be found under the/usr/local/mysql/share/mysql/directory, with names my-small.cnf, MY-MEDIUM.CNF, My-large.cnf and MY-HUGE.CNF. A description of the size in the file name describes the system type that the profile applies to. For example, if you run a MySQL server with little system memory and MySQL is only occasionally used, then using the MY-SMALL.CNF configuration file is ideal, and this profile tells Mysqld Daemon to use minimal system resources. Conversely, if the MySQL server is used to support a large-scale online store, the system has 2G of memory, then the use of MYSQL-HUGE.CNF is the most appropriate.
To use the example configuration file above, we should first copy a configuration file that is best suited to the requirement and name it my.cnf. This copy of the configuration file can be used in the following three ways:
Global: Copy this my.cnf file to the server's/etc directory, where the parameters defined in the file are globally valid, which is valid for all MySQL database servers running on that server.
Local: Copying this my.cnf file to [mysql-install-dir]/var/] will make the file valid only for the specified server, where [Mysql-install-dir] represents the directory where MYSQL is installed.
User: Finally, we can also limit the scope of the file to the specified user, which simply copies the my.cnf file to the user's root directory.
So, how do I set the parameters in the my.cnf file? Or further, what are the parameters that we can set? All of these parameters have a global impact on the MySQL server, but at the same time each parameter is closely related to the specific part of MySQL. For example, the max_connections parameter belongs to the Mysqld class. So how do you know that? This simply executes the following command:

% >/usr/local/mysql/libexec/mysqld–help
The command displays various options and parameters related to mysqld. It is convenient to look for these parameters, as they are behind the "Possiblevariables for Option–set-variable (-O) is" line. Once these parameters are found, we can set all these parameters in the My.cnf file as follows:

Set-variable = max_connections=100

The effect of this line of code is that the maximum number of connections to the MySQL server at the same time is limited to 100. Do not forget to add a set-variable directive in the my.cnf file [mysqld] section, see the example in the configuration file.

The mysql max_connections parameter is used to set the maximum number of connections (users). Each user connected to MySQL counts as a connection, and the default value for Max_connections is 100. This article explains the detailed effects and performance implications of this parameter.

[Max_connections]

=================================================================================================

MySQL will keep a connection for administrator (SUPER) login In any case, for the administrator to connect to the database for maintenance operations, even if the current number of connections has reached max_connections. So the actual maximum number of connections MySQL is max_connections+1;
The maximum value of this parameter actually works (the actual maximum number of connections) is 16384, that is, the maximum value of the parameter cannot exceed 16384, even if it is more than 16384;
Increase the value of the Max_connections parameter without consuming too much system resources. The utilization of system resources (CPU, memory) mainly depends on the density and efficiency of the query;
The most obvious feature that the parameter is set too small is the "Too many connections" error;

Let's look at the value of how to view the current MySQL max_connections:

The following SQL

Copy CodeThe code is as follows:
Show variables like "max_connections";

The results are shown in the following format

+-----------------+-------+
| variable_name | Value |
+-----------------+-------+
| max_connections | 100 |
+-----------------+-------+

You can set the value of Max_connections to 200 by using the following SQL statement, if the currently logged on user has sufficient permissions:

Set global max_connections = 200;

This setting will take effect immediately, but this setting will fail when MySQL restarts, a better way is to modify the MySQL ini configuration file My.ini

Locate the mysqld block, and modify or add the following settings:

max_connections=200

After this modification, the configuration will be loaded by default even if you restart MySQL.

However, in order to secure the period, we recommend that we go directly to the My.ini to modify, you can add.

Adjust the value of the Max_connections parameter

There are several ways to adjust this parameter, either at compile time or in MySQL configuration file my.cnf, or directly with the command adjustment and immediate effect.

1. Set the default maximum number of connections at compile time

Open the MySQL source code, enter the SQL directory, modify the mysqld.cc file:

Copy CodeThe code is as follows:
{"Max_connections", Opt_max_connections,
"The number of simultaneous clients allowed.", (gptr*) &max_connections,
(gptr*) &max_connections, 0, Get_ulong, Required_arg, 100, 1, 16384, 0, 1,
0},

The red "100″" is the default value for the parameter, modified to the desired value, and the disk exits. And then execute

Copy CodeThe code is as follows:
./configure;make;make Install

Recompile install MySQL; Note that this is best done prior to installing MySQL due to the installation and modification of MySQL source code;

2. Set the value of max_connections in configuration file my.cnf

Open MySQL configuration file my.cnf

Copy CodeThe code is as follows:
[Email protected] ~]# VI/ETC/MY.CNF

Find the Max_connections line, modify it to (if not, add it yourself),

Copy CodeThe code is as follows:
max_connections = 1000

The 1000 above is the value of the parameter.

3, real-time (temporary) modify the value of this parameter

First log in to MySQL and execute the following command:

Copy CodeThe code is as follows:
[Email protected] ~]# mysql-uroot-p

Then enter the password for MySQL root.

To view the current max_connections parameter values:

Copy CodeThe code is as follows:
Mysql> SELECT @ @MAX_CONNECTIONS as ' MAX CONNECTIONS ';

Set the value of this parameter:

Copy CodeThe code is as follows:
Mysql> set GLOBAL max_connections=1000;

(Note the upper case of the command)

After the modification is complete, it takes effect in real time without restarting MySQL.

Overall, this parameter should be set as large as possible when the server resources are sufficient to meet the needs of multiple client simultaneous connections. Otherwise, there will be an error like "Too many connections".

"Thread_cache"

=================================================================================================

MySQL inside in order to improve the performance of client requests to create the connection process, a connection pool is provided, which is the Thread_cache pool, where idle connection threads are placed in the connection pool rather than immediately destroyed. The advantage is that when there is a new request, MySQL does not immediately create the connection Thread, but first go to Thread_cache to find the idle connection thread, and if it exists, it is used directly and does not exist to create a new connection thread.

There are several important parameters about Thread_cache in MySQL, briefly described below:

Thread_cache_size

The maximum number of connection threads that are stored in the Thread_cache. Thread_cache is very effective in short-connected applications because the connection and creation of databases in an application is very frequent, and the resources that are consumed if you do not use Thread_cache are very impressive! Although the improvement in the long connection is not as obvious as the short link, the benefits are obvious. But it's not as big as it is. The certainty of wasting resources is generally considered to be related to physical memory, as follows:

Copy CodeThe code is as follows:
1g-> 8
2g-> 16
3g-> 32
>3G-> 64

If you have more short connections, you can increase them appropriately.

Thread_stack

When each connection is created, MySQL allocates the memory to it. This value is generally considered to be applied to most scenarios by default, unless it is necessary to do so.

Thread_handing

Use Thread_cache to handle connections, 5.1.19 add new features. There are two values to choose [No-threads|one-thread-per-connection] look at the literal meaning we should guess eight or nine points, hehe, no-threads The server uses one thread, and the One-thread-per-connection server uses one thread for each client request. As mentioned in the original manual, No-threads is a downgrade of the Linux trial.

Copy CodeThe code is as follows:
mysql> Show variables like ' thread% ';
+ ——————-+ ————————— +
| variable_name | Value |
+ ——————-+ ————————— +
| Thread_cache_size | 32 |
| thread_handling | one-thread-per-connection |
| Thread_stack | 196608 |
+ ——————-+ ————————— +
3 rows in Set (0.01 sec)

Mysql> Show status like '%connections% ';
+ ———————-+--–+
| variable_name | Value |
+ ———————-+--–+
| Connections | 199156 |
| max_used_connections | 31 |
+ ———————-+--–+
2 rows in Set (0.00 sec)

Mysql> Show status like '%thread% ';
+ ———————— +--–+
| variable_name | Value |
+ ———————— +--–+
| Delayed_insert_threads | 0 |
| Slow_launch_threads | 0 |
| threads_cached | 3 |
| threads_connected | 6 |
| threads_created | 8689 |
| threads_running | 5 |
+ ———————— +--–+
6 rows in Set (0.00 sec)

With the above 3 commands, you can see that the server's Thread_cache pool can hold up to 32 connection threads and one thread per client ball. Allocate 192k of memory space for each connected thread.

The server has a total of 199,156 connections, the maximum number of concurrent connections is 31, the current number of connections in the Thread_cashe pool is 3, the number of connections is 6, in the active state of 5, a total of 8,689 connections were created. Obviously, this is a short connection. You can figure out Thread_ Cache Hit ratio, the formula is:

Copy CodeThe code is as follows:
thread_cache_hit= (connections-thread_created)/connections*100%

The current server's Thread_cache hit rate is about 95.6% I'm still quite satisfied with the result. But it can be seen that thread_cache_size a bit more than 16 or 8 more reasonable.

"Table_open_cache"

==========================================================================================================

Because MySQL is a multi-threaded mechanism, to improve performance, each thread is opening its own file descriptor for the table that it needs, rather than sharing it already open. The methods for handling different storage engines are of course not the same.

In the MyISAM table engine, the descriptor (descriptor) of the data file is not shared, but the descriptor for the index file is shared by all threads. InnoDB and the use of table space types, if it is a shared tablespace then actually a data file, of course, the data file descriptor will be less than the independent table space.

Personally feel a bit like PHP inside the fopen open a connection, after the operation of the data, not immediately shut down, but the cache, waiting for the next connection to the file request will not have to reopen the file, do not know the kind of understanding is right, ha.

The manual has a description of the opening table:

Copy CodeThe code is as follows:
A MyISAM table is opened for each concurrent access. This means the table needs to be opened twice if the threads access the same table or if a thread accesses the table twice In the same query (for example, by joining the table to itself). Each concurrent open requires a entry in the table cache. The first open of any MyISAM table takes, file Descriptors:one for the data file and one for the index file. Each additional use of the table takes only one file descriptor for the data file. The index file descriptor is a shared among all threads.

If you are opening a table with the HANDLER tbl_name Open statement, a table will be allocated specifically for that thread. The table is not shared by other threads and is closed only if the thread calls handler Tbl_name close or the thread terminates. When the table is closed, it is pulled back into the table cache (if the cache is not satisfied).

The recommended size for the MySQL manual is: table_cache=max_connections*n

n represents the maximum number of tables in a query statement, and some additional file descriptors need to be reserved for temporary tables and files.

This data has been questioned a lot, table_cache enough, check the Opened_tables value, if this value is large, or growth soon then you have to consider increasing table_cache.

In the following conditions, unused tables are closed and moved out of the table cache:

When the cache is full and a thread tries to open a table that is not in the cache.

When the cache contains more than Table_cache entries, and the tables in the cache are no longer used by any thread.

When a table refresh operation occurs. Occurs when the Flush tables statement is executed or the mysqladmin flush-tables or mysqladmin refresh command is executed.

When the table cache is full, the server uses the following procedure to locate a cache entry to use:

Tables that are not currently in use are freed to the least recently used order.

If the cache is full and no tables can be freed, but a new table needs to be opened, the cache must be temporarily expanded.

If the cache is in a temporary widening state and a table is removed from the in-use state, it is closed and freed from the cache.

Several status values about Table_cache:

1. Table_cache: The number of tables opened by all threads. Increasing this value can increase the number of file descriptors required by mysqld. The default value is 64.

2. Open_tables: The number of tables currently open.

3. Opened_tables:number of table cache misses, if the opened_tables is large, the Table_cache value may be too small.

4. Open_table_definitions:the number of cached. frm files. This variable is added in MySQL 5.1.3.

5. Opened_table_definitions:the number of. frm files that has been cached. This variable is added in MySQL 5.1.24.

MySQL Configuration and performance optimizations