印刻数据库

外观

MariaDB 系统优化 ​

MariaDB 的性能不仅取决于数据库本身的配置，还受到操作系统、存储系统、网络等底层系统的影响。系统级别的优化可以为 MariaDB 提供更好的运行环境，显著提高数据库的性能和稳定性。本文将从多个方面介绍 MariaDB 系统优化的方法和最佳实践。

系统优化概述 ​

系统优化是一个综合性的工作，需要考虑多个方面：

  • 操作系统：调整内核参数、文件系统、进程管理等
  • 存储系统：选择合适的存储设备、文件系统和 RAID 级别
  • 网络：优化网络参数、连接管理和防火墙设置
  • 内存：合理分配内存资源，优化内存管理
  • CPU：优化 CPU 调度和进程优先级
  • 监控：建立完善的监控体系，及时发现和解决问题

系统优化的目标是：

  • 提高系统资源利用率
  • 减少系统瓶颈
  • 提高系统稳定性和可靠性
  • 为 MariaDB 提供最佳的运行环境

操作系统优化 ​

Linux 系统优化 ​

1. 内核参数优化 ​

编辑 /etc/sysctl.conf 文件，添加或修改以下参数：

bash
# 共享内存设置
kernel.shmmax = 68719476736    # 最大共享内存大小（字节），建议为物理内存的 50%
kernel.shmall = 4294967296     # 共享内存页数量
kernel.shmmni = 4096          # 共享内存段数量限制

# 信号量设置
kernel.sem = 250 32000 100 128  # SEMMSL SEMMNS SEMOPM SEMMNI

# 文件描述符设置
fs.file-max = 65536            # 系统最大文件描述符数

# 网络参数设置
net.core.netdev_max_backlog = 4096    # 网络设备接收队列长度
net.core.somaxconn = 4096             # 监听队列最大长度
net.core.rmem_default = 8388608       # 默认接收缓冲区大小
net.core.rmem_max = 16777216          # 最大接收缓冲区大小
net.core.wmem_default = 8388608       # 默认发送缓冲区大小
net.core.wmem_max = 16777216          # 最大发送缓冲区大小

# TCP 参数设置
net.ipv4.tcp_max_syn_backlog = 4096    # TCP SYN 队列长度
net.ipv4.tcp_fin_timeout = 30         # TCP FIN 超时时间
net.ipv4.tcp_tw_reuse = 1             # 允许重用 TIME_WAIT 套接字
net.ipv4.tcp_tw_recycle = 0             # 禁用 TCP TIME_WAIT 快速回收
net.ipv4.tcp_max_tw_buckets = 5000    # TIME_WAIT 套接字最大数量
net.ipv4.tcp_keepalive_time = 300     # TCP 保持连接时间
net.ipv4.tcp_keepalive_probes = 5      # TCP 保持连接探测次数
net.ipv4.tcp_keepalive_intvl = 15       # TCP 保持连接探测间隔

# 内存管理设置
vm.swappiness = 10                    # 控制内存交换行为，建议 0-10
vm.dirty_ratio = 40                   # 脏页比例，建议 30-40
vm.dirty_background_ratio = 10        # 后台脏页写入触发比例，建议 5-10
vm.dirty_writeback_centisecs = 500    # 脏页写入回写时间，单位厘秒
vm.dirty_expire_centisecs = 3000      # 脏页过期时间，单位厘秒

应用内核参数：

bash
sysctl -p

2. 文件描述符限制 ​

编辑 /etc/security/limits.conf 文件，添加以下内容：

bash
# MariaDB 用户限制
mysql soft nofile 65536
mysql hard nofile 65536
mysql soft nproc 65536
mysql hard nproc 65536
mysql soft stack 8192
mysql hard stack 8192

对于 CentOS 7+，还需要编辑 /etc/security/limits.d/20-nproc.conf 文件，调整 nproc 限制。

3. I/O 调度器 ​

对于 SSD 磁盘，建议使用 none 或 mq-deadline 调度器；对于 HDD 磁盘，建议使用 deadline 调度器。

查看当前 I/O 调度器：

bash
cat /sys/block/sda/queue/scheduler

临时修改 I/O 调度器：

bash
echo deadline > /sys/block/sda/queue/scheduler

永久修改 I/O 调度器，在 GRUB 配置中添加 elevator=deadline，然后更新 GRUB 并重启系统。

4. 禁用不必要的服务 ​

