Почему запрос на MySQL 8 выполняется в десятки раз медленнее?

Question

[GeKskill]

Таблица на 260к строк, без индексов. И обычный запрос на поиск по текстовой строке, показывает разительное падение производительности MySQL 8 по сравнению с MySQL 5.7, подскажите пожалуйста, в чем может быть проблема. Проверил как на локальной машине OpenServer так и на хостинге, результаты аналогичные.



Тут дамп таблицы и конфигов MySQL

Comments

[YepBro]

Покажи планы обоих запросов (EXPLAIN).

[GeKskill]

YepBro, Если это то, что вы имели ввиду, то они идентичные

[Олег]

Вопрос поставлен не правильно. Нужно было спросить каким чудом это могло раньше работать быстро ?
У вас фулл скан по всем записям в таблице. Скорее всего my.ini(my.cnf) у 5.7 модифицирован так, что хватало оперативки держать все записи в озу.

Создать индекс почему не хотите ?

[GeKskill]

Олег, а не знаете какой параметр в конфиге искать, наверное что-то вроде buffer_pool_size? Но так то на локальном сервере у меня дефолтные конфиги.

[Олег]

Параметров тюнинга много. Лучше смотрите примеры конфигов для продакшн.
Но ещё раз . Запрос в примере элементарный. Кей-валуе по сути. Индекс по полю решит проблему, не надо насиловать полным перечитывание таблицы.

[Akina]

Снимите профили выполнения запроса для обоих версий сервера.

[YepBro]

GeKskill, Базы на одной машине или на разных? Если разные, то там есть отличие по объему памяти, загрузке процессора?

[GeKskill]

Akina, Это вот это?

[GeKskill]

YepBro, На локальном сервере "OpenServer" создал БД в каждой из версий MySQL и импортировал туда эту таблицу, без каких-либо настроек с моей стороны. Так что да, мощности одни и те же.

[Олег]

GeKskill, "тюнинг mysql 8" гугли. Я не вкурсе даже типа ваших таблиц (исам или инодб).
тема обширная, кроме того следует учитывать тип хостинга. Ну на шареде вас к конфигам обычно не пустят.
Остаются дедики и впс/впд. Если с первым понятно сколько ресурсов, то вот с виртуалками не все так просто.
Можно до оптимизироваться в минус из-за оверсела ресурсов хостером. Укажешь по лимитам виртуалки и весь выигрыш пропадет в постоянном свопе.

[GeKskill]

Олег, Таки да, планировалось, что этот проект будет висеть на шареде, поэтому и хочется понять где затуп. А конфиги уже гуглю, хотя бы чтобы на локалке добиться результата.

[Олег]

GeKskill, запрос выполняет полное сканирование таблицы. То есть в одном случае где быстрее работает кеширование. Не придирайся к этому термину. Под кешем понимается что к данным есть быстрый доступ.
Это могло быть из-за разности настроек базы данных. Второе лежит глубже. База данных должна считать их с жесткого диска (hdd/ssd). И вот тут еще вступает в действие операционная система. То что нужно часто вынимается из файлового кеша. Еще момент у тебя два инстанса базы данных. Тут тоже операционка рулит ресурсами.
В общем живая система вещь сложная и просто вот так сказать почему это быстрее нельзя. нужно смотреть кучу параметров и глубоко погрузиться в администрирование серверов.

Но скажите запрос у вас по полю без индекса боевой или просто минимальный пример ?
Когда я еще занимался пыхом и муськой, конфиги на локал хосте у меня специально были минимальными, чтобы тормоза "не оптимальность запросов" сразу вылезпли, а не маскировались механизмами оптимизации.

[GeKskill]

Олег, Боевой больше некуда, сейчас этот сайт на шареде с mysql 5.3 и без индекса ( кстати само поле формата ТЕХТ), а поставили задачу перенести все проекты клиента на один хостинг а там mysql 8 и тут началось). Про то, что где-то кэшируется вся таблица я склонен согласиться, но тут как бы и выходит, что надо определить этот механизм... В любом случае, спасибо что уделили время.

[Олег]

GeKskill, идиотский запрос по юзерагенту. Нафига он там вообще? Проверь может результат вообще не используется. Типа сайт на поддержке был и эту таблицу чистили раз в месяц, чтобы показать необходимость работ

[Александр]

Я думаю есть разница в конфигах my.cnf. Покажи конфиги от обоих версий MySQL.
И для такой большой таблице нужны индексы.

[GeKskill]

Александр, сделал полные дампы переменных MySQL и дамп этой таблицы на всякий
Google Drive Дампы

[Александр]

GeKskill, Нужно именно содержимое my.cnf смотреть. Они лежат в папке \Openserver\userdata\config\ и файлы c такими названиями:
MariaDB-5.5_my.ini
MariaDB-10.4_my.ini и т.д. но в вашем случае MySQL-5.7 и 8.

[GeKskill]

Александр, Понял, добавил в папку по ссылке Google Drive

[Александр]

GeKskill, Чот не то по ссылке открывается у Вас.

[GeKskill]

Александр, ох, действительно... извиняюсь, поправил Тыц

[Александр]

GeKskill, Различия есть. Например в конфиге для 8ой версии нет таких параметров:
query_cache_limit = 256K
query_cache_size=25M
query_cache_type=1
read_buffer_size=2M

[GeKskill]

Александр, первые три точно deprecated и были удалены из 8 версии, а про read_buffer_size почитаю

[GeKskill]

Александр, кстати если перевести таблицу на InnoDB то результаты в обеих версиях примерно равны 0.2s, выходит где-то затуп с MyISAM

[Александр]

GeKskill, Для такого количества записей само собой лучше использовать InnoDB. Если использовать MySQL то тогда нужно конфиг тюнить хорошо.

[dmitryklerik]

У вас тут full table scan, т.е. MyISAM табличка вычитывается с диска и каждое значение проверяется на совпадение. Я вот знаю только одну рекомендацию для ускорения таких запросов: это тюнинг размеров настройки read_buffer_size. Но я попробовал её менять и что-то ничего в скорости не меняется :) Вообще MyISAM сейчас считай depricated и инфы по нему мало, наверно что-то связанное с MyISAM в MySQL 8 поменяли, а никто и не заметил и не вынес на обсуждение.

Самое быстрое решение для вас это добавить индекс по полю AGENT

[GeKskill]

dmitryklerik, Спасибо за комментарий, да я в том проекте поменял таблицу на InnoDB и пробовал добавить индекс c лимитом в 150 символов т.к иначе ошибка создания индекса (столбец типа blob) и это более менее сработало. А по итогу проследил где используется этот запрос и просто заглушил его за ненадобностью :)