Shulou(Shulou.com)06/03 Report--

The following content mainly brings you mysql optimization index, configuration, and slow query explanation, the knowledge here is slightly different from books, are summed up by professional and technical personnel in the process of contact with users, have a certain experience sharing value, hope to bring help to the majority of readers.

S first of all the basic ideas

1) location of performance bottleneck

Use the show command,

Slow query log,

Explain Analysis query,

Profiling Analysis query,

2) Index and query optimization

3) configuration optimization

There are two common bottlenecks in MySQL database: cpu and iCompo:

CPU mainly occurs when the data is loaded into memory or read on disk when it is saturated.

ICompo occurs when the loaded data is much larger than the memory capacity. If the application is distributed on the network, then there is a considerable network bottleneck in the amount of query. We can check the performance status of the system through mpstat, iostat, vmstat, sar and other commands.

For example: mpstat 3 3 {means output three times every 3 seconds}

In addition to the performance bottleneck of the CVM hardware, we can use tools to optimize the performance of the database for the mysql system itself. There are usually three types:

Use indexes, use explain to analyze queries, adjust the internal configuration of mysql

1: query and index optimization analysis

When optimizing mysql, it is usually necessary to analyze the database. The common analyses include slow query log, explain analysis query, profiling analysis and show command query system status and system variables.

Show command

You can check the mysql status and variables through the show command to find the bottleneck of the system.

View mysql server configuration information,

View the various states that the mysql server is running,

Show system variable: = > > mysqladmin variables-u username-ppassword

Display system status: = > > mysqladmin exlended-stautus-u username-ppassword

In addition, you can pass:

Slow query log

Open the slow log; add three parameters to the configuration file / etc/my.cnf

Slow_query_log=1 [1 for on, 0 for off]

Storage location of slow_query_log_file=/usr/local/mysql/data/slow-query.log slow search log

Long-query_time=1 indicates the time record of querying more than 1 second.

Add a log-queries-not-using-indexes parameter to my.cnf to indicate that queries that do not use indexes are recorded in the slow query log

The slow log can also be opened on the command line:

It's only temporary on the command line and permanent in the main configuration file.

View the setting information of slow query:

View the time limit for timeout

In addition, we can also check the slow query log to see the inefficient sql statements.

You can see that the execution time of the command that just specified the path of the slow query file is more than 0.01 seconds, so it is also recorded.

If there is a lot of content in the slow log, you can use mysqldmpslow to classify and summarize the log files

For specific usage, you can view the parameters used through mysqldmpslow-help

If there is a slow query statement, how to optimize it?

One: create an index on the data entertime column

Second: optimize the sql query statement

Using the mysqldumpslow command, we can clearly get all kinds of query statements we need; the monitoring, analysis and optimization of mysql query statements is a very important part of mysql.

Explain Analysis query:

Use the explain keyword to simulate the optimizer's execution of sql query statements; you know how mysql handles sql statements. You can analyze performance bottlenecks in query statements or table structures

EXPLAIN field:

Table: shows which table the data in this row is about

Type: this is one of the most important fields that show what type the query uses. The best to worst connection types are system, const, eq_reg, ref, range, index and ALL

Possible_keys: displays the indexes that may be applied to this table. If empty, there is no possible index.

Key: the index actually used. If NULL, the index is not used.

Key_len: the length of the index used. The shorter the length, the better without losing accuracy.

Ref: shows which column of the index is used and, if possible, a constant

The number of rows that rows:MySQL believes must be retrieved to return the request data

Extra: additional information about how MYSQL parses queries

Judging from the execution of the sql statement by the explain simulation optimizer above, there is no index query, but a full table scan

From the above explain simulation optimizer executing the sql statement, it seems that instead of using an index query, it is a full table scan.

Optimization method:

The results show that the query statement uses the index_stuname index to query the data instead of a full table scan.

Profiling Analysis query:

Through the slow log query, we can know that the execution efficiency of those sql statements is low, and we can know the specific implementation of sql statements through explain; index and so on, we can get more accurate information of sql execution consumption of system resources through the profiling command.

Profiling is off by default, and you can view it by:

Or through

Open the function of profiling: as shown below

Next, test the sql statement to be executed

Status: is the state in profile, and duration: is the time spent in the status state. So what we focus on is which state is the most time-consuming and which of these states can be optimized.

Of course, you can also check more information such as CPU and so on.

SHOW PROFILE [type [, type]...] [FOR QUERY n]

Type:

