Ubuntu SQL Server性能调优实战案例

Ubuntu 上 SQL Server 性能调优实战案例

一环境与瓶颈定位

目标环境：Ubuntu 20.04/22.04 LTS，SQL Server 2019/2022，OLTP 为主，存储为本地 NVMe 或云盘。
快速定位路径：
- 系统层：用 iostat -x 1、vmstat 1、pidstat -u -d 1 观察 IOPS、吞吐、await、svctm、CPU steal、内存压力；检查 dmesg 是否有 I/O 超时。
- SQL 层：用 sp_BlitzFirst/sp_WhoIsActive 定位高 CPU/逻辑读/持续时间 的查询；检查 PAGEIOLATCH_/WRITELOG 等待；确认 tempdb 争用与版本存储热点。
- 存储层：确认数据/日志/tempdb 是否分离、条带与对齐是否合理、文件系统与对齐是否匹配 RAID 几何。

二存储与文件系统配置

推荐配置要点（适用于 Ubuntu 18.04+ 与 SQL Server 2017 CU6+）：
- 文件系统：优先使用 XFS 托管数据/日志；ext4 仅在 Linux 内核 ≥ 5.6 时考虑。
- 条带与对齐：RAID 条带单元（stripe unit）与 XFS 的 sunit/swidth 对齐；例如 64 KB 条带应对应 sunit=16 blks（16×4096=64 KB），swidth 等于“数据盘数量”（不含校验盘）。
- FUA（强制单位访问）：满足内核与存储支持时，启用 TF 3979 并设置 mssql-conf control.writethrough=1、control.alternatewritethrough=0；否则使用 TF 3982 与 control.writethrough=1、control.alternatewritethrough=1。

示例：在 Ubuntu 22.04 上创建对齐的 XFS 日志卷（RAID-10，64 KB 条带）

假设日志由 6 盘 RAID-10 组成，条带 64 KB：

sudo mdadm --create --verbose /dev/md3 --level=raid10 --chunk=64K --raid-devices=6 \
  /dev/sda /dev/sdb /dev/sdc /dev/sdd /dev/sde /dev/sdf

sudo mkfs.xfs /dev/md3 -f -L log

检查对齐（应见 sunit=16，swidth=48 对应 3 数据盘）：
```
sudo xfs_info /dev/md3 | egrep 'sunit|swidth'
```

挂载到数据/日志目录（示例）：

sudo mkdir -p /var/opt/mssql/data /var/opt/mssql/log
echo '/dev/md3 /var/opt/mssql/log xfs defaults,noatime 0 0' | sudo tee -a /etc/fstab
sudo mount -a

将新数据库默认目录指向高速卷（SQL Server 2019/2022 示例）：

sudo /opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /var/opt/mssql/data
sudo /opt/mssql/bin/mssql-conf set filelocation.defaultlogdir  /var/opt/mssql/log
sudo systemctl restart mssql-server

FUA 启用（满足支持条件时）：

# /etc/default/msodbcsql17 或 /etc/default/mssql-server 中加入
MSSQL_STARTUP_ARGS="-T3979"
sudo systemctl restart mssql-server

sudo /opt/mssql/bin/mssql-conf set control.writethrough 1
sudo /opt/mssql/bin/mssql-conf set control.alternatewritethrough 0

以上要点与示例基于 Linux 上 SQL Server 的性能与 FUA 配置指南，包含 XFS/ext4 选择、sunit/swidth 对齐、以及 TF 3979/3982 与 mssql-conf 的推荐组合。

三内存、调度与资源限制

内存：SQL Server on Linux 默认会占用“几乎所有可用内存”用于缓冲池。为系统保留 至少 4–8 GB（视监控/备份代理而定），避免 OOM 与系统抖动。
资源限制：提升 文件描述符与进程数，编辑 /etc/security/limits.d/mssql-server.conf（示例）：
```
mssql soft nofile 65536
mssql hard nofile 65536
mssql soft nproc  65536
mssql hard nproc  65536
```
重启后生效（systemctl restart mssql-server）。
I/O 调度与调度器：对 NVMe 优先使用 none/mq-deadline；对 SSD/SAS 可用 mq-deadline 或 cfq（视内核版本与负载而定）。示例（在 /etc/default/grub 的 GRUB_CMDLINE_LINUX 增加）：
```
elevator=none
```
更新 grub 并重启：sudo update-grub && sudo reboot。

透明大页（THP）：数据库负载建议禁用或设置为 madvise：

echo never | sudo tee /sys/kernel/mm/transparent_hugepage/enabled
echo madvise | sudo tee /sys/kernel/mm/transparent_hugepage/defrag

持久化可写入 /etc/rc.local 或 systemd 服务。

电源与 NUMA：BIOS 开启 Performance 电源策略；NUMA 绑定与中断亲和可按需细化（生产变更需评估与灰度）。

四查询与索引优化清单

执行计划稳定性：
- 使用 参数嗅探友好 的写法，必要时使用 OPTIMIZE FOR 或 RECOMPILE。
- 避免 **SELECT ***，只取必要列；减少 TOP N + OFFSET/FETCH 的无谓排序。
连接与子查询：
- 优先 JOIN 替代相关子查询；减少不必要的 JOIN 数量。
- 小结果集驱动大表；分页查询合理使用 OFFSET/FETCH。
统计信息与索引：
- 保持 统计信息自动更新 开启；对大表/高变更表定期重组或重建索引。
- 为高频 WHERE/JOIN/ORDER BY/GROUP BY 列建立合适索引；结合 覆盖索引 减少键查找。
临时表与版本存储：
- 避免频繁创建/销毁大对象；为 tempdb 预分配足够文件数（按 CPU 核数，通常 8/16 起），统一大小，避免自动增长抖动。
T‑SQL 习惯：
- 在存储过程与批处理中减少往返与消息开销（如减少不必要的信息返回），优先集合操作替代逐行处理。以上为在 Ubuntu 上提升 SQL 效率与存储/执行优化的通用实践要点，可作为现场优化的检查清单。

五变更验证与回退

基准与压测：在变更前后使用相同工作负载进行压测，记录 P95/P99 延迟、QPS、平均逻辑读、PAGEIOLATCH/WRITELOG、CPU/IO 利用率。
监控告警：对 WRITELOG、PAGEIOLATCH_SH、LCK_M_* 等设置阈值告警；对 tempdb 空间与版本存储增长设置监控。
回退预案：对 FUA、调度器、THP、文件描述符 等变更，保留当前配置与命令，出现稳定性或性能退化时按相反顺序回滚；变更窗口内保持 回滚时间 < 业务容忍阈值。

最新问答

相关标签