Форум русской поддержки Joomla!® CMS
04.12.2016, 08:01:22 *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

Войти
   
   Начало   Поиск Joomla 3.0 FAQ Joomla 2.5 FAQ Joomla 1.5 FAQ Правила форума Новости Joomla Реклама Войти Регистрация Помощь  
Страниц: [1] 2 3 4   Вниз
  Добавить закладку  |  Печать  
Автор

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

 (Прочитано 835 раз)
0 Пользователей и 1 Гость смотрят эту тему.
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« : 08.09.2016, 13:24:15 »

Таких запросов в день до 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


Код
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, 16:02:10 от HumanVW » Записан
zomby6888
Живу я здесь
******

Репутация: +168/-3
Offline Offline

Пол: Мужской
Сообщений: 1538


« Ответ #1 : 08.09.2016, 14:14:47 »

Запрос конечно совершенно неоптимизированный. Joomla разочаровывает. Однако у меня он не настолько тормозной. У вас 13 секунд уходит на копирование данных во временную таблицу.
Попробуйте поигратся с настройками tmp_table_size и max_heap_table_size. Если памяти для временной таблицы не хватает он пишет данные на диск. Посмотрите какой у него размер вообще:

http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #2 : 08.09.2016, 14: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
Профи
********

Репутация: +487/-86
Offline Offline

Пол: Мужской
Сообщений: 8694


любит наш народ всякое гавно...


« Ответ #3 : 08.09.2016, 14:25:01 »

Это я на денвере тестирую
Пора наверно уже в правилах запрещать этого динозавра!
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #4 : 08.09.2016, 14:30:07 »

Пора наверно уже в правилах запрещать этого динозавра!

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

Репутация: +487/-86
Offline Offline

Пол: Мужской
Сообщений: 8694


любит наш народ всякое гавно...


« Ответ #5 : 08.09.2016, 14:43:50 »

(27 000 материалов).
А в первом посте почему не написали?
Запрос этот где формируется?
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #6 : 08.09.2016, 14:47:27 »

А в первом посте почему не написали?
Запрос этот где формируется?

В категории "Шаблон списка материалов" и при 404 ошибке.
Записан
zomby6888
Живу я здесь
******

Репутация: +168/-3
Offline Offline

Пол: Мужской
Сообщений: 1538


« Ответ #7 : 08.09.2016, 14:49:21 »

27 000 материалов и такой запрос, ничего удивительного. Если запрос обязательный для com_content, придется от него отказаться. Хотя там выборка в основном категорий идет. А категорий много?
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #8 : 08.09.2016, 15:05:02 »

Если запрос обязательный для com_content, придется от него отказаться. 
Создать новый, или без него работать ?

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

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

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



Записан
zomby6888
Живу я здесь
******

Репутация: +168/-3
Offline Offline

Пол: Мужской
Сообщений: 1538


« Ответ #9 : 08.09.2016, 15:32:00 »

Создать новый, или без него работать ?

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

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

Я вот не пойму зачем вообще все 27000 строк перебирать если в запросе только количество материалов выбирается
« Последнее редактирование: 08.09.2016, 15:35:51 от zomby6888 » Записан
ChaosHead
Профи
********

Репутация: +381/-10
Offline Offline

Пол: Мужской
Сообщений: 4384



« Ответ #10 : 08.09.2016, 15: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, 15:42:37 от ChaosHead » Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #11 : 08.09.2016, 15:39:28 »

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

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

Это Joomla 3.6.2
Записан
zomby6888
Живу я здесь
******

Репутация: +168/-3
Offline Offline

Пол: Мужской
Сообщений: 1538


« Ответ #12 : 08.09.2016, 15:42:15 »

Там все равно запрос жуткий. Разработчиком бы стоило его оптимизировать. Using temporary; Используется filesort - тут какбы надо что то решать с сортировкой и группировкой.  Rows_sent: 2 Rows_examined: 27000 - 27000 строк перебрать чтобы только 2 выбрать какбы тоже не айс, какие то лимиты может имеет смысл добавить в запрос. Хотя он вобще их не должен перебирать он должен только подсчитать кол-во.
« Последнее редактирование: 08.09.2016, 15:51:31 от zomby6888 » Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #13 : 08.09.2016, 15:44:30 »

Временные таблицы в оперативки сделайте, если её достаточно.

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

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

Под категорий нет. Везде указана конктерная категория.
Записан
ChaosHead
Профи
********

Репутация: +381/-10
Offline Offline

Пол: Мужской
Сообщений: 4384



« Ответ #14 : 08.09.2016, 15:48:26 »

Цитировать
Кеш включен, но такой запрос на долго не кеширует, так как его часто запрашивают с разных адресов.
Кэш запросов MySQL, если вы на своём VDS, а не кэш страниц в Joomla. MySQL запомнит результат запроса и если не было изменений в базе данных (не редактировали ничего в этих таблицах), то будет сразу выдавать готовый ответ. Это ускорит соответсвенно и другие запросы. Но это конечно не решение проблемы.
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #15 : 08.09.2016, 15:51:30 »