禁用不需要的系统服务，减少资源占用：

bash
# 查看运行的服务
systemctl list-units --type=service

# 禁用不必要的服务
systemctl disable postfix bluetooth NetworkManager-wait-online

Windows 系统优化 ​

1. 电源计划 ​

将电源计划设置为「高性能」，确保 CPU 始终以最高性能运行：

powershell
# 设置电源计划为高性能
powercfg /setactive 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c

2. 虚拟内存设置 ​

合理设置虚拟内存大小，建议为物理内存的 1.5-2 倍：

  1. 右键「此电脑」→「属性」→「高级系统设置」
  2. 点击「高级」选项卡→「性能」→「设置」
  3. 点击「高级」选项卡→「虚拟内存」→「更改」
  4. 取消勾选「自动管理所有驱动器的分页文件大小」
  5. 选择系统盘，设置「自定义大小」，初始大小和最大值设置为物理内存的 1.5-2 倍
  6. 点击「设置」→「确定」保存设置

3. 网络参数优化 ​

编辑注册表，优化网络参数：

reg
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters]
"TcpTimedWaitDelay"=dword:0000001e    ; TIME_WAIT 超时时间（30秒）
"MaxUserPort"=dword:0000fffe          ; 最大用户端口号（65534）
"TcpNumConnections"=dword:00fffffe     ; 最大 TCP 连接数
"TcpMaxSegs"=dword:00000000           ; TCP 最大段数
"TcpMaxDataRetransmissions"=dword:00000005 ; 最大重传次数

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\AFD\Parameters]
"DefaultReceiveWindow"=dword:00100000  ; 默认接收窗口大小
"DefaultSendWindow"=dword:00100000     ; 默认发送窗口大小
"FastSendDatagramThreshold"=dword:00000400 ; 快速发送数据报阈值
"SocketReceiveBufferLength"=dword:00100000 ; 套接字接收缓冲区长度

4. 禁用不必要的服务 ​

禁用不需要的 Windows 服务，减少资源占用：

powershell
# 查看运行的服务
Get-Service | Where-Object {$_.Status -eq "Running"}

# 禁用不必要的服务
Set-Service -Name "Print Spooler" -StartupType Disabled
Set-Service -Name "Windows Search" -StartupType Disabled
Set-Service -Name "Superfetch" -StartupType Disabled

macOS 系统优化 ​

1. 关闭不必要的功能 ​

  • 关闭 Spotlight 索引（如果不需要）
  • 关闭 Dashboard
  • 关闭自动备份（Time Machine）
  • 关闭不必要的登录项

2. 网络参数优化 ​

编辑 /etc/sysctl.conf 文件，添加或修改以下参数：

bash
# 网络参数设置
net.inet.tcp.sendspace=1048576
net.inet.tcp.recvspace=1048576
net.inet.tcp.mssdflt=1460
net.inet.tcp.v6mssdflt=1440
net.inet.tcp.path_mtu_discovery=1
net.inet.tcp.blackhole=2
net.inet.udp.blackhole=1
net.inet.tcp.fastopen=1

应用网络参数：

bash
sysctl -w net.inet.tcp.sendspace=1048576

3. 磁盘优化 ​

使用磁盘工具进行磁盘验证和修复：

bash
# 验证磁盘
diskutil verifyDisk /

# 修复磁盘
diskutil repairDisk /

存储系统优化 ​

1. 存储设备选择 ​

  • SSD：推荐用于生产环境，具有高性能、低延迟的特点
  • NVMe SSD：比 SATA SSD 更快，适合高并发场景
  • HDD：适合存储大容量、访问频率低的数据

2. 文件系统选择 ​

  • Linux：推荐使用 XFS 或 Ext4 文件系统
    • XFS：适合大容量存储和高并发场景
    • Ext4：适合一般场景，稳定性好
  • Windows：推荐使用 NTFS 文件系统
  • macOS：推荐使用 APFS 文件系统

3. 磁盘分区策略 ​

  • 数据目录和日志目录分开存储，减少 I/O 竞争
  • 操作系统和数据分开存储，提高安全性
  • 为 Swap 分区分配适当的空间（建议为物理内存的 1-2 倍）

