max_user_connections

[freakenergy 0]

Здраствуйте, на сайте два дня назад начался постоянный перегруз. Всегда пишет User srv11886_freake already has more than 'max_user_connections' active connections либо просто не грузится и выдает Время истекло.

Не знаю после чего это началось, сторонних модулей на сайте практически нет, стоит только блок Топа юзеров, который кешируется и интеграция с форумом SMF (но база форума находится на другом аккаунте, т.е. не создает нагрузки).

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

На хостинге у нас дорогой тариф, да и посещаемость не такая большая (5 тысяч).

Сайт: http://www.freakenergy.ru/

Целый день мучались с тех поддержкой хостинга, они также не смогли ничем помочь, изменили в engine.php запросы с :

Это версия движка была 7.0, сегодня обновились до 7.2, проблемма не исчезла.

Нами была проанализирована проблема.

Запросы вида

SELECT SQL_CALC_FOUND_ROWS id, autor, date, short_story, SUBSTRING(full_story,

1, 15) as full_story, xfields, title, category, tags, alt_name, comm_num,

allow_comm, allow_rate, rating, vote_num, news_read, flag FROM srv11886_post

WHERE approve = '1' AND allow_main = '1' ORDER BY fixed desc, date DESC LIMIT

0,10;

которые предназначены для вывода последних новостей на главной странице,

используют сортировку после выборки.

т.к. mysql не может хранить все записи в оперативки (таблица около 70mb), то

используется метод filesort (создание и сортировка во временном файле) более

16000 строк.

В итоге каждый такой запрос обрабатывется по 10-60 секунд.

Вывод, данная версия движока, который у Вас уситановлен (DLE) не предназначен

для таких больших обьёмов данных. Оптимальным решение будет сменить его на

другую версию или вообще другой движок.

Пока мы немного заменили запрос в скрипте [engine/engine.php] (не затирайте

наши изменения) с

SELECT SQL_CALC_FOUND_ROWS id, autor, date, short_story, SUBSTRING(full_story,

1, 15) as full_story, xfields, title, category, tags, alt_name, comm_num,

allow_comm, allow_rate, rating, vote_num, news_read, flag FROM srv11886_post

WHERE approve = '1' AND allow_main = '1' ORDER BY fixed desc, date DESC LIMIT

...

на

SELECT SQL_CALC_FOUND_ROWS * FROM srv11886_post WHERE approve AND allow_main

ORDER BY fixed desc, date DESC LIMIT ...

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

достаточно большой.

Есть и другие тяжёлые запросы, которые в обызательном порядке должны быть

переписаны с учётом работы с большой базой данных.

Приведенное выше изменение запроса, практически ничем не помогло.

Приведенная ниже цитата, это уже после обновления и с измененным как выше запросами в engine.php (со стандартными запросами сайт лежал). сайт также в ауте.

Ограничение на 50 соединений.

В списке процессов начали повисали кучи запросов вида

UPDATE LOW_PRIORITY srv11886_users set lastdate='1219160413' where

user_id='54671'

UPDATE LOW_PRIORITY srv11886_users set lastdate='1219160359' where

user_id='54147'

UPDATE LOW_PRIORITY srv11886_users set lastdate='1219160345' where

user_id='15788'

, наверно, что то сервисное движок начал по расписание прокручивать.

Изменено 19 августа 2008 пользователем freakenergy

[celsoft 6 094]

Смотрите есть ошибка

User srv11886_freake already has more than 'max_user_connections' active connections

что означает достигнут лимит на максимальное одновременное количество подключений к БД, но посещаемость у вас небольшая как вы говорите, поэтому достигнуть лимит в 50 соединений крайне сложно.

В списке процессов начали повисали кучи запросов вида

UPDATE LOW_PRIORITY srv11886_users set lastdate='1219160413' where

user_id='54671'

UPDATE LOW_PRIORITY srv11886_users set lastdate='1219160359' where

user_id='54147'

UPDATE LOW_PRIORITY srv11886_users set lastdate='1219160345' where

user_id='15788'

, наверно, что то сервисное движок начал по расписание прокручивать.

в этом и есть причина, сервер MySQL не выполняет и не завершает запросы типа UPDATE, это не сервисные запросы скрипта, это обновление информации о том когда пользователь был последний раз на сайте, выполняется данный запрос только один раз в 4 часа, для каждого зарегистрированного пользователя. Но ваш сервер невыполняет данный запросы и не закрывает соединение с БД, хотя в скрипте это принудительно установлено. К тому же любые зависшие запросы должны удалятся сервером MySQL по таймауту, а они у вас невыполняются и автоматичски неудаляются, отсюда и возникает превышение лимита на количество одновременных подключений к БД

Нужно смотреть настройки MySQL, если это началось неожиданно 2 дня назад, то видимо внесли изменения в кофигурацию MySQL сервера, раньше он у вас исправно выполнял указанные запросы и не возникало списка зависших запросов

[freakenergy 0]

Т.е. эта проблема со стороны хостера?

Меня еще смущает то что раньше все было нормально, и посещаемость сайта была на 2 тысячи больше.

[celsoft 6 094]

Т.е. эта проблема со стороны хостера?

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

[freakenergy 0]

Спасибо за оперативность, процитировал хостеру ваше сообщение, жду ответа. Позже отпишусь.

[Al-x 7]

Не думаю, что дело в движке, точнее в том, что он стал причиной сейчас, но сервер он действительно грузит.

celsoft,

используется метод filesort (создание и сортировка во временном файле)

тут они правы. Движок будет использовать подобную сортировку даже при 500 килобайтах базы данных.

Кстати тоже ожидает и dle форум

