MySQL не справляется с нагрузкой от веб-приложения, нужны логи для дальнейшего разбора

Question

[Валерий Русин]

Приветствую!

Я являюсь одним из разработчиков некоего программного комплекса (PHP), использующего для нужд хранения информации базу данных MySQL. В прошлое воскресенье, при выполнении довольно сложных запланированных задач, MySQL с трудом выдержала нагрузки, сопровождаемые большим количеством таймаутов ожидания блокировки (Lock wait timeout exceeded; try restarting transaction) и огромным количеством простых запросов, ожидающих своей очереди в InnoDB queue уже как около 3-х (!) часов.

Вопрос заключается в следующем: через небольшой промежуток времени, а именно уже в эти выходные, будут выполнятся подобные по объему и сложности задачи. Есть необходимость подготовиться, чтобы либо на месте понять причину блокировок и найти просчеты архитектуры, либо (приоритетнее) получить максимальное количество полезной информации для дальнейшего разбора, например: состояние блокировок в определенное время, запросы, блокирующие таблицы, запросы, которые так и не получили возможность выполниться из-за блокировки. Одного лога медленных запросов будет не достаточно. Какими инструментами можно собрать подобную информацию?

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

Answer 1

[Пума Тайланд]

Однозначно вам надо было бы выложить свой my.cnf
и параметры сервера mysql
а также включить mysql slow query log и выложить его сюда.
Советы ставь перкону тут больше от лукавого чем в помощь.

Answer 2

[Nilard]

Мне помогла настройка по подобным статьям:
www.pentarh.com/wp/2011/03/02/mysql-innodb-highload-optimization/
www.opennet.ru/base/dev/mysql_innodb_tune.txt.html

Производительность выросла в несколько раз, до этого были жуткие тормоза на большом количестве одновременных запросов, а с таблицами в несколько сот тысяч строк вообще еле-еле ворочался. Сейчас все летает. В принципе, достаточно несколько директив в my.cnf добавить.

Answer 3

[Nastradamus]

У меня для таких целей был скриптик, работающий в фоне: раз в 5 секунд считает количество активных тредов MySQL, и если кол-во запросов больше N, то он дампит исполняющиеся запросы в лог-файл.

Именно таким способом были выявлены основные блокирующие запросы.

Для FreeBSD, запускался через screen:

#!/bin/sh

HOSTNAME=`hostname -s`
DIR="/var/log/mlog.$HOSTNAME"
pass='testpass'

while :
do
    threads=`mysql -uroot -p$pass -B -e "show status"|grep ^Threads_running|cut -f 2`

# если тредов больше 60, пишем в лог:
    if [ "$threads" -gt 60 ] ; then
        {
            mysqladmin -uroot -p$pass processlist --verbose |grep -v Sleep|sed 's|                   ||g'
            iostat -dNx 2 2            
        } |gzip > "$DIR/`date "+%Y-%m-%d_%H:%M:%S"`.list.gz"
    fi
    sleep 5
done

Answer 4

[Dzen_Marketing]

Однозначно ставь Percona, там более полная статистика по планам и вообще производительности, нежели у ванильного мускуля

Comments

[Валерий Русин]

в перспективе — возможно. но до следующих выходных мы точно не успеем (:

[Dzen_Marketing]

Установка перконы это полчаса.

[Валерий Русин]

именно техническая установка — да, вполне возможно, что полчаса. принятие решения, оценка рисков и прочие подготовительные формальности — заметно больше времени.

[olean]

На тестовый сервер накатите, прогоните тесты и всё.
Или риски касаются возможных будущих проблем с лицензией?

Answer 5

[nochkin]

Кроме my.cnf было бы полезно ещё указать конфигурацию сервера (минимально — сколько памяти и cpu) и посмотреть «top» в момент нагрузки.
У меня было похожее когда нагрузка стала вырастать на сервере. Настраивал постепенно через my.cnf и добавлением памяти.
Тут нашёл достаточно много интересных советов и идей: www.mysqlperformanceblog.com/

Answer 6

[Александр Борисович]

А почему не достаточно одного лога медленных запросов? Ведь сам MySQL особо то… ничего не дает для анализа.

Comments

[AxisPod]

Ну если поглядите на dev.mysql.com/doc/refman/5.5/en/show-profile.html то поймете, что slow log далеко не единственное средство.

[Валерий Русин]

не столько беспокоят медленные запросы, сколько неявно блокирующие запросы, из-за которых остальным приходится ждать, долго ждать.

Answer 7

[Alexey Kamchatkin]

Почитайте статейки по работе MySQL.
Если у Вас настройки сервера те которые по-умолчанию то, рекомендую их исправить на правильные — в сети есть информация, как правильно настроить InnoBD конф-ю для максимального роста производительности сервера.
Также для MySQL нужно быстрое «железо».
Вам с первых ответов рекомендуют ставить Percona, но уверяю Вас не стоит прибегать к Percona,MySQL InnoBD уже подрос и не плох, как были предыдущие версии.
Вообщем начните с этого, а потом посмотрите логи.удачи.