Форум MySQL

Здравствуйте!

При количестве одновременных соединение с базой данных MySQL (смотрю Максимально одновременных в phpMyAdmin) 80-100 загрузка ЦП возрастает до 100% и страницы грузятся по 5-10 секунд.

На страничке состояние в phpMyAdmin красным выделены следующие параметры:
Innodb_buffer_pool_reads 401
Handler_read_rnd 88 k
Handler_read_rnd_next 906 M
Created_tmp_disk_tables 197
Select_full_join 44 k
Opened_tables 253
Table_locks_waited 51

В чём может быть проблема?

Железо:
Intel Xeon E5506 2.13 4 ядра
ОЗУ 16 Гб

MySQL Версия сервера: 5.5.9-log
Apache/2.2.17 (Win32) PHP/5.3.5

Если не сложно прикрепите my.ini и хотя бы вкратце опишите базу данных (проект один или несколько, какие таблицы в основном используете (MyISAM или InnoDB), какой объем самых больших (и используемых для выборки) таблиц в мегабайтах/гигабайтах)?

Один проект.
Сайт и форум.
Форум использует движок IPB 3, на второй версии было тоже самое.
Практически всё MyISAM, за исключением 3 таблиц от форума, которые используют InnoDB.

Объём по форуму самый большой skin_cache 117 записи 3.0 МБ
по сайту 2,631 записи 397.2 КБ

Я так понимаю, что проблема точно не в железе

>Объём по форуму самый большой skin_cache 117 записи 3.0 МБ
Хм... т.е. ответов вообще практически нет (обычно самыми большими являются таблицы с текстом и таблицы для поиска)?

>Я так понимаю, что проблема точно не в железе
Нет, железо хорошее, хотя MySQL плохо использует многоядерность, но зато хорошо использует память, которой не мало.

Давайте my.ini поправим, у вас явно маловато памяти для MySQL выделено и это при том, что вы в ней буквально купаетесь.

key_buffer_size=348M

В первую очередь у вас неприлично низкий кэш ключей, я бы его сделал 2 Гб (а вообще при вашей конфигурации рекомендуется 4 Гб).

query_cache_size=0

Вообще отключен кэш запросов, ваше конечно, право, если он используется неэффективно, то только тормозит систему. Однако, если у вас такие маленькие таблицы - у вас запросы в нем просто жить будут. А кэш этот примечательный, если из него извлекается запрос - СУБД вообще ничего не делает, даже запрос не анализирует.

myisam_sort_buffer_size=404M

Эта память потребляется только когда она нужна, но вообще учитывайте, что это на каждый запрос... т.е. если будет 10 запросов, которым потребуется такой кэш, они сожрут 4Гб. Я бы поменьше сделал.

read_buffer_size=64K read_rnd_buffer_size=256K

Не понятно почему тут так мало - смело мегабайт 2 ставьте, а то и 8. Если эти буферы потоку не потребуется, MySQL их и выделать не будет. Зато если они потребуется, то на 64К сделать будет ничего нельзя. Это полный просмотр таблиц, когда нельзя использовать ключи, и он у вас идет на диск, вместо того, чтобы протекать в оперативной памяти.

Огромное спасибо. Уже сейчас при малой загрузке существенно снизилась нагрузка на ЦП.
Не думал, что эти параметры так сильно влияют и в стандартной конфигурации настолько малы.
Осталось проверить при максимальной загрузке.

Какое оптимально значение для query_cache_size? А то настолько уж большие разбросы в значениях.

>Какое оптимально значение для query_cache_size? А то настолько уж большие разбросы в
>значениях.
В нем хранятся результаты запросов (полностью, т.е. вернет запрос 200 Кб, будет хранится 200Кб, вернет 1 Мб, прибавится еще 1Мб). Думаю для начала 256Мб будет самое оно. Только следите за переменной Qcache_lowmem_prunes - она не должна расти слишком быстро, идеально, если она вообще не будет сильно изменяться, на фоне постоянно растущей Qcache_hits (каждый хит - это не выполненный SQL-запрос, каждый Qcache_lowmem_prunes - ушедший запрос, на который были потрачены ресурсы и от которого пользы уже не будет). Чем больше соотношение Qcache_hits к Qcache_lowmem_prunes - тем лучше. Если оно 1 или меньше, отключайте кэш запросов.

PS По остальным кэшам и буферам ситуация примерно такая же - нужно смотреть за динамикой переменных и их отношением. Разумеется динамику снимать и принимать во внимание нужно когда сервер "прогреется", т.е. спустя некоторое время после старта и заполнения буферов.