Mysql优化参数

老蔡
2024-09-26 09:15:15
数据库
mysql

参数说明：

在实际场景中，每个参数的应用和调整都应该基于您的工作负载和数据库的使用模式。以下是一些具体的例子，说明在不同场景下如何调整这些参数：

innodb_buffer_pool_size：
- 如果您的数据库主要是读取操作，并且有大量缓存的数据，增加这个参数可以减少磁盘I/O，提高查询性能。
query_cache_size：
- 对于MySQL 5.7或更早版本，如果您的应用执行许多相同的查询，可以启用查询缓存。但如果是写密集型的或者有大量的更新和插入操作，建议将其设置为0。
tmp_table_size 和 max_heap_table_size：
- 如果您的应用经常执行需要临时表的操作，如大型JOIN或复杂计算，增加这些参数可以减少磁盘上的临时表写入，提高性能。
innodb_log_buffer_size：
- 在高写入负载下，增加这个参数可以减少事务日志的写入次数，提高事务处理速度。
sort_buffer_size：
- 如果您的查询经常需要对结果进行排序，并且这些查询涉及到大量数据，增加这个参数可以提高排序操作的性能。
read_buffer_size：
- 对于大量顺序读取的操作，如全表扫描，增加这个参数可以提高读取速度。
read_rnd_buffer_size：
- 如果您的应用经常执行随机读取操作，如基于特定条件的查询，增加这个参数可以提高性能。
join_buffer_size：
- 对于复杂的JOIN操作，增加这个参数可以提高处理速度。
thread_stack：
- 如果您的数据库处理复杂的存储过程或函数，增加这个参数可以避免线程栈溢出的错误。
binlog_cache_size：
- 如果您的数据库执行大量写操作，并且使用二进制日志，增加这个参数可以提高写入性能。
thread_cache_size：
- 如果您的数据库有大量的并发连接，增加这个参数可以减少线程创建和销毁的开销。
table_open_cache：
- 如果您的数据库有大量的表，并且这些表经常被访问，增加这个参数可以提高打开表的速度。
max_connections：
- 如果您的数据库需要处理大量的并发用户，增加这个参数可以允许更多的用户同时连接到数据库。
innodb_file_per_table：
- 如果您的数据库有大量的表，并且表的大小差异很大，启用这个选项可以让每个表有自己的数据文件，有助于管理大表。
innodb_flush_log_at_trx_commit：
- 如果您需要确保数据的持久性，并且可以接受稍微降低写入性能，设置为1可以提供最强的数据保护。

在调整参数之前，建议进行性能基准测试，并在测试环境中进行充分测试。调整参数后，需要重启MySQL服务才能生效。此外，您可能需要根据实际的工作负载和监控结果进一步调整这些参数。

基于的物理内存大小（385315 MB），以下是一些建议的 MySQL 配置参数，用于优化一个拥有192核的服务器。

innodb_buffer_pool_size：
- 建议设置为物理内存的50%-75%。这是一个大参数，所以我们可以稍微保守一些，尤其是如果您还有其他内存密集型应用在同一个服务器上运行。
- 建议值：192000M（约50%的385315 MB）
innodb_log_buffer_size：
- 用于事务日志的数据，如果您有大量的写操作，可以适当增加。
- 建议值：32M（对于一般的用途足够了，除非您有非常大的单个事务）
innodb_thread_concurrency：
- 控制InnoDB可以有多少线程同时运行，通常设置为CPU核心数的1.5到2倍。
- 建议值：300（192核的1.5倍）
tmp_table_size 和 max_heap_table_size：
- 控制内部临时表的最大大小，如果经常需要使用临时表，可以适当增加这两个值。
- 建议值：128M（可以根据实际需要调整）
query_cache_size：
- MySQL 8.0中已移除查询缓存，如果是MySQL 5.7或更早版本，可以考虑设置为0。
sort_buffer_size：
- 每个线程用于排序的缓冲区大小，可以按照每个核心分配一定量的内存。
- 建议值：4M（192核 * 4MB = 768MB）
read_buffer_size：
- 读入缓冲区大小，对于大量顺序读的操作非常有效。
- 建议值：2M（可以根据实际需要调整）
read_rnd_buffer_size：
- 随机读缓冲区大小，对于大量随机读的操作有效。
- 建议值：16M（可以根据实际需要调整）
join_buffer_size：
- 关联操作的缓冲区大小。
- 建议值：4M（可以根据实际需要调整）
thread_stack：
- 每个线程的堆栈大小。
- 建议值：256K（默认值，通常足够）
binlog_cache_size：
- 二进制日志缓存大小，对于大量写操作的场景很有用。
- 建议值：32M（可以根据实际需要调整）
thread_cache_size：
- 线程池大小，减少线程创建和销毁的开销。
- 建议值：900（根据并发连接数调整）
table_open_cache：
- 打开表的缓存数量。
- 建议值：5000（根据实际打开的表数量调整）
max_connections：
- 最大连接数。
- 建议值：5000（根据实际需要调整）
innodb_file_per_table：
- 开启这个选项可以让InnoDB为每个表创建独立的数据文件，有助于管理大表。
- 建议值：ON
innodb_flush_log_at_trx_commit：
- 控制事务日志的刷新频率，对于保证数据的持久性很重要。
- 建议值：1（最安全的选择）