MySQL性能分析和优化-part 1

MySQL性能优化

平时我们在使用MySQL的时候,怎么评估系统的运行状态,怎么快速定位系统瓶颈,又如何快速解决问题呢?

本文总结了多年来MySQL优化的经验,系统介绍MySQL优化的方法。

OS性能分析

使用top观察top cpu/memory进程

  ~ top
top - 09:34:29 up 10 days, 20:11,  1 user,  load average: 0.61, 0.59, 0.60
Tasks: 208 total,   1 running, 207 sleeping,   0 stopped,   0 zombie
%Cpu0  :  7.0 us,  2.3 sy,  0.0 ni, 90.0 id,  0.3 wa,  0.3 hi,  0.0 si,  0.0 st
%Cpu1  :  3.0 us,  2.7 sy,  0.7 ni, 91.3 id,  2.0 wa,  0.3 hi,  0.0 si,  0.0 st
KiB Mem:   4046820 total,  3851092 used,   195728 free,   133212 buffers
KiB Swap:        0 total,        0 used,        0 free.   270700 cached Mem

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 1035 root      20   0  436892 260444   2444 S   4.0  6.4  91:51.04 celery
 9644 root      25   5  845648 120256   1808 S   1.3  3.0  60:00.06 python
 3718 root      20   0  808752   6292   2224 S   0.7  0.2  27:55.43 AliHids
 4322 root      20   0  250296   5020   2016 S   0.7  0.1   2:28.77 zabbix_server
16331 admin     20   0 1575644 307948   1252 S   0.7  7.6   5:04.47 ruby2.1
22518 997       20   0  759948 251540   1184 S   0.7  6.2  73:24.72 mysqld
    7 root      20   0       0      0      0 S   0.3  0.0  16:37.29 rcu_sched
    8 root      20   0       0      0      0 S   0.3  0.0  15:10.74 rcuos/0
top - 09:35:29 up 10 days, 20:12,  1 user,  load average: 0.22, 0.48, 0.56
Tasks: 207 total,   4 running, 203 sleeping,   0 stopped,   0 zombie
%Cpu(s):  4.5 us,  2.5 sy,  0.7 ni, 90.8 id,  1.2 wa,  0.3 hi,  0.0 si,  0.0 st

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
13103 mysql     20   0 1752220 580048   3852 S   0.3 14.3   5:01.95 mysqld
16331 admin     20   0 1575644 307948   1252 S   0.0  7.6   5:04.62 ruby2.1
 1035 root      20   0  436892 260444   2444 S   2.0  6.4  91:52.69 celery
 1034 root      20   0  433440 256924   2436 S   0.0  6.3  92:49.27 celery
13841 admin     20   0  681696 254392   2364 S   0.0  6.3   0:03.20 ruby2.1
13844 admin     20   0  681564 254180   2360 S   0.0  6.3   0:03.21 ruby2.1
13838 admin     20   0  680188 252484   2276 S   0.0  6.2   0:02.68 ruby2.1
22518 997       20   0  759948 251536   1180 S   0.7  6.2  73:24.95 mysqld
13189 admin     20   0  675812 250620   2448 S   0.0  6.2   0:21.44 ruby2.1
 9644 root      25   5  845648 120300   1808 S   2.0  3.0  60:01.63 python

使用mpstat观察每个CPU核心的CPU使用情况

  ~ mpstat -P ALL 1
Linux 3.13.0-32-generic (dtstack-dev1)     04/16/2016     _x86_64_    (2 CPU)

09:36:22 AM  CPU    %usr   %nice    %sys %iowait    %irq   %soft  %steal  %guest  %gnice   %idle
09:36:23 AM  all    3.02    0.50    2.01    1.01    0.00    0.00    0.00    0.00    0.00   93.47
09:36:23 AM    0    3.00    0.00    1.00    0.00    1.00    0.00    0.00    0.00    0.00   95.00
09:36:23 AM    1    2.97    0.99    2.97    1.98    0.00    0.00    0.00    0.00    0.00   91.09

使用iostat观察系统io状况

  ~ iostat -kx 3
