我目前在我的16gb服务器上为我的~15个网站(主要是WordPress和PHP框架)使用以下配置:
key_buffer_size = 256M
max_allowed_packet = 256M
thread_stack = 256K
thread_cache_size = 256K
table_open_cache = 10000
table_definition_cache = 8192
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 10
innodb_log_file_size = 1G
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000
tmp_table_size = 256M
max_heap_table_size = 256M
sort_buffer_size = 4M
read_buffer_size = 256K
join_buffer_size = 256K
read_rnd_buffer_size = 256K
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 128M
myisam-recover-options = BACKUP
max_connections = 500
几个月来一切都很顺利，我认为我找到了优化和内存使用之间的完美平衡(有些网站真的是SQL密集型的)。但是几天前，我看到MySQL被杀死了，因为ram使用量超过了可用ram(如果我没记错的话，16超过了10gb)。
所以我的问题是，当这种情况发生时，你应该首先在MySQL配置上减少什么?我在*_buffer_size和inno_pool_size之间犹豫不决，所以为了在更短的时间内释放更多的内存，我将这些变量减少为:
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
目前一切都很好，我有9gb/16gb，但我不知道这是不是正确的方法。
谢谢。编辑:添加了一个来自网站的示例查询
"SELECT c.cover, c.type, cl.slug, cl.label, GROUP_CONCAT(p.id) AS links
FROM card c
INNER JOIN card_language cl ON cl.card_id = c.id AND cl.language_id = " . $language->id . "
INNER JOIN card_has_card_category chcc ON chcc.card_id = c.id
INNER JOIN card_category_language ccl ON ccl.card_category_id = chcc.card_category_id AND ccl.language_id = " . $language->id . "
LEFT JOIN card_has_links cp ON cp.card_id = c.id
LEFT JOIN link p ON p.id = cp.link_id
WHERE c.type = '" . $type . "'
AND ccl.slug = '" . $slug . "'
GROUP BY cl.slug
ORDER BY c.id DESC
LIMIT " . $limit . "
OFFSET " . $offset . "
;"
一般来说，最好使用MySQL提供的默认值。
我确实看到一些设置，你可能增加到危险的量。请记住，如果MySQL使用如此多的内存，它需要交换，性能可能会急剧下降。
key_buffer_size = 256M --> 20M (Assuming no tables are MyISAM)
max_allowed_packet = 256M -- no more than 1% of RAM
thread_stack = 256K -- remove (to get default)
thread_cache_size = 256K -- This is NOT bytes!!; change to 20
table_open_cache = 10000 -- Do you really have thousands of tables?
table_definition_cache = 8192 -- Do you really have thousands of tables?
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_buffer_pool_size = 10G -- the main tunable; 10G is good
innodb_buffer_pool_instances = 10
innodb_log_file_size = 1G
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 2000 -- Good, IF you have SSD, not HDD
tmp_table_size = 256M -- no more than 1% of RAM
max_heap_table_size = 256M -- no more than 1% of RAM
sort_buffer_size = 4M
read_buffer_size = 256K
join_buffer_size = 256K
read_rnd_buffer_size = 256K
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 128M
myisam-recover-options = BACKUP
max_connections = 500 -- see below
SHOW GLOBAL STATUS LIKE 'Max_used_connections';对max_connections给出了"高水位标记"。你可能不需要"max_connections"比"used"大很多。
为了加速WP，因此整个系统，琼斯和我做了一个WordPress插件来改进某些INDEXes: https://wordpress.org/plugins/index-wp-mysql-for-speed我们检测各种各样的东西，比如InnoDB存储引擎的Barrucuda版本的存在，以及其他MySQL奥秘，并做正确的事情。