Медленный SQL запрос на Joomla 3.6.2

  • 113 Ответов
  • 1468 Просмотров

0 Пользователей и 1 Гость просматривают эту тему.

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Таких запросов в день до 1000. Сильно нагружают базу данных.

Вот скрины (кому не удобно читать ниже текст):
1) Сам запрос - http://picua.org/img/2016-09/08/32vo6fuwn7xih8o4midqyla8s.png
2) План запроса -  http://picua.org/img/2016-09/08/bcr6kmyb8d8dxuqthz2edvrly.png
3) Профилирование - http://picua.org/img/2016-09/08/8a8s9a5kmsmkvjoj463bugyfx.png

Время запроса: 13277.17 ms Память запроса: 0.021 MB Выбрано строк: 27


Код: sql
SELECT c.id, c.asset_id, c.access, c.alias, c.checked_out, c.checked_out_time,
c.created_time, c.created_user_id, c.description, c.extension, c.hits, c.language, c.level,
c.lft, c.metadata, c.metadesc, c.metakey, c.modified_time, c.note, c.params, c.parent_id,
c.path, c.published, c.rgt, c.title, c.modified_user_id, c.version,
  CASE WHEN CHAR_LENGTH(c.alias)!= 0 THEN CONCAT_WS(':', c.id, c.alias) ELSE c.id END as slug,COUNT(i.`id`) AS numitems

  FROM mqpn1_categories as c

  LEFT JOIN  (SELECT cat.id as id
  FROM mqpn1_categories AS cat JOIN mqpn1_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

  LEFT JOIN `mqpn1_content` AS i
  ON i.`catid` = c.id
  AND i.state = 1

  WHERE (c.extension='com_content' OR c.extension='system')
  AND c.access IN (1,1,5)
  AND c.published = 1
  AND badcats.id is null

  GROUP BY c.id, c.asset_id, c.access, c.alias, c.checked_out, c.checked_out_time,
c.created_time, c.created_user_id, c.description, c.extension, c.hits, c.language, c.level,
c.lft, c.metadata, c.metadesc, c.metakey, c.modified_time, c.note, c.params, c.parent_id,
c.path, c.published, c.rgt, c.title, c.modified_user_id, c.version

  ORDER BY c.lft

Код
План SQL-запросов (Explain)

id  select_type   table       type  possible_keys         key                        key_len   ref      rows    Extra
1   PRIMARY    <derived2>  system             NULL          Индекс не используется         NULL NULL  0 const row not found
1   PRIMARY c       ALL  cat_idx,idx_access    Индекс не используется        NULL NULL 30   Using where; Using temporary; Используется filesort
1   PRIMARY i       ref  idx_state,idx_catid   idx_state                   1 const    12810
2   DERIVED    parent        range      cat_idx,idx_left_right   cat_idx                 153 NULL      2      Using where; Using temporary; Используется filesort
2   DERIVED    cat     index     idx_left_right           idx_left_right           8     NULL     30     Using where; Using index; Using join buffer



Профилирование SQL-запросов
Status   Duration
starting   0.54 ms
checking permissions   0.01 ms
checking permissions   0.01 ms
checking permissions   0.01 ms
checking permissions   0.01 ms
Opening tables   0.09 ms
System lock   0.35 ms
optimizing   0.04 ms
statistics   0.26 ms
preparing   0.05 ms
Creating tmp table   0.09 ms
executing   0.01 ms
Copying to tmp table   0.15 ms
Sorting result   0.03 ms
Sending data   0.01 ms
removing tmp table   0.02 ms
Sending data   0.01 ms
init   0.24 ms
optimizing   0.04 ms
statistics   0.42 ms
preparing   0.09 ms
Creating tmp table   6.78 ms
executing   0.04 ms
Copying to tmp table   13261.10 ms



« Последнее редактирование: 08.09.2016, 17:02:10 от HumanVW »

*

zomby6888

  • Живу я здесь
  • ******
  • 1484
  • 167
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #1 : 08.09.2016, 15:14:47 »
Запрос конечно совершенно неоптимизированный. Joomla разочаровывает. Однако у меня он не настолько тормозной. У вас 13 секунд уходит на копирование данных во временную таблицу.
Попробуйте поигратся с настройками tmp_table_size и max_heap_table_size. Если памяти для временной таблицы не хватает он пишет данные на диск. Посмотрите какой у него размер вообще:

http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html
интернет-блог: http://websiteprog.ru

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #2 : 08.09.2016, 15:22:43 »
Запрос конечно совершенно неоптимизированный. Joomla разочаровывает. Однако у меня он не настолько тормозной. У вас 13 секунд уходит на копирование данных во временную таблицу.
Попробуйте поигратся с настройками tmp_table_size и max_heap_table_size. Если памяти для временной таблицы не хватает он пишет данные на диск. Посмотрите какой у него размер вообще:

http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

Увеличил  tmp_table_size и max_heap_table_size по 256 Мб - ничего не изменилось. Размер самой базы - 54 МБ

Это я на денвере тестирую . На сервере такой запрос выполняется за 3-6 секунд.

*

voland

  • Профи
  • ********
  • 9365
  • 421
  • СКАЙП утерян! Пишите в телеграм @volandku
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #3 : 08.09.2016, 15:25:01 »
Это я на денвере тестирую
Пора наверно уже в правилах запрещать этого динозавра!

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #4 : 08.09.2016, 15:30:07 »
Пора наверно уже в правилах запрещать этого динозавра!

Да, но в данном вопросе это роли не играет. Такой запрос везде будет долго выполняться ибо Joomla сканирует всю базу (27 000 материалов).

*

voland

  • Профи
  • ********
  • 9365
  • 421
  • СКАЙП утерян! Пишите в телеграм @volandku
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #5 : 08.09.2016, 15:43:50 »
(27 000 материалов).
А в первом посте почему не написали?
Запрос этот где формируется?

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #6 : 08.09.2016, 15:47:27 »
А в первом посте почему не написали?
Запрос этот где формируется?

В категории "Шаблон списка материалов" и при 404 ошибке.

*

zomby6888

  • Живу я здесь
  • ******
  • 1484
  • 167
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #7 : 08.09.2016, 15:49:21 »
27 000 материалов и такой запрос, ничего удивительного. Если запрос обязательный для com_content, придется от него отказаться. Хотя там выборка в основном категорий идет. А категорий много?
интернет-блог: http://websiteprog.ru

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #8 : 08.09.2016, 16:05:02 »
Если запрос обязательный для com_content, придется от него отказаться. 
Создать новый, или без него работать ?

Хотя там выборка в основном категорий идет. А категорий много?

Этот запрос сканирует все категории (таблица_category - 27) и все материалы (таблица_content - 27 000). А выводит в итоге 27 категорий.(Это при 404 ошибке) Rows_sent: 27  Rows_examined: 27000

Запрос на "Шаблоне списка категории" - Rows_sent: 2  Rows_examined: 27000




*

zomby6888

  • Живу я здесь
  • ******
  • 1484
  • 167
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #9 : 08.09.2016, 16:32:00 »
Создать новый, или без него работать ?

Ну можно просто отказаться от шаблона списка категорий. Вместо него написать модуль или компонент который будет категории выводить из com_content. Тот же запрос без сортировки пробовали выполнять?

Цитировать
Rows_examined: 27000

Я вот не пойму зачем вообще все 27000 строк перебирать если в запросе только количество материалов выбирается
« Последнее редактирование: 08.09.2016, 16:35:51 от zomby6888 »
интернет-блог: http://websiteprog.ru

*

ChaosHead

  • Профи
  • ********
  • 4667
  • 396
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #10 : 08.09.2016, 16:36:44 »
Временные таблицы в оперативки сделайте, если её достаточно.

Кэширование запросов сделайте, чтобы одно и то-же по сто раз не пересчитывало:
Цитировать
query_cache_size= 128M
query_cache_limit=2M
query_cache_type=1

Ещё попробуйте увеличить join_buffer_size
join_buffer_size = 16M

Цитировать
Ну можно просто отказаться от шаблона списка категорий. Вместо него написать модуль или компонент который будет категории выводить из com_content.
Там есть нюансы, если много категорий и подкатегорий материалов, то в настройках вывода желательно указывать конкретные категории, а подкатегории отключать, чтобы лишних переборов не было.
« Последнее редактирование: 08.09.2016, 16:42:37 от ChaosHead »

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #11 : 08.09.2016, 16:39:28 »
Ну можно просто отказаться от шаблона списка категорий. Вместо него написать модуль или компонент который будет категории выводить из com_content. Тот же запрос без сортировки пробовали выполнять?

А смысл без сортировки . Ведь Copying to tmp table   13261.10 ms.
Я вот не пойму зачем вообще все 27000 строк перебирать если в запросе только количество материалов выбирается

Это Joomla 3.6.2

*

zomby6888

  • Живу я здесь
  • ******
  • 1484
  • 167
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #12 : 08.09.2016, 16:42:15 »
Там все равно запрос жуткий. Разработчиком бы стоило его оптимизировать. Using temporary; Используется filesort - тут какбы надо что то решать с сортировкой и группировкой.  Rows_sent: 2 Rows_examined: 27000 - 27000 строк перебрать чтобы только 2 выбрать какбы тоже не айс, какие то лимиты может имеет смысл добавить в запрос. Хотя он вобще их не должен перебирать он должен только подсчитать кол-во.
« Последнее редактирование: 08.09.2016, 16:51:31 от zomby6888 »
интернет-блог: http://websiteprog.ru

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #13 : 08.09.2016, 16:44:30 »
Временные таблицы в оперативки сделайте, если её достаточно.

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

Кеш включен, но такой запрос на долго не кеширует, так как его часто запрашивают с разных адресов. когда не может найти материал - выдается этот запрос в базу и полностью её сканирует, затем выдает 404 ошибку.  ОЗУ - 8ГБ

Под категорий нет. Везде указана конктерная категория.

*

ChaosHead

  • Профи
  • ********
  • 4667
  • 396
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #14 : 08.09.2016, 16:48:26 »
Цитировать
Кеш включен, но такой запрос на долго не кеширует, так как его часто запрашивают с разных адресов.
Кэш запросов MySQL, если вы на своём VDS, а не кэш страниц в Joomla. MySQL запомнит результат запроса и если не было изменений в базе данных (не редактировали ничего в этих таблицах), то будет сразу выдавать готовый ответ. Это ускорит соответсвенно и другие запросы. Но это конечно не решение проблемы.

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #15 : 08.09.2016, 16:51:30 »
Кэш запросов MySQL, если вы на своём VDS, а не кэш страниц в Joomla. MySQL запомнит результат запроса и если не было изменений в базе данных (не редактировали ничего в этих таблицах), то будет сразу выдавать готовый ответ. Это ускорит соответсвенно и другие запросы.
Все включено, Сайт на физическом выделенном сервере с выделенным каналом.

query_cache_size = 148M
query_cache_limit = 4M
query_cache_type = 1

Он их незапоминает на долго!

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #16 : 08.09.2016, 16:53:12 »
join_buffer_size = 16M не помогло.
 Проблема в том что полностью сканирует базу, чтобы вывести 2 (в шаблоне списка материалов) или при 404 (27).

*

zomby6888

  • Живу я здесь
  • ******
  • 1484
  • 167
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #17 : 08.09.2016, 16:57:13 »
Там похоже еще для каждой категории идет выборка из 27000 материалов чтобы только их кол-во подсчитать в этой категории. Видимо поэтому он такой тормозной + сортировка с группировкой.
интернет-блог: http://websiteprog.ru

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #18 : 08.09.2016, 17:02:20 »
Вот скрины кому не удобно читать текст:
1) Сам запрос - http://picua.org/img/2016-09/08/32vo6fuwn7xih8o4midqyla8s.png
2) План запроса -  http://picua.org/img/2016-09/08/bcr6kmyb8d8dxuqthz2edvrly.png
3) Профилирование - http://picua.org/img/2016-09/08/8a8s9a5kmsmkvjoj463bugyfx.png