4. RAID 配置 ​

  • RAID 0：条带化，提高性能，无冗余
  • RAID 1：镜像，提供冗余，性能一般
  • RAID 5：分布式奇偶校验，适合读多写少场景
  • RAID 6：双重奇偶校验，比 RAID 5 更安全
  • RAID 10：镜像+条带化，提供高性能和冗余，推荐用于生产环境

5. 文件系统挂载选项 ​

在 /etc/fstab 文件中添加以下挂载选项：

bash
# XFS 文件系统挂载选项
/dev/sdb1 /var/lib/mysql xfs defaults,noatime,nodiratime,logbufs=8,logbsize=256k 0 2

# Ext4 文件系统挂载选项
/dev/sdb1 /var/lib/mysql ext4 defaults,noatime,nodiratime,data=writeback,barrier=0 0 2

挂载选项说明：

  • noatime：不更新访问时间
  • nodiratime：不更新目录访问时间
  • logbufs：日志缓冲区数量
  • logbsize：日志缓冲区大小
  • data=writeback：数据写入模式
  • barrier=0：禁用写屏障，提高性能（仅适用于有 UPS 的环境）

网络优化 ​

1. 网络参数优化 ​

  • 调整 TCP 缓冲区大小：增大 TCP 发送和接收缓冲区，提高网络吞吐量
  • 启用 TCP Fast Open：减少连接建立时间
  • 调整 TIME_WAIT 超时时间：减少 TIME_WAIT 套接字占用的资源
  • 启用 SYN Cookie：防止 SYN 洪水攻击

2. 连接管理优化 ​

  • 使用连接池：减少连接建立和关闭的开销
  • 限制最大连接数：根据服务器资源调整 MariaDB 的 max_connections 参数
  • 设置合理的连接超时时间：避免连接长时间占用资源

3. 防火墙设置 ​

  • 仅开放必要的端口（默认 3306）
  • 限制访问 IP 范围
  • 启用防火墙日志，监控异常访问

Linux 防火墙设置：

bash
# 开放 3306 端口
firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --reload

# 限制特定 IP 访问
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="192.168.1.0/24" port protocol="tcp" port="3306" accept'
firewall-cmd --reload

Windows 防火墙设置：

powershell
# 开放 3306 端口
New-NetFirewallRule -DisplayName "MariaDB" -Direction Inbound -Protocol TCP -LocalPort 3306 -Action Allow

# 限制特定 IP 访问
New-NetFirewallRule -DisplayName "MariaDB Restricted" -Direction Inbound -Protocol TCP -LocalPort 3306 -RemoteAddress 192.168.1.0/24 -Action Allow

内存管理优化 ​

1. 内存分配策略 ​

  • InnoDB 缓冲池：分配总内存的 50%-70%
  • MyISAM 键缓冲：分配总内存的 5%-10%（如果使用 MyISAM 表）
  • 操作系统缓存：保留足够的内存给操作系统缓存
  • Swap 空间：设置适当的 Swap 空间，避免内存不足导致系统崩溃

2. 内存监控 ​

  • 定期监控内存使用率，及时调整配置
  • 使用 free、top、htop 等工具监控内存使用情况
  • 使用 vmstat 工具监控虚拟内存使用情况

3. 内存优化建议 ​

  • 关闭不必要的服务和进程，释放内存
  • 使用 OOM Killer 保护重要进程
  • 调整 swappiness 参数，减少内存交换

CPU 优化 ​

1. CPU 选择 ​

  • 选择多核、高主频的 CPU
  • 优先选择支持 AVX2、AVX-512 等指令集的 CPU
  • 考虑 CPU 的缓存大小，更大的缓存有助于提高性能

2. CPU 调度优化 ​

  • 调整进程优先级，确保 MariaDB 进程获得足够的 CPU 资源
  • 绑定 MariaDB 进程到特定 CPU 核心，减少上下文切换

绑定进程到 CPU 核心：

bash
# 查看 MariaDB 进程 ID
pidof mysqld

# 绑定到 CPU 核心 0-3
taskset -cp 0-3 <PID>

3. CPU 监控 ​

  • 使用 top、htop 工具监控 CPU 使用率
  • 使用 mpstat 工具监控每个 CPU 核心的使用情况
  • 使用 sar 工具监控 CPU 历史使用情况

4. CPU 优化建议 ​

  • 关闭不必要的服务和进程，减少 CPU 占用
  • 优化 SQL 查询，减少 CPU 密集型查询
  • 考虑使用线程池，减少线程创建和销毁的开销

系统监控和调优 ​

