Монитор загрузки MySQL

Question

[julos]

Друзья, такая ситуация — есть отдельный сервер под мускуль, на котором крутится один сайт с относительно небольшой посещаемостью(100k хитов в сутки), сайт сам по себе достаточно крупный, в нем много логики и отдельных сервисов, поэтому присутствуют и медленные запросы, при этом сервер базы спокойно живет несколько недель, пока в один момент не уходит в даун, помогает остановка веб-сервера на несколько минут, чтобы mysql завершил все запросы, после чего тот снова в строю

так вот, мой вопрос — как узнать из-за каких именно запросов(или их совокупности) сервер уходит в даун? подскажите плз средства для этого

просмотр slow query log и show proccesslist особо ничего не дает т.к. медленные запросы есть и от них никуда не деться, но ведь и с ними он прекрасно может работать месяцами

Answer 1

[julos]

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

Answer 2

[Jurik]

Воспользуйтесь утилитой mytop.

Comments

[julos]

поставил, посмотрел — по сути оболачка над SHOW PROCESSLIST, моей проблемы не решает

Answer 3

[julos]

щас смотрю top — mysqld жрет 150-200% CPU, смотрю SHOW PROCESSLIST(и mytop) — с обновлением в секундуто проскакивает 5-10 запросов, то пусто, долго никто не висит… при этом база откровенно поттупливает даже на элементарных запросах

в чем может быть дело?

Comments

[julos]

libffi.so.5: cannot open shared object file: No such file or directory
чет не хочет под центос 6 она вставать, может есть альтернативы?

[julos]

поставил пакет sysstat в который входит iostat, запустил как вы написали, тока с интервалом в 1 секунду
параметр %util выглядит очень скачущим — несколько секунд выдает 1-10%, потом скачки 30,50,100% потом опять 1-10%, иногда несколько секунд держится 60-80%

наверное это означает что в mysql периодически приходят тяжелые запросы которые и сжирают ресурсы на несколько секунд?

из описания я не очень понял что означает эта утилизация, если вы знаете поясните плз

[julos]

диски RAID 1, так что ОС вместе с базой, но на разных разделах (на этом сервере крутится только база, веб-сервер на другой физической машине)

утилиту запустил

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Reduce your overall MySQL memory footprint for system stability
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    join_buffer_size (> 128.0M, or always use indexes with joins)
    tmp_table_size (> 128M)
    max_heap_table_size (> 128M)

[julos]

еще пишет что у меня много «Joins performed without indexes» — а как найти это запросы-то?

Answer 4

[Степан]

Из вашего объяснения я понял что база не уходит в даун, а просто собирается большая очередь запросов?
Какого рода запросы? insert/select/update?

Посещаемость сайта имеет не большую роль в нагрузке… Все зависит от структуры базы, ее размеров, действием над данными в ней. Можно повесить mysql и одним запросом на очень долгий период.

Comments

[Степан]

Если проблема в выборке, используйте кеш если это возможно. Почти на любых проектах не везде нужны актуальные данные за эту секунду. Кеш slow query запросов может дать прирост в производительности в разы!

Возможно у вас проблема с апдейтами данных. Если таблица имеет структуру myisam, при апдейте поля лочиться вся таблица и запросы стают в очередь пока запрос не выполнится.

[julos]

капитанские истины про кеш и оптимизацию я тоже понимаю, можно это опустить

вопрос не в том, как решить проблему падения сервера, а как выяснить что конкретно к этому приводит, почему он 3 недели работает норм даже с тяжелыми запросы(которые кстати выполняются в бэкенде и очень трудно поддаются оптимизации), а в один момент перестает отвечать, мне нужно понять где конкретно затык

[julos]

по теме:
таблицы InnoDB
каких запросов больше — на чтение или запись я не знаю, наверняка на чтение больше запросов, но и на запись немало

как узнать каких запросов больше? какими инструментами?

[Степан]

пока в один момент не уходит в даун, помогает остановка веб-сервера на несколько минут