Linux 3.13.0-32-generic (dtstack-dev1)     04/16/2016     _x86_64_    (2 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           6.86    0.18    3.08    3.41    0.09   86.39

Device:         rrqm/s   wrqm/s     r/s     w/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
xvda              1.13    18.64   14.80   50.55   237.69   804.84    31.91     1.38   21.16   58.68   10.18   1.14   7.48

使用sar -n DEV观察网卡流量

  ~ sar -n DEV 3
Linux 3.13.0-32-generic (dtstack-dev1)     04/16/2016     _x86_64_    (2 CPU)

09:40:02 AM     IFACE   rxpck/s   txpck/s    rxkB/s    txkB/s   rxcmp/s   txcmp/s  rxmcst/s   %ifutil
09:40:05 AM veth9b40f2c     68.67     64.67      5.81      6.50      0.00      0.00      0.00      0.00
09:40:05 AM      eth0     76.33     69.00      5.26      5.79      0.00      0.00      0.00      0.00
09:40:05 AM      eth1    162.33    159.00     26.10     27.22      0.00      0.00      0.00      0.00
09:40:05 AM        lo    133.00    133.00     30.23     30.23      0.00      0.00      0.00      0.00

使用vmstat查看系统内存使用情况

  ~ vmstat -w 3 100
procs ---------------memory-------------- ---swap-- -----io---- -system-- ------cpu-----
 r  b     swpd     free     buff    cache   si   so    bi    bo   in   cs us sy id wa st
 0  0        0   265108   119284   218556    0    0   119   403   19    1  7  3 86  3  0
 0  0        0   261616   119316   222416    0    0     4  6096 1198 1732  8  4 81  7  0

查看系统日志

  ~ tail -2 /var/log/kern.log
Apr 16 04:07:18 localhost kernel: [917047.906866] Out of memory: Kill process 2063 (ruby2.1) score 84 or sacrifice child
Apr 16 04:07:18 localhost kernel: [917047.907696] Killed process 2063 (ruby2.1) total-vm:710128kB, anon-rss:342320kB, file-rss:0kB

使用dstat

  ~ dstat -a -i --tcp --vm
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- ----interrupts--- ----tcp-sockets---- -----virtual-memory----
usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw |  77    78    79 |lis act syn tim clo|majpf minpf alloc  free
  7   3  86   3   0   0| 475k 1610k|   0     0 |   0     0 |1138  1972 |  70   127    68 | 20 117   0 545   0|   5  6100  3234  3242
  2   2  95   1   1   0|8192B  928k| 149k  138k|   0     0 |1005  2396 |  30    97    10 | 20 117   1 546   0|   0  1357   544   818
  2   2  94   2   0   0|   0   984k|  23k   27k|   0     0 | 958  1687 |  39   198     8 | 20 118   0 547   0|   0  2880  1324  1053

记录和查看历史数据

  ~ dpkg -l | grep sysstat
ii  sysstat                             10.2.0-1                         amd64        system performance tools for Linux

  cron.d cat /etc/cron.d/sysstat
# The first element of the path is a directory where the debian-sa1
# script is located
PATH=/usr/lib/sysstat:/usr/sbin:/usr/sbin:/usr/bin:/sbin:/bin

# Activity reports every 10 minutes everyday
5-55/10 * * * * root command -v debian-sa1 > /dev/null && debian-sa1 1 1

# Additional run at 23:59 to rotate the statistics file
59 23 * * * root command -v debian-sa1 > /dev/null && debian-sa1 60 2

查看昨天的数据

  cron.d sar -u -1 | head
Linux 3.13.0-32-generic (dtstack-dev1)     04/15/2016     _x86_64_    (2 CPU)

12:00:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
12:05:01 AM     all      9.75      0.78      3.99      5.06      0.10     80.31
12:15:01 AM     all      9.59      0.83      3.84      4.27      0.10     81.37
12:25:01 AM     all      8.90      0.83      3.78      4.22      0.10     82.17

查看swap

  cron.d sar -W | head
Linux 3.13.0-32-generic (dtstack-dev1)     04/16/2016     _x86_64_    (2 CPU)

12:00:01 AM  pswpin/s pswpout/s
12:05:01 AM      0.00      0.00
12:15:01 AM      0.00      0.00
12:25:01 AM      0.00      0.00

查看load

  cron.d sar -q | head
Linux 3.13.0-32-generic (dtstack-dev1)     04/16/2016     _x86_64_    (2 CPU)

12:00:01 AM   runq-sz  plist-sz   ldavg-1   ldavg-5  ldavg-15   blocked
12:05:01 AM         4       511      0.14      0.34      0.39         0
12:15:01 AM         6       512      0.14      0.30      0.37         0
12:25:01 AM         3       507      0.41      0.40      0.41         0

查看IO使用情况

  cron.d sar -d | head
Linux 3.13.0-32-generic (dtstack-dev1)     04/16/2016     _x86_64_    (2 CPU)

12:00:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
12:05:01 AM  dev202-0     81.14    143.50   2076.97     27.37      0.61      7.47      1.36     11.00
12:15:01 AM  dev202-0     62.26     47.31   1836.64     30.26      0.56      8.97      1.29      8.02
12:25:01 AM  dev202-0     67.85     46.25   1904.55     28.75      0.52      7.59      1.28      8.72

查看内存使用情况

  cron.d sar -r | head
Linux 3.13.0-32-generic (dtstack-dev1)     04/16/2016     _x86_64_    (2 CPU)

12:00:01 AM kbmemfree kbmemused  %memused kbbuffers  kbcached  kbcommit   %commit  kbactive   kbinact   kbdirty
12:05:01 AM    238284   3808536     94.11     95652    180648   6231508    153.99   3486028    135316         0
12:15:01 AM    280972   3765848     93.06     82324    173376   6215156    153.58   3437744    142076         0
12:25:01 AM    240208   3806612     94.06    110392    190312   6195792    153.10   3467544    153012         0

查看多核CPU使用情况

  cron.d sar -u -P ALL | head
Linux 3.13.0-32-generic (dtstack-dev1)     04/16/2016     _x86_64_    (2 CPU)

12:00:01 AM     CPU     %user     %nice   %system   %iowait    %steal     %idle
12:05:01 AM     all      7.31      0.80      3.59      4.41      0.10     83.80
12:05:01 AM       0      7.89      0.93      3.95      6.55      0.06     80.61
12:05:01 AM       1      6.72      0.68      3.24      2.26      0.13     86.97

MySQL性能分析

配置slowlog

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like '%slow%';
+---------------------------+--------------------------------------+
| Variable_name             | Value                                |
+---------------------------+--------------------------------------+
| log_slow_admin_statements | OFF                                  |
| log_slow_slave_statements | OFF                                  |
| slow_launch_time          | 2                                    |
| slow_query_log            | OFF                                  |
| slow_query_log_file       | /var/lib/mysql/dtstack-dev1-slow.log |


mysql> set global slow_query_log=ON;
Query OK, 0 rows affected (0.06 sec)

mysql> set global long_query_time=0.01;
Query OK, 0 rows affected (0.02 sec)

分析slow log

# User@Host: dtlog[dtlog] @  [10.0.0.5]  Id: 279883
# Query_time: 0.012148  Lock_time: 0.000088 Rows_sent: 0  Rows_examined: 0
use dtops_perf;
SET timestamp=1460773285;
insert into dtops_process_stat(user_id, tenant_id,
            host_name, proc_exec, proc_args, proc_cnt, cpu_used, io_read_mb, io_write_mb, mem_rss_mb, check_time
        ) values (
            1,1,'iZ233z4w1cdZ', 'python', '/usr/bin/python manage.py runserver 0.0.0.0:8000 ', '1', '0.71', '0.0', '0.02734375', '490.796875', '2016-04-16 02:21:25.636334'
        );

使用pk-query-digest

  mysql pt-query-digest dtstack-dev1-slow.log | head -100

# 540ms user time, 20ms system time, 26.32M rss, 84.35M vsz
# Current date: Sat Apr 16 10:30:05 2016
# Hostname: dtstack-dev1
# Files: dtstack-dev1-slow.log
# Overall: 1.04k total, 46 unique, 0.00 QPS, 0.00x concurrency ___________
# Time range: 2016-02-18 18:00:39 to 2016-04-16 10:29:08
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time         15021s     3ms    110s     14s     40s     15s     15s
# Lock time          5141s       0     69s      5s     18s      7s      1s
# Rows sent         92.31k       0   6.53k   91.15  329.68  313.17       0
# Rows examine     785.62k       0 360.68k  775.77  329.68  14.45k       0
# Query size       265.90k       6   1.69k  262.57  874.75  332.24   56.92

# Profile
# Rank Query ID           Response time   Calls R/Call  V/M   Item
# ==== ================== =============== ===== ======= ===== ============
#    1 0x8D6600C5091DA2FB 2545.6864 16.9%    83 30.6709  3.77 INSERT dtops_mysql_stat
#    2 0xFA23984B8E060B24 2179.4857 14.5%    53 41.1224 10.99 SELECT INFORMATION_SCHEMA.INNODB_METRICS
#    3 0x28FC5B5D583E2DA6 1773.7506 11.8%    83 21.3705  0.48 SHOW GLOBAL STATUS
#    4 0xE2A7DFF3037A3A81 1537.1841 10.2%    80 19.2148  0.53 SHOW /*!50003 GLOBAL*/ STATUS
#    5 0xAC0F6B2C861D21D7  826.3786  5.5%    31 26.6574  6.46 SELECT ecs_ecsinstance
#    6 0x026EA5484A97C3C0  801.5408  5.3%    31 25.8562  3.18 SELECT information_schema.processlist
#    7 0x94F6F954ED486D6F  791.8242  5.3%    23 34.4271  9.62 SELECT ecs_ecsdisk
#    8 0xD8AB5099D4B1CC21  675.0423  4.5%    30 22.5014  0.57 SELECT auth_user
#    9 0xDE28A3CBA34F3335  589.1568  3.9%    26 22.6599  1.72 SHOW /*!40003 GLOBAL*/ VARIABLES
#   10 0x0ED17AA0B68A517F  521.2626  3.5%    29 17.9746 10.80 INSERT dtops_linux_stat
#   11 0x53168008EC50F512  358.3756  2.4%    29 12.3578  0.17 SELECT
#   12 0x3EB3BC982F72ED66  319.3946  2.1%    13 24.5688  1.07 INSERT dtops_disk_free
#   13 0x50C47AC11BB2ABD8  280.2534  1.9%    14 20.0181  2.62 SELECT ecs_hourinstancemonitordata
#   14 0x3783A78AF527ACA4  207.8257  1.4%    10 20.7826  2.79 SELECT dtops_host_list
#   15 0xEF2E30AE81C60FE3  192.6594  1.3%    18 10.7033 13.90 INSERT dtops_disk_stat
#   16 0xEE7686F2E0817183  181.4489  1.2%     7 25.9213  5.01 SELECT ecs_ecsinstancestatistic
#   17 0x6238F0E765A0C029  133.1747  0.9%     5 26.6349  0.99 SELECT information_schema.innodb_trx information_schema.innodb_lock_waits
#   18 0x577997B04D055D37  114.8435  0.8%     5 22.9687  2.10 SHOW TABLES
#   19 0xED823F6211063056  106.3436  0.7%     4 26.5859  3.22 UPDATE ecs_ecsinstancestatistic
#   20 0xD2BA5922D8365D80   86.9031  0.6%     2 43.4515  0.16 SELECT rds_rdssqlreport
# MISC 0xMISC              798.2521  5.3%   461  1.7316   0.0 <26 ITEMS>

# Query 1: 0.00 QPS, 0.00x concurrency, ID 0x8D6600C5091DA2FB at byte 144946
# This item is included in the report because it matches --limit.
# Scores: V/M = 3.77
# Time range: 2016-02-18 22:22:02 to 2016-03-01 14:03:43
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          8      83
# Exec time     16   2546s     13s     83s     31s     42s     11s     29s
# Lock time     30   1549s      1s     69s     19s     32s     11s     17s
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0       0       0       0       0       0       0       0
# Query size    26  71.63k     866     912  883.69  874.75   20.83  874.75
# String:
# Databases    dtops_perf
# Hosts        dtstack-dev1
# Users        lazydba
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s
#  10s+  ################################################################
# Tables
#    SHOW TABLE STATUS FROM `dtops_perf` LIKE 'dtops_mysql_stat'\G
#    SHOW CREATE TABLE `dtops_perf`.`dtops_mysql_stat`\G
insert into dtops_mysql_stat(user_id, tenant_id, instance_name, innodb_rows_deleted, threads_connected, innodb_buffer_pool_pages_flushed, ins, innodb_os_log_written, innodb_rows_updated, innodb_data_fsyncs, select_scan, questions, innodb_log_writes, innodb_data_read, qps, open_files, sel, recv, threads_running, innodb_rows_read, innodb_rows_inserted, innodb_row_lock_waits, sort_rows, innodb_row_lock_time_avg, upd, open_tables, innodb_data_written, sqps, innodb_data_writes, tps, innodb_bp_reads, innodb_buffer_pool_pages_dirty, del, innodb_data_reads, innodb_bp_read_requests, sent, innodb_os_log_fsyncs, check_time)
    values(1,1,'dtstack-dev:3306', '0', '14', '1', '0', '204', '0', '1', '2', '8', '0', '0', '2', '-1', '2', '690', '5', '0', '0', '0', '0', '0', '0', '0', '39628', '0', '1', '0', '0', '0', '0', '0', '3', '14535', '0', '2016-02-26 16:31:50.036858')\G

TCPDUMP + pt-query-digest

# tcpdump -s 65535 -i any -c 1000 port 3306 -w tmp.pcap
tcpdump: listening on any, link-type LINUX_SLL (Linux cooked), capture size 65535 bytes
1000 packets captured
1323 packets received by filter
0 packets dropped by kernel
# tcpdump -r tmp.pcap  -s 65535 -x -nn -q -tttt | pt-query-digest --type tcpdump
reading from file tmp.pcap, link-type LINUX_SLL (Linux cooked)
Reading from STDIN ...
TCP session 192.168.0.1:53744 had errors, will save them in /tmp/pt-query-digest-errors.dYTKmpS

# 480ms user time, 20ms system time, 28.12M rss, 86.31M vsz
# Current date: Sat Apr 16 10:57:03 2016
# Hostname: dtstack-dev1
# Files: STDIN
# Overall: 131 total, 38 unique, 28.04 QPS, 0.03x concurrency ____________
# Time range: 2016-04-16 10:56:21.810539 to 10:56:26.482281
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          128ms       0    18ms   976us     5ms     3ms    98us
# Rows affecte          51       0      10    0.39    0.99    1.37       0
# Query size        14.78k       6     920  115.52  537.02  204.71   26.08
# Warning coun           0       0       0       0       0       0       0
# Boolean:
# No index use  12% yes,  87% no

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x5D51E5F01B88B79E  0.0667 52.1%    12 0.0056  0.01 ADMIN CONNECT
#    2 0x11D0C2162F1C5721  0.0216 16.9%     2 0.0108  0.01 INSERT dtops_disk_stat
#    3 0x0ED17AA0B68A517F  0.0154 12.0%     2 0.0077  0.00 INSERT dtops_linux_stat
#    4 0x0E9D1E6D473DBD20  0.0052  4.1%     1 0.0052  0.00 SELECT hosts items functions triggers
#    5 0x251BDDE46C5899A9  0.0034  2.6%     1 0.0034  0.00 SELECT hosts items functions triggers
#    6 0x3AEAAD0E15D725B5  0.0015  1.2%    24 0.0001  0.00 SET
#    7 0x4256B25BD80D888C  0.0013  1.0%     4 0.0003  0.00 SELECT dtuic_userprofile
#    8 0x860902B0157FAFD9  0.0012  0.9%     5 0.0002  0.00 INSERT history_uint
#    9 0x60038AB9468C9A61  0.0009  0.7%     1 0.0009  0.00 SELECT items hosts
#   10 0x8A10718E7D316421  0.0009  0.7%     8 0.0001  0.00 BEGIN
#   11 0xCC47B42511EA22DD  0.0009  0.7%    12 0.0001  0.00 SET
#   12 0x3B93991D5396A384  0.0008  0.6%    12 0.0001  0.00 SET
#   13 0x2719E98AB2E682FE  0.0007  0.5%     2 0.0003  0.00 UPDATE hosts
#   14 0xAC7F5F65B4A51C07  0.0006  0.5%     8 0.0001  0.00 COMMIT
#   15 0xBF081E5AB46DF811  0.0006  0.4%     1 0.0006  0.00 SELECT httptest hosts
# MISC 0xMISC              0.0063  5.0%    36 0.0002   0.0 <23 ITEMS>

# Query 1: 12.87 QPS, 0.07x concurrency, ID 0x5D51E5F01B88B79E at byte 1657076
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2016-04-16 10:56:23.901443 to 10:56:24.833671
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          9      12
# Exec time     52    67ms   279us    18ms     6ms    16ms     7ms   445us
# Rows affecte   0       0       0       0       0       0       0       0
# Query size     2     360      30      30      30      30       0      30
# Warning coun   0       0       0       0       0       0       0       0
# String:
# Databases    dtuicmysq... (8/66%),  (4/33%)
# Hosts        192.168.1.3 (8/66%), 218.74.37.4 (4/33%)
# Users        dtcloud_de... (8/66%), dtlog (4/33%)
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms
#  10ms  ################################
# 100ms
#    1s
#  10s+
administrator command: Connect\G

# Query 2: 2.57 QPS, 0.03x concurrency, ID 0x11D0C2162F1C5721 at byte 1660770
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2016-04-16 10:56:24.096396 to 10:56:24.874351
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1       2
# Exec time     16    22ms     5ms    16ms    11ms    16ms     8ms    11ms
# Rows affecte   3       2       1       1       1       1       0       1
# Query size     6     962     481     481     481     481       0     481
# Warning coun   0       0       0       0       0       0       0       0
# String:
# Databases
# Hosts        123.74.33.4
# Users        dtlog
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `1` LIKE 'dtops_disk_stat'\G
#    SHOW CREATE TABLE `1`.`dtops_disk_stat`\G

        insert into dtops_disk_stat(
            user_id, tenant_id, host_name, disk_name,
            rd_s, rd_avgkb, rd_mb_s, rd_mrg_s, rd_cnc, rd_rt,
            wr_s, wr_avgkb, wr_mb_s, wr_mrg_s, wr_cnc, wr_rt,
            busy, in_prg, io_s, qtime, stime,
            check_time)
        values
            (1, 1, 'test01', 'sda', '0.0','0.0','0.0','0.0','0.0','0.0','6.4','11.56','0.07','1.0','0.07','10.0','6.48','0.0','6.4','1.24','8.76','2016-04-16 02:54:12.369120')
    \G

通过processlist查看系统当前运行情况

mysql> select * from information_schema.processlist where command !='Sleep' order by time desc limit 10\G
*************************** 1. row ***************************
     ID: 292486
   USER: root
   HOST: localhost
     DB: NULL
COMMAND: Query
   TIME: 0
  STATE: executing
   INFO: select * from information_schema.processlist where command !='Sleep' order by time desc limit 10

通过genenral log查看系统运行的SQL

mysql> show variables like '%general%';
+------------------+---------------------------------+
| Variable_name    | Value                           |
+------------------+---------------------------------+
| general_log      | OFF                             |
| general_log_file | /var/lib/mysql/dtstack-dev1.log |
+------------------+---------------------------------+
2 rows in set (0.00 sec)

mysql> set global general_log=ON;

tail -10f  /var/lib/mysql/dtstack-dev1.log
        294645 Query    SET NAMES utf8
        294645 Query    set autocommit=0
        294645 Query    set autocommit=1
        294645 Query    SET SQL_AUTO_IS_NULL = 0
        294645 Quit
        294646 Connect    user@host on dtuic
        294646 Query    SET NAMES utf8
        294646 Query    set autocommit=0
        294646 Query    set autocommit=1
        294646 Query    SET SQL_AUTO_IS_NULL = 0
        294646 Query    SELECT * FROM `dtuic_userprofile` WHERE `dtuic_userprofile`.`agent_token` = 'xx'
        294646 Quit
        294648 Connect    user@host on dtops_db
        294648 Query    SET NAMES utf8
        294648 Query    SET NAMES utf8
        294648 Query    set autocommit=0
        294648 Query    set autocommit=1
        294648 Query    SET SQL_AUTO_IS_NULL = 0
        294648 Query    insert into dtops_linux_stat(user_id, tenant_id, host_name, load1, load5, load15, cpu_usr, cpu_sys, cpu_idle, cpu_wai, mem_used, mem_free, mem_buff, mem_cache, swap_used, swap_free,  io_read, io_write, io_queue, io_await, io_svctm, io_util, io_read_mb, io_write_mb,  net_recv, net_send, proc_run, proc_blk, proc_new, intr, csw, softirq,  page_in, page_out, swap_in, swap_out, page_fault, page_mjfault,  check_time) values(1, 1, 'test01', '0.63', '0.72', '0.79', '6.44', '1.21', '91.4', '0.95', '9264.0', '281.0', '253.0', '5851.0', '15680.0', '696.0', '0.0', '11.2', '0.0', '13.15', '5.15', '6.8', '0.0', '0.1', '1.2', '1.46', '0.4', '1.0', '0.0', '9953.4', '10081.6', '516.6', '0.0', '102.0', '0.0', '0.0', '13.0', '0.0', '2016-04-16 02:39:03.487738')
        294648 Quit

通过binlog查看系统DML


root@test01:/var/lib/my3307# mysqlbinlog -v mysql-bin.000002 > tmp.sql

root@test01:/var/lib/my3307# head -100 tmp.sql
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#160413 19:28:37 server id 10  end_log_pos 120 CRC32 0xef6cb6a8     Start: binlog v 4, server v 5.6.29-log created 160413 19:28:37
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
ZS0OVw8KAAAAdAAAAHgAAAABAAQANS42LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAai2
bO8=
'/*!*/;
# at 120
#160413 19:28:37 server id 10  end_log_pos 191 CRC32 0xaaca06a8     Previous-GTIDs
# 08eef41e-df72-11e5-be92-0024e84cb72f:1-12
# at 191
#160413 19:29:43 server id 6033  end_log_pos 239 CRC32 0xde16d70b     GTID [commit=yes]
SET @@SESSION.GTID_NEXT= '08eef41e-df72-11e5-be92-0024e84cb72f:13'/*!*/;
# at 239
#160413 19:29:43 server id 6033  end_log_pos 333 CRC32 0x61812d71     Query    thread_id=539    exec_time=0    error_code=0
SET TIMESTAMP=1460546983/*!*/;
SET @@session.pseudo_thread_id=539/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database hell
/*!*/;
# at 333
#160413 20:10:37 server id 10  end_log_pos 381 CRC32 0x590a33e4     GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'c59b9266-0090-11e6-968b-0024e84cb72f:1'/*!*/;
# at 381
#160413 20:10:37 server id 10  end_log_pos 472 CRC32 0x1b5e7367     Query    thread_id=512    exec_time=0    error_code=0
SET TIMESTAMP=1460549437/*!*/;
create database yyy


# at 863
#160413 20:14:21 server id 6033  end_log_pos 906 CRC32 0xfd739e72     Table_map: `xxx`.`a` mapped to number 71
# at 906
#160413 20:14:21 server id 6033  end_log_pos 946 CRC32 0x5d365030     Write_rows: table id 71 flags: STMT_END_F

BINLOG '
HTgOVxORFwAAKwAAAIoDAAAAAEcAAAAAAAEAA3h4eAABYQABAwABcp5z/Q==
HTgOVx6RFwAAKAAAALIDAAAAAEcAAAAAAAEAAgAB//5kAAAAMFA2XQ==
'/*!*/;
### INSERT INTO `xxx`.`a`
### SET
###   @1=100
# at 946

查看MySQL错误日志

mysql> show variables like 'log_error';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| log_error     | /var/log/mysql/error.log |


$ tail -100 /var/log/mysql/error.log

2016-04-12 20:38:21 25937 [Warning] Slave: Operation DROP USER failed for 'monitor'@'127.0.0.1' Error_code: 1396
2016-04-12 20:38:21 25937 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000002' position 2461
2016-04-13 19:28:37 25937 [Note] @@GLOBAL.GTID_PURGED was changed from '' to '08eef41e-df72-11e5-be92-0024e84cb72f:1-12'.
2016-04-13 19:28:37 25937 [Note] @@GLOBAL.GTID_EXECUTED was changed from '' to '08eef41e-df72-11e5-be92-0024e84cb72f:1-12'.
2016-04-13 19:29:04 25937 [Warning] Slave SQL: If a crash happens this configuration does not guarantee that the relay log info will be consistent, Error_code: 0
2016-04-13 19:29:04 25937 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000002' at position 2461, relay log './mysqld-relay-bin.000003' position: 1178
2016-04-13 20:11:50 25937 [Note] Error reading relay log event: slave SQL thread was killed
2016-04-13 20:11:50 25937 [ERROR] Error reading packet from server: Lost connection to MySQL server during query ( server_errno=2013)
2016-04-13 20:11:50 25937 [Note] Slave I/O thread killed while reading event
2016-04-13 20:11:50 25937 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000002', position 346980

查看MySQL状态

mysql> show global status like '%threads_runn%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_running | 1     |
+-----------------+-------+

| Com_select                | 307064  |
| Com_insert                | 203395  |
| Com_delete                | 0       |
| Com_update                | 32287   |


mysql> show global status like '%innodb_rows%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| Innodb_rows_deleted  | 0        |
| Innodb_rows_inserted | 184427   |
| Innodb_rows_read     | 26099334 |
| Innodb_rows_updated  | 32551    |


show global status like '%innodb_buffer_pool_pages%';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_pages_data    | 6901   |
| Innodb_buffer_pool_pages_dirty   | 69     |
| Innodb_buffer_pool_pages_flushed | 205928 |
| Innodb_buffer_pool_pages_free    | 1024   |
| Innodb_buffer_pool_pages_misc    | 266    |
| Innodb_buffer_pool_pages_total   | 8191   |


mysql> show global status like '%innodb_buffer_pool_read%';
+---------------------------------------+----------+
| Variable_name                         | Value    |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_ahead_rnd     | 0        |
| Innodb_buffer_pool_read_ahead         | 2426     |
| Innodb_buffer_pool_read_ahead_evicted | 0        |
| Innodb_buffer_pool_read_requests      | 16119505 |
| Innodb_buffer_pool_reads              | 6115     |

mysql> show global status like '%innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 10    |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 2     |
| Innodb_row_lock_waits         | 22    |


mysqladmin extended-status -i 3 -r  | grep -i innodb_rows
| Innodb_rows_deleted                           | 0           |
| Innodb_rows_inserted                          | 182053      |
| Innodb_rows_read                              | 25740195    |
| Innodb_rows_updated                           | 32287       |
| Innodb_rows_deleted                           | 0           |
| Innodb_rows_inserted                          | 43          |
| Innodb_rows_read                              | 943         |
| Innodb_rows_updated                           | 0           |
| Innodb_rows_deleted                           | 0           |
| Innodb_rows_inserted                          | 8           |
| Innodb_rows_read                              | 390         |
| Innodb_rows_updated                           | 0           |

查看Innodb状态

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2016-04-16 12:02:59 7fef13d0d700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 47 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 15203 srv_active, 0 srv_shutdown, 13276 srv_idle
srv_master_thread log flush and writes: 28472
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 49397
OS WAIT ARRAY INFO: signal count 49296
Mutex spin waits 47682, rounds 1356865, OS waits 44752
RW-shared spins 3909, rounds 116413, OS waits 3858
RW-excl spins 828, rounds 24083, OS waits 755
Spin rounds per wait: 28.46 mutex, 29.78 RW-shared, 29.09 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 65462789
Purge done for trx's n:o < 65461073 undo n:o < 0 state: running but idle
History list length 2881
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 349713, OS thread handle 0x7fef13d0d700, query id 2399249 localhost root init
show engine innodb status
---TRANSACTION 65462442, not started
MySQL thread id 355002, OS thread handle 0x7fef13c76700, query id 2396568 180.173.242.219 dtcloud_dev cleaning up
---TRANSACTION 65396897, not started
MySQL thread id 316553, OS thread handle 0x7fef13e02700, query id 2129942 180.173.242.219 dtcloud_dev cleaning up
---TRANSACTION 65352135, not started
MySQL thread id 288067, OS thread handle 0x7fef13bb2700, query id 1941170 180.173.242.219 dtcloud_dev cleaning up
---TRANSACTION 65327485, not started
MySQL thread id 274710, OS thread handle 0x7fef13c45700, query id 1847425 180.173.242.219 dtcloud_dev cleaning up
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
9793 OS file reads, 457309 OS file writes, 263759 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 18.87 writes/s, 10.87 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 11, seg size 13, 551 merges
merged operations:
 insert 1709, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 266 buffer(s)
13.79 hash searches/s, 21.34 non-hash searches/s
---
LOG
---
Log sequence number 9675441693
Log flushed up to   9675441693
Pages flushed up to 9675441693
Last checkpoint at  9675441693
0 pending log writes, 0 pending chkp writes
243472 log i/o's done, 9.98 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 1945330
Buffer pool size   8191
Free buffers       1024
Database pages     6901
Old database pages 2527
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 20983, not young 80923
0.00 youngs/s, 0.00 non-youngs/s
Pages read 9086, created 2328, written 207366
0.00 reads/s, 0.06 creates/s, 8.60 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 6901, unzip_LRU len: 0
I/O sum[398]:cur[6], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 13103, id 140664767006464, state: sleeping
Number of rows inserted 186160, updated 33021, deleted 0, read 26128219
7.83 inserts/s, 0.00 updates/s, 0.00 deletes/s, 49.34 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.08 sec)

