MySQL基础理论~参数优化

系统参数优化建议：

1. vm.swappiness=0
   • 将内核使用交换空间的倾向降至最低，以提升性能。
2. I/O Scheduler 设置为 noop
   • 对于低延迟存储设备（如SSD），使用noop I/O调度器以减少开销。
3. TCP 参数调整
   • tcp_tw_reuse=1
     • 启用后，允许快速将TIME-WAIT状态的sockets重新用于新的TCP连接。
   • tcp_fin_timeout=15
     • 修改处于FIN-WAIT-2状态的socket保持时间，以防止DOS攻击。
   • tcp_keepalive_time=300
     • 设置keepalive消息发送的频率，用于检测TCP连接的另一端是否仍然响应。
   • tcp_max_syn_backlog=26144
     • 增加SYN队列长度，以处理更多的并发SYN请求。
4. 文件句柄限制
   • 根据应用程序需求，适当增加文件句柄的限制，以避免达到限制而导致的问题。
5. 关闭 NUMA
   • 关闭NUMA（Non-Uniform Memory Access）以提高在某些系统上的内存访问性能。
6. 使用 XFS 文件系统
   • XFS是一个高性能的文件系统，适合用于需要高吞吐量的场景。

MySQL参数优化建议：

• innodb_buffer_pool_size: 设置为总内存的60%。
• sync_binlog=1: 确保每次事务提交时都同步binlog到磁盘，增加数据安全性，但可能会略微影响性能。
• flush-log-at-trx-commit=1: 确保每次事务提交时都刷新日志到硬盘，与sync_binlog配合使用，可以进一步增强数据安全性。
• max_connections: 根据系统资源（如CPU、内存）和预期并发访问量来决定最大连接数。需要确保系统有足够资源来处理这些连接。
• max_user_connections: 限制每个用户的最大并发连接数，以防止单个用户占用过多资源。
• character_set_server: 设置为utf8mb4以支持更多的字符集，特别是表情符号和特殊字符。
• innodb_temp_data_file_path: 设置InnoDB临时文件的路径和大小上限，以防止写满磁盘空间。建议配置为足够大的大小，并根据需要调整。
• max_allowed_packet=64M: 设置网络传输中数据包的最大大小为64MB，以防止因参数太小而导致大数据量传输失败。根据实际应用需求，可以适当调整此值。
• innodb_io_capacity: 提高InnoDB每秒刷脏页和插入缓冲的I/O次数，以优化性能。具体数值需根据磁盘I/O性能来调整。
• innodb_io_capacity_max: 在高I/O负载下，此参数可以限制I/O操作的速率，以防止系统过载。建议设置为innodb_io_capacity的1.5-2倍。

双1参数：

1. flush-log-at-trx-commit 参数： memory->os_buffer->disk
   • 设置为 0：
     • MySQL 每秒将事务日志写入文件系统缓存，并触发操作系统进行硬盘刷新（fsync()）。
     • 如果 mysqld 进程或 Linux 系统崩溃，可能会导致上一秒钟内所有事务数据的丢失。
   • 设置为 1：
     • 每个事务提交时都会将日志写入文件系统缓存并立即刷新到硬盘。
     • 当 MySQL 或 Linux 出现故障时，不会丢失数据，提供了最高的数据安全性。
   • 设置为 2：
     • 每个事务提交时都会将日志写入文件系统缓存，操作系统会每秒刷新缓存到硬盘。
     • 在 Linux 系统崩溃时，可能会损失最后一秒钟内的事务数据。
2. sync-binlog 参数：-> os_buffer->disk(主要看文件系统缓存到磁盘的刷新)
   • 设置为 0：
     • MySQL 不会主动刷新二进制日志到磁盘，而是依赖于操作系统的刷新策略。
   • 设置为 1：
     • 每次事务提交时，MySQL 都会将文件系统缓存binlog刷新到磁盘。
     • 优化建议：在数据安全性要求极高的场景下使用，但需注意可能对性能造成一定影响。
   • 设置为 N（N > 1）：
     • 每进行 N 次事务提交后，MySQL 才会将文件系统缓存的binlog刷新到磁盘。