MySQL Performance monitoring Tool

Reference Documentation:

http://www.linuxidc.com/Linux/2012-09/70459.htm

1. Log slow Query SQL

#配置开启 (Linux) modify My.cnf:log-slow-queries=/var/log/mysql/slowquery.log (Specify the log file location, can be empty, the system will give a default file host_name-slow.log) Long_query_time=2(record over time, default is 10s) log-queries-not-using-indexes (log down without using the indexed query, depending on the situation can decide whether to open) log-Long-format (if set, all queries that do not use the index will also be logged) (Windows) modified My.ini:log-slow-queries=D:\mysql\log\mysqlslowquery.loglong_query_time=2... (Other parameters above) #查看方式使用mysql自带命令mysqldumpslow查看, common commands:-S ORDER what toSortBy (T, at, L, AL, R, Aretc),'At ' is default-T NUM just show the top n queries-G PATTERNgrep: Only consider stmts that includethisstringEg:s, the order of orders, the description is not detailed enough to write, I use down, including read the code, mainly have C,t,l,r and Ac,at,al,ar, respectively, according to the query number, time, lock time and the number of records returned to sort, preceded by a of the reverse-T, is the meaning of top N, that is, to return the number of the front of the data-g, behind can write a regular match pattern, case insensitive Mysqldumpslow-S C-T -host-Slow.logmysqldumpslow-S R-T -host-slow.log The above command, you can see the 20 SQL statements with the most accesses and the 20 SQL that returns the maximum number of recordsets. Mysqldumpslow-TTen-S T-g "leftJoinHost-slow.log This is the SQL statement that contains the left link in the first 10 bars by time.

2.explain (SQL Execution Plan interpretation command)

Expain [SQL]

3.profile

Mysql>Select@ @profiling;+-------------+| @ @profiling |+-------------+|0|+-------------+1RowinchSetmysql> SET profiling =1; Query OK,0rows Affectedmysql>Select@ @profiling;+-------------+| @ @profiling |+-------------+|1|+-------------+1RowinchSetmysql> SELECT *From T_im_tokens WHERE profileID in (SELECT profileID from T_enduser_customer_service);+-----------+----------------------------------+| profileID | Token |+-----------+----------------------------------+|10000152| a2898efe07380a813efa53e0fd4b3697 | |10000153| b37122b662a6a6f7ad0a16e1a03f7f01 | |10000190| 7235940f4438dbceeaf288540ba68f19 | |10000302| 7ccb852b19562fc296f82f95f8e0d9ef | |10000363| a672510b73a34a3d1d6227a53086b248 | |10000387| 209a5085acbcd3969a98668d62fbd1a3 | |10000550| 52324116187ce5b558b8a082762b657b | |10000556| d6b61c83675b373f5b7887c088e6806e |+-----------+----------------------------------+8RowsinchSetmysql>show Profiles;+----------+------------+-------------------------------------------------------------------------------------- -----------+| query_id | Duration | Query |+----------+------------+ -------------------------------------------------------------------------------------------------+|1|0.00018075|Select@ @profiling | |2|0.000613| SELECT * from T_im_tokens WHERE profileID in (SELECT profileID from T_enduser_customer_service) |+----------+------------ +-------------------------------------------------------------------------------------------------+2RowsinchSetmysql> Show Profile forQuery2;+----------------------+----------+| Status | Duration |+----------------------+----------+| Starting |6.3E-5|| Checking Permissions | 5e-6|| Checking Permissions | 4e-6|| Opening Tables | 2e-5|| init |2.1E-5|| System Lock | 7e-6|| Optimizing | 2e-5|| Statistics |0.000236|| Preparing |1.7E-5|| Executing | 3e-6|| Sending Data |0.000129|| End | 5e-6|| removing TMP table | 8e-6|| End | 3e-6|| Query End | 5e-6|| Closing Tables | 8e-6|| Freeing items |3.7E-5|| Cleaning Up |2.5E-5|+----------------------+----------+ -RowsinchSetmysql>

MySQL Performance monitoring Tool