ALL: displays all the cost information

BLOCK IO: display block IO related overhead

CPU: displays user CPU time and system CPU time

IPC: displays information about sending and receiving overhead

PAGE FAULTS: displays overhead information related to page errors

SWAPS: displays information about the cost related to the number of exchanges

After the test is complete, remember to turn off the debugging feature so as not to affect the normal use of the database:

Mysql > set profiling=0

2: configuration optimization:

Mysql parameter optimization is related to different websites, its line volume, visits, posts, network conditions, and hardware equipment. Optimization can not be completed at one time, and requires constant observation and debugging in order to achieve the best results.

The variables that have a great impact on performance are divided into link request variables and buffer variables.

1) variable of connection request

Max_connections

The maximum number of connections to mysql. If the server has a large number of concurrent requests, it is recommended to increase the number of parallel connections. Of course, this is based on the fact that the server can support it. If the number of connections is larger, mysql recalls that each connection provides a connection buffer, so the memory overhead will increase. So adjust the value appropriately. We can't improve blindly.

However, if the value is too small, there will be an error in ERROR 1040:Too many connections. You can check the number of connections by using the following command.

Mysql > show variables like 'max_connections' maximum number of connections

Number of connections to the mysql > show status like 'max_used_connections' response

Max_used_connections / max_connections * 100% (ideal value ≈ 85%)

Max_used_connections/ max_connections * 100% {ideal value = 85%}

So how to set up max_xonnections?

Modify the / etc/my.cnf file and add the following under [mysqld]. If the maximum number of connections is set to 1024

Max_connections=1024

Then restart the mysql service

2:back_log

The number of connections that mysql can hold temporarily. When the main mysql thread gets the right connection request in a very short period of time; it works, and when the number of links in the mysql; reaches max_connections, the new request will be stored on the stack. Wait for a link to release resources. The number of links in the stack is back_log. If the number of links exceeds back_log, link resources will not be granted.

The back_ log value indicates how many requests can be stored on the stack within a short period of time before mysql temporarily stops answering new requests. If you expect to link to many requests in a short period of time, you need to add it.

How to set back_log?

Modify the / etc/my.cnf file and add the following under [mysqld], such as setting the maximum number of connections to 1024

Back_log = numeric

Restart the mysql service

3. Wait_timeout and interactive_timeout

Wait_timeout-refers to the number of seconds that MySQL waits before closing a non-interactive connection

Interactive_time-refers to the number of seconds that mysql has to wait before closing an interactive connection, for example, when we enter mysql management on the terminal, even if the interactive connection is used, it will be disconnected automatically if the time of no operation exceeds the time set by interactive_time. The default value is 28800 and can be tuned to 7200.

Impact on performance:

Wait_timeout:

(1) if the size is set, the connection closes quickly so that some persistent connections do not work.

(2) if the setting is too large, it is easy to cause the connection to be opened for too long. In show processlist, you can see too many connections in sleep status, resulting in too many connections errors.

(3) generally, wait_timeout is expected to be as low as possible.

The setting of interactive_timeout will not have much impact on your web application

View wait_timeout and interactive_timeout

Mysql > show variables like'% wait_timeout%'

Mysql > show variables like'% interactive_timeout%'

How do I set up wait_timeout and interactive_timeout?

Modify the / etc/my.cnf file and add the following under [mysqld]

Wait_timeout=100

Interactive_timeout=100

After restarting MySQL Server, check that the settings have taken effect.

2) variables in Shouchong area

Global buffering:

4.key_buffer_size

Key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status values Key_read_requests and Key_reads, you can see whether the key_buffer_size setting is reasonable. The key_reads / key_read_requests ratio should be as low as possible, at least 1 SHOW STATUS LIKE 100 and 1 SHOW STATUS LIKE 1000 is better. (the above state values can be obtained using the state value).

There are a total of 6 index read requests, 3 of which are not found in memory to read the index directly from the hard disk, and calculate the probability that the index misses the cache:

Key_cache_miss_rate = Key_reads / Key_read_requests * 50%

Key_buffer_size only works on MyISAM tables. Use this value even if you do not use the MyISAM table, but the internal temporary disk table is the MyISAM table. You can use the check status value created_tmp_disk_tables to learn more.

How to adjust key_buffer_size

The default configuration value is 8388608 (8m). The host has 4GB memory and can be tuned to 268435456 (256MB).

Modify the / etc/my.cnf file and add the following under [mysqld]