数据库参数调优

MySQL优化器参数

mysql> show variables like '%optimizer%';
+------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name                | Value                                                                                                                                                                                                                                                                                                                                            |
+------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| optimizer_prune_level        | 1                                                                                                                                                                                                                                                                                                                                                |
| optimizer_search_depth       | 62                                                                                                                                                                                                                                                                                                                                               |
| optimizer_switch             | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on |
| optimizer_trace              | enabled=off,one_line=off                                                                                                                                                                                                                                                                                                                         |
| optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on                                                                                                                                                                                                                                                                       |
| optimizer_trace_limit        | 1                                                                                                                                                                                                                                                                                                                                                |
| optimizer_trace_max_mem_size | 16384                                                                                                                                                                                                                                                                                                                                            |
| optimizer_trace_offset       | -1

myisam参数

show variables like '%myisam%';
+---------------------------+----------------------+
| Variable_name             | Value                |
+---------------------------+----------------------+
| myisam_data_pointer_size  | 6                    |
| myisam_max_sort_file_size | 9223372036853727232  |
| myisam_mmap_size          | 18446744073709551615 |
| myisam_recover_options    | OFF                  |
| myisam_repair_threads     | 1                    |
| myisam_sort_buffer_size   | 8388608              |
| myisam_stats_method       | nulls_unequal        |
| myisam_use_mmap           | OFF                  |