*

ChaosHead

  • Профи
  • ********
  • 4667
  • 396
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #19 : 08.09.2016, 17:05:41 »
На 4500 материалов он у меня отрабатывает моментально и в tmp не копирует. Может всё такие не в денвере попробовать, а в OpenServer на MySQL 5.6?

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #20 : 08.09.2016, 17:08:54 »
На 4500 материалов он у меня отрабатывает моментально и в tmp не копирует. Может всё такие не в денвере попробовать, а в OpenServer на MySQL 5.6?
На сервере он грузится 3-6 секунд.

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #21 : 08.09.2016, 17:11:36 »
Если кто хочет увидеть у себя этот запрос, могу написать как его найти.

*

ChaosHead

  • Профи
  • ********
  • 4667
  • 396
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #22 : 08.09.2016, 17:20:02 »
Пишите, я его увидел, сделав пункт меню на список материалов категории. Интересно, будет ли у людей он писать в tmp.

Подозреваю, что что-то нужно тюнить в MySQL под большое количество материалов, чтобы этот запрос не использовал временную таблицу.
Возможно буферы
key_buffer_size = 256M
sort_buffer_size = 32M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
join_buffer_size = 16M

innodb_buffer_pool_size = 3072M #должен быть более общего размера всех таблиц innodb