Key_buffer_size=268435456 or key_buffer_size=256M

After restarting MySQL Server, check that the settings have taken effect.

5. Query_cache_size (query cache referred to as QC)

Using query buffering, MySQL stores the query results in a buffer, and in the future, for the same SELECT statement (case sensitive), the results will be read directly from the buffer.

If a SQL query starts with select, the MySQL server will try to use query caching for it.

Note: as long as there is a difference of even one character between two SQL statements (for example, different case; one more space, etc.), the two SQL will use a different CACHE.

You can see whether the query_cache_size setting is reasonable by checking the status value 'Qcache%',. (the above status values can be obtained using SHOW STATUS LIKE' Qcache%').

Qcache_free_blocks: the number of adjacent memory blocks in the cache. If the value is large, there is more memory fragmentation in Query Cache, and FLUSH QUERY CACHE defragmenting the cache to get a free block.

Note: when a table is updated, the cache blocks associated with it is free. But the block may still exist in the queue, except at the end of the queue. You can use the FLUSH QUERY CACHE statement to empty free blocks

The amount of memory currently remaining in the Qcache_free_memory:Query Cache. Through this parameter, we can accurately observe whether the Query Cache memory size in the current system is enough, whether it needs to be increased or too much.

Qcache_hits: indicates how many times the cache has been hit. We can mainly use this value to verify the effect of our query cache. The higher the number, the better the cache.

Qcache_inserts: indicates how many times you missed and then inserted, meaning that the new SQL request was not found in the cache and had to perform query processing, and then insert the results into the query cache. The more times this happens, the less query caching is applied, and the effect is not ideal. Of course, after the system has just started, the query cache is empty, which is normal.

Qcache_lowmem_prunes: how many Query are purged out of Query Cache due to insufficient memory. Through the combination of "Qcache_lowmem_prunes" and "Qcache_free_memory", we can know more clearly whether the memory size of Query Cache in our system is really enough and whether Query is swapped out very frequently because of insufficient memory. This number is best seen over a long period of time; if it is growing, it means that the fragmentation may be very serious, or there may be very little memory. (the free_blocks and free_memory above can tell you which case it is.)

Qcache_not_cached: the number of queries that are not suitable for caching, usually because they are not SELECT statements or use functions such as now ().

Qcache_queries_in_cache: the number of Query of cache in the current Query Cache

Qcache_total_blocks: the number of block in the current Query Cache.

Let's check the server's configuration for query_cache again:

As you can see in the figure above, query_cache_type for ON means caching any query

Explanation of each field:

Query_cache_limit: queries larger than this size will not be cached

Query_cache_min_res_unit: the minimum size of the cache block. The configuration of query_cache_min_res_unit is a "double-edged sword". The default is 4KB. Setting a large value is good for big data queries, but if your queries are small data queries, it is easy to cause memory fragmentation and waste.

Query_cache_size: query cache size (Note: the minimum storage unit of QC is 1024 byte, so if you set query_cache_type: cache type to determine what kind of query to cache, note that this value cannot be set casually, it must be set to a number. Optional items are described as follows:

If it is set to 0, then it can be said that your cache is useless at all, which is equivalent to disabling it.

If set to 1, all results will be cached unless your select statement uses SQL_NO_CACHE to disable query caching.

If set to 2, only the queries specified by SQL_CACHE in the select statement that need to be cached are cached.

Modify / etc/my.cnf. Some of the files after configuration are as follows:

Query_cache_size=256M

Save the file, restart the MYSQL service, and verify that it is turned on with the following query:

Query_cache_wlock_invalidate: when another client is writing to the MyISAM table, if the query is in query cache, whether to return the cache result or wait for the write operation to complete and then read the table to get the result.

Query cache fragmentation rate = Qcache_free_blocks / Qcache_total_blocks * 100%

If the query cache fragmentation rate exceeds 20%, you can use FLUSH QUERY CACHE to defragment the cache, or try to reduce query_cache_min_res_unit, if your query is a small amount of data.

Query cache utilization = (query_cache_size-Qcache_free_memory) / query_cache_size * 100%

If the query cache utilization is below 25%, it means that the query_cache_size setting is too large and can be reduced appropriately; if the query cache utilization is above 80% and the Qcache_lowmem_prunes is more than 50, the query_cache_size may be a little small, or there may be too many fragments.

Query cache hit ratio = Qcache_hits/ (Qcache_hits + Qcache_inserts) * 100%

Limitations of Query Cache

A) external query SQL in all subqueries cannot be Cache

