sitecreator

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

Рекомендуемые сообщения

sitecreator    541

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

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

 

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ГГц)

 

a26c1b63cd.jpg

 

 

 

77291e082f.jpg

 

 

 

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

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

 

13de051508.jpg

 

5d47577f6d.jpg

 

 

 

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

 

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

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

 

если ставлю

LIMIT 5000000

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

 

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

 

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

Изменено пользователем sitecreator

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
ArtemPitov    402

может из-за 

 

Цитата

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

 

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
sitecreator    541
6 минут назад, ArtemPitov сказал:

может из-за 

 

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

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

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

 

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

 

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
ArtemPitov    402
Цитата

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

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

 

Цитата

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

 

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

 

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
pantagruel964    219

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

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
sitecreator    541
8 минут назад, pantagruel964 сказал:

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

 

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

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

 

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
pantagruel964    219

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

 

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
sitecreator    541

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

 

engine=MEMORY

 

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

 

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

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

 

 

 

c379b8d39e.jpg

Изменено пользователем sitecreator

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
sitecreator    541
engine=Aria;

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

 

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

 

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

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

 

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

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

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

 

 

 

31ffc205ff.jpg

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
chukcha    979

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
chukcha    979

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

 

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

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

 

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

 


 

 

 

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
sitecreator    541
13 часов назад, chukcha сказал:

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

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

 

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

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

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

 

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

 

dcdf68ea2c.jpg

 

a526a78fc4.jpg

 

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

 

178b5fc086.jpg

 

Видно, что в совершенно одинаковых условиях замена 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    219

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
sitecreator    541
17 минут назад, pantagruel964 сказал:

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

 

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

mariadb 5.5

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
pantagruel964    219
11 минут назад, sitecreator сказал:

mariadb 5.5

Не заметил.

 

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

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
sitecreator    541
15 минут назад, pantagruel964 сказал:

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

 

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

 

ec653aeaf7.jpg

 

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

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

 

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

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

 

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

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

 

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

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты
pantagruel964    219
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    541
1 час назад, sitecreator сказал:

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

 

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

innodb очевидно.

 

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

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

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

 

Цитата

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

 

???

 

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

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

 

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

 

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

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

 

это так

Изменено пользователем sitecreator

Поделиться сообщением


Ссылка на сообщение
Поделиться на другие сайты

Для публикации сообщений создайте учётную запись или авторизуйтесь

Вы должны быть пользователем, чтобы оставить комментарий

Создать учетную запись

Зарегистрируйте новую учётную запись в нашем сообществе. Это очень просто!

Регистрация нового пользователя

Войти

Уже есть аккаунт? Войти в систему.

Войти


  • Последние посетители   0 пользователей онлайн

    Ни одного зарегистрированного пользователя не просматривает данную страницу