(Не был уверен, был ли это вопрос ServerFault или StackOverflow, поэтому, пожалуйста, простите перекрестную публикацию)
У меня есть база данных MySQL 5.1.61, работающая за двумя веб-серверами Apache с балансировкой нагрузки, на которых размещены довольно загруженные (100 000 уникальных посетителей в день) сайты Wordpress. Я кэширую с помощью Cloudflare, W3TC и Varnish. В большинстве случаев сервер базы данных очень хорошо обрабатывает трафик. «показать полный список процессов» показывает 20-40 запросов в любой момент времени, большинство из которых находятся в состоянии сна.
Однако периодически (особенно при скачках трафика или при удалении большого количества комментариев) MySQL перестает отвечать. Я найду 1000-1500 запущенных запросов, много "отправляющих данных" и т. Д. Никакой конкретный запрос, похоже, не напрягает базу данных (это все стандартные запросы Wordpress), но похоже, что одновременный объем запросов вызывает все запросы повесить. Я (обычно) все еще могу войти в систему, запустить «показать полный список процессов» или другие запросы, но уже есть более 1000 запросов. Кажется, единственное решение - перезапустить mysql (иногда жестоко через kill -9, если я не могу подключиться).
Все таблицы innodb, у сервера 8 ядер, 24 ГБ ОЗУ, много места на диске, а вот мой my.cnf:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
skip-external-locking
skip-name-resolve
user=mysql
query_cache_type=1
query_cache_limit=16M
wait_timeout = 300
query_cache_size=128M
key_buffer_size=400M
thread_cache_size=50
table_cache=8192
skip-name-resolve
max_heap_table_size = 256M
tmp_table_size = 256M
innodb_file_per_table
innodb_buffer_pool_size = 5G
innodb_log_file_size=1G
#innodb_commit_concurrency = 32
#innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 0
thread_concurrency = 8
join_buffer_size = 256k
innodb_log_file_size = 256M
#innodb_concurrency_tickets = 220
thread_stack = 256K
max_allowed_packet=512M
max_connections=2500
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
#2012-11-03
#attempting a ram disk for tmp tables
tmpdir = /db/tmpfs01
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Любые предложения, как я могу потенциально улучшить конфигурацию MySQL или другие шаги для поддержания стабильности базы данных при большой нагрузке?
Я вижу три (3) мёртвых раздачи по поводу недостаточной производительности
Вы используете MySQL 5.1.61. Большинство людей не устанавливают подключаемый модуль InnoDB, поставляемый с MySQL 5.1, начиная с MySQL 5.1.38. В подключаемый модуль InnoDB внесены новые улучшения, позволяющие InnoDB выполнять многоядерное взаимодействие.
Рекомендация: либо установите подключаемый модуль InnoDB, либо обновитесь до MySQL 5.5.
Это то, что у вас есть (из вопроса)
innodb_file_per_table
innodb_buffer_pool_size = 5G
innodb_log_file_size=1G
#innodb_commit_concurrency = 32
#innodb_thread_concurrency = 32
innodb_flush_log_at_trx_commit = 0
thread_concurrency = 8
innodb_log_file_size = 256M
Если у вас установлен подключаемый модуль InnoDB или обновлен до MySQL 5.5, вы должны настроить InnoDB, чтобы активировать многоядерные улучшения.
Вот мои рекомендации:
innodb_thread_concurrency = 0 (default in MySQL 5.5)
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_io_capacity = 5000
Пожалуйста прочти мой пост DBA StackExchange от Mar 16, 2012 это обсуждает это подробно.
Ваш innodb_buffer_pool_size может быть слишком маленьким. Попробуйте увеличить его. По факту, Я написал сообщение на Apr 22, 2011 в WordPress StackExchange об изменении размера буферного пула.