读书笔记：MySQL服务器性能调优监控

①CPU

     数据库的应用场景一般分为OLTP（Online Transaction Processing，在线事务处理）和OLAP（Online Analytical Processing，在线分析处理），而这两种场景对CPU的要求也会有所差异。OLAP多用在数据仓库或数据集市中，一般需要执行复杂的SQL计算查询；OLTP多用于日常事务处理，如银行交易、电商、Blog以及网络游戏等。InnoDB存储引擎一般都是应用于OLTP的数据库应用，包括以下特点：

• 高并发
• 事务处理时间短
• 查询语句简单且一般都走索引
• 复杂查询较少

     复杂的查询可能需要执行比较、排序以及连接等非常消耗CPU的操作，这些操作在OLTP数据库应用中较少发生。所以，OLTP数据库应用本身对CPU的要求并不是很高。即OLAP是CPU密集型操作，而OLTP是IO密集型操作。不过，多个CPU或者多核CPU对处理大并发量的请求还是会有帮助。如果CPU是多核的，Windows系统可以修改参数innodb_read_io_threads和innodb_write_io_threads来增加数据库IO线程，以更加充分有效地利用CPU的多核性能。其他的线程，如Purge Thread和Page Cleaner Thread，分别可以通过变量innodb_purge_threads和innodb_page_cleaners设置。

②RAM

     内存的大小最能直接反映数据库的性能。InnoDB存储引擎既缓存数据页，又缓存索引页，这是一个很大的缓冲池，即InnoDB Buffer Pool。如果缓冲池的大小足够大，以至于可以放下所有的数据文件，这样的数据库性能当然是最优的，所有对数据文件的操作都是在内存中进行的。但这只是理想的情况，实际应用中需要通过预估“活跃”数据的大小来确定数据库服务器内存的大小。如何判断内存的设置已经到了瓶颈？可以通过查看当前服务器的状态，比较物理磁盘的读取和内存读取的比例来判断缓冲池的命中率。通常InnoDB存储引擎缓冲池的命中率不应该小于99%。

mysql> SHOW GLOBAL STATUS LIKE 'innodb%read%'\G*************************** 1. row ***************************Variable_name: Innodb_buffer_pool_read_ahead_rnd        Value: 0*************************** 2. row ***************************Variable_name: Innodb_buffer_pool_read_ahead        Value: 0*************************** 3. row ***************************Variable_name: Innodb_buffer_pool_read_ahead_evicted        Value: 0*************************** 4. row ***************************Variable_name: Innodb_buffer_pool_read_requests        Value: 1543*************************** 5. row ***************************Variable_name: Innodb_buffer_pool_reads        Value: 311*************************** 6. row ***************************Variable_name: Innodb_data_pending_reads        Value: 0*************************** 7. row ***************************Variable_name: Innodb_data_read        Value: 5165568*************************** 8. row ***************************Variable_name: Innodb_data_reads        Value: 338*************************** 9. row ***************************Variable_name: Innodb_pages_read        Value: 310*************************** 10. row ***************************Variable_name: Innodb_rows_read        Value: 810 rows in set (0.04 sec)

     上述相关参数的具体含义如下：

• Innodb_buffer_pool_read_requests：The number of logical read requests（从缓冲池中读取页的请求次数页数）。
• Innodb_buffer_pool_reads：The number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk（从物理磁盘上读取页的请求次数页数）。
• Innodb_buffer_pool_read_ahead：The number of pages read into the InnoDB buffer pool by the read-ahead background thread（预读的页数）。
• Innodb_buffer_pool_read_ahead_evicted：The number of pages read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries（预读的页但是没有被查询读取就从缓冲池中被替换的页的数量，用来判断预读的效率）。
• Innodb_data_read：The amount of data read since the server was started(in bytes)（总共读入的字节数）。
• Innodb_data_reads：The total number of data reads(OS file reads)（向操作系统文件发起读取请求的次数，一次可能读取多个页）。
  

     以下公式用于计算各种对缓冲池的操作：

缓冲池命中率 = Innodb_buffer_pool_read_requests / ( Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads )

[ 即： 缓冲池命中率 = 内存读 / ( 内存读 + 预读 + 磁盘读 ) ]