Уходит в даун или собирается огромная очередь? Ваши действия и объяснения говорят только о том, что база то работает! То что вы стопите веб-сервер просто не дает новых запросов и база оживает.
Или вы все таки базу ребутите?

[Степан]

как узнать каких запросов больше? какими инструментами?

SHOW GLOBAL STATUS

[julos]

а какие конкретно значения там смотреть?

да, база работает, но новые запросы не проходят — mysql has gone away

это означает что собралась слишком большая очередь запросов? а как посмотреть эту очередь и как задавать её лимит?

[Степан]

Возможно к вам в гости просто заходит поисковик и ваша база перестает отвечать:)

Смотрите slow query log во время вашего «ухода в даун».
Эти запросы и вешают вашу базу.
Очередь можно смотреть через show processlist.

Answer 5

[AGvin]

www.fromdual.com/tools + Zabbix
А вообще, ранее был такой вопрос (http://habrahabr.ru/qa/10967/ )

Comments

[AGvin]

Вот более точная ссылка www.fromdual.com/mysql-performance-monitor

[julos]

мощно, надо разбираться, а там можно посмотреть медленные запросы предшествовавшие уходу в даун?

[AGvin]

на сколько знаю, мониторинг Slow Queries есть, он должен помочь с разбором ситуации. Но есть ли, просмотр запросов которые спровоцировали падение — я не в курсе.
В принципе, только что напоролся, на вот такой список: www.thehub.net/index.php?page=search/web&search=mysql+performance+monitor
Думаю, он Вам будет интересен =)

[julos]

о, спасибо за ссылку, много интересного

[AGvin]

Пожалуйста )

Answer 6

[eresik]

Поищите, недавно была статья как сервер падал раз в 4 недели из-за переполнения auto_increment, может ваш случай.

Comments

[AGvin]

Вот она, Поведение INSERT…ON DUPLICATE KEY UPDATE в крайней ситуации. Проблема будет воспроизводиться, в случае большого количества «INSERT…ON DUPLICATE KEY UPDATE», когда исчерпываются значения auto increment -а

[julos]

спасибо, но с автоинкрементами все в порядке

Answer 7

[lubezniy]

Железо (винт или RAID, на котором крутится БД) в порядке?

Comments

[julos]

ну ошибки не лезут, наверное в порядке (RAID 1)

[lubezniy]

Ошибки не обязательны. На одиночном диске мы столкнулись как-то с проблемой: при отсутствии ошибок и нормальном SMART на каких-то местах пошли сильные провалы по скорости чтения/записи с зависанием системы (Windows) в процессе.

[julos]

ребят напиши плз как проверить это… если дело окажется в винтах и я все правильно понял это значит что mysql уткнулась в производительность винтов?

[lubezniy]

В нашем случае мы «проверили» тупо заменой винта (склонировали всю инфу на новый с помощью Acronis MigrateEasy). Как конкретно в Вашем RAID такие вещи делать, не знаю.

Answer 8

[julos]

еще давний вопрос на который все руки не доходят узнать ответ

имеем запрос:
SELECT id FROM something WHERE param1 = 1 param2 = 2 param3 = 3

структура таблицы состоит только из этих 4х полей участвующих в запросе

вопрос — если структура таблицы будет состоять из 100 полей, а не 4х, будет ли выполняться медленнее указанный запрос?

Comments

[Евгений Николаев]

Тут зависит от условия выборки. Самый быстрый способ это конечно бить по индексу.

Если я правильно понял вопрос, то скорость выборки не изменится из-за большего количества полей, если конечно вы их не включите в условия.

Answer 9

[shagguboy]

если по этим полям есть индекс то разница будет заметна только приборами

Comments

[julos]

а если индексов нет или он не правильный, и записей мильоны, будет ли разница между таблицей в 4 поля и в 100 полей, если в запросе фигурируют только 4 поля?

[shagguboy]

тогда да. серверу придется прочитать всё это с диска, сравнить значение полей с теми что в запросеи выдать нужные.