MySQL tuning series _ log analysis

By Karen Adams,2015-05-09 13:44
17 views 0
MySQL tuning series _ log analysis

    Our fingertips


    Several kinds of logs, this paper mainly summarize the MySQL database is used for routine maintenance in the process of problem solving and performance optimization, etc., slightly, daily accumulation.

    Part of the article to the MySQL database and SQL Server database part to do a comparison, the relative merits of attack, narrative technique, only right to make using of the study.

    Idle time, but it will get into this business directly.

    Technical preparation

    Host in Ubuntu14.04.2 platform, based on MYSQL5.5.46 version.

    Log file records the MySQL database of various kinds of activities, as one of the most commonly used daily positioning problem analysis method, that is commonly used in the MySQL database log file is divided into the following categories: the error log, binary log, slow query log, query log.

    First, the error log

    I remember when writing the SQL Server, I have written an article about some mistakes in the process of SQL Serverstartup log, you canClick on thisTo look at it.Also, in the process of running MySQL database, have their own database error log.

    The log records the MySQL operation process in the process ofstarting, running and shut down some detail records, when once appear problem, to view the log, the log not only recorded the error message, also recorded some warning, of course, there are some information.

    Can use the following command to view the error log file path:

show variables like ' log_error ' ;

    By the above command location on the error log file, if a problem, can in the corresponding up and view the log Server detail, see as follows:

more / var / log /mysql /error. log

    Of course, the above command in the Linux platform, generally used to modify the file, but can also be used as a view file to use.

    And, under the right conditions, MySQL will automatically archive these error log, to find later.

    In the log file above you can see, there is a hint is the IP address of the DNS problem, so I can pass the file to record the login information, etc.

    Second, the slow query log

    The slow query log is used to record in MySQL statement execution speed slow, so this file is very convenient in overall performance tuning, we know that in the SQL Server can find through the corresponding DMV.

    , of course, what kind of statement which can be called a slower, so there is need to have a defined threshold, once run time exceeds this value will be recorded in the slow query log.

    Let's take a look at the threshold Settings, can be set by long_query_time, the default value is 10, mean to run more than 10 s statement.

    By default, Mysql database does notstart the slow query log, we need to manually set this parameter, of course, if not tuning needed, generallystart this parameter is not recommended, because there is a performance impact.

    View, set mode

show variables like ' %long% ' ;

show variables like ' log_slow_queries '

    Let's open the slow query.

SET global slow_query_log = 1

SHOW variables like ' %query_log% ' ;

    Of course, we can dispatch the default time threshold is small, as follows

SET global long_query_time = 0.1 ;

    Verify, here is a little small skills, need to open a new window for the query, there is no effect of the current window to find, don't know calculating do not calculate Mysql a small Bug. We open a new window to verify:

    Isn't it great?We come to a statement under validation, see how it works.

    In order to facilitate presentation, I will be the threshold set to 0.001 S, quite small a value, we test it to find a statement:

    We have to execute the following script:

select * from tables;

    Let's take a look at the slow query log is recorded:

sudo more / var / lib / mysql / wu - virtual - Ubuntu01 - slow. log

    The log view here, need the right operation.

    You can see, the current has put our query statement to output to the log, of course, some of the other statements also be recorded.

    And detailed record of the execution time, the implementation of the user, running time, the lock time, return line information, etc.

    And, of course, there are many students here to demand, generally we run, to monitor Server, don't let me to see files on running the machine, I want to make a monitoring system, need to monitor each missile log files, and parsing.

    In order to solve this problem, thoughtful and MySQL provides us with a system list view, it's convenient for our operation, such as I want to see the slowest in the first 10 statement...

    There needs to be prompt, the method based on MySQL 5..1 above, the process is as follows:

    First of all, let's take a look at the default output mode, the script is as follows:

show variables like ' log_output ' ;

    The default output way can see, here is the FILE, the FILE, we will be here into a Table.

set global log_output = ' TABLE ' ;

select sleep( 10 );

select * from mysql.slow_log;

    Whether very close...You can be checked by T - SQL statements for a variety of.

    In our database optimization, a lot of time by creating a suitable index, optimization, so, if we know that a database of those statements are not applied to the index, or a full table scan, it is very convenient and easy to we optimize.

    So, in the slow logs of Mysql, for we have intimate added a parameter, used to record without using the index statement;

show variables like ' log_queries_not_using_indexes ' ;

    Is off by default, this parameter can be opened, a detailed record;

SET global log_queries_not_using_indexes = 1

    Through the setting of this parameter, you can track the MySQL didn't use the index and run the statement with longer time, everyone understand the following optimization.

    Space is a bit long, here is not demonstrated with everybody.


    This article to the first, about the content of the MYSQL performance tuning scope is very wide, follow-up article analyses in turn.

    If you look at this blog, feel something for you, please don't stingy your "recommended".

Report this document

For any questions or suggestions please email