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

This article mainly introduces mysql's slow query analysis and tuning tool show profile how to use, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

Show profile is also an analysis and tuning tool that comes with MySQL services, but this is more advanced and closer to tuning the underlying hardware parameters.

View show profile settings

Show variables like 'profiling%';// is turned off by default, saving the results of nearly 15 runs

open

Set profiling = on

View the results of the last 15 runs

Show profiles; remarks: show warnings;// can display warning and error messages

Diagnostics running SQL

Command: show profile cpu,block io for query query_id; example: show profile cpu,block io for query 3 Through the Status column, you can see the running process of the whole SQL: 1. Starting / / start 2. Checking permissions / / check permissions 3. Opening tables / / Open data Table 4. Init / / initialize 5. System lock / / lock mechanism 6. Optimizing / / optimizer 7. Statistics / / parse syntax tree 8. Prepareing / / preparation 9. Executing / / engine execution starts 10. End / engine execution ends 11. Query end / / query ends .12. Closing tables / / release data Table 13. Freeing items / / release memory 14. Cleaning up / / Clean thoroughly

Type:ALL / / display index overhead information BLOCK IO / / display block IO related overhead CONTEXT SWITCHES / / context switch related overhead CPU / / display CPU related overhead information IPC / / display send and receive related overhead information MEMORY / / display memory related overhead information PAGE FAULTS / / display page error related overhead information SOURCE / / display and source_function,source_file Source_line related overhead information SWAPS / / displays information related to the number of exchanges, such as one or more of the following situations It shows that the execution performance of SQL is extremely low and needs to be optimized urgently. * converting HEAP to MyISAM / / query results are too large to use. * Creating tmp table / / create temporary tables to disk: copy data to temporary tables, delete them after use * Copying to tmp table on disk / / copy temporary tables in memory to disk, dangerous * locked / / deadlock occurs

Diagnose SQL by querying data tables (the second query method)

Select * from information_schema.profiling

Global query log (the second SQL diagnostic method)

This method is relatively simple to diagnose (few parameters, suitable for locating problematic SQL) and record it to the database (it is recommended to do it only in the test library environment)

Set up

Mode 1: command line 1. Set global general_log = 1 TABLE'; 2. Set global log_output = 'TABLE'; mode 2: configuration file * vim my.cnfgeneral_log = 1general_log_file = / path/logfilelog_output = FILE* restart the MySQL service

Diagnosing SQL

Select * from mysql.general_log

Thank you for reading this article carefully. I hope the article "how to use mysql's slow query Analysis and tuning tool show profile" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.