Processing MySQL slow SQL

Source: Internet
Author: User
Tags mysql slow query log

 
When the slow_query_log value is on (the default value is off, the default value is 10 s, and accurate to microseconds), the default value (when the log_output value is fifl) will record this slow query: the slow_query_log_file value is specified in the file.
Mysql> select @ Global. log_output;
+ --------------------- +
| @ Global. log_output |
+ --------------------- +
| File |
+ --------------------- +
1 row in SET (0.00 Sec)

Note that mysql5.1 supports storing slow query log records in mysq. slow_log, but you need to set the log_output variable value to table:

Mysql> set @ Global. log_output = 'table ';
Error 2006 (hy000): MySQL server has gone away
No connection. Trying to reconnect...
Connection ID: 6
Current Database: *** none ***

Query OK, 0 rows affected (0.00 Sec)
Before enabling slow SQL:
Mysql> show variables like '% slow % ';
+ --------------------- + -------------------------------------- +
| Variable_name | value |
+ --------------------- + -------------------------------------- +
| Log_slow_queries | off |
| Slow_launch_time | 2 |
| Slow_query_log | off |
| Slow_query_log_files |/var/MySQL/data/localhost-slow.log |
+ --------------------- + -------------------------------------- +
4 rows in SET (0.00 Sec)
Enable slow SQL
Mysql> set @ Global. slow_query_log = on;
Query OK, 0 rows affected (0.00 Sec)

Mysql> System CAT/var/MySQL/data/localhost-slow.log
/Usr/local/MySQL/bin/mysqld, version: 5.5.15-log (Source Distribution). Started:
TCP port: 3306 UNIX socket:/tmp/MySQL. Sock
Time id command argument
/Usr/local/MySQL/bin/mysqld, version: 5.5.15-log (Source Distribution). Started:
TCP port: 3306 UNIX socket:/tmp/MySQL. Sock
Time id command argument
/Usr/local/MySQL/bin/mysqld, version: 5.5.15-log (Source Distribution). Started:
TCP port: 3306 UNIX socket:/tmp/MySQL. Sock
Time id command argument
/Usr/local/MySQL/bin/mysqld, version: 5.5.15-log (Source Distribution). Started:
TCP port: 3306 UNIX socket:/tmp/MySQL. Sock
Time id command argument
Mysql>
Go to the/var/MySQL/data/directory and view
Localhost-slow.log mysqldumpslow
[Root @ localhost data] # mysqldumpslow localhost-slow.log

Reading MySQL slow query log from localhost-slow.log
Count: 1 time = 0.00 S (0 s) Lock = 0.00 S (0 s) rows = 0.0 (0), 0users @ 0 hosts

Generally, General log is not enabled for performance purposes. Slow log can locate SQL statements with performance problems, while general log records all SQL statements.
For MySQL, If You Want To enable slow log and general log, you need to restart. From MySQL 5.1.6, general query log and slow query log support writing to files or database tables, in addition, logs can be dynamically modified at the global level when they are enabled and output.
 
Mysql> select version ();
+ ------------ +
| Version () |
+ ------------ +
| 5.5.15-log |
+ ------------ +
1 row in SET (0.00 Sec)
 
Set the log output mode to a file (if log_output = table is set, the log results will be recorded in the table named gengera_log, and the default engine of this table is CSV ):
 
Mysql> set global log_output = file;
Query OK, 0 rows affected (0.00 Sec)
 
Set the log file path for general log:
 
Mysql> set global general_log_file = '/tmp/General. log ';
Query OK, 0 rows affected (0.00 Sec)
 
Enable General log:
 
Mysql> set global general_log = on;
Query OK, 0 rows affected (0.00 Sec)
 
After a while, close the General log:
 
Mysql> set global general_log = off;
Query OK, 0 rows affected (0.01 Sec)
 
Check the tmp/General. log information. You can see which SQL queries, updates, deletes, and inserts frequently. For example, if some tables do not change frequently and the query volume is large, the data can be fully cached. If tables with low requirements on Master/Slave latency, the data can be read to the slave database.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.