Систематические многочисленные запросы "UPDATE ibf_cache_store SET

[daitepiva]

Есть довольно большой форум на движке 2.3.6 - днём больше 6 тысяч пользователей онлайн. Два выделенных сервера - под сайт и под БД. Обычно работает вполне бодро, но систематически, довольно точно раз в час, СУБД забивают несколько сотен запросов вот такого вида:

UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400501160\";s:9:\"loadlimit\";N;s:10:\"mail_queue\";i:0;s:16:\"last_virus_check\";N;s:19:\"last_deepscan_check\";N;}' WHERE cs_key='systemvars'

Например:

| 1693309 | d5 | d6:40208 | d5 | Query | 9 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693310 | d5 | d6:40215 | d5 | Query | 13 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693311 | d5 | d6:40218 | d5 | Query | 1 | query end | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:7:{s:12:\"total_topics\";i:33932719;s:13:\"total_r |
| 1693312 | d5 | d6:40220 | d5 | Query | 9 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693313 | d5 | d6:40223 | d5 | Query | 3 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693314 | d5 | d6:40224 | d5 | Query | 12 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693315 | d5 | d6:40225 | d5 | Query | 3 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693317 | d5 | d6:40233 | d5 | Query | 9 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693318 | d5 | d6:40234 | d5 | Query | 8 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693319 | d5 | d6:40236 | d5 | Query | 12 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693320 | d5 | d6:40238 | d5 | Query | 10 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693321 | d5 | d6:40240 | d5 | Query | 11 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |
| 1693322 | d5 | d6:40263 | d5 | Query | 3 | Updating | UPDATE ibf_cache_store SET cs_array=1,cs_value='a:5:{s:13:\"task_next_run\";s:10:\"1400740920\";s:9: |

Лимит подключений к БД (400) забивается и сайт недоступен несколько минут, а то и больше.

 

Все таблицы в ИнноДБ. Оптимизацию для этой таблицы делал.

Вот статус таблицы:

show table status like 'ibf_cache_store';
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
| ibf_cache_store | InnoDB | 10 | Compact | 22 | 102027 | 2244608 | 0 | 0 | 4194304 | NULL | 2014-05-20 07:50:32 | NULL | NULL | cp1251_general_ci | NULL | | |
+-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

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

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

 

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

 

Короче нужен совет )

Изменено 22 Мая 2014 пользователем malik

[Denis Chursinov]

Вообще запросов на обновление кеша у вас довольно много должно быть и между запусками задач. А индексы у этой таблички на месте (cs_key)? 
А может есть какая-то задача на самом сервере, которая нагружает систему и запросы не проходят?

[bifidokk]

Запросы долго висят или всё же выполняются? Дедлоков не наблюдаете?

[daitepiva]

Вообще запросов на обновление кеша у вас довольно много должно быть и между запусками задач. А индексы у этой таблички на месте (cs_key)?  А может есть какая-то задача на самом сервере, которая нагружает систему и запросы не проходят?

Да, единственный индекс на месте:

describe ibf_cache_store;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| cs_key   | varchar(255) | NO   | PRI |         |       |
| cs_value | longtext     | YES  |     | NULL    |       |
| cs_extra | varchar(255) | NO   |     |         |       |
| cs_array | tinyint(1)   | NO   |     | 0       |       |
+----------+--------------+------+-----+---------+-------+

Возможно и не сами эти запросы виноваты, а какая-то другая задача. Но очередь в БД забита именно этими запросами и они в топе, например, при просмотре mtop-ом, другие запросы есть, но совсем мало.

На этом сервере никаких других задач нет, только MySQL.

 

 

Запросы долго висят или всё же выполняются? Дедлоков не наблюдаете?

Судя по времени выполнения запроса в столбце Time запросы выполняются, максимальное время этих запросов, которое я видел 20-30 секунд. Если бы запросы просто висели, то время было бы намного больше.

Дедлоков вообще ни разу не видел, ни в show process, ни в логах. Бывают блокировки "Waiting for table metadata lock" или query cache (query_cache_size у меня вроде небольшой - 45 МБ), но редко.

