Очень долго выполняется SELECT SQL_CALC_FOUND_ROWS

[goletsa]

Можно с этим что-то сделать?
Запрос может идти по 15-20 секунд.
И во время его выполнения блокируются все остальные запросы к базе (Locked в mtop'е).

[Efendy]

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

[goletsa]

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

Вроде крайняя из 50.32 стоит.
Вот тоже приходило в голову избавиться от старых ревизий, вопрос тогда как.
Еще когда на 50.19(точно не помню) обновлялись были проблемы с большим количеством ревизий, где-то топик был.
Там как раз проблема была при WHERE .. IN (вложенный запрос).

Нашел топик, там было 50.19->50.31. Где-то между ними ревизии полей появились.

220 тысяч записей в dopdata

[Efendy]

ну вот я нашел в форуме

Код:

DELETE FROM dopvalues WHERE revision NOT IN (SELECT rev FROM rev_users) AND revision NOT IN (SELECT rev FROM rev_equip)

обязательно сделай бекап

[goletsa]

ну вот я нашел в форуме

Код:

DELETE FROM dopvalues WHERE revision NOT IN (SELECT rev FROM rev_users) AND revision NOT IN (SELECT rev FROM rev_equip)

обязательно сделай бекап

Не сильно помогло, из 220к осталось 150к записей.
Остается только на N+ переежать?

[Efendy]

50.33, только немного функционал уменьшится

[goletsa]

50.33, только немного функционал уменьшится

Какой именно?

[Андрій]

пробую видалити в себе старі ревізії, але нічого не видаляється -

Код:

mysql> DELETE FROM dopvalues WHERE revision NOT IN (SELECT rev FROM rev_users) AND revision NOT IN (SELECT rev FROM rev_equip);
Query OK, 0 rows affected (1 min 29.53 sec)

mysql>

[goletsa]

Грусть-пичаль, у меня время выполнения запроса доросло до 32 секунд.
Причем странно.
Раскатал базу на другой комп, на ссд\хдд - там запрос на незагруженной базе выполняется 12 секунд независимо от того с какого носителя загружена база. И в момент запроса сильно грузит 1 ядро.
Т.е. перенос основной базы на ssd и 24 потока на сервере бесполезны, так как запрос похоже грузит только 1 ядро.

[Efendy]

А проверьте сколько времени выполняется

Код:

SELECT parent_id AS id, template_num, MAX(revision) AS rev FROM (SELECT * FROM dopfields f JOIN dopvalues v ON f.id=v.dopfield_id) d WHERE parent_type=0 GROUP BY parent_id,template_num

и

Код:

SELECT parent_id AS id, template_num, MAX(revision) AS rev FROM dopfields f JOIN dopvalues v ON f.id=v.dopfield_id WHERE parent_type=0 GROUP BY parent_id, template_num

[Efendy]

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

[goletsa]

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

А он один такой:

Код:

	Основной запрос:
SELECT SQL_CALC_FOUND_ROWS id FROM users WHERE grp IN (2,3,4,10,20,29,33,32,37,38,45,47,48,49,50,56,57,58,59,61,62,63,64,65,66,67,68,69,0) AND id IN (SELECT DISTINCT parent_id AS id FROM dopvalues WHERE revision IN (SELECT revision FROM dopvalues WHERE revision IN (SELECT rev FROM rev_users WHERE template_num=2) AND dopfield_id=8 AND field_value LIKE '32') AND dopfield_id=7 AND field_value LIKE '6') LIMIT 0,60
Время выполнения sql: 31.93646 сек
Всего строк: 27

[goletsa]

А проверьте сколько времени выполняется

Код:

SELECT parent_id AS id, template_num, MAX(revision) AS rev FROM (SELECT * FROM dopfields f JOIN dopvalues v ON f.id=v.dopfield_id) d WHERE parent_type=0 GROUP BY parent_id,template_num

и

Код:

SELECT parent_id AS id, template_num, MAX(revision) AS rev FROM dopfields f JOIN dopvalues v ON f.id=v.dopfield_id WHERE parent_type=0 GROUP BY parent_id, template_num

Первый
Отображение строк 0 - 24 (25825 всего, Query took 2.1825 seconds.)
Второй
Отображение строк 0 - 24 (25825 всего, Query took 0.9509 seconds.)

[Efendy]

Можно оптимизировать на ту одну секунду, что и дало изменение rev_users,  а толку? Чтоб вы понимали что делает этот запрос:
- те несколько сотен тысяч записей группируются по клиенту и выбирается максимальная ревизия для каждого (тут, кстати, оптимизируется та одна секунда и тут еще используются индексы). Получили 25 тыс записей
- в этих 25 тыс ищется поле, у которого значение = 32 (по твоему примеру), причем ищется по like - без индексов
- из полученного количества записей выуживаем ревизии и по каждому номеру ревизии выбираем набор данных с такой же ревизией и еще фильтруем по значению 6 (опять же без индексов)

К сожалению, такой пиздец для субд очень и очень затратен и оптимизировать это нихрена не удастся - неверная архитектура. Я это понял когда в моей сети стало 1000 абонов, поэтому я попытался улучшить ситуацию и убрал ревизии вообще нафик в 50.33 версии. Но полностью менять архитектуру уже было поздно. В N+ я и избавился от такой горизонтальной организации столбцов данных.

Запрос я вроде оптимизировал (надо тестить):

Код:

SELECT SQL_CALC_FOUND_ROWS id FROM users
WHERE grp IN (2,3,4,10,20,29,33,32,37,38,45,47,48,49,50,56,57,58,59,61,62,63,64,65,66,67,68,69,0)
    AND EXISTS(
  SELECT * FROM dopvalues d1 JOIN dopvalues d2 ON d1.revision=d2.revision
  WHERE d1.dopfield_id=8 AND d1.field_value LIKE '32' AND d2.dopfield_id=7 AND d2.field_value LIKE '6'
  AND EXISTS (SELECT rev FROM rev_users WHERE template_num=2 AND rev=d1.revision)
  AND d1.parent_id=users.id
)

Вопрос в том "как его в N50" внедрить?

[Efendy]

Насколько я понял, надо править файл nDopdatAPI.pl функцию nDopdata_search