запросы SQL: время выполнения на разных серверах.

[sitecreator]

Столкнулся с интересной особенностью.

Есть два сервера со схожими мощностями.

 

1) 4 ядра * 3.3 ГГц и 8Гб оперативки, HHD

2) 4ядра * 4 ГГц и 16 Гб оперативки, SSD

 

Собственно, для работы сервера БД (Mariadb 5.5)  характеристики можно считать идентичными, учитывая то, что нагрузка на процессор в обоих вариантах на уровне 1% и ниже, а памяти более чем достаточно.  За счет частоты процессора разницу можно ожидать не более 20% во времени выполнения запроса.

 

Сервера БД одинаковой версии. Настроены одинаково, т. е. конфиги совпадают.  Но в одном случае сервер на linux, а в другом - на windows (локальная машина).

 

Не смотря на одинаковые конфиги не могу сообразить почему определенный запрос на 1-м мнее мощном сервере  (linux 3.3 ГГц) работает существенно быстрее (раза в два).  Остальные запросы (SELECT) как и положено отличаются на 20% на менее мощном сервере в сторону времени увеличения.

Создание таблицы MyISAM на менее мощном сервере тоже ожидаемо занимает больше времени, но тут в первую очередь дело связано с тем, что диски только HHD.

 

SELECT COUNT(*) AS val, filter_group, filter_id 
FROM `bf_tmp_product` AS p INNER JOIN `bf_filter` AS f ON (p.product_id = f.product_id)
WHERE match_filters = 1 AND f.language_id = '1' 
GROUP BY filter_group, filter_id;	

 

вот этот запрос на разных серверах отличается по времени примерно в два раза.  Причем менее мощный процессор делает его быстрее.

 

Еще заметил, что если в конце запроса поставить

 

LIMIT 50000

тут значение цифра не имеет, хоть 50, хоть 50000,  то время выполнения на более мощном сервере резко уменьшается.

 

 

Без "LIMIT"  на серверах такой результат (1-й - 3.3ГГц,  2-й - 4ГГц)

 

 

 

 

 

 

 

Если добавляю "LIMIT 50000"

С "LIMIT"  на серверах такой результат (1-й - 3.3ГГц,  2-й - 4ГГц)

 

 

 

 

 

Т. е. для одного сервера время не меняется, а для другого меняется в зависимости от того есть ли в запросе LIMIT.

 

Во всех экспериментах таблица MyISAM (изменение на другой тип не меняет картину, хоть на MEMORY)в кодировке utf8 без индексов (для 1-й таблицы).  количество строк и размер таблицы в байтах одинаковый для обоих серверов как для одной таблицы (10616), так и для другой ( 528862 строк).  В запросе две таблицы участвуют.

Кеширование запросов не задействовано,  т. к. перед запросом таблица удаляется и создается вновь.  Время удаления и создания я не учитываю.

 

если ставлю

LIMIT 5000000

то результат по времени будет такой же (тормозной) как и без "LIMIT".

 

А если "LIMIT 5" или "LIMIT 500000", то все работает быстро.  В этом диапазоне цифра в "LIMIT"  не влияет на время выполнения запроса.

 

В чем я туплю? Отчего может быть зависимость? Может быть я не учитываю что-то элементарное?

Змінено 5 липня 2017 користувачем sitecreator

[ArtemPitov]

может из-за 

 

Цитата

windows (локальная машина).

 

[sitecreator]

6 минут назад, ArtemPitov сказал:

может из-за 

 

была такая мысль.

не придумаю как измерить или отловить такую тонкость.

почему отсутствие или присутствие LIMIT вообще влияет на время обработки запроса? По идее ведь не должно.

 

Была мысль поставить на виртуальной машине (Linux) точно такой же сервер MariaDB и посмотреть как там дела будут. Сделаю ради эксперимента.

 

Интересно то, что на windows пробовал разные версии MariaDB: 5.5 и 10.2.  Ведут себя одинаково в вопросе LIMIT.

[ArtemPitov]

Цитата

была такая мысль

Я протупил, не удивил что EXPLAIN, смотрите конфиги, что то не досмотрели 

 

Цитата

почему отсутствие или присутствие LIMIT вообще влияет на время обработки запроса? 

 

Уберите WHERE и появится разница. По сути в WHERE переберется вся таблица по этому лимит так отрабатывает. 

 

[pantagruel964]