Изменено 23 Мая 2014 пользователем malik

[Denis Chursinov]

Как-то очень это все странно выглядит. Запросы выполняются, висят со статусом update, т.е. они не ждут блокировки, не висят в очереди, а выполняются параллельно, но медленно. Может быть уменьшить количество тредов? Возможно файловая система не справляется с таким количеством запросов на запись и их будет эффективнее держать в очереди. Поэкспериментируйте с innodb_thread_concurrency и innodb_thread_sleep_delay. 
innodb_thread_concurrency обычно ставят не больше, чем (наименьшее из количества дисков и количества CPU) *2 . Т.е. при 4 ядрах и 2 дисках в массиве будет 4.
innodb_thread_sleep_delay это задержка в микросекундах при повторной проверке количества тредов. Механизм там такой: запрос приняли, проверили, есть ли свободный тред, если нету, то подождали innodb_thread_sleep_delay и проверили снова. Если окно появилось, то выполняем, если нет, то ставим в конец очереди на исполнение.

Ну и innodb_write_io_threads и innodb_read_io_threads тоже стоит посмотреть.

[Arhar]

на движке 2.3.6 - днём больше 6 тысяч пользователей онлайн

на 2.3.6 должен уже быть memcache/xcache, в который как раз таки кладется все из таблицы кеш стор

на 6к пользователей имеет смысл доустановить один из вышеперечисленных

наугад скажу

sudo apt-get install php5-xcache

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

xcache.size 128M - всего сколько таблица кеша весит, ставьте под свою

xcache.var_size 16M - сколько весит одна самая большая запись в ней

[daitepiva]

 

 

 innodb_thread_concurrency и innodb_thread_sleep_delay

innodb_thread_concurrency=0 в документации к MySQL 5.5 сказано, что это означает бесконечное число тредов. Вообще у меня два проца Intel Xeon по 6 ядер, памяти 44 ГБ, массив RAID-50 из 8 дисков.

innodb_thread_sleep_delay было по-умолчанию 10000 мс, поставил 3000 мс, изменений не заметил, может ещё меньше сделать? Как бы посмотреть сколько запросов попадает в этот цикл ожидания?

 

 

 

Ну и innodb_write_io_threads и innodb_read_io_threads тоже стоит посмотреть.

И то, и другое я не задвал и по-умолчанию равно 4. Не уверен, что есть необходимость, в выводе команды "SHOW ENGINE InnoDB STATUS" не совсем понятно состояние тредов чтения и записи, так что не менял. Однако, сейчас подумал и решил указать 8. Правда поменять параметры на лету нельзя, а перегрузить СУБД пока не могу.

 

 

 

на 2.3.6 должен уже быть memcache/xcache, в который как раз таки кладется все из таблицы кеш сторна 6к пользователей имеет смысл доустановить один из вышеперечисленных

Как-то уже экспериментировал с memcache - никакой разницы не заметил, ни по этой проблему, ни вообще. Попробую ещё.

 

Стал чаще замечать query cache lock, выключил query_cache. mysqltunner показывает эффективность query_cache около 20%. Вроде неплохой показатель. Верну, наверное, кеш обратно, но сделаю его поменьше.

 

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

[Denis Chursinov]

innodb_thread_concurrency получается можно попробовать поставить около 50 . innodb_thread_sleep_delay не должен работать при неограниченном количестве тредов, очередь же не создается в этом случае MySQLем. 

Memcached работает через сетевую систему, а xcache напрямую. Xcache должен быть быстрее в случае одного сервера.

[daitepiva]

 

 

innodb_thread_concurrency получается можно попробовать поставить около 50 . innodb_thread_sleep_delay не должен работать при неограниченном количестве тредов, очередь же не создается в этом случае MySQLем. 

Опа... неожиданно. Поставил 50, благо на ходу можно поменять.

Как бы посмотреть сколько этих самых тредов используется в данный момент?

 

 

 

Memcached работает через сетевую систему, а xcache напрямую. Xcache должен быть быстрее в случае одного сервера.

У меня два сервера, так что, получается, надо пробовать memcached. Спасибо.

[Denis Chursinov]