平均每次读取的字节数 = Innodb_data_read / Innodb_data_reads

     即便缓冲池的大小已经大于数据库文件的大小，但并不意味着就没有了磁盘操作。缓冲池只是一个用来存放热点数据的区域，后台线程还会负责将脏页异步刷新回磁盘。除此之外，每次事务提交时还需要将redo log buffer写入重做日志文件。

     InnoDB存储引擎缓冲池的配置调整，包含一系列参数。

• innodb_buffer_pool_size：缓冲池大小。
• innodb_buffer_pool_instances：缓冲池实例个数（默认1个）。
• innodb_log_buffer_size：重做日志缓冲大小。
• innodb_change_buffer_max_size：Change Buffer可使用缓冲池大小（默认25，表示最多使用1/4的缓冲池空间，最大有效值50）。
• innodb_lru_scan_depth：LRU List中页的数量（默认1024）。
• innodb_max_dirty_pages_pct：触发Checkpoint的缓冲池脏页大小值（默认75，表示缓冲池中脏页数量占据75%时强制Checkpoint）。
• innodb_purge_batch_size：Full Purge时回收的undo页数量。

③DISK

     当前大多数数据库系统使用的都是传统的机械磁盘。机械磁盘有两个重要的指标：寻道时间和转速。当前机械硬盘的寻道时间已经能够达到3ms，转速能够达到15000RPM（rotate per minute）。传统机械硬盘的最大问题在于其读写磁头为随机访问。

     通常可以将多块机械硬盘组成RAID（Redundant Array of Independent Disks，独立磁盘冗余数组）来提高数据库的性能，也可以将数据文件分布在不同硬盘上来达到负载均衡。对于数据库应用来说，一般RAID 10是最好的选择，虽然需要较多的硬盘，但是它兼顾了RAID 0的速度和RAID 1的安全。

     基于闪存（Flash Memory）的SSD（Solid-State Disk，固态硬盘）是一种新的存储设备。不同于机械硬盘，闪存是一个完全的电子设备，具有低延迟性、低功耗以及防震性。需要注意的是，闪存提供的读写速度是非对称的。读取速度要远远快于写入速度。因此，对于固态硬盘在数据库中的应用，应该好好利用其读取的性能，避免过多的写入操作。

     可以增加innodb_io_capacity变量的值以充分利用SSD带来的高IOPS。同样，可以关闭刷新邻近页innodb_flush_neighbours为数据库性能带来一定效果的提升。

④OS

     操作系统的选择，首先是要使用64位系统和64位的软件。当然，这是以64位的CPU为前提的。具体来说，Linux是MySQL数据库服务器中最常使用的操作系统，因为开源性Linux有着众多的发行版，在选择数据库服务器的Linux版本的时候，需要考虑更多的是其稳定性，而不是新特性。Solaris也是高性能、高可靠的操作系统，其ZFS文件系统非常适合MySQL数据库应用，可以将它的开源版本OpenSolaris作为一种选择。

⑤FS

     不同文件系统对数据库性能影响的差异并不明显。对DBA来说，文件系统提供的功能是可以关注的。例如，ZFS文件系统本身就可以支持快照，因此就不需要LVM这样的逻辑卷管理工具了。

⑥TOOL

     Unix/Linux系统上，服务器性能相关的监控已经有一些可供使用的命令行工具。

• uptime

     返回服务器的运行时长、登录用户数和服务器负荷（近1、5、15分钟等待CPU处理的进程数）。

[root@bilery ~]# uptime 21:24:39 up 51 min,  2 users,  load average: 0.16, 0.05, 0.06

• vmstat

     返回服务器的进程情况、内存使用情况、交换页和I/O块使用情况、中断以及CPU使用情况。

        Procs
            r: 等待运行的进程数（进程队列）
            b: 处在非中断睡眠状态的进程数（进程阻塞）
        Memory
            swpd: 虚拟内存使用情况（提示物理内存不足），单位：KB
            free: 空闲的内存，单位KB
            buff: 操作系统缓存使用的内存数，单位：KB
            cache: 操作系统缓存使用的内存数，单位：KB
        Swap
            si: 从磁盘交换到内存的交换页数量（提示物理内存不足），单位：KB/秒
            so: 从内存交换到磁盘的交换页数量（提示物理内存不足），单位：KB/秒
        IO
            bi: 发送到块设备的块数，单位：块/秒
            bo: 从块设备接收到的块数，单位：块/秒
        System
            in: 每秒的中断数，包括时钟中断
            cs: 每秒的环境（上下文）切换次数
        CPU
            按 CPU 的总使用百分比来显示
            us: CPU 使用时间（用户）
            sy: CPU 系统使用时间（内核）
            id: 闲置时间
            wa: I/O等待时间