*

zomby6888

  • Живу я здесь
  • ******
  • 1484
  • 167
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #23 : 08.09.2016, 17:29:13 »
У меня на локалке с 3 материалами:

Using where; Using temporary; Using filesort
Using where
Using where; Using join buffer (flat, BNL join)
Using index condition; Using temporary; Using file..
Using where; Using index; Using join buffer (flat,...

Временная таблица используется дважды. Все причины по которым она может использоваться описаны здесь:

http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

В данном случае я думаю что это из-за сочетания orderBy и groupBy в запросе.
интернет-блог: http://websiteprog.ru

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #24 : 08.09.2016, 17:31:50 »
Пишите, я его увидел, сделав пункт меню на список материалов категории. Интересно, будет ли у людей он писать в tmp.

Вы увидели запрос для категории, он  похож, у меня на скринах запрос который вызывается при 404 ошибке, вот его та и не просто увидеть.
 Попробуйте найти или я могу написать.

Подозреваю, что что-то нужно тюнить в MySQL под большое количество материалов, чтобы этот запрос не использовал временную таблицу.
Возможно буферы
key_buffer_size = 256M
sort_buffer_size = 32M
read_buffer_size = 16M
read_rnd_buffer_size = 16M
join_buffer_size = 16M

innodb_buffer_pool_size = 3072M #должен быть более общего размера всех таблиц innodb

Непомогло.

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #25 : 08.09.2016, 17:36:58 »
ЧТобы увидеть этот запрос (Должен быть создан пункт меню категория (не главную), его и меняйте):

1) Зайдите в базу данных сайта и затем в таблицу вашсуффикс_menu и выберите там ваш пункт меню "список категории", откройте его и в поле link изменить "index.php?option=com_content&view=category..." на слово из поле alias.

