mysql优化升级

1. 调整 InnoDB 日志文件大小 (innodb_log_file_size)

• 当前值: 48MB
• 建议: 增加 innodb_log_file_size 的值至 256MB 或 512MB。更大的日志文件大小可以减少日志切换的频率，从而减少磁盘写入的负载。
• 操作步骤:
  1. 停止MySQL服务：

     sudo systemctl stop mysqld
     

  2. 编辑MySQL配置文件 my.cnf：

     [mysqld]
     innodb_log_file_size=512M
     

  3. 删除旧的日志文件（谨慎操作，确保备份数据）：

     rm /var/lib/mysql/ib_logfile*
     

  4. 重启MySQL服务：

     sudo systemctl start mysqld
     

2. 调整同步二进制日志 (sync_binlog)

• 当前值: 1
• 建议: 将 sync_binlog 值调整为 10 或 100，以减少每次事务提交时的磁盘同步频率，从而降低磁盘写入压力。
• 操作步骤:
  1. 编辑MySQL配置文件 my.cnf：

     [mysqld]
     sync_binlog=100
     

  2. 重启MySQL服务以应用更改：

     sudo systemctl restart mysqld
     

3. 优化 InnoDB 日志缓冲区大小 (innodb_log_buffer_size)

• 当前值: 16MB
• 建议: 增加 innodb_log_buffer_size 至 64MB 或更高，以减少频繁的日志缓冲区刷新次数。
• 操作步骤:
  1. 编辑MySQL配置文件 my.cnf：

     [mysqld]
     innodb_log_buffer_size=64M
     

  2. 重启MySQL服务以应用更改：

     sudo systemctl restart mysqld
     

4. 定期清理二进制日志

• 背景: 启用二进制日志会产生大量日志文件，长期不清理会占用大量磁盘空间，增加磁盘I/O。
• 建议: 配置自动清理策略，定期清理旧的二进制日志。
• 操作步骤:
  1. 编辑MySQL配置文件 my.cnf，添加日志保留策略：

     [mysqld]
     expire_logs_days=7
     

  2. 重启MySQL服务以应用更改：

     sudo systemctl restart mysqld
     

  3. 手动清理老旧的二进制日志（如有必要）：

     PURGE BINARY LOGS BEFORE 'YYYY-MM-DD HH:MM:SS';
     

5. 监控和优化慢查询

• 背景: 慢查询可能会导致高I/O负载，尤其是涉及大量排序、临时表或不适当的索引使用时。
• 建议: 开启慢查询日志，并优化涉及大量I/O操作的查询。
• 操作步骤:
  1. 编辑MySQL配置文件 my.cnf，开启慢查询日志：

     [mysqld]
     slow_query_log=1
     slow_query_log_file=/var/log/mysql/slow.log
     long_query_time=1
     

  2. 重启MySQL服务以应用更改：

     sudo systemctl restart mysqld
     

  3. 定期检查慢查询日志并优化涉及的大量I/O的查询。

6. 调整磁盘I/O调度器

• 背景: 操作系统的I/O调度器可以影响磁盘写入的效率。对于数据库服务器，可能需要调整I/O调度策略以获得更好的性能。
• 建议: 将I/O调度器调整为 deadline 或 noop，这在数据库负载下通常表现更好。
• 操作步骤:
  1. 检查当前的I/O调度器：

     cat /sys/block/sdX/queue/scheduler
     

  2. 临时调整I/O调度器：

     echo deadline > /sys/block/sdX/queue/scheduler
     

  3. 永久调整：编辑 /etc/default/grub，在 GRUB_CMDLINE_LINUX 中添加：

     elevator=deadline
     

  4. 更新grub并重启系统：

     sudo update-grub
     sudo reboot
     

通过实施这些具体的优化建议，你应该能够显著降低MySQL对磁盘的写入负载，从而提升系统整体的性能和稳定性。