B) Query in Procedure,Function and Trigger cannot be Cache

C) Query containing many other functions that may get different results each time you execute cannot be Cache.

In view of the above limitations, in the process of using Query Cache, it is recommended to use precise settings, so that only the data of the appropriate table can be entered into the Query Cache, and only the query results of some Query can be Cache.

How to set up query_cache_size?

Modify the / etc/my.cnf file and add the following under [mysqld]

Query_cache_size=256M

Query_cache_type=1

After restarting MySQL Server, check that the settings have taken effect.

6. Max_connect_errors is a security-related counter value in MySQL, which is responsible for preventing too many failed clients from breaking the password violently. When the number of times is exceeded, the MYSQL server will prohibit host connection requests until the mysql server restarts or clears the relevant information of this host through the flush hosts command. The value of max_connect_errors does not have much to do with performance.

Modify the / etc/my.cnf file and add the following under [mysqld]

Max_connect_errors=20

After restarting MySQL Server, check that the settings have taken effect.

MySQL performance optimization-factors affecting performance

If the mysql server is compared to a sports car, then the server hardware is like the engine, engine and other public appliances, and the leather chair inside can be compared to the performance optimization of MySQL. Only a combination of both can be regarded as a complete sports car.

Here we are mainly aimed at optimizing the performance of mysql belongs to the facilities mentioned just now.

Including the number of connections, query cache, etc.

MySQL factors that affect performance:

1. The impact of business demand

two。 The impact of system architecture and implementation

1) binary multimedia data

2) very large text data

Improper use of 3:sql statements and changes before and after optimization

The most important optimizations in the database include cpu, memory and disk iUnix optimizations.

Of course, these must be judged according to your own company's server: for example, CPU can support multi-core, minimum memory 64GB, above, and so on.

Example: optimize your query for query caching

Most MySQL have enabled query caching, which is one of the most effective ways to improve optimization; many of the same queries are executed multiple times and are placed in a cache, and the subsequent queries access the data in the cache directly without manipulating the table

Query_cache_size (query cache referred to as QC)

Note: as long as there is a difference of even one character between two SQL statements (for example, different case; one more space, etc.), the two SQL will use a different CACHE.

You can see whether the query_cache_size setting is reasonable by checking the status value 'Qcache%',. (the above status values can be obtained using SHOW STATUS LIKE' Qcache%').

Qcache_free_blocks: the number of adjacent memory blocks in the cache. If the value is large, there is more memory fragmentation in Query Cache, and FLUSH QUERY CACHE defragmenting the cache to get a free block.

Note: when a table is updated, the cache blocks associated with it is free. But the block may still exist in the queue, except at the end of the queue. You can use the FLUSH QUERY CACHE statement to empty free blocks

Let's check the server's configuration for query_cache again:

As can be seen in the figure above, query_cache_type is off, which means no query is cached.

Explanation of each field:

Query_cache_limit: queries larger than this size will not be cached

Query_cache_min_res_unit: the minimum size of the cache block. The configuration of query_cache_min_res_unit is a "double-edged sword". The default is 4KB. Setting a large value is good for big data queries, but if your queries are small data queries, it is easy to cause memory fragmentation and waste.

Query_cache_size: query cache size (Note: the minimum unit of QC storage is 1024 byte, so if you set a value that is not a multiple of 1024, this value will be rounded to the nearest current value equal to a multiple of 1024.)

Query_cache_type: cache type, which determines what kind of query to cache. Note that this value cannot be set arbitrarily, but must be set to a number. Optional items and instructions are as follows:

If it is set to 0, then it can be said that your cache is useless at all, which is equivalent to disabling it.

If set to 1, all results will be cached unless your select statement uses SQL_NO_CACHE to disable query caching.

If set to 2, only the queries specified by SQL_CACHE in the select statement that need to be cached are cached.

Query_cache_wlock_invalidate: when another client is writing to the MyISAM table, if the query is in query cache, whether to return the cache result or wait for the write operation to complete and then read the table to get the result.

Modify / etc/my.cnf. Some of the files after configuration are as follows:

Query_cache_size=256M

Query_cache_type=1

Save the file, restart the MYSQL service, and verify that it is actually turned on with the following query:

2:explain your select query

Help you see how your mysql handles sql statements and analyze performance bottlenecks in your queries or table structures.

It will also tell you what index you are using and how the data tables are searched and sorted

Explain Analysis query

