|
mysql 关于慢日志参数 | general_log | OFF | 是否开启general_log | general_log_file | /var/run/mysqld/mysqld.log | general_log文件存哪里 | log_output | FILE | 以什么方式输出Log | log_slow_queries | ON | 是否指定日志文件名 | long_query_time | 10.000000 默认10秒以上的都会记录为慢日志 | slow_query_log | ON | 是否开启slow_log | slow_query_log_file | /home/mysqllog/mysqld-slow 慢日志文件的地址 分析线上执行慢的sql（开启slow_query_log时间短，之后要关闭，由于数据会比较大） mysql> set global slow_query_log = 1; Query OK, 0 rows affected (0.00 sec) mysql> set global long_query_time = 0; Query OK, 0 rows affected (0.00 sec) flush tables； mysql> show global variables like'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | FILE | +---------------+-------+ 1 row in set (0.01 sec) use vtweb_mddb; mysql> select * from meta; 959840 rows in set (21.70 sec) 由于log_output 用FILE形式记录日志，所以我们在vim /home/mysqllog/mysqld-slow将会看到执行超过0秒的所有的sql set global log_output =’TABLE‘; mysql> SHOW GLOBAL VARIABLES LIKE 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+ 1 row in set (0.00 sec) 如果log_output 用TABLE形式记录日志，mysql会以表的形式将slow_Log存入mysql表中的slow_log表中。 同理general_log 也是这样的道理 mysqldumpslow --verbose -s c -t 15 mysqld-slow 查询sql中执行最频繁的前15个sql Count: 1（执行次数） Time=2514.17s (2514s) （执行时间） Lock=0.00s (0s) （锁时间） Rows=0.0 (0)（返回多少行）, root[root]@localhost （哪里连过来的） update daily_views_cnts0319 a,upgrade_metainfo b set upgrade_metainfo_id = b.vddb_meta_id where b.id = a.tracking_meta_id root@bingo:/home/mysqllog# mysqldumpslow --help Usage: mysqldumpslow [ OPTS... ] [ LOGS... ] Parse and summarize the MySQL slow query log. Options are --verbose verbose --debug debug --help write this text to standard output -v verbose -d debug -s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default al: average lock time ar: average rows sent at: average query time c: count l: lock time r: rows sent t: query time -r reverse the sort order (largest last instead of first) -t NUM just show the top n queries -a don't abstract all numbers to N and strings to 'S' -n NUM abstract numbers with at least n digits within names -g PATTERN grep: only consider stmts that include this string -h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard), default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l don't subtract lock time from total time PS：遇到问题解决 root@(none) 07:12:22>set global slow_query_log = 1; ERROR 13 (HY000): Can't get stat of './mysql/slow_log.CSV' (Errcode: 2) /mysqldata/mysql# touch slow_log.CSV /mysqldata/mysql# chown -R mysql:mysql slow_log.CSV mysqldata/mysql# chmod 660 slow_log.CSV root@(none) 07:13:07> root@(none) 07:15:49>set global slow_query_log = 1; Query OK, 0 rows affected, 1 warning (0.17 sec) root@(none) 07:23:02>show warnings; +-------+------+--------------------------------------------------------------+ | Level | Code | Message | +-------+------+--------------------------------------------------------------+ | Error | 1194 | Table 'slow_log' is marked as crashed and should be repaired | +-------+------+--------------------------------------------------------------+ 1 row in set (0.00 sec) root@(none) 07:23:10>repair table mysql.slow_log; +----------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +----------------+--------+----------+----------+ | mysql.slow_log | repair | status | OK | +----------------+--------+----------+----------+ 1 row in set (0.61 sec) root@(none) 07:23:43>set global slow_query_log = 1; Query OK, 0 rows affected (0.00 sec) |
|
|
