数据库服务器应该做的优化工作

概述

介绍一下一般数据库服务器应该做的一些优化，这里不涉及数据库层面，只考虑服务器监控和优化方面。

1、系统层面监控

主要关注以下三点。

1.1、cpu方面：

vmstat、sar 、top+P、htop、nmon、mpstat

1.2、内存：

free 、ps -aux 、top+M

1.3、IO 设备(磁盘、网络)：

iostat 、 ss 、 netstat 、 iptraf、iftop、lsof

补充：

vmstat 命令说明：

• Procs：r 显示有多少进程正在等待 CPU 时间。b 显示处于不可中断的休眠的进程数量。在等待 I/O
• Memory：swpd 显示被交换到磁盘的数据块的数量。未被使用的数据块，用户缓冲数据块，用于操作系统的数据块的数量
• Swap：操作系统每秒从磁盘上交换到内存和从内存交换到磁盘的数据块的数量。s1 和 s0 最好是 0
• Io：每秒从设备中读入 b1 的写入到设备 b0 的数据块的数量。反映了磁盘 I/O
• System：显示了每秒发生中断的数量(in)和上下文交换(cs)的数量
• Cpu：显示用于运行用户代码，系统代码，空闲，等待 I/O 的 CPU 时间

iostat 命令说明实例命令： iostat -dk 1 5

iostat -d -k -x 5 (查看设备使用率(%util)和响应时间(await))

• tps：该设备每秒的传输次数。“一次传输”意思是“一次 I/O 请求”。多个逻辑请求可能会被合并为“一次 I/O 请求”。
• iops ：硬件出厂的时候，厂家定义的一个每秒最大的 IO 次数,"一次传输"请求的大小是未知的。
• kB_read/s：每秒从设备(drive expressed)读取的数据量;
• KB_wrtn/s：每秒向设备(drive expressed)写入的数据量;
• kB_read：读取的总数据量;
• kB_wrtn：写入的总数量数据量;这些单位都为 Kilobytes。

2、系统层面问题解决办法

在实际的生产中，一般认为 cpu 只要不超过 90% 都没什么问题 ，当然不排除下面这些特殊情况：

2.1、cpu 负载高，IO 负载低

• 内存不够
• 磁盘性能差
• SQL 问题 ------>去数据库层，进一步排查 sql 问题
• IO 出问题了(磁盘到临界了、raid 设计不好、raid 降级、锁、在单位时间内 tps 过高)
• tps 过高: 大量的小数据 IO、大量的全表扫描

2.2、IO 负载高，cpu 负载低

• 大量小的 IO 写操作：
• autocommit ，产生大量小 IO
• IO/PS 磁盘的一个定值，硬件出厂的时候，厂家定义的一个每秒最大的 IO 次数。
• 大量大的 IO 写操作
• SQL 问题的几率比较大

2.3、IO 和 cpu 负载都很高

硬件不够了或 SQL 存在问题

3、基础优化-- 硬件优化

3.1、主机方面：

• 根据数据库类型，主机 CPU 选择、内存容量选择、磁盘选择
• 平衡内存和磁盘资源
• 随机的 I/O 和顺序的 I/O
• 主机 RAID 卡的 BBU(Battery Backup Unit)关闭

3.2、cpu 的选择：

• cpu 的两个关键因素：核数、主频
• 根据不同的业务类型进行选择：
• cpu 密集型：计算比较多，OLTP 主频很高的 cpu、核数还要多
• IO 密集型：查询比较，OLAP 核数要多，主频不一定高的

3.3、内存的选择：

• OLAP 类型数据库，需要更多内存，和数据获取量级有关。
• OLTP 类型数据一般内存是 cpu 核心数量的 2 倍到 4 倍，没有最佳实践。

3.4、存储方面：

• 根据存储数据种类的不同，选择不同的存储设备
• 配置合理的 RAID 级别(raid 5、raid 10、热备盘)
• 对与操作系统来讲，不需要太特殊的选择，最好做好冗余(raid1)(ssd、sas 、sata)

3.5、raid 卡：主机 raid 卡选择：

• 实现操作系统磁盘的冗余(raid1)
• 平衡内存和磁盘资源
• 随机的 I/O 和顺序的 I/O
• 主机 RAID 卡的 BBU(Battery Backup Unit)要关闭

3.6、网络设备方面：

使用流量支持更高的网络设备(交换机、路由器、网线、网卡、HBA 卡)

注意：以上这些规划应该在初始设计系统时就应该考虑好。

4、操作系统优化

Cpu：

基本不需要调整，在硬件选择方面下功夫即可。

内存：

基本不需要调整，在硬件选择方面下功夫即可。

SWAP：

MySQL 尽量避免使用 swap。阿里云的服务器中默认 swap 为 0

IO ：

• raid、no lvm、 ext4 或 xfs、ssd、IO 调度策略
• Swap 调整(不使用 swap 分区)

这个参数决定了 Linux 是倾向于使用 swap，还是倾向于释放文件系统 cache。在内存紧张的情况下，数值越低越倾向于释放文件系统 cache。当然，这个参数只能减少使用 swap 的概率，并不能避免 Linux 使用 swap。

修改 MySQL 的配置参数 innodb_flush_method，开启 O_DIRECT 模式。这种情况下，InnoDB 的 buffer pool 会直接绕过文件系统 cache 来访问磁盘，但是redo log 依旧会使用文件系统 cache。值得注意的是，Redo log 是覆写模式的，即使使用了文件系统的 cache，也不会占用太多。

5、系统参数调整

Linux 系统内核参数优化：

1. vim /etc/sysctl.conf 
2.  net.ipv4.ip_local_port_range = 1024 65535 # 用户端口范围 
3.  net.ipv4.tcp_max_syn_backlog = 4096  
4.  net.ipv4.tcp_fin_timeout = 30  
5.  fs.file-max=65535 # 系统最大文件句柄，控制的是能打开文件最大数量 

用户限制参数：

1. vim /etc/security/limits.conf  
2.  * soft nproc 65535 
3.  * hard nproc 65535 
4.  * soft nofile 65535 
5.  * hard nofile 65535 

6、不相干应用优化

业务应用和数据库应用独立,防火墙：iptables、selinux 等其他无用服务(关闭)：

1. chkconfig --level 23456 acpid off 
2. chkconfig --level 23456 anacron off 
3. chkconfig --level 23456 autofs off 
4. chkconfig --level 23456 avahi-daemon off 
5. chkconfig --level 23456 bluetooth off 
6. chkconfig --level 23456 cups off 
7. chkconfig --level 23456 firstboot off 
8. chkconfig --level 23456 haldaemon off 
9. chkconfig --level 23456 hplip off 
10. chkconfig --level 23456 ip6tables off 
11. chkconfig --level 23456 iptables off 
12. chkconfig --level 23456 isdn off 
13. chkconfig --level 23456 pcscd off 
14. chkconfig --level 23456 sendmail off 
15. chkconfig --level 23456 yum-updatesd off