mysql> show variables like '%key%';
+--------------------------+----------------------+
| Variable_name            | Value                |
+--------------------------+----------------------+
| delay_key_write          | ON                   |
| foreign_key_checks       | ON                   |
| have_rtree_keys          | YES                  |
| key_buffer_size          | 8388608              |
| key_cache_age_threshold  | 300                  |
| key_cache_block_size     | 1024                 |
| key_cache_division_limit | 100                  |
| max_seeks_for_key        | 18446744073709551615 |

innodb

mysql> show variables like '%innodb_flush%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_timeout    | 1     |
| innodb_flush_log_at_trx_commit | 1     |
| innodb_flush_method            |       |
| innodb_flush_neighbors         | 1     |
| innodb_flushing_avg_loops      | 30    |

mysql> show variables like '%innodb_buffer%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |

mysql> show variables like '%innodb_io%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| innodb_io_capacity     | 200   |
| innodb_io_capacity_max | 2000  |


mysql> show variables like '%innodb%thread%';
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| innodb_purge_threads      | 1     |
| innodb_read_io_threads    | 4     |
| innodb_thread_concurrency | 0     |
| innodb_thread_sleep_delay | 10000 |
| innodb_write_io_threads   | 4     |

mysql> show variables like '%double%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| innodb_doublewrite | ON    |

mysql> show variables like '%innodb_stats%';
+--------------------------------------+-------------+
| Variable_name                        | Value       |
+--------------------------------------+-------------+
| innodb_stats_auto_recalc             | ON          |
| innodb_stats_method                  | nulls_equal |
| innodb_stats_on_metadata             | OFF         |
| innodb_stats_persistent              | ON          |
| innodb_stats_persistent_sample_pages | 20          |
| innodb_stats_sample_pages            | 8           |
| innodb_stats_transient_sample_pages  | 8           |
上一篇:PageAdmin Cms网站管理系统修改上传格式、大小、缩略图和水印等


下一篇:在linux中安装nginx