Use the EXPLAIN keyword to simulate the optimizer's execution of SQL queries to know how MySQL handles your SQL statements. This can help you analyze the performance bottlenecks of your query or table structure. You can get this through the explain command:

> explain select * from test1.tb1 where stuname='admin'\ G

Profiling Analysis query

Through the slow log query, we can know which SQL statements are inefficient. Through explain, we can know the specific implementation of SQL statements, index use, and so on. We can also check the execution status with the show command. If you feel that the explain information is not detailed enough, you can get more accurate information about the SQL execution consumption of system resources through the profiling command.

Profiling is off by default. You can view it with the following statement

Mysql > show variables like'% profiling%'; / / off indicates that it is not open

Open the profiling function: mysql > set profiling=1; to execute the sql statement to be tested:

Mysql > select @ @ profiling

+-+

| | @ @ profiling |

+-+

| | 1 |

+-+

Execute the sql statement to be tested

Mysql > select * from test1.tb1 where stuname='admin' and entertime='2016-9-1'

Mysql > show profiles\ G; / / you can get the time and ID of the executed SQL statement

* * 1. Row *

Query_ID: 1

Duration: 0.00012650

Query: select @ @ profiling

* 2. Row * *

Query_ID: 2

Duration: 0.00121725

Query: select * from test1.tb1 where stuname='admin' and entertime='2016-9-1'

Mysql > show profile for query 2; / / get the execution details of the corresponding SQL statement

+-+ +

| | Status | Duration |

+-+ +

| | starting | 0.000230 | |

| | checking permissions | 0.000013 | |

| | Opening tables | 0.000030 | |

| | init | 0.000087 | |

| | System lock | 0.000018 | |

| | optimizing | 0.000128 | |

| | statistics | 0.000378 | |

| | preparing | 0.000026 | |

| | executing | 0.000005 | |

| | Sending data | 0.000187 | |

| | end | 0.000013 | |

| | query end | 0.000011 | |

| | closing tables | 0.000010 | |

| | freeing items | 0.000061 | |

| | cleaning up | 0.000021 | |

+-+ +

Status: is the state in profile, and duration: is the time spent in the status state. So what we focus on is which state is the most time-consuming and which of these states can be optimized.

Of course, you can also check more information such as CPU and so on.

SHOW PROFILE [type [, type]...] [FOR QUERY n]

Type:

ALL: displays all the cost information

BLOCK IO: display block IO related overhead

CPU: displays user CPU time and system CPU time

IPC: displays information about sending and receiving overhead

PAGE FAULTS: displays overhead information related to page errors

SWAPS: displays information about the cost related to the number of exchanges

After the test is complete, remember to turn off the debugging feature so as not to affect the normal use of the database:

Mysql > set profiling=0

3: index the search field:

Indexes are not necessarily created for primary keys or fields, but for targets that often need to be queried, the equivalent of a dictionary directory, with high speed and efficiency. Improve query efficiency and quickly locate data

There are four types of indexes:

CREATE INDEX indexName ON tablename (column1 [, column2, …])

Full-text index

Used only for MyISAM tables to index text fields. Field types include char, varchar, text

However, keep in mind that for large data tables, generating a full-text index is a very time-consuming and hard disk space-consuming practice.

CREATE FULLTEXT INDEX indexname ON tablename (column)

Global buffering: size of index cache

.key _ buffer_size

Key_buffer_size specifies the size of the index buffer, which determines the speed of index processing, especially the speed of index reads. By checking the status values Key_read_requests and Key_reads, you can see whether the key_buffer_size setting is reasonable.

How to adjust key_buffer_size

The default configuration value is 8388608 (8m). The host has 4GB memory and can be tuned to 268435456 (256MB).

Modify the / etc/my.cnf file and add the following under [mysqld]

Key_buffer_size=268435456 or key_buffer_size=256M

The effect of innodb_buffer_pool_size is the same as that of key_buffer_size for MyISAM tables. InnoDB uses this parameter to specify the size of memory to buffer data and indexes.

4: avoid using select*:

If there is too much data in the database, use'*'to increase the query time to increase the load of cpu and iCompo, full table query and slow speed, so you should develop a certain field when you query.

5: select the correct storage engine:

Myisam uses and queries a large number of applications; sometimes a update field can cause full table locking, which is, of course, very fast in calculations like count (*) because of counters

Innodb's complex storage engine supports row locks, swaps for things, and is not suitable for count (*)

6: view the slow query log:

