Долгие запросы MySql

InstantCMS 2.X

Как бороться с задумчивостью мускула?

#16 8 февраля 2018 в 00:14

Если есть смысл поменять на innodb — напишите, пожалуйста, в чем смысл.
На тестовом сервере переконвертил базу в innodb — особого ускорения не увидел.

Ris

А memcached разве работает с MyISAM?
Хотя может я просто не в курсе. )
Про RocksDB упомянул потому что все говорят что он "летает" smileно на mariadb
#17 8 февраля 2018 в 02:37

Про RocksDB упомянул потому что все говорят что он "летает" но на mariadb

eoleg
В ноябре писали, что тесты еще не все прошел, бывает ложит всю базу безвозвратно!
Но время идет… может быть есть уже стабильная версия…
#18 8 февраля 2018 в 16:33
Блин, что-то не то в обсуждении. Я на это не рассчитывал. shock
Я прекрасно понимаю, что за все в жизни приходится платить. И я готов платить некоторым уменьшением скорости открывания страниц за расширяемость и функционал второй ветки. Но меня все время гложет мысль, что я что-то делаю не так, поэтому и результат такой.
Я надеялся, что мне подскажут правильный путь и ткнут в мою ошибку. А тут холивар...

explain, отладка и судя по всему проверка почему у вас индекс date_pub используется при такой выборке. Ну нет проблем с производительностью от слова совсем. Через explain проверьте какие индексы используются запросом. Полагаю, вы либо сами удалили индекс в таблице от набора, либо при создании набора индекс автоматически не создался.

