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)
本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请
点击举报。