Как настроить mysql в debian для высокой нагрузки?

Question

[Владимир Мерк]

Здравствуйте.
На сервере (8 ядер, 32gb, linux debian) mysql 5.5.31 - выполняется до нескольких тысяч запросов за раз.
С утра все в порядке, но с 15:30 и до вечера нагрузка повышается и nginx начинает постоянно выдавать 502 ошибку.

Когда все в порядке:
screencast.com/t/ZpATtcji
Длительность запроса 0,3сек в среднем. Т.е. сам MYSQL запрос не очень тяжелый.

Когда все плохо:
screencast.com/t/PsUD9fIssttL

При этом команда top показывает нагрузку на ЦП процесса mysql 100%-350%.
И что еще хуже, временами выдает ошибку "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (11)"

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

Вот файл my.cfg:
yadi.sk/d/SF7hykLEHMmTr

Для предотвращения ошибки "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (11)" установил open_files_limit 8192, правда бестолку. Вроде как еще и в самом линуксе нужно что-то поправить, но непонятно что.

В логах апача и mysql ничего. Никаких ошибок и такое ощущение, что логгирование просто не работает толком.

Я явно что-то упустил. Конфигурация сервера, то, совсем не слабая и эти 2-3 тысячи подключений наверняка не предел.

Заранее благодарю за помощь.

Comments

[Александр]

пробовали переключать mysql с socket на tcp/ip?

[Владимир Мерк]

Нет, не пробовал. Для этого при подключении к бд нужно указать "127.0.0.1" вместо "localhost"? При этом проблем при большом количестве подключений не возникнет?

Answer 1

[Vit]

Во-первых, надо привести в порядок базу данных - расставить индексы, где надо.
Во-вторых - оптимизировать само приложение, возможно от многих запросов удастся избавиться.
В mysql можно включить лог медленных запросов (slow query log)

Что касается настройки mysql, то в случае innodb могу посоветовать следующие рекомендации www.pentarh.com/wp/2011/03/02/mysql-innodb-highloa...

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

Comments

[Владимир Мерк]

По поводу индексов, я правильно понимаю, что нужно ставить их на поля, которые используются в условии WHERE?

slow log включен, в my.cfg, но сам файл лога остается пустым, хотя для медленных запросов установлено 3 секунды.

На innodb работают две таблицы, как раз к ним основное количество запросов и выполняется. Рекомендации посмотрю.

Если бы проблема была в количестве одновременных подключений, mysql бы выдавал "Too many connections", установлено довольно большое значение в 2000, как раз хватает. Тут же проблема с загрузкой процессора.

[Vit]

@VladimirMerk Поставьте в slow query log время поменьше. Имеет смысл даже меньше секунды поставить. Там же можно поставить галочку, чтобы логировались запросы без индексов - может вам помочь. А вообще темя составления индексов заслуживает отдельного внимания. Если вкратце, то индексы должны быть по колонкам, участвующим в WHERE. Если по нескольким колонкам делается выборка с оператором AND, то имеет смысл делать составной индекс. Но это, повторюсь, отдельная и довольно обширная тема.

[Владимир Мерк]

@fornit1917 Позвольте задать еще вопрос, не относящийся к основной теме, по поводу индексов. Вот смотрите, у меня очень сложный запрос, с несколькими подзапросами и лефт джойном. Для примера приведу абстрактный запрос, несколько сокращенный, с похожей же структурой:
yadi.sk/d/HozAiEVQHNJoY
Суть в том, что у меня выборка по условию сравнения нескольких таблиц, да еще и подзапросы. Я пробовал все поля которые в WHERE участвовали добавить в составной индекс, но запрос после этого стал выполняться в разы дольше. Что бы Вы могли порекомендовать?

[Vit]

@VladimirMerk
1. Избавляйтесь от подзапросов. Подзапросы зло.
2. Если не получается - меняйте подзапрос типа IN или NOT IN на EXISTS или NOT EXISTS, а внутри подзапроса не делайте чтение данных из таблицы (выбирайте константу). Пример:

SELECT id FROM table1 WHERE NOT EXISTS
(
  SELECT 1 FROM table2 WHERE table2.column = table1.id
)

3. Для того, чтобы понять, какие индексы следует добавить, используйте оператор EXPLAIN. О том что это и как применять в интернете информации много.

4. Бывает так, что если один сложный запрос разбить на несколько простых и выполнить каждый из них отдельно - в итоге скорость будет выше.

[Merlyel]

--log-slow-queries[=file_name]
Deprecated 5.1.29, by slow-query-log

....
The destination for log output is controlled by the log_output system variable; if that value is NONE, no log entries are written even if the log is enabled.

Answer 2

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

Включите slow log и посмотрите какие запросы ложат mysql
запустите mysqltuner.pl и он вам скажет какие стандартные оптимизации можно сделать
наймите нормального сисадмина и живите спокойно.

Comments

[Владимир Мерк]

slow log включен, в my.cfg, но сам файл лога остается пустым, хотя для медленных запросов установлено 3 секунды.

Тюнер запускал, рекомендации по мере знаний выполнил.
screencast.com/t/hcuky9u723R5
query_cache_limit - сколько не устанавливай, тюнер будет писать что нужно больше

Answer 3

[Merlyel]

0) а вы уверены, что ошибка из-за мускуля? может, у вас что-то с php (если он используется) или с самим nginx? его логи смотрели?
1) почитать про persistent mysql connections
2) добавить логирование запросов без индексов
3) почитать, что пишет mysqltuner/tuning-primer - бывает советуют полезные вещи
4) проверить лог сервера и веб-сервера, может они тоже ругаются на количество открытых файлов - тогда нужно править эти лимиты в системе (например, вот один мануальчик www.cyberciti.biz/faq/linux-increase-the-maximum-n...)
5) выделите больше памяти mysql, особенно innodb
6) таймауты очень большие - 4 часа - возможно, у вас висит куча неиспользуемых процессов
7) зайти в ЧНН в консоль mysql и посмотреть список процессов (show processlist;) - посмотреть, что там происходит, возможно сразу будут видны locks
8) переключение на tcp/ip может помочь с ошибкой на открытые файлы, но одновременно может уменьшить производительность

While you don't hit the entire IP stack when going over localhost, you still hit a big part of it. A unix socket is essentially just a 2-way pipe. It's faster and lighter.

да и в любом случае - это временный обход проблемы, а не решение. Просто рано или позже эта же ошибка вылезет в другом месте
9) посмотрите на значения iowait в top/iotop - возможно, проблема не в запросах, а в дисковой подсистеме. В таком случае может помочь вынос мускуля на отдельный раздел
10) попробуйте посмотреть загрузку по процессорам (нажать 1 в top), возможно один-два процессора забиты под завязку, а остальные простаивают. Возможно, в таком случае будет смысл разнести задачи по процам

Answer 4

[ChemAli]

Это веб-сервер? Если да, сначала расставьте контрольные точки по коду веб-приложения и посмотрите на каких участках оно спотыкается или долго исполняется с упором в БД. Оттуда увидите проблемные запросы и поймете где узкие места.

Answer 5

[Алексей Помогаев]

Гуглите mysql ulimit например duntuk.com/how-raise-ulimit-open-files-and-mysql-o...

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

Answer 6

[Влад Животнев]

Пишите в почту, попробую поделиться подходящим конфигом.
А вообще - переходите вы на перкону 5.5 уже)

Answer 7

[Дэн]

Попробуйте настроить параметры по этому руководству.
Из инструментов для анализа - munin для графиков, mysql/fpm slow log, maatkit для анализа этих логов.