kashey
@kashey
Программирую большую половину жизни

Как понять почему тупит MySQL?

Сервер 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Гб, фрагментация там была дайбоже )


LA сервера, 0.9

утилизация винта — 15-20%


Конфиг тут

Свободная память — есть.

Идей откуда тормоза и что делать — нет


Как вариант — Percona или MariaDB (5.1.6?)

Бонус пак — когда mysql зависает — конекты от него не отваливаются, процесы не завершаются.

Никак кроме как kill -9....
  • Вопрос задан
  • 7653 просмотра
Пригласить эксперта
Ответы на вопрос 9
@pwlnw
> Рейд держит нормальная железякя
>LSI
уже смешно. батарейки и памяти там ведь нет?

>RAID5
>innoDB
>утилизация винта — 15-20%
>innodb_flush_log_at_trx_commit = 2
>самый обычный запрос… ДВЕ секунды…

Запись в 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.
Ответ написан
Комментировать
iscsi
@iscsi
Не страшно с 3-мя hdd на RAID5? (IMO в нем и проблема)
Ответ написан
Комментировать
Janaaki
@Janaaki
В каком состоянии находится mysql в момент зависания?

Железу в глазки смотрели? Первое подозрение падает на раид. Во-первых, он пятый со всеми вытекающими, во вторых для нынешних винчестеров 2 года — вполне себе пенсионный возраст.
Ответ написан
@scatmanoleg
мне кажется проблема именно в RAID 5. Не очень то он подходит для баз данных. я бы поставил еще один винт и сделал 2 RAID 0. Скорость работы с диском заметно возрастет.
Ответ написан
casey
@casey
SELECT SQL_NO_CACHE?
Ответ написан
Комментировать
@torsten
как много процессов у mysql?
что показывает «vmstat 1» во время тормозов и во время нормальной работы? сравните context switching.
Ответ написан
Комментировать
mgyk
@mgyk
sort_buffer_size = 256M
Этот параметр выставляется на КАЖДОГО КЛИЕНТА. Если есть тяжелые запросы, или тяжелые хранимки, то выкручивайте его внутри сессии. Хотя по моим наблюдениям толку от выкручивания больше 1M нет. 256 *100 клиентов = 25G оперативы :)
Ответ написан
@fallen
www.mysqlperformanceblog.com/ — всяческие тюнинги конфига там детально разжеваны
И я бы попробовал 5.5 версию на денек, там innodb заметно посвежее. Ну и собирать icc.
Потом уже обсуждалась тема переноса таблиц в оперативку — в поиск
Ответ написан
@BaBL
1. Для начала советую запустить mysqltuner.pl и посмотреть что он расскажет
2. Включить slow_query_log в mysql и посмотреть там
3. Не забывать что выполнить минутный запрос за 0.0001 секунду можно сразу же после этого самого запроса. Банально работает кеш. Это я к тому, что если Вы конфиг писали «добавить нолик — работает быстрее», то возможно не подозреваете что выполняя подряд один и тот же запрос, получите совсем разные цифры. Второе выполнение будет почти мгновенным.
4. размер базы под 11Гб, 2% идет на апдейты, а сбрасываете ли вы индекс перед апдейтом? Если таблица большая, то такой апдейтик запросто может ее прогрузить до почек.
5. RAID тут вряд ли виновен, все же 600 запросов в секунду, из которых две трети служебные — это не такая уж и большая нагрузка.

Ну и если найдете узкие места в первых 5 пунктах, проверяйте запросы EXPLAIN'ом, оптимизируйте их.
Ответ написан
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы