Joomla очень плохо переваривает базу данных даже с несколькими тысячами статей в таблице _content. При нескольких десятках тысяч запросы в базу стандартных модулей типа mod_articles_popular могут зависать на секунды.
Всё дело в ACL (Access Control List) — политике контроля доступа. Проверка законности доступа пользователя к материалам занимает свыше 98% времени выполнения запроса.
Тем временем, есть сайты, которым это не нужно. Например, новостной сайт, показывающий в левой колонке модуль «Самые читаемые статьи» всем подряд. Что делать в этом случае? Отключить проверку ACL в helper.php модуля. Благо это не трудно — просто комментируем строку:
$model->setState('filter.access', $access);Давайте посмотрим на результат. Включаем в админке отладку, смотрим в дебаг-информации запрос в базу данных этого модуля. Вот он без ACL:
Запрос в БД mod_articles_popular без ACL
Время запроса: 18.84 ms После последнего запроса: 6.38 ms Память запроса: 0.012 MB Память до запроса: 7.288 MB Выбрано строк: 5
SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias,
CASE WHEN a.modified = '0000-00-00 00:00:00' THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,
CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.language, LENGTH(a.fulltext) AS readmore,
CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,
CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published,
CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_categories AS c
ON c.id = a.catid
LEFT JOIN jos_users AS ua
ON ua.id = a.created_by
LEFT JOIN jos_users AS uam
ON uam.id = a.modified_by
LEFT JOIN jos_categories as parent
ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v
ON a.id = v.content_id
LEFT
OUTER JOIN (SELECT cat.id as id
FROM jos_categories AS cat JOIN jos_categories AS parent
ON cat.lft BETWEEN parent.lft
AND parent.rgt
WHERE parent.extension = 'com_content'
AND parent.published != 1
GROUP BY cat.id ) AS badcats
ON badcats.id = c.id
WHERE
CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1
AND a.catid IN (9,11,12,13,15,21,24,25)
AND a.publish_up >= DATE_SUB('2018-04-30 03:27:24', INTERVAL 60 DAY)
ORDER BY a.hits DESC
LIMIT 5Вот с ACL:
Запрос в БД mod_articles_popular c ACL
Время запроса: 972.79 ms После последнего запроса: 3.96 ms Память запроса: 0.012 MB Память до запроса: 7.378 MB Выбрано строк: 5
SELECT a.id, a.title, a.alias, a.introtext, a.fulltext, a.checked_out, a.checked_out_time, a.catid, a.created, a.created_by, a.created_by_alias,
CASE WHEN a.modified = '0000-00-00 00:00:00' THEN a.created ELSE a.modified END as modified, a.modified_by, uam.name as modified_by_name,
CASE WHEN a.publish_up = '0000-00-00 00:00:00' THEN a.created ELSE a.publish_up END as publish_up,a.publish_down, a.images, a.urls, a.attribs, a.metadata, a.metakey, a.metadesc, a.access, a.hits, a.xreference, a.featured, a.language, LENGTH(a.fulltext) AS readmore,
CASE WHEN badcats.id is not null THEN 0 ELSE a.state END AS state,c.title AS category_title, c.path AS category_route, c.access AS category_access, c.alias AS category_alias,
CASE WHEN a.created_by_alias > ' ' THEN a.created_by_alias ELSE ua.name END AS author,ua.email AS author_email,parent.title as parent_title, parent.id as parent_id, parent.path as parent_route, parent.alias as parent_alias,ROUND(v.rating_sum / v.rating_count, 0) AS rating, v.rating_count as rating_count,c.published,
CASE WHEN badcats.id is null THEN c.published ELSE 0 END AS parents_published
FROM jos_content AS a
LEFT JOIN jos_categories AS c
ON c.id = a.catid
LEFT JOIN jos_users AS ua
ON ua.id = a.created_by
LEFT JOIN jos_users AS uam
ON uam.id = a.modified_by
LEFT JOIN jos_categories as parent
ON parent.id = c.parent_id
LEFT JOIN jos_content_rating AS v
ON a.id = v.content_id
LEFT
OUTER JOIN (SELECT cat.id as id
FROM jos_categories AS cat JOIN jos_categories AS parent
ON cat.lft BETWEEN parent.lft
AND parent.rgt
WHERE parent.extension = 'com_content'
AND parent.published != 1
GROUP BY cat.id ) AS badcats
ON badcats.id = c.id
WHERE a.access IN (1,1,2,3,6)
AND c.access IN (1,1,2,3,6)
AND
CASE WHEN badcats.id is null THEN a.state ELSE 0 END = 1
AND a.catid IN (9,11,12,13,15,21,24,25)
AND a.publish_up >= DATE_SUB('2018-04-30 03:36:50', INTERVAL 60 DAY)
ORDER BY a.hits DESC
LIMIT 5Разница всего в двух проверках в WHERE клаузе:
a.access IN (1,1,2,3,6) AND c.access IN (1,1,2,3,6)а также во времени выполнения запроса в первой строке: 19 миллисекунд и 973 миллисекунды. 98%. На базе данных с 5000 статей. На мощном сервере хостера. Это цена широко разрекламированного Joomla ACL.
Но, оказывается, разработчики этого фреймворка прекрасно в курсе данного момента, и встроили в ее админку возможность отключить проверку ACL. Но сделали это так, чтобы никто не догадался.
Опция находится в «Общие настройки» -> «Материалы», внизу первого таба «Материалы», называется «Показывать ссылки неавторизованным». Расшифровывается всплывающей подсказкой как: "Если установлено значение Да, то ссылки на просмотр полного текста материалов смогут видеть все пользователи, в том числе, не прошедшие авторизацию, но для просмотра полного текста система потребует ввести логин и пароль".
Вроде бы к делу не относится, но выполняет именно то, что надо — отменяет проверку ACL для всех модулей (которые используют эту политику). Переменная $access в $model->setState('filter.access', $access) — это именно этот чекбокс (с обратным/восклицательным знаком). О его существовании подсказал один из разработчиков этого CMS-а.
Кому нужно, могут оживить свою Джумлу.
SDKiller
CMS — не КМС, она женского рода
altrus Автор
В английском языке мужской и женский рода могут быть только у одушевленных предметов. Неодушевленные и младенцы — средний (it).
shoorick
Ну да, конечно.
А почему тогда, ну например, по отношению к кораблям применяются местоимения she и her?
altrus Автор
На названия кораблей посмотрите
zelenin
например?
Samouvazhektra
Это фишка мореходов, из серии что корабли ходят, а не плавают
http://www.boatsafe.com/kids/kidsquesshe.htm
justhabrauser
Это исключение. Грубо говоря для англичан корабль == лодка. Оно she.
Tippy-Tip
Традиция, сэр! Вон во французском языке слово «весна» вообще мужского рода.
VolCh
Текст на русском языке.
nikitasius
Чтобы понять род Joomla, достаточно как в школе, поставить вопрос:
Почти все CMS, которые доступны сейчас среднего рода, даже если кто-то вам говорит, что "эта вот женского", а "эта вот мужского", на деле же они все среднего.
zikkuratvk
CMS — система управления контентом, то есть система — она.
Joomla — среднего среднего, хотя бы из-за этимологии слова.
altrus Автор
а VIP по-вашей схеме это он или она?
PastorGL
VIP это person, а person это they с тех пор как политкорректность стала частью западной культуры.
altrus Автор
Тогда и CMS-ы — they. Из поликорректности. Как часть западной культуры.
nikitasius
Ух, языческие боги и макаронный монстр! Они среднего рода потому, что есть одно слово, вернее субстанция, пахучая и несъедобная, которая их весьма точно харакатеризует.
Тоже спойлер нужен?
zikkuratvk
Вот читаю про открытие это и думаю, что об этом со времен Joomla 3 говорят.
Реализация ACL всегда была скорей минусом нежели плюсом Joomla, конечно хорошо, что он есть, но он избыточен и при это не гибок, тем более его реализация для материалов Joomla.
Ну а если говорить про материалы, они ни когда не проектировались под большие нагрузки, хотя вроде в вашем примере и не должно быть больших нагрузок, есть подозрение, что у вас изначально не так, что-то с базой (допустим неудачная миграция базы), так как 5000 материалов для Joomla это пшик, проблемы начинаются, когда больше 20 тысяч, что редко бывает на обычном сайте.
А так сама Joomla может сколько угодно контента переваривать, все зависит от того, как напишешь компонент.
altrus Автор
А что может быть не так с базой? )))
Всё почти стандартное, «из под коробки», левого софта не стоит.
Честно говоря, я сам удивился, такой задержке — почти в секунду. Когда материалов было около 30 тысяч, такая задержка была. Но иногда и до 6 секунд, помню, прыгало (как-то недетерминированно, видимо зависит от иной нагрузки на сервер). Возможно и тут флуктуация такая случилась. Но как минимум 400 мс стопудово будет.
Не в абсолютном значении дело, а в относительном. И компонент тут ни при чем — запрос происходит в модуле. Я не спец по реляционным базам, поэтому не буду давать оценку, почему так происходит и как это исправить в корне. Но лекарство указал. О нем многие знают знатоки джумлы, но я в свое время кучу времени и нервов (хостер тыкал пальцем на загрузку сервера и прайслист доп.услуг) потратил на эту фигню, так что пусть будет документально оформлено тут)
sumanai
Банально индексы отвалились.
zikkuratvk
Как сказали выше индексы отвалились.
Неудачная миграция с Joomla 1.0/1.5/2.5.
Неудачная миграция в рамках линейки 3, когда они добавили поддержку utf8mb4.
Вариантов много — это лишь самые распространенные.
Вы думаете, что из коробки самые лучшие расширения стоят? На самом деле из коробки расширения больше, как пример реализации API стоит рассматривать.
altrus Автор
«Из коробки» имелось ввиду, что это чистая джумла с официального сайта, правильно заполняемая данными, без импортов/экспортов данных и прочего, что могло бы нарушить изначальную целостность схемы
Dmitri-D
перестройте индексы, проапдейтите статистику, а потом сравните будет ли такая же разница. Речь про таблицы, участвующие в запросе и их индексах.
Если всё останется так же медленно (большие шансы, что нет) — то тогда надо строить query plan для случаев с и без ACL и сравнивать это даст ответ на вопрос почему база перестала работать быстро.
altrus Автор
Спасибо, проверю.
altrus Автор
Вы правы, это был индекс. Но как-то странно.
Сделал на этих двух таблицах optimize, и время запроса резко упало — в пределах 30 мс. Начал тестировать, обновлять страницу — три раза было 25-30 мс, и тут на четвертый 360 мс. На пятый — 400 мс. Опять делаю optimize — время снова падает. Много рефрешил, но уже не смог отловить поломку.
В среднем, запрос c ACL был процентов на 50% по времени тяжелей с рабочими индексами, чем без ACL. Но что опять произошло с индексами через небольшое время после перестройки и почему?
bgBrother
Не уверен в своём высказывании, прошу сразу простить, но может у вас индексы в отведенную БД память не влазят? Смотрели конфигурацию?
altrus Автор
Всё на хостинге. Качеством и техническим уровнем сервиса пока был доволен.
Dmitri-D
Возможно это не ваш случай, но _обычно_ индексы получаются перекошенные, если идут вместе подряд DDL и DML запросы. Т.е. менять схему на лету с данными.
Или вот — тоже простой способ выстрелить себе в ногу — это удалить все старые данные таблицы и быстренько наполнить другие данные, для которых старая статистика работать не будет. Например были все записи с одним и тем же значением в проиндексированном поле — такой индекс игнорируется по статистике, а потом вы наполняете новыми, где эта колонка содержит или уникальные или почти уникальные данные — если индекс по-прежнему игнорируется (статистика велит), то всё, запросы будут тормозить.
База данных — штука не глупая, обычно она сам обновляет статистику, но иногда почему-то не обвновляет, или забывает обновить
altrus Автор
Давайте конкретно мой тестируемый случай рассматривать. Вчера были запросы большие, я сделал optimize на _content и _categories. Время запросов стало маленьким на 3 минуты, и затем опять большим. Снова optimize, снова маленькое и уже надолго. Утром сегодня проверяю — опять большое (300мс-1200мс). Причем, за ночь в _content ничего не добавлялось.
Из того, что я делал с БД раньше (давно) — чистка _assets вот этим способом и удалял руками из _content данные.
fspare
Глупый вопрос, но нельзя ли для ACL воткнуть кэширование? По идее это такие вещи, которые не так часто меняются и отлично подпадают под возможность кэширования(я про ACL). Механизмов хватает — Redis/Memcache/(да даже тупое кэширование файловое) должно улучшить на порядки производительность. Конечно, это приведет к модификации исходного кода Joomla(что не хорошо в плане последующих апгрейдов)… Но даже не знаю стоит ли из бетонных блоков пытаться слепить детский песочный домик или слегка допилить напильником то, что уже есть…
Kwisatz
Чисто из академического интереса: покажите пожалуйста explain длинного запроса.
altrus Автор
Время опять стало большим (1100мс)
Dmitri-D
скорее всего — using temporary и using filesort — вот что тормозит
Плюс к этому вопрос — почему ключ такой длинный? Что там такое в ключе аж на 203 байт?
Похоже индекс не влазит в память и был вытеснен. Вам нужно увеличить память для кеширования или поменять диск на более быстрый
altrus Автор
То есть во время выполнения этого запроса на большой таблице при создании temporary table слетают индексы у _content, правильно я понимаю?
Kwisatz
Во время выполнения этого запроса сервер получает 201 тысячу строк от storage engine частично без использования индексов а затем все это весело размешивает, используя дисковую сортировку.
Dmitri-D
в таблице большой ключ cat_idx — 203 байта на каждую запись. Что в этом ключе? Из-за того что он большой, а памяти выдено мало — данные сортируются во временном файле. Поэтому и тормоза.
Что касается быстрых и медленных периодов — сравните query plan когда быстро и когда медленно.
Kwisatz
Я думаю по сравнению с остальными проблемами это слезы.
Dmitri-D
и sort buffer тоже увеличить
altrus Автор
База на хостинге, я ничего не могу. Hostland.
Kwisatz
1. Нужно увеличивать размер памяти под сортировку. filesort это вообще за пределами добра и зла. А тут их аж два. Это дисковые операции. И если 25 строк еще не проблема, то вот 4022 уже могут вполне ею быть.
2. В остальном explain без отрезанных колонок читать невозможно. Можно глянуть в личке если смущает светить именами таблиц.
ЗЫ group by без агрегатных функций это чтобы сложней читать?) это distinct
ЗЫЗЫ а вообще на беглый взгляд с запросом все очень плохо.
altrus Автор
1. Это полный explain запроса. Я ничего не обрезал.
2. Это сгенерированный sql-запрос стандартного модуля джумлы. Оценивать его эффективность можно, но менять нет смысла. В смысле, только если переписать модуль, (что давно есть в планах сделать).
3. Ваш запрос попробовал. 30 мс, но и мои тяжелые с утра запросы тоже стали выполняться быстро. Причем
4. с таблицами я ничего не делал, индексы не перестраивал.
5. Вопрос: это из-за shared хостинга такое? Недерминированное выделение памяти под сортировку и временную таблицу?
Kwisatz
1. Это phpMyAdmin так выводит? Ндаааа, тогда лучше конечно в консоли.
2. Пичалька. Ибо за такие запросы нужно по руками роялем. Я внес чисто косметические изменения: как раз с расчетом на то чтобы в генераторе поменять было легко. По идее я бы еще часть в подзапрос вывел и перелопатил серьезно.
3. Для него важна на скорость а explain. И для исходного запроса его бы сейчас выполнить, должна быть иная картина.
4.5 Под сортировку выделения памяти как раз строго соответствует конфигу. Увеличение этого показателя ускорить запросы где есть файлсорт значительно но общей проблемы не решит. Запросы подобные этому это извращенное издевательство над mysql оптимизатором и память тут весьма критична.
Чтобы ручками не лазить можно взять mysqltuner.pl и запустить. Он выдает много интересных циферок и общая картина как правило сразу видна. Не знаю только насколько этот совет применим к вашему хостингу.
sumanai
Вангую что никак.
altrus Автор
Почему? SSH есть, python тоже. Да нормальный хостинг. Не в деньгах дело — с ним проще, чем VDS настраивать и за всем следить.
altrus Автор
Запросы стали опять долгие. Ваш — быстрый.
Смотрите, конкретно этот запрос сам по себе сейчас не важен. Он в принципе общий для модулей джумла (схема построения запроса).
В основной статье тут оказалась допущена ошибка — многократное увеличение времени выполнения запроса происходит не всегда. Нужно понять (оценить), насколько эта проблема имеет общий характер. Относится ли она только ко мне, только к shared hosting, насколько она детерминирована. Ну и корень проблемы, желательно.
Правильно я понимаю, что при общих настройках MySQL и достаточно большом количестве данных в таблице _content, она может возникнуть у достаточно большого неопределенного числа пользователей джумлы?
Kwisatz
Я его хотел использовать для оценки масштабов катастрофы.
Выборка без индекса с вычисляемыми полями в условии да еще с использованием дисковых операций это очень плохо.
Вы вынуждаете меня гадать без полных данных) Если бы вы могли показать explain второго запроса. А еще лучше обоих из консоли и результат работы скрипта что я выше линканул я бы сказал точнее.
Но на вскидку, в зависимости от наличия памяти, такие проблемы будут у всех. Большое количество памяти и ssd хранилище будут нивелировать сей эффект.
Дебильные запросы.
Оператор in это вообще своеобразная штука.
На вскидку, самое простое: добавить индекс по времени и поставить условие по нему первым (или в подзапрос вынести), это уже облегчит жизнь небольшим сайтам. Убрать кейс из условия к чертям, он там не нужен абсолютно. Ну и ACL должен работать по маскам или хешу но не через in, но при малом количестве данных по идее уже не будет играть столь серьезную роль.
altrus Автор
Этот модуль делает следующее — показывает 5 самых читаемых статей их определенных категорий. То есть, по сути, вот это:
Kwisatz
Я догадался уже)
Конечно есть, индекса по датам то нет как минимум. У нормального запроса должны быть указано using index. А у вас скорее всего using where,using filesort.
Я не уверен будет ли mysql использовать индекс по cat_id, но самый простой эксперимент над таким запросом: поставьте условия по датам первым и добавьте индекс (publish_up, cat_id, hits) ну или хоты бы по publish_up
И его можно очень сильно облегчить если грамотно организовать подзапрос. Даже без оптимизаций.
Какая универсальность в том же left outer join к таблице content я честно не догадываюсь.
А приколы вроде case которое спокойно разворачивается в нормальное условия практически разрывают оптимизатор пополам.
Kwisatz
Вот выдержки:
altrus Автор
Да, правильно. Когда долгие запросы. у меня «Using temporary; Using filesort». Когда быстрые, «Using where; Using filesort», но это тоже не правильно, как я понимаю.
Будет время я все-таки замеряю разницу в скорости стандартного решения и оптимизированного под конкретные запросы. Хотя не понятно, как запись в temporary table оценивать — оно то есть, то нет на одном запросе и с одними данными. А влияние оказывает критическое.
Kwisatz
Попробуйте кстати explain extended
altrus Автор
Хостер ответил, что sort_buffer_size = 4194304 байт (4 Мб).
Kwisatz
Попробуйте этот запросик
Хотя смысл аутер джоина от меня ускользает, но пусть будет для чистоты эксперимента.
VolCh
Возможно, оптимизатор какое-то время собирает статистику, а потом решает, что filesort и temporary быстрее будет. Сравните explain сразу после optimize и когда опять начнёт тормозить.
al707
В Joomla > 1.6 есть таблица assets. Если проводилась миграция контента или еще какая вставка контента прямо в базу, то могла быть нарушена структура таблицы assets. Эта неправильно сформированная таблица может очень сильно тормозить.
Когда-то немного вникал в этот вопрос, собственно, погуглите «joomla assets table slow down». Вот, кстати, для начала docs.joomla.org/Fixing_the_assets_table.
Не знаю, имеет ли это отношение к вашему случаю. Но Joomla достаточно сложна и то что вы сделали какие-то действия и после это кажутся очевидными какие-то выводы, то всё может быть далеко не так как кажется, а просто были задействованы механизмы, о которых вы даже не подозревали (например, почистился или создался какой-о кэш, Джумловской или серверный и т. п. бог знает что ещё).
altrus Автор
Никаких миграций не было, но я чистил эту таблицу вот по этой схеме
Давно чистил. После этого вчера вот перестраивал индексы и опять все слетело.
konservs
А ещё при сохранении материала в админке Joomla делает столько же апдейтов в базу, сколько там было материалов: https://konservs.com/post/179.
zikkuratvk
Кроме ээээ??? это статья ничего не вызывает. Я не исключаю, что так могло быть на указанном сайте, однако очень интересно почему он не обновлялся. Сдается из-за каких то кривых расширений или хаков движка, но можете попробовать на чистом движке Joomla, такого не должно быть, так как это бессмысленное действие.
Кстати в этом блоге в основном информация про Joomla 2.5, учитывая, то что линейка Joomla 3 вышла 6 лет назад и за это время ее основательно переписали, сложно говорить о применимости этих всех рассуждений.
altrus Автор
С чего утверждение про Joomla 2.5 и нечистый движок?
zikkuratvk
Joomla 2.5 быстрее, чем Joomla 3, но я очень мало сайтов видел на Joomla 2.5.28, в основном они почему-то, либо на 2.5.8 (одна из самых кривых версий в этой линейке), либо на 2.5.14 и самая главная особенность 2.5 ее очень много хакали, так как было еще живо поколение разработчиков, которые выросли на Joomla 1.5, где и правда без хаков ядра было тяжело.
А мой комент, к тому, что с большой долей вероятности на чистой установке последней Joomla история из блога не воспроизведется.
altrus Автор
Joomla сильно тормозит на большом количестве статей — это известный факт, поднимался на форумах не раз.
Торможения из-за не эффективных запросов и ACL — это тоже факт.
Статья моя об этом. Если у вас все хорошо — значит эта проблема вас не касается.
altrus Автор
Извиняюсь.
Что-то я не заметил, что разговор про статью konservs.com/post/179. и все перепутал
konservs
Да, сайт был на Joomla 2.5. Однако кастомных расширений почти не было. И всё которые были — это модули. И проблема была в админ. панели, в com_content.
Я глянул быстренько на GitHub — код пересортировки всей таблицы все ещё остался: https://github.com/joomla/joomla-cms/blob/staging/administrator/components/com_content/models/article.php
Будет время — гляну детальнее, подниму чистую Joomla, попробую воссоздать проблему. Для чистоты эксперимента.
zikkuratvk
Попробуйте ради интереса, просто переписывать всю таблицу после сохранения смысла нет, собственно вы своей статье об этом говорили.