MariaDB在Ubuntu上如何进行性能调优

Ubuntu上MariaDB性能调优实操指南

一 基线监控与瓶颈定位

• 服务与连接
  • 查看服务状态：systemctl status mariadb
  • 实时连接与线程：mysqladmin -u root -p status、SHOW STATUS LIKE 'Threads_connected';、SHOW PROCESSLIST;
• 系统资源
  • 资源占用：top/htop
  • 磁盘I/O：iostat -x 1
  • 网络与端口：ss -tulnp | grep mariadb
• 慢查询与日志
  • 启用慢查询日志（建议）：在配置中设置 slow_query_log=1、slow_query_log_file、long_query_time=1，定期分析慢日志定位问题SQL。

二 配置参数优化

• 关键InnoDB参数（编辑配置文件：/etc/mysql/mariadb.conf.d/50-server.cnf 或 /etc/my.cnf）
  • 缓冲池：将 innodb_buffer_pool_size 设为物理内存的50%–75%（专用数据库可接近70%），减少磁盘I/O。
  • 日志文件：适度增大 innodb_log_file_size（如256M或更高），提升写入吞吐；变更前需按步骤备份并移走旧日志，重启后确认。
  • 提交策略：将 innodb_flush_log_at_trx_commit=2 可提升性能（每秒落盘），但宕机可能丢失1秒事务；对强一致场景保持默认值1。
  • 临时表：提高 tmp_table_size 与 max_heap_table_size（如各256M），减少磁盘临时表。
  • 连接数：按负载设置 max_connections（如500起步），避免过高导致上下文切换与内存压力。
• 查询缓存
  • MariaDB 10.1及以下可用 query_cache_size（如64M）；MariaDB 10.2+ 默认移除查询缓存，不建议依赖，改用应用层缓存（如 Redis/Memcached）。
• 生效方式
  • 动态参数可用 SET GLOBAL 在线调整；涉及缓冲池大小、日志文件大小等静态参数需修改配置并重启服务。

三 索引与SQL优化

• 索引策略
  • 为高频查询列建立合适索引；避免过度索引（写放大、占用空间）。
  • 长字符串使用前缀索引；多列组合查询使用联合索引并遵循最左前缀原则。
• SQL编写
  • 使用 EXPLAIN 分析执行计划，关注扫描行数、索引使用、临时表与文件排序。
  • 避免 **SELECT ***，仅返回必要列；减少大偏移分页（如用游标/键集分页替代大 OFFSET）。
  • 合理使用 JOIN 替代复杂子查询，减少嵌套与函数计算；必要时拆分大事务。

四 系统级与硬件优化

• 硬件与存储
  • 优先使用 SSD，充足内存与多核CPU；为数据库分配专用资源，避免与高负载应用争用。
• 操作系统
  • 文件描述符：提升 ulimit -n（如65535），并在服务配置中同步设置 LimitNOFILE=65535。
  • 内核参数（/etc/sysctl.conf）：vm.swappiness=10（降低换页）、vm.vfs_cache_pressure=50（平衡VFS缓存）。
  • 存储与文件系统：使用 XFS/ext4，挂载选项如 noatime,nodiratime；确保充足的 IOPS 与合理的 RAID 策略。

五 维护与扩展

• 例行维护
  • 定期分析并优化表碎片（如 OPTIMIZE TABLE 针对碎片较多的表），清理无用日志，校验备份可用性。
• 架构扩展
  • 读写分离、主从复制提升读扩展；超大数据量考虑分区/分表；进一步可做分片与缓存层（Redis/Memcached）联动。
• 监控与可视化
  • 结合 Prometheus + Grafana 或 Zabbix 建立指标与慢查询面板，持续观测连接、缓冲池命中、I/O、复制延迟等关键指标并迭代调优。