[root@bilery ~]# vmstatprocs -----------memory---------- ---swap-- -----io---- -system-- ------cpu----- r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st 6  0      0 574200    932 535736    0    0    63     6   53   93  1  1 99  0  0

• free

     返回服务器的内存（RAM + SWAP）使用情况。

[root@bilery ~]# free              total        used        free      shared  buff/cache   availableMem:        1867276      801176      481360       10604      584740      828052Swap:       2098172           0     2098172

• top

     服务器运行情况和进程列表的实时动态，是uptime输出 + vmstat输出的cpu部分 + free输出 + 一个“processlist”。

[root@bilery ~]# toptop - 21:58:21 up  1:25,  2 users,  load average: 0.45, 0.22, 0.13Tasks: 176 total,   1 running, 175 sleeping,   0 stopped,   0 zombie%Cpu(s):  1.1 us,  0.6 sy,  0.0 ni, 98.3 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 stKiB Mem :  1867276 total,   479668 free,   802388 used,   585220 buff/cacheKiB Swap:  2098172 total,  2098172 free,        0 used.   826748 avail Mem    PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND          3054 student   20   0 1738792 195176  48852 S  25.0 10.5   1:49.10 gnome-shell      2417 root      20   0  272076  57368  10588 S  18.8  3.1   1:10.37 Xorg             3639 student   20   0  574924  25392  15472 S   6.2  1.4   0:09.65 gnome-terminal-  5367 root      20   0  157696   2164   1516 R   6.2  0.1   0:00.02 top                 1 root      20   0  128088   6708   3964 S   0.0  0.4   0:01.97 systemd             2 root      20   0       0      0      0 S   0.0  0.0   0:00.01 kthreadd            3 root      20   0       0      0      0 S   0.0  0.0   0:00.04 ksoftirqd/0         6 root      20   0       0      0      0 S   0.0  0.0   0:00.18 kworker/u256:0      7 root      rt   0       0      0      0 S   0.0  0.0   0:00.09 migration/0         8 root      20   0       0      0      0 S   0.0  0.0   0:00.00 rcu_bh              9 root      20   0       0      0      0 S   0.0  0.0   0:00.85 rcu_sched          10 root      rt   0       0      0      0 S   0.0  0.0   0:00.04 watchdog/0         11 root      rt   0       0      0      0 S   0.0  0.0   0:00.05 watchdog/1………

• ps

     top命令的静态版。

• netstat

     网络监控。“-i”选项可以监控每个网络接口的流量（-ie相当于ifconfig）。

        Iface：网络接口名
        MTU（Maximum Transmission Unit）：最大传输单元
        RX-OK：接收的正确数据包数
        RX-ERR：接收的错误数据包数
        RX-DRP：接收时丢弃的数据包数
        RX-OVR：接收时由于过速（数据传输中接收设备不能接收按照发送速率传送来的数据）而丢失的数据包数
        TX-OK：发送的正确数据包数
        TX-ERR：发送的错误数据包数
        TX-DRP：发送时丢弃的数据包数
        TX-OVR：发送时由于过速而丢失的数据包数
        Flg：旗标
            B：接口已设广播地址
            L：接口为回送设备
            M：接口接收所有数据包（混乱模式）
            N：接口避免跟踪
            O：接口禁用ARP
            P：接口为点到点连接
            R：接口正在运行
            U：接口处于活动状态

[root@bilery ~]# netstat -iKernel Interface tableIface      MTU    RX-OK RX-ERR RX-DRP RX-OVR    TX-OK TX-ERR TX-DRP TX-OVR Flgens33     1500     6695      0      0 0           375      0      0      0 BMRUlo       65536        0      0      0 0             0      0      0      0 LRUvirbr0    1500        0      0      0 0             0      0      0      0 BMU

内容整理自：

https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.html

《MySQL技术内幕 InnoDB存储引擎》（https://book.douban.com/subject/24708143/）

《高可用MySQL》（ https://book.douban.com/subject/26630834/ ）