MySQL itself supports slow query logging, which can be configured to log queries that exceed a specific time in SQL queries. This is a very effective tool in optimizing SQL queries to help us quickly locate the SQL that has the problem. Variables
MySQL has a log, called the slow query log , is mainly used to record some time-consuming queryFor With this log we can analyze what operations are impacting performance, and we need toPerform some optimization measures.View open StatusAbove is the
1 Slow query definitionmeans that MySQL logs all SQL statements that execute a time threshold that exceeds the Long_query_time parameter setting. The slow query log is the log that logs these SQL.2 Turn on slow query logLocate the MySQL
Mysqlsla is a MySQL log analysis tool launched by hackmysql.com, which is very powerful. Data reports are very useful for analyzing the causes of slow queries, including frequency of execution, amount of data, query consumption, etc.First, use
=========================================================Method One: This method I am using, hehe, compare like this kind of instant sex.Versions above Mysql5.0 can support the recording of slower-performing SQL statements.Mysql> Show variables like
MySQL's "slow query" refers to an SQL statement that exceeds the maximum allowable query time ( long_query_time ), and the "unused index" query, by definition, is the SQL statement that the query statement does not use to the index.Slow query
First, open the slow query log1. Query slow query time default 10 seconds like "Long_query_time"2. Query slow query log file address like "%slow%"3. If the slow query log is not turned on, configure the item under My.ini [MYSQLD] and restart the
Briefly:The method of analyzing the performance of MySQL statement query in addition to using the EXPLAIN output execution plan, you can also let the MySQL record query over a specified time of the statement, we will exceed the specified time of the
Summary of common MySQLDBA manuals. For more information about using mysql, see.
MySQL DBA common manual summary, mysql friends can refer.
1. mysql remote connection commands can be used to remotely import and export data
Mysqldump --
MySQL database administrator common command manual bitsCN.com
1. mysql remote connection commands can be used to remotely import and export data
mysqldump --default-character-set=gb2312 -h255.255.000.00 -uroot -pxxxxxx
MySQL log-slow query log (slow-query-log)Slow query log: All SQL statements that run longer than long_query_time in MySQL slow query log records help you find slow SQL statements, so that we can optimize these SQL statements. Slow query log
Recently, a development team complained that the mysqlcluster on our platform was not good and always reported mysql heartbeatError. after analyzing the logs they collected, they did not find any network problems between mysqlcluster nodes, however,
Mysqldumpslow:Died at/usr/local/mysql/bin/mysqldumpslow line 161, <> chunk 236.
Conclusion: The number of top jobs is too large, and mysqldumpslow cannot be traversed.
/Usr/local/mysql/bin/mysqldumpslow-s-t 15/root/db01-102-slow.log
1. Try removing
1. mysql remote connection commands can be used to remotely import and export data
Mysqldump -- default-character-set = gb2312-hsf-0000000.00-uroot-pxxxxxx mydatabase> d: \ data. SQL
Export the specified database to an external SQL file!
Restore
MySQL Log System details
All those who have done large systems know that the log function is not to be underestimated. In the middle and late stages of the project, the optimization and upgrade of the project are based on the log to make the upgrade
1. mysql remote connection commands can be used to remotely import and export data
Mysqldump -- default-character-set = gb2312-hsf-0000000.00-uroot-pxxxxxx mydatabase> d: \ data. SQL
Export the specified database to an external SQL file!
Restore
MySQL slow query record logs are useful for tracking MySQL load tuning issues in the PHP + MySQL system, such as installing a lot of Discuz! Plug-in users, so that you can probably find out which plug-ins have code problems. In fact, it is very easy
MySQL Slow query log is a log record provided by MySQL, which is used to record the corresponding time in MySQL over the threshold of the statement, that is, the running time exceeds the long_query_time value of SQL, will be recorded in the slow
Slow query log in MySQL is a very important feature, we can turn on the MySQL slow query log function, so that we can analyze the state and performance of each SQL execution to optimize.One, slow query log configurationTo turn on the slow query log,
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.