MySQL slow query usage and parameters detailed

Slow query for the system query time exceeds the value of long_query_time, analysis of slow query is the basis for optimizing SQL, the default clear MySQL slow query for the shutdown status, you can

Show variables where variable_name = ' log_slow_queries ';

To see if it's turned on, and if off you need to modify the MySQL configuration file, add the following parameters below Mysqld

#慢查询日志地址, need MySQL run account to have write permission to the directory
log-slow-queries= "/log/slow.log"
#当query语句大于2s时记录慢查询日志
Long_query_ time=2
#没有使用索引的query也计入慢查询日志 (can be increased according to circumstances)
Log-queries-not-using-indexes

Slow query analysis

You can use MySQL's own mysqldumpslow for analysis, which can take 3 parameters
-S, is to indicate the way in which C, T, L, R are sorted according to the number of records, time, query time, the number of records returned, AC, at, AL, AR, the corresponding flashback;
-T is the meaning of top N, which is the data that returns the previous number of bars;
-G, you can write a regular matching mode, the case is not sensitive;
For example, return the first 10 SQL statements that contain the left connection in the time

Mysqldumpslow-s t-t 10-g "left join"/alidata/log/mysql/slow.log

MySQL run status and variable view

Optimize MySQL by looking at MySQL status and variables

1, slow query configuration, not open in the case of the proposed open, you can find the system of slow query and slow query the number of bars

mysql> show variables like  '%slow% '; +---------------------+-----------------------------+ | variable_name        | Value               
        | +---------------------+-----------------------------+ | log_slow_queries    | on                            | | slow_launch_time    | 2                             | | slow_query_log      |  ON                            | | slow_query_log_file | /alidata/log/mysql/slow.log 
| +---------------------+-----------------------------+ 4 rows in set  (0.00 sec) MySQL
> show global status like  '%slow% '; +---------------------+-------+ | variable_name       | value
 | +---------------------+-------+ | slow_launch_threads | 4     | | 
slow_queries        | 3     | +---------------------+-------+ 2 rows in set  (0.00 sec)

2, the number of connections to view, max_connections for the maximum number of connections allowed, max_used_connections system has been the maximum number of connections, it is said that the ideal setting is

max_used_connections / max_connections * 100% ≈ 85% mysql> show 
variables like  '%connections% '; +----------------------+-------+ | variable_name        | 
value | +----------------------+-------+ | max_connections      | 2000 
 | | max_user_connections | 0     | +----------------------+-------+ 2 rows in set  (0.00 sec) mysql> show 
global status like  '%connections% '; +----------------------+-------+ | variable_name        | 
value | +----------------------+-------+ | connections          
| 35049 | | max_used_connections | 12    | +----------------------+-------+ 2 rows in set  (0.00 sec) 

Analysis log? Mysqldumpslow

Analysis log, available with MySQL Mysqldumpslow, using very simple, parameters can be? Help view

# -s: How to sort. c , t , l , r  indicates the number of records, the time, the number of query times, the number of records returned, sorted; #                               ac , at , al , ar  show a corresponding flashback; # - T: Returns the previous number of data; # -g: What contains, is not sensitive to case; Mysqldumpslow -s r -t 10  /slowquery.log       #slow记录最多的10个语句 mysqldumpslow -s t -t 10 -g  "left  join "  /slowquery.log      #按照时间排序前10中含有" left join "Recommended analysis Log Tool  ? mysqlsla wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz tar zvxf  mysqlsla-2.03.tar.gz cd mysqlsla-2.03 perl makefile.pl make Make install mysqlsla /data/ Mysqldata/slow.log # mysqlsla automatically determines the log type, and for convenience you can create a profile "~/.mysqlsla" #  write in the file: top=100, which prints out the first 100 results.

Description
Queries total: Number of queries unique: Amount of SQL after heavy
Sorted by: Sort the contents of the output report
The most significant slow SQL statistics, including the average execution time, the wait lock time, the total number of resulting rows, and the total number of rows scanned.
Count, the number of executions of SQL and the percentage of the total number of slow logs.
Time, execution times, including total time, average time, minimum, maximum time, and time as a percentage of total slow SQL time.
Of time, the fastest and slowest SQL is removed, with coverage accounting for 95% of SQL execution times.
Lock time, waiting for the lock.
of lock, 95% slow SQL wait lock time.
Rows sent, the result row statistics quantity, including average, minimum, maximum quantity.
Rows examined, the number of lines scanned.
database, which databases belong to
Users, which user, IP, percent of SQL executed by all users
Query abstract, abstract SQL statement
Query sample, SQL statement