Как бы посмотреть сколько этих самых тредов используется в данный момент?

Так список висящих запросов покажет. Сколько запросов висит в активном состоянии, не wait, не lock, не sending data и т.д. , столько и тредов.

Разные источники предлагают разные формулы, вот тут разработчики предлагают 2* количество процессоров + количество дисков. Тогда получается вообще 32.

 

Про несколько серверов я имел в виду, у вас пачка серверов под мемкеш и к ним лезет пачка серверов побольше с php. Ну а в случае с IPB непонятно как можно это использовать. IPB хватит и xcache. Он еще и прекомпилирует сами скрипты. А память на втором сервере приберегите для MySQL

[daitepiva]

 

 

Так список висящих запросов покажет. Сколько запросов висит в активном состоянии, не wait, не lock, не sending data и т.д. , столько и тредов.

Т.е. это параметр Threads_running из вывода команды extended-status? Его я мониторю, график рисую. Обычно их меньше 10, а в те самые проблемные моменты, ради которых создана данная тема, случаются выхлопы и до 400. Кстати, вот тут вся статистика по этому серверу:

http://d7.elcat.kg/my/stat/index.html

Данные собираются каждые 5 минут, а графики обновляются каждые 17 минут.

 

 

 

 

 

Про несколько серверов я имел в виду, у вас пачка серверов под мемкеш и к ним лезет пачка серверов побольше с php. Ну а в случае с IPB непонятно как можно это использовать. IPB хватит и xcache. Он еще и прекомпилирует сами скрипты. А память на втором сервере приберегите для MySQL

Кеш для пхп-код у меня стоит - APC. Кеширует, судя по его же статистике, 100% запросов к скриптам.

memcache, как я понимаю, будет кешировать запросы в базу, что, возможно, снизит нагрузку на MySQL.

 

Сегодня поменял один, судя по некоторым описаниям, параметр - innodb_buffer_pool_instances, у меня было по-умолчанию 1, я поставил 5. innodb_buffer_pool_size у меня равен 15G, я ставил больше, т.к. оперативки у меня 48ГБ, но заметно разрастался свап.

На ходу этот параметр не меняется, СУБД перестартую позднее.

[Denis Chursinov]

интересно. Каждый час в 50минут идет какой-то локальный сброс. Каждый день в 5 утра выполняется какое-нибудь перестроение кешей на форуме, количество запросов максимально. А наши всплески тредов в 12:10 и в 16:10 соответствуют провалам в количестве запросов и коннектов. При этом всплеска запросов им и не предшествует. Нету и всплеска нагрузки на винт или процессор. Непонятно, что же мешает запросам отработать, во что они там упираются.

[daitepiva]

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

В 5:04 перестартовывается MySQL, это я тоже делаю в надежде освободить какие-то ресурсы системы, но, наверное, смысла в этом нет, я убрал эту задачу.

В 5:13 запускается дамп базы при помощи команды mysqldump.

 

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

Также грешил на поисковики. Боролся с ними не только при помощи настроек в файле robots.txt, но даже и файрволом. Конечно, нагрузку они создают, но не катастрофическую.

 

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

Изменено 2 Июня 2014 пользователем malik

[Arhar]

а во встроенном планировщике какая-нибудь обновлялка количества постов у юзеров селектит раз в час всю базу несколько раз подряд...

[Denis Chursinov]

Дак в том-то и дело, что не каждый час. Нет периодики. Нет нагрузки, нет всплеска количества запросов, нет нагрузки на проц или винт. Просто создается 50 процессов, которые одновременно апдейтят одну и ту же  таблицу, а может и строку. И они вроде как выполняются, но не торопясь и при этом ресурсы не заняты. Вот и появилась мысль, что если эти процессы в очереди посидят, то выполнятся они побыстрее, чем все разом.

А по сегодняшним графикам уже затыков и нету вообще. Но всплески количества тредов около 9:50, около 10:50, в 13, в 14 , в 14:40, в 15 и в 16:10. При том в 16 часов провал по коннектам и количеству запросов. Больше похоже, что MySQL скидывает какой-то кеш, синхронизирует таблицу или еще какую-то свою внутреннюю задачу выполняет.