NTFS  - тормоз, не знали?

У вас же окружение разное, вообще разное. В чем тогда смысл замера?

VM можете даже не ставить... потому что NTFS - тормоз.

[sitecreator]

8 минут назад, pantagruel964 сказал:

NTFS  - тормоз, не знали?

 

а про это я даже и не подумал.

Предполагал, собственно, что в окружении дело, грешить уже не на что было больше, но не думал, что может быть настолько все запущено.

 

Давно хотел машину для разработки перевести на linux, но photoshop сдерживает.  Макеты для верстки присылают в файлах фотошопа.  А так ничего на windows и не держит.  Разрываюсь, по сути, между желанием работать на linux и иметь одновременно полноценный фотошоп.

[pantagruel964]

Вы верстать в линуксе собрались? Ну-ну.. Думаю дня через три будет церемониальное возвращение в windows.

 

Зачем вообще куда-то переходить? Можно комфортно работать в винде, для разработки есть vagrant и docker

[sitecreator]

Сделал я самую большую таблицу (на пол-лимона записей)  для эксперимента (чтобы убрать фактор влияния файловой системы NTFS):

 

engine=MEMORY

 

И результат прямо чудесный стал.  вместо 1 секунды сразу 0.16 сек.

 

для MyISAM результат 0.92 сек

для InnoDB результат 0.69

 

 

 

Змінено 6 липня 2017 користувачем sitecreator

[sitecreator]

engine=Aria;

0.287 сек  (это на windows)

 

а почему мы не используем Aria как замену MyISAM ?

 

На сервере под Linux Aria вместо MyISAM (для одной лишь таблицы, но самой большой: пол-миллиона с лишним )дало:

0.45 сек против 0.6 сек

 

Если Aria обратно совместима с MyISAM , то что может остановить? Подводные камни?

Прирост то большой наблюдаю - на уровне 20...25%. 

Может быть я измеряю не так? Но время генерации страницы уменьшается реально.

 

 

 

[chukcha]

План запросов смотреть бесполезно,а вот профайлинг - все покажет

[chukcha]

По идее Aria не должно давать преимущества в скорости.
Это более "стресоустойчивая" архитектура. Т.е. более стойкая к разрушениям, чем MyIsam

 

Чтобы проверить
Не вопрос

Создать две одинаковые таблицы по структуре, и данным, но разного типа хранения
И сделать запросы с SQL_NO_CACHE
И на каждый запросить profile

 

А все ли mysql-сервера БД поддерживают Aria, кроме как MariaDB

 

 

 

 

[sitecreator]

13 часов назад, chukcha сказал:

Чтобы проверить
Не вопрос

Создать две одинаковые таблицы по структуре, и данным, но разного типа хранения
И сделать запросы с SQL_NO_CACHE
И на каждый запросить profile

 

именно это и сделал.

я изначально рассматривал самые долгие и тяжелые запросы, связанные с фильтрованием товаров в тяжелой категории (более 10000 шт. товара в одной).

Поэтому и исследовал таблицы, которые участвуют в этих запросах.

 

результат таков (linux, mariadb 5.5):

 

 

 

Админер умеет сам точно профилировать запрос.  Чтобы это проверить достаточно сделать следующее:

 

 

Видно, что в совершенно одинаковых условиях замена MyISAM на Aria  дает выигрыш примерно 15%.  Вроде бы и не сильно много, но почему бы не воспользоваться?  

Фильтрация - слишком слабое место чтобы не воспользоваться разными способами ради уменьшения времени генерации страницы.  А разные способы в сумме способны дать определенный прирост производительности.

 

13 часов назад, chukcha сказал:

А все ли mysql-сервера БД поддерживают Aria, кроме как MariaDB

 

Тут согласен, не все.

Но если у вас VPS и Centos, то MariaDB по любому есть. 

Да и если у вас VPS (а в случае десятков тысяч товаров это скорее всего так), то что вас может ограничивать от применения MariaDB?

 

Мне думается, что для увеличения скорости работы сайта все методы хороши, кроме вносящих потенциальную опасность взлома/поломки/несовместимости и т. п.

 

А тут вместе с увеличением производительности в самом нагруженном месте получаем еще и увеличение надежности за счет более высокой поломкостойкости Aria по сравнению с MyISAM.

 

Я специально пока рассматривал лишь таблицы, которые создает фильтр.  Стандартные таблицы opencart я никак не трогал.

