免费视频淫片aa毛片_日韩高清在线亚洲专区vr_日韩大片免费观看视频播放_亚洲欧美国产精品完整版

mysql 關(guān)于慢日志參數(shù)

| general_log                     | OFF                                   |  是否開(kāi)啟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                 默認(rèn)10秒以上的都會(huì)記錄為慢日志
| slow_query_log                  | ON                                    | 是否開(kāi)啟slow_log
| slow_query_log_file             | /home/mysqllog/mysqld-slow 慢日志文件的地址
分析線(xiàn)上執(zhí)行慢的sql（開(kāi)啟slow_query_log時(shí)間短，之后要關(guān)閉，由于數(shù)據(jù)會(huì)比較大）
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形式記錄日志，所以我們?cè)趘im /home/mysqllog/mysqld-slow將會(huì)看到執(zhí)行超過(guò)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會(huì)以表的形式將slow_Log存入mysql表中的slow_log表中。
同理general_log  也是這樣的道理


mysqldumpslow --verbose -s c -t 15 mysqld-slow  查詢(xún)sql中執(zhí)行最頻繁的前15個(gè)sql
Count: 1（執(zhí)行次數(shù)）  Time=2514.17s (2514s) （執(zhí)行時(shí)間） Lock=0.00s (0s) （鎖時(shí)間） Rows=0.0 (0)（返回多少行）, root[root]@localhost （哪里連過(guò)來(lái)的）
  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：遇到問(wèn)題解決

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)