http://c2n.me/ieku2A.jpg

[daitepiva]

а во встроенном планировщике какая-нибудь обновлялка количества постов у юзеров селектит раз в час всю базу несколько раз подряд...

Во встроенном планировщике только одна задача выполняется каждый час - очистка форума. Я её и подозревал, но периодичность не всегда совпадает, бывает и каждые два часа.

 

Задал этот вопрос поддержку Инвижна, сказали что:

The main IP.Board cache (the ibf_cache_store table) gets updated regularly the most likely cause based on the information above is that a scheduled task is running far too often, most likely due to an error

 

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

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

[Arhar]

в этих запросах в сериализованной строке какие ключи видны всегда?

task_next_run, loadlimit, mail_queue, last_virus_check, last_deepscan_check

во всех запросах?

обновление этих значений происходит в двух трех случаях по вине юзера, зашедшего на форум:

- при завершении работы скрипта, если происходит рассылка емеил

- при начале работы скрипта, если этот кеш не загрузился из базы

- при обновлении параметра server_load, который читается командами операционной системы

 

также этот кеш обновляется, если кто-то запостил в тему/форум, в которой есть подписка

 

почему такое происходит со строгой периодичностью - тут надо делать дополнительный дебаг как раз в этих местах (init_load_cache, my_deconstructor, process_mail_queue, topic_tracker, forum_tracker) и смотреть, после выполнения какой из этих команд вываливаются кровь и кишки

на жирный обратить внимание, вдруг в этом и проблема

[Denis Chursinov]

в этих запросах в сериализованной строке какие ключи видны всегда? task_next_run, loadlimit, mail_queue, last_virus_check, last_deepscan_check во всех запросах?

 

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

Задача тогда сводится к тому, чтобы найти, какая функция обновляет systemvars больше 50 раз в секунду.

[bifidokk]

Найти в коде все обновления systemvars, поставить лог на каждую, профит

[Arhar]

не ну вдруг это хардкод мода, а не вызов update_cache

в общем да, впендюрить логи

 

 

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

[daitepiva]

 

 

Найти в коде все обновления systemvars, поставить лог на каждую, профит

 

в общем да, впендюрить логи

Ну это да, именно ковыряния в коде я надеялся избежать )
Во втором письме от поддержки Ивижна мне сообщили, что версия 2.3.6 давно не поддерживается и посоветовали обновиться на 3. Я и сам бы, очень хочу обновиться, я уже и пробовал, но у меня только обновление ПМ длится двое суток, а потом ещё и всё жутко тормозит. Особенно не поэкспериментируешь когда сайт приходится отключать на двое суток.

[Denis Chursinov]

3.4 в целом прожорливее, но таких конкретных затыков поменьше. А эксперементировать надо на отдельном сервере наверное. PM можно, кстати, и не обновлять сразу, их можно потом скриптом из консоли конвертнуть. 

[daitepiva]

3.4 в целом прожорливее, но таких конкретных затыков поменьше. А эксперементировать надо на отдельном сервере наверное. PM можно, кстати, и не обновлять сразу, их можно потом скриптом из консоли конвертнуть. 

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

Экспериментировать без полноценной нагрузки нет смысла - работает всё шустро и без проблем.

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

[Arhar]

а это самое - нельзя на локалхосте конвертировать а на сервер залить тупым бекапом базу?

[Denis Chursinov]

 

 

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

 

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

Другое дело, что и без конвертации личка функционирует нормально. Т.е. можно параллельно конвертировать не отключая форум.

[daitepiva]

 

 

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

Ага, согласен.

 

 

 

Другое дело, что и без конвертации личка функционирует нормально. Т.е. можно параллельно конвертировать не отключая форум.

Т.е. как это? Старые сообщения будут на месте и новые можно будет писать? А конвертация пусть идёт потихоньку? Конвертация же не зря нужна, очевидно, структура таблиц иная. Вероятно, из старой таблицы PM сообщения конвертируются и складываются в новую таблицу, а если туда же в это время будут попадать свеженаписанные сообщения, то возникнет, мягко говоря, путаница.