Slow log is enabled:

Add three configuration parameters under the [mysqld] line in the configuration file my.cnf, and restart the mysql service

Slow_query_log = 1 / / 0 off and 1 on

Slow_query_log_file = / usr/local/mysql/data/slow-query.log / / slow query log storage location

Long_query_time = 1 / / indicates that the query takes more than 1 second to record.

Add the log-queries-not-using-indexes parameter to my.cnf to indicate that queries that do not use indexes are recorded to the slow query log.

Slow query log opening method 2:

We can also start slow log queries immediately by setting variables on the command line

Mysql > set global slow_query_log = on

Mysql > set long_query_time = 0.01,

Mysql > set global slow_query_log_file = "/ usr/local/mysql/data/slow-query.log"

View the setting information of slow query

Mysql > show variables like'% slow_query_log%'

Mysql > show variables like'% long_query_time%'

We can see which SQL execution is inefficient by opening the log file.

[root@localhost data] # cat slow-query.log

7: restrictions on mass data:

If you add data in large quantities, it will lead to low query efficiency, and the data will be stored for a long time, sometimes as long as several hours.

Max_allowed_packet = 32m

MySQL limits the packet size accepted by Server based on the configuration file. Sometimes large inserts and updates are limited by the max_allowed_packet parameter, causing writes or updates to fail. The maximum value is 1GB, and a multiple of 1024 must be set.

8: turn off interaction:

For example, when dba uses the interactive interface to add, modify, delete and query the database, he forgot to exit the interactive page of the database. If someone sees the operation and modification of the data above, it will cause inestimable losses to the company. Here, we can only configure the file to adjust the interactive existence time to prevent other people from operating.

In addition, you can also release the number of links for a user and increase the number of links.

.wait _ timeout and interactive_timeout

Wait_timeout-refers to the number of seconds that MySQL waits before closing a non-interactive connection

Interactive_time-refers to the number of seconds that mysql has to wait before closing an interactive connection, for example, when we enter mysql management on the terminal, even if the interactive connection is used, it will be disconnected automatically if the time of no operation exceeds the time set by interactive_time. The default value is 28800 {8 hours} and can be tuned to 7200.

9: increase the number of user links:

Sometimes the performance of the database suddenly slows down; it takes a long time for the link customer to respond, and sometimes the customer will continue to link, so that the database will be even busier. Finally, if the situation is serious, it may cause the database to hang up. Here you need to set the maximum number of links.

1.max_connections

The maximum number of connections to MySQL. If the server has a large number of concurrent connection requests, it is recommended to increase the number of parallel connections. Of course, this is based on the condition that the machine can support it, because if there are more connections, MySQL will provide a connection buffer for each connection, which will cost more memory, so you should adjust this value appropriately, not blindly increase the setting value.

Mysql > show variables like 'max_connections' maximum number of connections

Number of connections to the mysql > show status like 'max_used_connections' response

Max_used_connections / max_connections * 100% (ideal value ≈ 85%)

If the max_used_connections is the same as max_connections, then the max_connections setting is too low or exceeds the server load limit, and less than 10% is too large.

How to set up max_connections?

Modify the / etc/my.cnf file and add the following under [mysqld], such as setting the maximum number of connections to 1024

Max_connections = 1024

Stack setting for 10:MySQL: 2.back_log

If the number of links is too many and the maximum number of links is not enough, you can set up a stack, like a room, where too many links are stored first, and then deal with the links in the room after the previous links are processed. If the number of waiting connections exceeds back_log, connection resources will not be granted.

The back_ log value indicates how many requests can be stored on the stack in a short period of time before MySQL temporarily stops answering new requests. Only if you expect to have many connections in a short period of time, you need to add it.

When looking at the list of your host processes (mysql > show full processlist), you can find a large number of

Xxxxx | unauthenticated user | xxx.xxx.xxx.xxx | NULL | Connect | NULL | login | when the process of NULL is to be connected, it is necessary to increase the value of back_log or max_connections.

View the settings of back_log through mysql > show variables like 'back_log';

How to set up back_log?

Modify the / etc/my.cnf file and add the following under [mysqld], such as setting the maximum number of connections to 1024

Back_log = value {1024}

Restart the mysql service

For the above explanation on mysql optimization index, configuration, and slow query, if you have more information, you can continue to pay attention to the innovation of our industry. If you need professional answers, you can contact the pre-sale and after-sale ones on the official website. I hope this article can bring you some knowledge updates.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.