1. 监控工具 ​

  • Linux：top, htop, vmstat, iostat, sar, netstat, ss
  • Windows：任务管理器, 性能监视器, Resource Monitor
  • macOS：Activity Monitor, top, vm_stat, iostat
  • 第三方工具：Prometheus, Grafana, Zabbix, Nagios

2. 关键监控指标 ​

  • CPU：使用率、负载、上下文切换
  • 内存：使用率、Swap 使用情况、缓存使用情况
  • 磁盘 I/O：吞吐量、IOPS、延迟、队列长度
  • 网络：带宽使用率、连接数、丢包率、延迟
  • MariaDB：连接数、查询率、慢查询、锁等待

3. 调优流程 ​

  1. 建立基准性能指标
  2. 监控系统性能，发现瓶颈
  3. 分析瓶颈原因，制定优化方案
  4. 实施优化方案
  5. 验证优化效果，调整优化方案
  6. 定期监控和调整

最佳实践 ​

1. 定期备份和测试 ​

  • 定期备份数据和配置文件
  • 测试备份的可用性
  • 制定灾难恢复计划

2. 定期更新和升级 ​

  • 定期更新操作系统和 MariaDB 到最新版本
  • 及时应用安全补丁
  • 测试升级后的兼容性

3. 建立监控体系 ​

  • 建立全面的监控体系，监控系统和数据库性能
  • 设置合理的告警阈值，及时发现问题
  • 定期分析监控数据，优化系统性能

4. 文档化和标准化 ​

  • 记录系统配置和优化措施
  • 建立标准化的部署和优化流程
  • 培训团队成员，确保操作一致性

5. 容量规划 ​

  • 定期进行容量规划，预测未来资源需求
  • 考虑业务增长，预留足够的资源
  • 制定扩容计划，确保系统可扩展性

常见问题（FAQ） ​

1. 如何确定系统瓶颈？ ​

方法：

  • 使用监控工具分析 CPU、内存、磁盘 I/O、网络等指标
  • 识别哪个资源的使用率接近或达到 100%
  • 分析 MariaDB 慢查询日志，找出性能瓶颈

2. 如何优化磁盘 I/O 性能？ ​

建议：

  • 使用 SSD 或 NVMe SSD 存储设备
  • 选择合适的 RAID 级别（如 RAID 10）
  • 优化文件系统挂载选项
  • 分离数据目录和日志目录
  • 调整 InnoDB 配置参数，如 innodb_buffer_pool_size、innodb_io_capacity

3. 如何优化网络性能？ ​

建议：

  • 调整 TCP 缓冲区大小
  • 启用 TCP Fast Open
  • 调整 TIME_WAIT 超时时间
  • 使用连接池
  • 限制最大连接数

4. 如何减少内存交换？ ​

建议：

  • 调整 swappiness 参数为 0-10
  • 增加物理内存
  • 优化 MariaDB 配置，减少内存使用
  • 关闭不必要的服务和进程

5. 如何优化 CPU 使用率？ ​

建议：

  • 优化 SQL 查询，减少 CPU 密集型查询
  • 使用线程池
  • 绑定进程到特定 CPU 核心
  • 关闭不必要的服务和进程

6. 如何监控系统性能？ ​

建议：

  • 使用系统自带的监控工具，如 top、htop、iostat 等
  • 使用第三方监控工具，如 Prometheus + Grafana
  • 设置合理的告警阈值，及时发现问题
  • 定期分析监控数据，优化系统性能

7. 如何进行系统容量规划？ ​

方法：

  • 收集历史性能数据，分析资源使用趋势
  • 考虑业务增长，预测未来资源需求
  • 制定扩容计划，确保系统可扩展性
  • 定期更新容量规划，适应业务变化

总结 ​

系统优化是 MariaDB 性能调优的重要组成部分，需要从操作系统、存储系统、网络、内存和 CPU 等多个方面入手。合理的系统优化可以为 MariaDB 提供更好的运行环境，显著提高数据库的性能和稳定性。

在实际生产环境中，DBA 需要建立完善的监控体系，定期监控系统性能，及时发现和解决瓶颈问题。同时，需要根据业务需求和系统特点，制定合理的优化方案，并定期更新和调整。

通过本文的介绍，相信您已经掌握了 MariaDB 系统优化的方法和最佳实践，能够在实际生产环境中优化系统性能，提高数据库的可靠性和稳定性。