MySQL Slow query

MySQL turn on slow query log MySQL accelerated query speed of a unique weapon: query cache on the MySQL query cache from five angles for detailed analysis: How to query cache, how to configure, how to maintain, how to determine the performance of the queries caching, suitable business scenario analysis. n how the working principle of query caching works, basically can be summed up in two sentences: L Cache Select operations or preprocessing queries (note:5.1. 17 start support) result set and SQL statement; L New SELECT statement or preprocessing query statement, first go to query the cache, determine whether there is a recordset available, judging the criteria: The SQL statement with the cache is exactly the same, case-sensitive, query cache to what kind of query statement, It is not possible to cache its recordset, which has the following categories: L The Sql_no_cache parameter is added to the query statement, and the function containing the obtained value is included in the query statement, including the custom function, such as: Curdate (), Get_lock () ,RAND(), Convert_tz, etc; L query the system database: MySQL, information_schemal query statements use session level variables or local variables in stored procedures; l use lock in query statementsinchSHARE MODE, forUPDATE statement L query statement similar to select ... intoThe statement that exports the data; L Transaction ISOLATION level is: Serializable case, all query statements can not be cached; L query operation on temporal table; L Query statement with warning message; l No query statement with table or view; query cache with column-level permission for a user; The advantages and disadvantages: l do not need to do any parsing and execution of SQL statements, of course, syntax parsing must be directly from the query cache to obtain the results; L query caching rules, not smart enough, that is, improve the use of query caching threshold, reduce its efficiency; l query Cache, Will increase the cost of checking and cleaning the recordset in the query cache, and there are tables with SQL statements cached, each with a corresponding global lock, and n configuration to enable MySQL query caching with 2 parameters: Query_cache_type and Query_cache _size, where any one of the parameters is set to 0 means that the query caching feature is turned off, but the correct settings are recommended Query_cache_type=0. L query_cache_type Domain is:0 -– The query cache is not enabled;1 -– Query caching is enabled and the client's query statements and recordset buckets can be cached and used by other clients as long as the query caching requirements are met:2 -– Enable query caching, as long as the query statement adds parameters: Sql_cache, and conforms to the requirements of the query cache, the client's query statement and Recordset can be cached and used by all other clients; L query_cache_size allow settings query_cache_ The value of size is a minimum of 40K, for the maximum value can be almost considered unrestricted, the application experience of the actual production environment tells us that the value is not greater, the query cache hit rate is higher, and not to the server load drop contribution, but may offset its benefits, and even increase the server load, As to how to set up, the following section describes, the recommended setting is: 64m;l query_cache_limit limit query buffer maximum cache query Recordset, you can avoid a large query recordset takes up a large number of memory areas, and often small query Recordset is the most effective cache recordset, The default setting is 1M, it is recommended to modify to 16k~1024k Range, but the most important is based on the actual situation of their application analysis, estimate to set; L Query_cache_min_res_unit set the minimum unit of memory allocated for the query cache, set this parameter appropriately, This can be done to reduce the number of requests and allocations for memory blocks, but setting too large may cause memory fragmentation to increase in value. The default value is 4K and the recommended setting is 1k~16Kl Query_cache_wlock_invalidate This parameter mainly involves the MyISAM engine, if a client has a write lock on a table, other client-initiated query requests, and the query statement has a corresponding query cache record, Whether to allow direct read of the recordset information cached by the query, or to wait for the write lock to be released. The default setting is 0, which is allowed; N Maintenance l query buffer defragmentation query cache is used for a period of time, memory fragmentation generally occurs, for this need to monitor the relevant state values, and periodic memory fragmentation, defragmentation operation statement: FLUSH query cache;l empty query cache Data those operations may trigger query caching, emptying all cached information to avoid triggering or needing to know what to do, and two classes of commands that trigger the full emptying of the query cache data: (1). RESET QUERY CACHE; (2). FLUSH tables;n Performance monitoring L Fragment rate query cache memory Fragmentation rate=Qcache_free_blocks/Qcache_total_blocks*  -%l Hit rate query cache hit rate=Qcache_hits/(qcache_hits+Qcache_inserts)*  -%l Memory Usage Query cache memory Utilization=(query_cache_size–qcache_free_memory)/Query_cache_size*  -%l QCACHE_LOWMEM_PRUNES The value of this parameter is very critical for detecting the memory size setting of the query buffer, which means that the query cache removes the query cache information that has to be removed from the query buffer due to insufficient memory, and the deletion algorithm is lru;l Query_cache_min_res_unit memory block allocation of the smallest unit is very important, set too large may increase the probability of memory fragmentation, is too small and may increase the consumption of memory allocation, for this, after the system runs smoothly a phase, you can refer to the calculated value of the formula: query cache minimum memory block =(query_cache_size–qcache_free_memory)/Qcache_queries_in_cachel Query_cache_size We how to determine whether query_cache_size is set too small, still only pre-set a value, recommended as: 32M~the area between 128M, to be run smoothly for a period of time (at least 1 weeks), and observe the relevant status values during the week: (1). Qcache_lowmem_prunes; (2). Hit rate; (3). Memory utilization, if the information obtained during the entire smooth running period is higher than%, memory utilization exceeds 80%, and the value of qcache_lowmem_prunes is constantly increasing, and the added value is larger, it indicates that we allocate too little memory for the query buffer, which can increase the memory size of the query buffers appropriately; If the information obtained during the entire smooth running period is monitored, the hit rate is less than 40. %, the value of Qcache_lowmem_prunes is also maintained a steady state, it means that our query buffer memory settings too large, or the business scenario repeated execution of the same query statement is low probability, while also monitoring a certain amount of freeing items,  Then we must consider the memory of the query cache small, or even close the query cache function; n business scenarios through the above knowledge combing and analysis, we know at least the following points of query cache: L Query cache can speed up the existing cached query statements, can not be re-parsed and executed to obtain the correct recordset; Query cache involved in the table, each Table object has a own global nature of the lock; L table if the DDL, FLUSH TABLES and other similar operations, trigger the related table query cache information emptied; L Table object DML operations, you must first determine whether to clean up the relevant query cache record information, There will inevitably be a lock-wait event; L query cache memory allocation problem, inevitably produce some memory fragments; L query cache is the same query statement, demanding, and not intelligent; let's go back to the point in this section, what kind of business scenario is the query cache for? As long as it is clear that the query cache the above advantages and disadvantages, it is not difficult to list out, business scenario requirements: l The whole system to read-oriented business, such as portal, News category, report type, forum and other sites; L query the Table object of the statement operation, do not frequently DML operations, you can use Query_cache_type =2 mode, then SQL statement plus sql_cache parameter specified;/*SHOW VARIABLES like '%slow% '; Set GLOBAL log_slow_queries=on set Slow_query_log=on set long_query_time=1--Unit is seconds*/The most important part of optimizing MySQL is to first identify "problematic" query statements. Only by finding these SQL queries that are slow to query (long execution time) can we further analyze the cause and optimize it. MySQL provides us with the slow query log recording feature, which records queries that have been executed longer than a certain length of time. Analyzing slow query log helps us find the "problem" query. Record slow queries first, we need to see the MySQL server version number, and whether to configure slow query enabledLog. #打开服务 log_slow_queries=  onwhen Log_slow_queries is on, it indicates that the record slow query feature is enabled. By default, slow query is not logged. Enable slow query log #//Place the following configuration in My.cnf[mysqld] Log-Slow-Queries= /var/Lib/Mysql/Slow-Queries.Log //the newly added parameter Long_query_time= 3 Log-Queries-not-Using-IndexesLog-Slow-Admin-Statements The above configuration opens the slow query log, which captures queries that take more than 3 seconds to execute, including slower-performing management commands such as OptimezeTABLE), and records queries that do not use an index. These SQL will be logged to log-Slow-Queries the specified file/var/Lib/Mysql/Slow-the Queries.log file. Log-Slow-Queries<Slow_query_log_file>the file that holds the slow query log. You must ensure that the MySQL server process mysqld_safe process user has W permissions to the file. Long_query_time If query time exceeds this value, it is considered a slow query and is recorded. The unit is seconds, the minimum value is 1, and the default value is 10 seconds. 10 seconds is too long for most applications. We recommend starting from 3 seconds, decreasing in turn, finding the most expensive 10 SQL statements each time and optimizing them. Day after day, step by step optimization. Finding many SQL statements at once is not very meaningful for optimization. Log-Queries-not-Using-indexes MySQL logs queries that do not use an index to the slow query log. No matter how fast it executes, the query statement is logged without using an index. Sometimes, some queries that do not use a lasso are very fast (such as scanning a small table), but they can also cause the server to slow down and even use a lot of disk space. Log-Slow-Admin-statements Some management instructions will also be recorded. Like Optimeze .TABLE,ALTERtable and so on. Log file MySQL5.1 provides global variables Slow_query_log, Slow_query_log_file gives you the flexibility to control the Enable/disable slow query. You can also set the time by Long_query_time #//Deactivate slow query record #注意: Set the Slow_query_log global variable, log_slow_queries will also implicitly follow the changes to MySQL>SetGlobal Slow_query_log=OFFwe can get through tail .-F View the log file. $tail-F/var/Lib/Mysql/Slow-Queries.Log# Time:110107  -: A: One # User@Host: Root[Root]@ localhost[]# Query_time:9.869362Lock_time:0.000035Rows_sent:1Rows_examined:6261774 SET timestamp=1294388531; Select Count(*) fromep_friends; First row, SQL query execution time second row, execute SQL query connection information The third line records some of our more useful information query_time SQL execution time, the longer the more slowly lock_time in the MySQL server stage ( Not in the storage engine phase) waits for the table lock time rows_sent The number of rows returned by the query rows_examined the number of rows that the query examines Slow the queries log, although it helps you to record those SQL statements that have been executed. But it is not omnipotent, the meaning may not be as big as you think. It only tells you which statements are slow, but why slow? specific reasons, or need you to analyze, continuous debugging. Maybe you just need to change a more efficient SQL statement, maybe you simply add an index, but it's also possible that you need to tweak the design of your application. For example, the above statement is obvious, and it examines more than 6 million rows of data. Unfortunately, not every statement is so obvious. There may be other reasons, such as:*lock table, which causes the query to be in a state of equal states. Lock_time shows when a query waits for a lock to be translated*The data or index is not cached. It is common to start the server for the first time or the server is not tuned*Backup DATABASE, I/o Slow*Perhaps other queries have been run at the same time, reducing the current query MySQL5.1 can change journaling at run time, logging to a database table. Just set the MySQL global variable log_output to table. MySQL logs the log to table Mysql.gengera_log and mysql.slow_log two tables respectively. However, we recommend that you log records in a journal file. MySQL>Show variables like' log_output ' \g variable_name:log_output Value:FILEMySQL>SetGlobal Log_output=’TableSlow query can help you optimize your product. But the current version of MySQL, there are a few big limp foot of the place. 1. MySQL5.0 version, Long_query_time time granularity is not fine enough, the minimum value is 1 seconds. For web scripting with high concurrency, the 1-second appearance is not very significant. That is, fewer queries appear for 1 seconds. Until MySQL5.1. 21 provides finer-grained long_query_time settings.2. All queries performed by the server cannot be logged to the slow log. Although the MySQL normal log records all queries, they are recorded before parsing the query. This means that the normal log does not contain information such as execution time, lock table time, check row count, and so on. 3If the log_queries_not_using_indexes option is turned on, the slow query log is filled with excessive spam logging, and these fast and efficient full-table scan queries (small tables) flush out the truly useful slow queries records. Like Select*  fromqueries such as category will also be recorded. by Microslow-Patches can use finer granularity of time, and record all executed SQL statements. However, the use of this kernels have to compile their own MySQL, for stability filtering, we recommend in the Development test environment, you can hit this patch, enjoy the convenience of this patch. In the operating environment try not to do so ... MySQL has its own mysqldumpslow tool for analyzing slow query logs, and there are some useful open source tools. Like Myprofi, MySQL.-Log-Filter, and of course Mysqlsla.

MySQL Slow query