Сервер C2Q x 2 8G ram. RAID 5( 3 hdd ), mysql 5.1.26-rc \ Red Hat 4.1.2-14
Когда собирали(два года назад) были молодыми и глупыми, но сервер вообще влезает в свои параметры.
Итак имеем относительно высокую нагрузку на MySQL — 601.90 запросов в секунду, из них апдейты\инсерты — 2%, а ~70% — stmp prepare\execute\close, на долю чистого селекта остается 34.84%
И где-то неделю назад база научилась умирать — создавались кучи процесов которые работали по полчаса. Странность 1 — ровно через час все чинилось САМО
В общем начались разгребания состояния сервера.
Как один из пунктов этой программы в код движка был добавлен дамп времени выполнения операций в базу обратно в эту базу.
Этот код работал для запросов которые заняли дольше 0.1 сек — slow_log их еще не видит, но это уже тормоза…
В общем тут и пошли странности — самый обычный запрос, который, запусти его ручками, выполняется 0.0001 репортит в базу что он выполнялся 0.5 или даже ДВЕ секунды…
Странность номер два — тормоза идут мелкими сериями, по 5-10 тормознутых запросов, примерно раз в 11 секунд.
И в этот момент, обычно, только несколько таблиц торомозят( тоесть я вижу пачку по сути одинаковых запросов в логе в этот момент)
Так как 99 тормозных запросов приходились на innoDB таблицы были проведены некоторые танцы — включен file_per_table и таблицы из обшей свалки(11Гб) перевелись в свои маленькие файлики( конечный общий размер 4Гб, фрагментация там была дайбоже )
Запись в RAID5 уже по необходимым логическим операциям получается сложная, а в реализации LSI все обычно получается еще хуже. Как тут уже говорили, для баз данных предпочтительней RAID1,RAID10 и вариации.
Остается снизить интенсивность операций записи. Попробуйте в первую очередь
innodb_flush_log_at_trx_commit=0
innodb_support_xa=0
tmpdir = /tmp/ — перенесите в tmps
Во вторую очередь более опасные параметры:
innodb_doublewrite=0
delay_key_write=ALL
innodb_flush_method=nosync — вообще не знаю использует ли кто-то это. значение даже недокументировано, но кое-где можно найти его использование. если требования к производительности высокие и сервер не перегружается внезапно ( а с чего бы ему перегружаться в хорошем датацентре?), то можно использовать.
почитайте про каждый параметр, потому что такое изменение — компромисс между скоростью и надежностью хранения данных.
bin-log — точно знаете зачем он вам нужен?
query_cache_size = 0 — неужели он у вас вообще неэффективен? поставьте ну хотя бы 16Мб.
sort_buffer_size = 256M — с этим поосторожнее, он выделяется целиком в каждом обработчике вне зависимости от реальной потребности. При перегрузке сервера запросами возможно исчерпание памяти и убитие в первую очередь mysqld.
В каком состоянии находится mysql в момент зависания?
Железу в глазки смотрели? Первое подозрение падает на раид. Во-первых, он пятый со всеми вытекающими, во вторых для нынешних винчестеров 2 года — вполне себе пенсионный возраст.
мне кажется проблема именно в RAID 5. Не очень то он подходит для баз данных. я бы поставил еще один винт и сделал 2 RAID 0. Скорость работы с диском заметно возрастет.
sort_buffer_size = 256M
Этот параметр выставляется на КАЖДОГО КЛИЕНТА. Если есть тяжелые запросы, или тяжелые хранимки, то выкручивайте его внутри сессии. Хотя по моим наблюдениям толку от выкручивания больше 1M нет. 256 *100 клиентов = 25G оперативы :)
www.mysqlperformanceblog.com/ — всяческие тюнинги конфига там детально разжеваны
И я бы попробовал 5.5 версию на денек, там innodb заметно посвежее. Ну и собирать icc.
Потом уже обсуждалась тема переноса таблиц в оперативку — в поиск
1. Для начала советую запустить mysqltuner.pl и посмотреть что он расскажет
2. Включить slow_query_log в mysql и посмотреть там
3. Не забывать что выполнить минутный запрос за 0.0001 секунду можно сразу же после этого самого запроса. Банально работает кеш. Это я к тому, что если Вы конфиг писали «добавить нолик — работает быстрее», то возможно не подозреваете что выполняя подряд один и тот же запрос, получите совсем разные цифры. Второе выполнение будет почти мгновенным.
4. размер базы под 11Гб, 2% идет на апдейты, а сбрасываете ли вы индекс перед апдейтом? Если таблица большая, то такой апдейтик запросто может ее прогрузить до почек.
5. RAID тут вряд ли виновен, все же 600 запросов в секунду, из которых две трети служебные — это не такая уж и большая нагрузка.
Ну и если найдете узкие места в первых 5 пунктах, проверяйте запросы EXPLAIN'ом, оптимизируйте их.
My<cod/>.net
