Mysql slow query and slow query log analysis

As we all know, in the case of large traffic, can add node or change the schema can effectively alleviate the database pressure, but all the origin, all from a single MySQL start. The following summarizes some of the experiences that have been used or studied, and some optimizations are made to MySQL, starting with the configuration and adjusting the index.
The first step should be to troubleshoot the problem and find the bottleneck, so start with the log
Turn on slow query log

Mysql>Show variables like“%Slow%"; To view the slow query configuration, no is added in My.cnf, as followsLog-Slow-Queries= /Data/Mysqldata/Slowquery.Log#日志目录long_query_time= 1#记录下查询时间查过1秒Log-Queries-not-Using-Indexes #表示记录下没有使用索引的查询

Analysis Log –mysqldumpslow
Analysis log, available with MySQL Mysqldumpslow, easy to use, parameters can be help view

#-s: How to sort. C, T, L, R indicates the number of records, the time, the query time, the number of records returned; # AC, at, AL, AR indicates the corresponding flashback;-T: Returns the data for the previous number of bars;-g: Contains what, case insensitive; Mysqldumpslow-S R-TTen  /Slowquery.Log#slow记录最多的10个语句mysqldumpslow-S T-TTen -G " Left Join"/Slowquery.Log#按照时间排序前10中含有 " Left Join"Of

Recommended Analysis Log Tool –mysqlsla

wget http://Hackmysql.com/Scripts/Mysqlsla-2.03. Tar.gztar zvxf Mysqlsla-2.03. TAR.GZCD Mysqlsla-2.03Perl makefile.plmakemake Installmysqlsla/Data/Mysqldata/Slow.Log# Mysqlsla will automatically determine the log type, in order to facilitate the establishment of a configuration file "~/. Mysqlsla "# write in the file:Top= -, the first 100 results are printed.

Mysql slow query and slow query log analysis