①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: 8 10 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 ~]# vmstat procs -----------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 available Mem: 1867276 801176 481360 10604 584740 828052 Swap: 2098172 0 2098172
- top
服务器运行情况和进程列表的实时动态,是uptime输出 + vmstat输出的cpu部分 + free输出 + 一个“processlist”。
[root@bilery ~]# top top - 21:58:21 up 1:25, 2 users, load average: 0.45, 0.22, 0.13 Tasks: 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 st KiB Mem : 1867276 total, 479668 free, 802388 used, 585220 buff/cache KiB 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 -i Kernel Interface table Iface MTU RX-OK RX-ERR RX-DRP RX-OVR TX-OK TX-ERR TX-DRP TX-OVR Flg ens33 1500 6695 0 0 0 375 0 0 0 BMRU lo 65536 0 0 0 0 0 0 0 0 LRU virbr0 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/ )