php - 调整 mysql，mysqltuner 警告

Question

我正在运行一个 Magento 网站，访问者/小时为 100 人，配备 64GB RAM 和 Intel Core i7 CPU。当我运行 mysqltuner 时，我收到消息“MySQL 的最大内存使用量非常高”。

我需要帮助调整 MySQL 以满足我的需要，并清除警告。

这是我的.cnf文件:

key_buffer      = 32M
max_allowed_packet  = 16M
thread_stack        = 256K
thread_cache_size       = 100
table_open_cache = 4000
table_definition_cache = 8192
tmp_table_size = 512M
max_heap_table_size = 512M
innodb_buffer_pool_instances = 24
innodb_flush_log_at_trx_commit = 0
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 50000
innodb_flush_method = O_DIRECT
innodb_file_per_table
innodb_additional_mem_pool_size = 16M
transaction-isolation = READ-COMMITTED
innodb_support_xa = 0
innodb_commit_concurrency = 8
innodb_old_blocks_time = 1000
innodb_lock_wait_timeout = 120
innodb_buffer_pool_size = 40G
thread_concurrency = 10
max_connections        = 64
innodb_thread_concurrency = 18
thread_cache_size = 64
table_cache            = 2000
query_cache_type = 1
query_cache_size    = 128M
query_cache_limit   = 3M
key_buffer_size = 8G
sort_buffer_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
join_buffer_size = 128M

如何使 mysqltuner 警告消失？

Answer 1

关于 my.cnf-ini [mysqld] 部分的建议

以 # 开头或删除它们将允许 MySQL 默认值被使用并大大有助于消除消息。

sort_buffer_size = 64M
read_buffer_size = 64M
read_rnd_buffer_size = 64M
join_buffer_size = 128M

您会发现响应时间要好得多。

请发布 MySQL Tuner 报告的完整文本。

其他建议 2018 年 7 月 14 日

innodb_buffer_pool_instances=8  # from 24 minimize overhead
innodb_lru_scan_depth=128  # from 1024 to conserve CPU every second

如需更多帮助，请查看我的个人资料，点击网络个人资料以获取联系信息。谢谢