Сдается мне, что ради благого дела ускорения фильтрации эти таблицы фильтров можно и в MEMORY перевести. По сути эти таблицы - это некий кеш, который создает фильтр и который периодически обновляется.  Так что даже при сбое (перезагрузке или отключении сервера) потеря этих таблиц не приведет ни к чему страшному, ибо за несколько секунд они автоматически генерируются.

Вот тут вообще выигрыш многократный получается.

 

Aria и MEMORY потребляют больше памяти чем MyISAM.  Но разве проблема ради благого дела ускорения докупить лишние 500 Мб памяти?

 

Цитата

Дополнительные 512 MB памяти - 149 р.

 

это у популяроного админвпс.

 

или

Цитата

дополнительные 2048 Мб памяти - 440 р.

 

у ispserver.

 

Думаю, что для бизнеса, использующего высоконагруженный сайт, эти суммы вообще ничего не значат.

 

-----------------

тесты под windows не стал приводить, ибо, как показывал ранее,  у меня какие-то чудеса (тормоза) творятся при использовании больших таблиц (более пол-миллиона записей) MyISAM.  А с Aria  никаких проблем.  Поэтому на windows сравнение было бы некорректное.

[pantagruel964]

а mysql какой версии?

[sitecreator]

17 минут назад, pantagruel964 сказал:

а mysql какой версии?

 

имеется ввиду сервер? или иное?

mariadb 5.5

[pantagruel964]

11 минут назад, sitecreator сказал:

mariadb 5.5

Не заметил.

 

А почему используются анахронизмы?

Не думаете, что в 2017-м актуально было бы сравнивать mysql 5.7 и maridb 10.2? Никаких 15% прироста уже не будет. Также как и нет смысла использовать myisam в mysql 5.7

[sitecreator]

15 минут назад, pantagruel964 сказал:

А почему используются анахронизмы?

 

потому, что сегодня хостеры предлагают даже для последней centos 7 именно mariadb 5.5

 

 

22 минуты назад, pantagruel964 сказал:

актуально было бы сравнивать mysql 5.7 и maridb 10.2?

 

думаю, что именно это и было бы верно.

просто исходил из того, что mariadb 5.5 оказалась самой распространенной по непонятной для меня причине.

 

23 минуты назад, pantagruel964 сказал:

Также как и нет смысла использовать myisam в mysql 5.7

 

а что имеет смысл использовать? Учитывая также, что в движке (модулях) жестко прописан выбор engine  (только MyISAM и чуть-чуть innodb).

[pantagruel964]

47 минут назад, sitecreator сказал:

потому, что сегодня хостеры предлагают даже для последней centos 7 именно mariadb 5.5

Уже vps за $2.5 можно взять. Прошло время. когда ленивые хостеры устанавливали правила

 

 

47 минут назад, sitecreator сказал:

а что имеет смысл использовать?

innodb очевидно. В 5.6 и 5.7 было реализовано все, что было в myisam и чего не хватало в innodb. В mysql 8 myisam не будет использоваться даже как системное хранилище. Ну и как сами пишут: In MySQL 8.0 the MyISAM storage engine is still available. But in a very limited scope. Дальше просто выпилят или оставят как опцию.

Ничего же не мешает в опенкарте сменить тип хранилища, хотя смысла особого пока нет.

[sitecreator]

1 час назад, sitecreator сказал:

а что имеет смысл использовать?

 

32 минуты назад, pantagruel964 сказал:

innodb очевидно.

 

есть мнение , что innodb - это в определенной степени зло для опенкарт.  Много букв по этому поводу написано, и в подтверждение приводится плачевный опыт такого использования.

Правда, писалось про это больше года назад когда версия mysql была 5.6.4+ . 

Один из аргументов  (не копирую аргументы, относящиеся к более старым версиям сервера ):

 

Цитата

Везде написано с операциями на чтение данных MyIsam работает быстрее

 

???

 

1 час назад, pantagruel964 сказал:

актуально было бы сравнивать mysql 5.7 и maridb 10.2?

 

сейчас этим и занимаюсь.  предполагаю как и вы, что разницы не будет.

 

32 минуты назад, pantagruel964 сказал:

Ничего же не мешает в опенкарте сменить тип хранилища

 

это так

Змінено 6 липня 2017 користувачем sitecreator