Теперь откройте этот сайт и допишите слово из поле alias (в админке сайта включить откладка в общих настройках + плагин) и вы увидите этот запрос + его результат.

*

ChaosHead

  • Профи
  • ********
  • 4667
  • 396
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #26 : 08.09.2016, 17:43:27 »
Хорошо попробовал конкретно этот запрос в phpMyAdmin. mqpn1 заменил на свой префикс таблиц.
Запрос занял 0.1087 сек. Умножить на 5, т.к. у вас в 5 раз больше материалов, но это далеко до 6 секунд.
Повторный такой-же запрос занимает 0.0004 сек, т.к. уже закэшировался в MySQL пока не изменишь какой-то материал.

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #27 : 08.09.2016, 17:45:12 »
Хорошо попробовал конкретно этот запрос в phpMyAdmin. mqpn1 заменил на свой префикс таблиц.
Запрос занял 0.1087 сек. Умножте на 5, т.к. у вас в 5 раз больше материалов.
Повторный такой-же запрос занимает 0.0004 сек, т.к. уже закэшировался в MySQL
Это на ПК, Вы попробуйте это на реальном сервере сделать. Если бы все было так просто, я бы не писал здесь!

*

ChaosHead

  • Профи
  • ********
  • 4667
  • 396
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #28 : 08.09.2016, 17:48:45 »
Это на реальном рабочем сервере. 1 ядро процессора, 8гб оперативки, Debian, PHP 5.6, Mqsql 5.6
на этом сайте материалов около 100000, но большинство архивных. Таблица content - 173MB

Вот конфиг MySQL
Спойлер
[свернуть]

*

HumanVW

  • Осваиваюсь на форуме
  • ***
  • 61
  • 1
Re: Медленный SQL запрос на Joomla 3.6.2
« Ответ #29 : 08.09.2016, 17:50:43 »
Тогда ОК. У меня он кешируется на 1-2 минуты. Затем опять 3 сек.


У вас Joomla 3.6.2 ?