freakenergy,

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

[freakenergy 0]

Al-x,

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

[Al-x 7]

блин, не то написал) думал не отправил пост)

Давайте действительно подождём, держите в курсе, мне тоже интересна тема

Изменено 19 августа 2008 пользователем Al-x

[celsoft 6 094]

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

также меня очень смутило что хостер сделал замену запроса на

SELECT SQL_CALC_FOUND_ROWS * FROM

даже школьнику ясно что из БД нужно выбирать только те поля которые будут задействованы скриптом. Поставив * просто напросто увеличили расход памяти, т.к. например вы что на главной выводите полные новости? Нет, а из БД они берутся и передаются в скрипт, соответственно возрастает траффик между MySQL и PHP, а также на выполнение скрипта необходимо больше памяти под массивы с данными. Где логика оптимизации.

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

[max-money 0]

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

Ничего странного, хостер прав, но по сути ничего не поменял.

Вот пример стандартного запроса:

SELECT SQL_CALC_FOUND_ROWS id, autor, date, short_story, SUBSTRING(full_story, 1, 15) as full_story, xfields, title, category, alt_name, comm_num, allow_comm, allow_rate, rating, vote_num, news_read, flag, editdate, editor, reason, view_edit, tags FROM dle_post WHERE category IN ('12') AND approve = '1' AND date < '2008-08-20 12:26:17' ORDER BY fixed desc, date DESC LIMIT 0,10

уберу ненужное для анализа:

SELECT * FROM dle_post WHERE category IN ('12') AND approve = '1'  ORDER BY fixed desc, date DESC LIMIT 0,10

Если его заменить на:

SELECT * FROM dle_post WHERE category IN ('12') AND approve = '1'  ORDER BY date DESC LIMIT 0,10

Будет колосальный прирост производительности на больших базах.

Запрос который используется сейчас, сначала с десятков тысяч записей используя индекс очень быстро находит несколько fixed, но потом полностью без использования индекса лопатит всю базу строя сортировку по date. Если category много (относительно общего количества записей в базе) нужно принудительно указывать использование индекса category. Если нет, принудительно инспользовать индекс date.

А fixed давно пора отправить в другую таблицу или ихних id в текстовый файл, и делать 2 запроса, оставив возможность полностью отключить fixed.

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

Изменено 20 августа 2008 пользователем max-money

[celsoft 6 094]

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

Анализ запроса

SELECT * FROM dle_post WHERE category IN ('12') AND approve = '1'  ORDER BY fixed desc, date DESC LIMIT 0,10

1 SIMPLE dle_post ref category,approve category 202 const 1 Using where; Using filesort анализ запроса

SELECT * FROM dle_post WHERE category IN ('12') AND approve = '1'  ORDER BY date DESC LIMIT 0,10

1 SIMPLE dle_post ref category,approve category 202 const 1 Using where; Using filesort

Я что то неувидел разницы в использовании ключей, может ваша БД выдает другие результаты, тот же ключ для категорий, тот же Using filesort, единственное что на больших БД сортировка длится дольше, но опять таки все отключается в самих настройках.

[freakenergy 0]

Мой ответ им:

===============

Цитата *

Т.е. эта проблема со стороны хостера?

---

думаю да, если была посещаемость больше и все работало, и вы не вносили никаких

изменений в скрипт, ничего не происходит просто так, небывает так что работало и

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

поэтому если вы ничего не делали то, значит внесли изменения на сервере,

например сменили ПО.

---

Никаких изменений один-три дня назад на сайте не было, и сегодня мы обновили

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

Ответ поддержки:

Доброго времени суток !

Мы никакое ПО не меняли. Судя по тому, что мы видели, проблемы возникают в базе

данных. По всей вероятности, либо там что-то случилось с данными, и теперь часть

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

что-то переделывать в её архитектуре. В любом случае, всё это поддаётся анализу.

Проанализируйте проблнемные запросы с помощью команды EXPLAIN в MySQL - будет

видно, где происходит затык. Варианты запросов мы вам приводили.

Главное, что сегодня в 8 утра все стало нормально, сайт работает в обычном режиме, ничего не тормозит, хотя никто никаких изменений не вносил (судя по словам тех. поддержки). Видимо кто-то что-то умалчивает, либо все само восстановилось =) Сплошные загадки )

Хостинг: http://www.ht-systems.ru/ (не сочтите за рекламу)

[Al-x 7]

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

На сколько мне известно - решением является отделение текстовых полей с большими объёмами информации в отдельные таблицы и выведение текста присоединением таблиц.

В этом случае сортировке подвергается только первая таблица, имеющая краткую информацию (все поля, типа даты, ид, урл, ид пользователя, поля фиксации и публикации и т.д.). Вторая таблица с текстом подключается правым (или inner) присоединением.

При тестировании с помощью EXPLAIN мускул выдал использование filesort только на первой таблице, на второй же не используется даже where или индексы.

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

Попробуйте проведите подобные эксперементы, возможно они подтвердят мои слова)

Изменено 22 августа 2008 пользователем Al-x

[max-money 0]

На сколько мне известно - решением является отделение текстовых полей с большими объёмами информации в отдельные таблицы и выведение текста присоединением таблиц.

Полностью поддерживаю, на базах от 200 мб значительный прирост производительности.

[IT-Security 33]

Правый JOIN???Может всё таки левый???Правый сам тормозной...

[lifestar 18]

INNER используйте и будет вам.. спорить ещё вздумали))

А так Al-x прав

[Al-x 7]

Правый JOIN???Может всё таки левый???Правый сам тормозной...

ошибочка вышла, иннер)