Show status Chinese detailed Mark

Status name	Scope	Explain in detail
Aborted_clients	Global	Number of connections interrupted by client termination due to client failure to properly close connection
Aborted_connects	Global	Number of failed connections attempting to connect to the MySQL server
Binlog_cache_disk_use	Global	Number of transactions that use the temporary binary log cache but exceed the Binlog_cache_size value and use temporary files to hold statements in the transaction
Binlog_cache_use	Global	Number of transactions using temporary binary log caching
Bytes_received	Both	The number of bytes received from all clients.
Bytes_sent	Both	The number of bytes sent to all clients.
com*		Number of database operations
Compression	Session	Only the compression protocol is enabled between the client and the server
Connections	Global	Attempt to connect to (regardless of success) the number of connections to the MySQL server
Created_tmp_disk_tables	Both	Number of temporary tables that are automatically created on the hard disk when the server executes the statement
Created_tmp_files	Global	Mysqld the number of temporary files that have been created
Created_tmp_tables	Both	The number of in-memory temporary tables that are automatically created when the server executes the statement. If the created_tmp_disk_tables is larger, you may want to increase the tmp_table_size value so that the temporary table is based on memory and not on the hard disk
Delayed_errors	Global	The number of rows that were written with the insert delayed (possibly duplicate key).
Delayed_insert_threads	Global	Number of insert delayed processor threads used.
Delayed_writes	Global	Number of insert delayed lines written
Flush_commands	Global	The number of flush statements executed.
Handler_commit	Both	Number of internal commit statements
Handler_delete	Both	The number of times the row was deleted from the table.
Handler_discover	Both	MySQL server can ask NDB Cluster whether the storage engine knows a table of a single name. This is called discovery. Handler_discover describes the number of times discovered by this method.
Handler_prepare	Both	A counter for the prepare phase of two-phase commitoperations.
Handler_read_first	Both	The number of times the first article in the index was read. If higher, it suggests that the server is performing a large number of full index scans; for example, SELECT col1 from Foo, assuming col1 are indexed.
Handler_read_key	Both	The number of requests to read a line based on the key. If higher, the query and table indexes are correct.
Handler_read_next	Both	Reads the number of requests in the next row in the key order. This value is incremented if you query the indexed column with a range constraint or if you perform an index scan.
Handler_read_prev	Both	Reads the number of requests in the previous row in key order. This read method is mainly used to optimize order by ... DESC.
Handler_read_rnd	Both	The number of requests to read a row based on the fixed position. This value is higher if you are performing a large number of queries and need to sort the results. You may use a large number of queries that require MySQL to scan the entire table or your connection does not use the keys correctly.
Handler_read_rnd_next	Both	The number of requests to read the next line in the data file. If you are doing a lot of table scans, the value is higher. It usually indicates that your table index is incorrect or that the query you write is not indexed.
Handler_rollback	Both	The number of internal rollback statements.
Handler_savepoint	Both	The number of requests to place a savepoint in one storage engine.
Handler_savepoint_rollback	Both	The requirement to roll back to a save point at the request of a storage engine.
Handler_update	Both	The number of requests that update a row within a table.
Handler_write	Both	The number of requests to insert a row within a table.
Innodb_buffer_pool_pages_data	Global	The number of pages that contain the data (dirty or clean).
Innodb_buffer_pool_pages_dirty	Global	Current number of dirty pages.
Innodb_buffer_pool_pages_flushed	Global	Number of buffer pool pages required to be emptied
Innodb_buffer_pool_pages_free	Global	Empty pages.
Innodb_buffer_pool_pages_latched	Global	Number of pages locked in the InnoDB buffer pool. This is the number of pages that are currently being read or written or that cannot be emptied or deleted for other reasons.
Innodb_buffer_pool_pages_misc	Global	Number of busy pages, as they have been assigned priority for administration, such as row locking or applicable hash indices. This value can also be computed as Innodb_buffer_pool_pages_total -Innodb_buffer_pool_pages_free-innodb_buffer_pool_pages_data.
Innodb_buffer_pool_pages_total	Global	Total buffer pool size (pages).
Innodb_buffer_pool_read_ahead_rnd	Global	InnoDB the number of "random" read-aheads initialized. Occurs when the query scans a large portion of the table in random order.
Innodb_buffer_pool_read_ahead_seq	Global	InnoDB the number of sequential read-aheads initialized. Occurs when InnoDB performs a sequential full table scan.
Innodb_buffer_pool_read_requests	Global	InnoDB the number of logical read requests that have been completed.
Innodb_buffer_pool_reads	Global	Cannot meet the logical read quantity in a buffer pool that InnoDB must read on a single page.
Innodb_buffer_pool_wait_free	Global	Generally, the InnoDB buffer pool is written through the background. However, if you need to read or create a page and there is no clean page available, it also needs to wait for the page to empty. This counter counts the waiting instances. This value should be small if the buffer pool size is already set appropriately.
Innodb_buffer_pool_write_requests	Global	Number of writes to the InnoDB buffer pool.
Innodb_data_fsyncs	Global	Fsync () operand.
Innodb_data_pending_fsyncs	Global	The number of Fsync () operands currently pending.
Innodb_data_pending_reads	Global	The currently pending readings.
Innodb_data_pending_writes	Global	The number of currently pending writes.
Innodb_data_read	Global	The number of data (bytes) that have been read to this point.
Innodb_data_reads	Global	Total number of data reads.
Innodb_data_writes	Global	Total number of data writes.
Innodb_data_written	Global	The amount of data, in bytes, that has been written to this point.
Innodb_dblwr_pages_written	Global	Number of double write operations already performed
Innodb_dblwr_writes	Global	Number of pages that have been written in double write operations
Innodb_log_waits	Global	We have to wait for the time because the log buffer is too small and we must wait for it to clear before continuing
Innodb_log_write_requests	Global	Number of log write requests.
Innodb_log_writes	Global	The number of physical writes to the log file.
Innodb_os_log_fsyncs	Global	Number of Fsync () writes completed to the log file.
Innodb_os_log_pending_fsyncs	Global	The number of pending log file Fsync () operations.
Innodb_os_log_pending_writes	Global	Pending log File Write operations
Innodb_os_log_written	Global	The number of bytes written to the log file.
Innodb_page_size	Global	The compiled InnoDB page size (default 16KB). Many values are recorded on a page, and the size of the page can easily be converted to bytes.
innodb_pages_created	Global	The number of pages created.
Innodb_pages_read	Global	Number of pages read.
Innodb_pages_written	Global	Number of pages written.
Innodb_row_lock_current_waits	Global	The number of rows currently waiting to be locked.
Innodb_row_lock_time	Global	Total time spent in line locking, in milliseconds.
Innodb_row_lock_time_avg	Global	The average time, in milliseconds, for row locking.
Innodb_row_lock_time_max	Global	The maximum time, in milliseconds, that a row is locked.
Innodb_row_lock_waits	Global	The number of times a row must wait for a lock.
innodb_rows_deleted	Global	The number of rows deleted from the InnoDB table.
innodb_rows_inserted	Global	The number of rows inserted into the InnoDB table.
Innodb_rows_read	Global	The number of rows read from the InnoDB table.
innodb_rows_updated	Global	InnoDB the number of rows updated in the table.
Key_blocks_not_flushed	Global	The number of blocks of data in the key cache that have changed but have not yet been emptied to the hard disk.
Key_blocks_unused	Global	The number of unused blocks in the key cache. You can use this value to determine how many key caches are used
Key_blocks_used	Global	The number of blocks used within the key cache. The value is a high-level line mark indicating how many blocks have been used at the same time.
Key_read_requests	Global	The number of requests from the data block that reads the key from the cache.
Key_reads	Global	The number of times the key's data block was read from the hard disk. If the key_reads is larger, the key_buffer_size value may be too small. Cache loss rates can be computed using key_reads/key_read_requests.
Key_write_requests	Global	The number of requests to write a key's data block to the cache.
Key_writes	Global	The number of times the physical write of the data block to which the key was written to the hard disk.
Last_query_cost	Session	The total cost of the last compiled query computed with the query optimizer. The cost of comparing different query scenarios for the same query. The default value of 0 indicates that no query has been compiled. The default value is 0. Last_query_cost has a session scope.
Max_used_connections	Global	The maximum number of connections that have been used concurrently since the server was started.
ndb*		NDB Cluster Related
Not_flushed_delayed_rows	Global	Number of rows waiting to be written to the Insert delay queue.
Open_files	Global	The number of open files.
Open_streams	Global	The number of open streams (primarily for records).
Open_table_definitions	Global	Number of. frm Files Cached
Open_tables	Both	The number of tables currently open.
Opened_files	Global	The number of files opened. Files other than other types, such as sockets or pipes, are not included. Nor does it include files that the storage engine uses to do its own internal functions.
Opened_table_definitions	Both	Number of. frm files that have been cached
Opened_tables	Both	The number of tables that have been opened. If the opened_tables is larger, the Table_cache value may be too small.
Prepared_stmt_count	Global	The number of current preprocessing statements. (Maximum number is System variable: max_prepared_stmt_count)
Qcache_free_blocks	Global	The number of free memory blocks within the query cache.
Qcache_free_memory	Global	The amount of free memory used to query the cache.
Qcache_hits	Global	The number of times the query cache was accessed.
Qcache_inserts	Global	Number of queries added to the cache.
Qcache_lowmem_prunes	Global	The number of queries deleted from the cache because of less memory.
Qcache_not_cached	Global	The number of non-cached queries (not cached, or because the Query_cache_type setting value is not cached).
Qcache_queries_in_cache	Global	The number of queries registered in the cache.
Qcache_total_blocks	Global	The total number of blocks in the query cache.
Queries	Both	The number of requests executed by the server, including requests in the stored procedure.
Questions	Both	The number of queries that have been sent to the server.
Rpl_status	Global	Failed secure replication status (not yet in use).
Select_full_join	Both	The number of joins that do not use the index. If the value is not 0, you should carefully check the index of the table
Select_full_range_join	Both	The number of joins that use the range search in the referenced table.
Select_range	Both	The number of joins that use the range in the first table. The general situation is not a critical issue, even if the value is quite large.
Select_range_check	Both	The number of joins with no key values that are checked for key values after each row of data. If not 0, you should carefully check the index of the table.
Select_scan	Both	The number of joins that are fully scanned for the first table.
Slave_heartbeat_period	Global	The heartbeat interval of replication
Slave_open_temp_tables	Global	Number of temporary tables opened from the server
Slave_received_heartbeats	Global	Number of heartbeats from server
Slave_retried_transactions	Global	Chenghong the number of attempts to copy the line from the server since this start
Slave_running	Global	If the server is a server that is connected to the primary server, the value is on.
Slow_launch_threads	Both	Number of threads created longer than slow_launch_time seconds.
Slow_queries	Both	The number of queries with a query time exceeding long_query_time seconds.
Sort_merge_passes	Both	The number of merges that the sort algorithm has performed. If the value of this variable is large, consider increasing the value of the sort_buffer_size system variable.
Sort_range	Both	The number of sorts that are executed within the scope.
Sort_rows	Both	The number of rows that have been sorted.
Sort_scan	Both	The number of sorting completed by the scan table.
ssl*		SSL connection Related
Table_locks_immediate	Global	The number of times the lock of the table was immediately obtained.
table_locks_waited	Global	The number of times the lock of a table cannot be obtained immediately. If the value is higher and there is a performance problem, you should first optimize the query and then split the table or use replication.
Threads_cached	Global	The number of threads within the thread cache.
threads_connected	Global	The number of connections currently open.
threads_created	Global	Creates the number of threads to handle the connection. If the threads_created is larger, you may want to increase the thread_cache_size value. The method of calculating the cache access rate is threads_created/connections.
Threads_running	Global	The number of active (non-sleep state) threads.
Uptime	Global	The time, in seconds, that the server has been running.
Uptime_since_flush_status	Global	Last time the flush STATUS was used (in seconds)