Кэш запросов MySQL, если вы на своём VDS, а не кэш страниц в Joomla. MySQL запомнит результат запроса и если не было изменений в базе данных (не редактировали ничего в этих таблицах), то будет сразу выдавать готовый ответ. Это ускорит соответсвенно и другие запросы.
Все включено, Сайт на физическом выделенном сервере с выделенным каналом.

query_cache_size = 148M
query_cache_limit = 4M
query_cache_type = 1

Он их незапоминает на долго!
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #16 : 08.09.2016, 15:53:12 »

join_buffer_size = 16M не помогло.
 Проблема в том что полностью сканирует базу, чтобы вывести 2 (в шаблоне списка материалов) или при 404 (27).
Записан
zomby6888
Живу я здесь
******

Репутация: +168/-3
Offline Offline

Пол: Мужской
Сообщений: 1538


« Ответ #17 : 08.09.2016, 15:57:13 »

Там похоже еще для каждой категории идет выборка из 27000 материалов чтобы только их кол-во подсчитать в этой категории. Видимо поэтому он такой тормозной + сортировка с группировкой.
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #18 : 08.09.2016, 16: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
Профи
********

Репутация: +381/-10
Offline Offline

Пол: Мужской
Сообщений: 4384



« Ответ #19 : 08.09.2016, 16:05:41 »

На 4500 материалов он у меня отрабатывает моментально и в tmp не копирует. Может всё такие не в денвере попробовать, а в OpenServer на MySQL 5.6?
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #20 : 08.09.2016, 16:08:54 »

На 4500 материалов он у меня отрабатывает моментально и в tmp не копирует. Может всё такие не в денвере попробовать, а в OpenServer на MySQL 5.6?
На сервере он грузится 3-6 секунд.
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #21 : 08.09.2016, 16:11:36 »

Если кто хочет увидеть у себя этот запрос, могу написать как его найти.
Записан
ChaosHead
Профи
********

Репутация: +381/-10
Offline Offline

Пол: Мужской
Сообщений: 4384



« Ответ #22 : 08.09.2016, 16: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
Живу я здесь
******

Репутация: +168/-3
Offline Offline

Пол: Мужской
Сообщений: 1538


« Ответ #23 : 08.09.2016, 16: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 в запросе.
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #24 : 08.09.2016, 16: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
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #25 : 08.09.2016, 16:36:58 »

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

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

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

Репутация: +381/-10
Offline Offline

Пол: Мужской
Сообщений: 4384



« Ответ #26 : 08.09.2016, 16:43:27 »

Хорошо попробовал конкретно этот запрос в phpMyAdmin. mqpn1 заменил на свой префикс таблиц.
Запрос занял 0.1087 сек. Умножить на 5, т.к. у вас в 5 раз больше материалов, но это далеко до 6 секунд.
Повторный такой-же запрос занимает 0.0004 сек, т.к. уже закэшировался в MySQL пока не изменишь какой-то материал.
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #27 : 08.09.2016, 16:45:12 »

Хорошо попробовал конкретно этот запрос в phpMyAdmin. mqpn1 заменил на свой префикс таблиц.
Запрос занял 0.1087 сек. Умножте на 5, т.к. у вас в 5 раз больше материалов.
Повторный такой-же запрос занимает 0.0004 сек, т.к. уже закэшировался в MySQL
Это на ПК, Вы попробуйте это на реальном сервере сделать. Если бы все было так просто, я бы не писал здесь!
Записан
ChaosHead
Профи
********

Репутация: +381/-10
Offline Offline

Пол: Мужской
Сообщений: 4384



« Ответ #28 : 08.09.2016, 16:48:45 »

Это на реальном рабочем сервере. 1 ядро процессора, 8гб оперативки, Debian, PHP 5.6, Mqsql 5.6
на этом сайте материалов около 100000, но большинство архивных. Таблица content - 173MB

Вот конфиг MySQL
Показать текстовый блок
Записан
HumanVW
Осваиваюсь на форуме
***

Репутация: +1/-0
Offline Offline

Сообщений: 62


« Ответ #29 : 08.09.2016, 16:50:43 »

Тогда ОК. У меня он кешируется на 1-2 минуты. Затем опять 3 сек.


У вас Joomla 3.6.2 ?
Записан
Страниц: [1] 2 3 4   Вверх
  Добавить закладку  |  Печать  
 
Перейти в:  

Powered by SMF 1.1.21 | SMF © 2006, Simple Machines

Joomlaforum.ru is not affiliated with or endorsed by the Joomla! Project or Open Source Matters.
The Joomla! name and logo is used under a limited license granted by Open Source Matters
the trademark holder in the United States and other countries.

LiveInternet