Fuze
Все индексы создались, удалять индексы и наборы я тоже пробовал. Тип контента свой. Поле контент — голый текст, заголовок генерится из текста при создании итема контента.
  1. CREATE TABLE `cms_con_anec` (
  2. `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  3. `title` VARCHAR(255) DEFAULT NULL,
  4. `content` text DEFAULT NULL,
  5. `photo` text DEFAULT NULL,
  6. `slug` VARCHAR(100) DEFAULT NULL,
  7. `seo_keys` VARCHAR(256) DEFAULT NULL,
  8. `seo_desc` VARCHAR(256) DEFAULT NULL,
  9. `seo_title` VARCHAR(256) DEFAULT NULL,
  10. `tags` VARCHAR(1000) DEFAULT NULL,
  11. `date_pub` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
  12. `date_last_modified` TIMESTAMP NULL DEFAULT NULL,
  13. `date_pub_end` TIMESTAMP NULL DEFAULT NULL,
  14. `is_pub` tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
  15. `hits_count` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  16. `user_id` INT(11) UNSIGNED DEFAULT NULL,
  17. `parent_id` INT(11) UNSIGNED DEFAULT NULL,
  18. `parent_type` VARCHAR(32) DEFAULT NULL,
  19. `parent_title` VARCHAR(100) DEFAULT NULL,
  20. `parent_url` VARCHAR(255) DEFAULT NULL,
  21. `is_parent_hidden` tinyint(1) UNSIGNED DEFAULT NULL,
  22. `category_id` INT(11) UNSIGNED NOT NULL DEFAULT 1,
  23. `folder_id` INT(11) UNSIGNED DEFAULT NULL,
  24. `is_comments_on` tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
  25. `comments` INT(11) UNSIGNED NOT NULL DEFAULT 0,
  26. `rating` INT(11) NOT NULL DEFAULT 0,
  27. `is_deleted` tinyint(1) UNSIGNED DEFAULT NULL,
  28. `is_approved` tinyint(1) UNSIGNED NOT NULL DEFAULT 1,
  29. `approved_by` INT(11) UNSIGNED DEFAULT NULL,
  30. `date_approved` TIMESTAMP NULL DEFAULT NULL,
  31. `is_private` tinyint(1) UNSIGNED NOT NULL DEFAULT 0,
  32. PRIMARY KEY (`id`),
  33. KEY `dataset_best` (`is_pub`,`is_parent_hidden`,`is_deleted`,`is_approved`,`rating`),
  34. KEY `dataset_best_yearly` (`date_pub`,`is_pub`,`is_parent_hidden`,`is_deleted`,`is_approved`,`rating`),
  35. KEY `slug` (`slug`),
  36. KEY `date_pub` (`is_pub`,`is_parent_hidden`,`is_deleted`,`is_approved`,`date_pub`,`parent_id`,`rating`) USING BTREE,
  37. KEY `user_id` (`user_id`,`date_pub`,`is_deleted`) USING BTREE,
  38. KEY `dataset_random` (`is_pub`,`is_parent_hidden`,`is_deleted`,`is_approved`,`title`),
  39. KEY `parent_id` (`parent_id`),
  40. FULLTEXT KEY `content` (`title`,`content`)
  41. ) ENGINE=MyISAM AUTO_INCREMENT=128905 DEFAULT CHARSET=utf8 AVG_ROW_LENGTH=446
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE i ref dataset_best,date_pub,user_id,dataset_random dataset_best 5 const,const,const 119056 Using index condition; Using where; Using filesort
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 neos_1.i.user_id 1
1 SIMPLE f eq_ref PRIMARY PRIMARY 4 neos_1.i.folder_id 1 Using where
#19 8 февраля 2018 в 16:48
Ris, я вижу вы индекс date_pub правили вручную. Удалите в нем parent_id и rating, им там не место. В индексе user_id последней ячейкой должна быть поле date_pub. Поменяйте местами.
#20 8 февраля 2018 в 17:05

Удалите в нем parent_id и rating, им там не место. В индексе user_id последней ячейкой должна быть поле date_pub. Поменяйте местами.

Fuze
Удалил и поменял
Отображение строк 0 — 24 (30 всего, Запрос занял 3.8594 сек.)
SELECT i.*, u.nickname as user_nickname, f.title as folder_title FROM cms_con_anec i FORCE INDEX FOR ORDER BY (date_pub) INNER JOIN cms_users as u FORCE INDEX (PRIMARY) ON u.id = i.user_id LEFT JOIN cms_content_folders as f ON f.id = i.folder_id WHERE (i.is_parent_hidden IS NULL) AND (i.rating > '-20') AND (i.is_deleted IS NULL) AND (i.is_pub = '1') ORDER BY i.date_pub desc LIMIT 0, 30
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE u ALL PRIMARY NULL NULL NULL 1887 Using temporary; Using filesort
1 SIMPLE i ref dataset_best,dataset_random,date_pub,user_id user_id 7 neos_1.u.id,const 1 Using index condition; Using where
1 SIMPLE f eq_ref PRIMARY PRIMARY 4 neos_1.i.folder_id 1 Using where
Вижу, что подхватился индекс user_id, но почему-то пропропал индекс из таблицы юзеров.
Не могу сказать, что понял в чем дело, но сейчас попробую поэкспериментировать.
Может мне попробовать создать тип контента заново, чтобы все поля и индексы создались автоматически, а потом перенести туда только данные?
#21 8 февраля 2018 в 18:40

Блин, что-то не то в обсуждении. Я на это не рассчитывал.

Ris

Я надеялся, что мне подскажут правильный путь и ткнут в мою ошибку. А тут холивар...

Ris
но вы же ранее:

Что-то я в попытках разобраться в этих тормозах совсем запутался.
У меня уже нервный тик и глаз дергается. shock
Включено кэширование на 2 минуты в memcached, но почему-то вместе с кешем все равно производятся запросы в базу.
Вот это из одной отладки:
И в то же время использовался кэш:
Такое ощущение, что кэш сохраняется, но не используется.
Что, черт побери, можно еще проверить, чтобы оно зашевелилось?

Ris
Затем:

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

Ris
Вообще то это разные вещи, смешались в кучу кони люди и залпы тысячи орудий. smile
Насчёт ошибки — всё же memcached разве работает с MyISAM?
#22 8 февраля 2018 в 19:24

Насчёт ошибки — всё же memcached разве работает с MyISAM?

eoleg
Какой ошибки?
Мемкешед разве не результирующие html кэширует?
У мускула вроде свой кэш?
#23 8 февраля 2018 в 19:35
Ris, memcached нужно настраивать для работы с mysql.
Вы же его хотите с mysql использовать.
Ну ладно, вижу зря я тут к вам влез со своими умозаключениями, успехов! smile
#24 8 февраля 2018 в 19:41

Ну ладно, вижу зря я тут к вам влез со своими умозаключениями, успехов!

eoleg
Нет уж, Вы расскажите, как мемкешед мускул кеширует. Я хотя бы попробую на тестовом сервере.
#25 9 февраля 2018 в 15:27
Столкнулся с проблемой, что в админке инстант еле-еле ползает (несколько сайтов на 2.8.2 и 2.9.0 и разные конфигурации php). На фронте работает шустро вроде, а вот в админке полный атас. Это тоже долгие запросы или с чем-то другим может быть связано?
Техподдержка говорит "нагрузки на сервер не наблюдаем" (((
#26 10 февраля 2018 в 12:34
Fuze,
Вы оказались правы! Дело было в индексах.
Добавил во все индексы датасетов строку date_pub на первое место. Во всех индексах, где эта строка была, тоже выставил её на первое место.
Теперь запрос выполняется в тысячу раз быстрее:
  1. SELECT i.*, u.nickname AS user_nickname, f.title AS folder_title
  2. FROM cms_con_anec i
  3. FORCE INDEX FOR ORDER BY (date_pub)
  4. INNER JOIN cms_users AS u FORCE INDEX (PRIMARY) ON u.id = i.user_id
  5. LEFT JOIN cms_content_folders AS f ON f.id = i.folder_id
  6. WHERE (i.is_parent_hidden IS NULL) AND (i.rating > '-20') AND (i.is_deleted IS NULL) AND (i.is_pub = '1')
  7. ORDER BY i.date_pub DESC
  8. LIMIT 0, 30
  9. Время выполнения 0.00471 секунд
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE i index NULL date_pub 10 NULL 30 Using where
1 SIMPLE u eq_ref PRIMARY PRIMARY 4 neos_1.i.user_id 1
1 SIMPLE f eq_ref PRIMARY PRIMARY 4 neos_1.i.folder_id 1 Using where
Теперь еще одна проблема — выборка комментариев выполняется 15 секунд:
  1. /system\controllers\comments\model.php => 298 => modelComments->getComments()
  2. SELECT i.*, r.score AS is_rated, u.nickname AS user_nickname, u.avatar AS user_avatar
  3. FROM cms_comments i
  4. LEFT JOIN cms_users AS u ON u.id = i.user_id
  5. LEFT JOIN cms_comments_rating AS r ON r.comment_id = i.id AND r.user_id='86'
  6. WHERE (i.user_id = '86') AND (i.is_deleted IS NULL)
  7. ORDER BY i.is_approved DESC, i.date_pub DESC
  8. LIMIT 0, 20
  9. Время выполнения 15.11935 секунд
Причем, если убрать ORDER BY i.is_approved desc — время запроса резко уменьшается.
  1. Отображение строк 0 - 19 (20 всего, Запрос занял 0.0310 сек.) [date_pub: 2018-02-10 01:19:23... - 2018-02-08 20:10:31...]
  2. SELECT i.*, r.score AS is_rated, u.nickname AS user_nickname, u.avatar AS user_avatar FROM cms_comments i LEFT JOIN cms_users AS u ON u.id = i.user_id LEFT JOIN cms_comments_rating AS r ON r.comment_id = i.id AND r.user_id='86' WHERE (i.user_id = '86') AND (i.is_deleted IS NULL) ORDER BY i.date_pub DESC LIMIT 0, 20
Вопрос, откуда берется этот ORDER BY i.is_approved desc и как его убрать из запроса?
#27 3 марта 2018 в 23:42
Продолжение мерлезонского балета.
Сегодня на яндексе проснулся какой-то злой дух и начал сканировать комментарии семилетней давности. И всё, сайт лёг.
В логе долгих запросов 100500 записей типа:
  1.  
  2. # Query_time: 51.697987 Lock_time: 0.000473 Rows_sent: 20 Rows_examined: 1843896
  3. SET TIMESTAMP=1520069949;
  4. SELECT i.*, r.score AS is_rated, u.nickname AS user_nickname, u.avatar AS user_avatar
  5. FROM cms_comments i
  6. LEFT JOIN cms_users AS u ON u.id = i.user_id
  7. LEFT JOIN cms_comments_rating AS r ON r.comment_id = i.id AND r.user_id='0'
  8. WHERE (i.is_private = '0') AND (i.is_deleted IS NULL) AND (i.is_approved = '1')
  9. ORDER BY i.date_pub DESC
  10. LIMIT 922000, 20;
Полез в список комментариев, а там на последних страницах выборка по минуте:
Ничего умнее не придумал, как доработать \system\controllers\comments\frontend.php, по этой инструкции habrahabr.ru/post/217521/ таким образом:
  1. // Получаем количество и список записей
  2. $total = !empty($this->count) ? $this->count : $this->model->getCommentsCount();
  3. $this->model->limit($perpage)->join .= ("JOIN (SELECT id FROM cms_comments ORDER BY id DESC LIMIT ".($page-1)*$perpage.", $perpage ) c ON c.id = i.id ");
  4. $items = $this->model->getComments();
Сразу стало всё гораздо веселее:
  1. /system\controllers\comments\model.php => 296 => modelComments->getComments()
  2. SELECT i.*, r.score AS is_rated, u.nickname AS user_nickname, u.avatar AS user_avatar
  3. FROM cms_comments i
  4. JOIN (SELECT id FROM cms_comments ORDER BY id DESC LIMIT 958740, 20 ) c ON c.id = i.id LEFT JOIN cms_users AS u ON u.id = i.user_id
  5. LEFT JOIN cms_comments_rating AS r ON r.comment_id = i.id AND r.user_id='86'
  6. WHERE (i.is_deleted IS NULL) AND (i.is_approved = '1')
  7. ORDER BY i.date_pub DESC
  8. LIMIT 20
  9. Время выполнения 0.22628 секунд
но пропали списки комментариев у пользователя в профиле.
Сил экспериментировать больше не было и поэтому ограничил нововведение списком всех комментариев:
  1. // Получаем количество и список записей
  2. $total = !empty($this->count) ? $this->count : $this->model->getCommentsCount();
  3. if (strpos($_SERVER['REQUEST_URI'], 'comments/all')) {$this->model->limit($perpage)->join .= ("JOIN (SELECT id FROM cms_comments ORDER BY id DESC LIMIT ".($page-1)*$perpage.", $perpage ) c ON c.id = i.id ");}
  4. $items = $this->model->getComments();
Костыль, конечно, но вроде работает:
Может кто может подсказать более человеческий способ борьбы с limit offset на больших таблицах?
#28 5 марта 2018 в 04:28
AndroS, на 2.9 не должно быть уже. а на ранних версиях проблема была с подгрузкой инфы с сайта инстанта, не асинхрон был скрипт.
срипт ожидает коннекта, а потом отдает все остальное. поэтому кроме как времени исполнения нагрузки нет.
#29 5 марта 2018 в 07:26


AndroS, на 2.9 не должно быть уже. а на ранних версиях проблема была с подгрузкой инфы с сайта инстанта, не асинхрон был скрипт.
срипт ожидает коннекта, а потом отдает все остальное. поэтому кроме как времени исполнения нагрузки нет.

kirkr
Тем не менее, у меня именно на 2.9 данная проблема наблюдалась. Само собой все прошло, но техподдержке кровь посворачивал ) может, и они чего там подкрутили на сервере.
#30 5 марта 2018 в 08:44

Сегодня на яндексе проснулся какой-то злой дух и начал сканировать комментарии семилетней давности. И всё, сайт лёг.

Ris

Этот злой дух "сканирует" уже месяц, сайты. два сайта опустил ниже плинтуса, еще два — не поднимает выше +30 ни в какую
Вы не можете отвечать в этой теме.
Войдите или зарегистрируйтесь, чтобы писать на форуме.
Используя этот сайт, вы соглашаетесь с тем, что мы используем файлы cookie.