MariaDB, storage engine и тип физических носителей

12
Metal_Messiah
На сайте с 01.08.2010
Offline
127
215

Доброго времени суток!

Нужна помощь опытных спецов по высоким нагрузкам.

Пришло время для переезда с одного скурвившегося хостинга (скорость записи на SSD падает ниже чем у некоторых выдает HDD + есть другие проблемы). Облако подобрал, но пока еще не начал ничего перетаскивать по кускам - провел серию тестов, результаты которых мне не вполне понятны. Тестировал софтом sysbench в режиме rndrw (чтение и запись из случайного места, RW), rndrd (чтение из случайного места, оно же R), rndwr (запись в случайное место, оно же W). Основной раздел облачного сервера на NVMe, дополнительно фирма позволяет арендовать и подключить тома других типов  (SSD или HDD с разным уровнем зеркалирования - 2 либо 3 клона в рейде).

Чтобы не лить много воды, скажу все в относительных единицах. Raid с 3 зеркалами дает преимущество по скорости чтения, а тот что с 2 зеркалами - по скорости записи, это было ожидаемо. На HDD Level 3 чтение в 1.35 раза выше Level 2, скорость записи на L2 в 1.13(RW)-1.24(W) превышает L3.  На SSD томах прирост по чтению не значимый, L2 в 1.16 раз быстрее L3 на W или RW.

Чтение с SSD где-то в 2.5 раза быстрее, чем с HDD тома. W / RW на SSD дают преимущество более чем в 3 раза.  NVMe по чтению в 10 раз быстрее SSD и почти в 30 HDD, по записи 30 и 100 раз соответственно. Опять же всё было ожидаемо.

Теперь то что мне не понятно.

Есть MySQL табличка размером очень много гигабайт, не подлежащая разрезанию по вертикали или горизонтали (сейчас 2.5 GB, бывает до 10 GB, старые данные несколько раз в год архивируются или дропаются по мере потери актуальности) . Механизм хранения InnoDB. Почему? потому что в конец таблицы постоянно добавляются новые данные, регулярно выполняются запросы на чтение, и ждать UNLOCK всей таблицы ну совсем не вариант.

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

  1. Выбрать значение ключа1 и количество записей где ключ1 IN (1,2,3,....) и ключ2=значение2 (Count(*) и GROUP BY, ничего интересного)
  2. Выбрать все записи где ключ1=значение1 и ключ2=значение2 и время (timestamp в uint(4), не индекс) больше определенного значения

То есть по сути идет везде обход по значениям индекса, во 2 случае в найденном происходит еще отсев по дате. Так, ладно, о чем это я, заговорился.

Для теста я сейчас разместил копию этой самой таблички (хранение InnoDB) + ее клоны на MyISAM и Aria на носителе каждого типа, и меняя расположение данных MySQL (а точнее, MariaDB) с перезапуском БД выполняю несколько однотипных тестовых запросов, критерием является время выполнения.

SELECT SQL_NO_CACHE count(*) FROM table1 WHERE key1=value1 AND not_key2=value2;

По сути это должен быть аналог выбора по ключу + отсев по дате, хотя поле not_key2, не являющееся индексом, имеет меньший размер, но вместо выбора пары тысяч строк данных из скольких-то миллионов, происходит подсчет количества строк (а выбранному критерию удовлетворяют порядка 95% данных в таблице, потому по сути полученное время - это время обхода всей таблицы по ключу key1 (и в реале такого никогда не будет). В результате я нифига не получаю тех же отношений, что на скоростях чтения с носителей разного типа.

 # InnoDB
 Aria  MyISAM
NVMe
44.14 sec
4.00 sec
4.09 sec
SSD на 3
58.08 sec
12.02 sec
12.35 sec
SSD на 2
59.08 sec
12.22 sec 11.88 sec
HDD на 3
63.50 sec
15.18 sec
15.72 sec
HDD на 2
65.90 sec
15.71 sec
16.16 sec

Зато по каждой колонке вылазит 103% отношение HDD2 / HDD3 (то есть RAID на 3 носителях читается быстрее чем на 2 на 3%), SSD 2 против 3 дают менее заметную разницу, NVMe всего в 3 раза быстрее SSD и в 4 раза быстрее HDD, куда делись цифры 10 и 30? Почему страницы InnoDB настолько медленнее работают, чем MyISAM, при отсутствии параллельной записи? На этом тестовом стенде проводится только чтение, таблица - утренняя копия с рабочей машины.

В общем, прошу прощения за много букв, помогите поднять скорость до потолка.

anonymous, думай что говоришь и не забывай подписать отзыв :) Газета Сейчас (http://sejchas.pp.ua) - свежие новости на автомате
Евгений Крупченко
На сайте с 27.09.2003
Offline
153
#1

так... букв действительно много.

может что не заметил или не так понял, но обращу внимание на пару моментов:

1) скорость чисто работы с диском (в тесте) была в 10 раз выше, но почему она должна быть тоже в 10 раз выше при работе с mysql не понятно. там же не чисто диск один трудится, но и процессор.

почему не может упираться в него сильнее, чем в диск?

2) "скорость ssd падает ниже hdd" - ну так ведь оно и есть в большинстве случаев когда диск, не предназначенный для этого заставляют много записывать.

это только в коротких тестах на пустом все красиво. а если забить его больше чем на половину и дать поработать 24/7 продолжительное время, чтоб очищенных ячеек почти не осталось да размера кэша на всех потребителей не хватает - тут и показывают свое истинное лицо новые, модные, молодежные ssd'шечки.

и дальше, почему nvme всего в 3 раза быстрее? даже отбросив влияние cpu, почему нет?

у вас же бенчмарк каким именно образом намерил 10-30 кратное превосходство? наверняка либо в последовательном доступе крупными блоками, либо мелкими, но с очередью 30-60 небось.

а ваш mysql запрос наверняка гораздо более однопоточный.

и если взглянуть еще раз на синтетические бенчмарки в 1 поток 4к блоками, то тоже ведь окажется, что нет никакого 10-кратного превосходства. около пол сотни мегабайт/сек что у ssd, что nvme ssd.

оно достигается лишь при определенных условиях. например с пол-сотни подобных запросов одновременно будут выполняться... да и то, скорей всего cpu станет не хватать гораздо раньше.

а не 10, а 30крат на запись - это почти наверняка влияние кэша. т.е. как только он закончится, а работу дальше нужно будет делать, вот тут диск и покажет что он на самом деле может... в плохом смысле.

LEOnidUKG
На сайте с 25.11.2006
Online
1546
#2

COUNT(*) убивают любую InnoDB т.к. смертельный запрос по пересчету всех данных.

Он вам точно нужен? Может всё таки на PHP потом посчитать количество записей?

Если работаем с InnoDB, может ну его нафиг этот диск а? Дайте серверу 32 ГБ памяти и выделите 28 ГБ для хранения InnoDB пусть всё лежит в памяти.

✅ Трастовых площадок под размещение статей и ссылок. Опыт 12 лет! ( https://searchengines.guru/ru/forum/675690 ) ⭐ Купить вечные трастовые ссылки для сайта ( https://getmanylinks.ru/?srh ) ⭐ Ускорение ваших сайтов (WP, Opencart и др.) + Настройка сервера ( https://searchengines.guru/ru/forum/997205 )
Metal_Messiah
На сайте с 01.08.2010
Offline
127
#3

Если бы у меня было 32 ГБ памяти на халяву - так бы и сделал. К сожалению, идея не катит.

Бенчмарк гонялся в режиме случайного доступа, а не последовательного. Он создает сколько-то файлов равного размера, суммарно заданного размера, потом происходит в них рандомное чтение, рандомная запись, или чтение-запись (как оно конкретно работает - не вникал). В моем понимании именно так работает MySQL: по ключам определяются смещения нужных записей в Талмуде, дальше идет setfilepointer() и ReadFile() либо WriteFile() (прошу прощения за терминологию Windows API, другой не знаю, и вообще тут Linux) и считываются блоки данных по нужным смещениям из многогигабайтного файла.

> COUNT(*) убивают любую InnoDB

COUNT(*) это обычный счетчик, инфа сама не передается за счет этого по идее экономия ресурсов. На реальных запросах там счет идет максимум на пару тысяч записей, удовлетворяющих запросу. Все всегда отрабатывает  в пределах 5 сек, но хочу быстрее. Сейчас я специально гоняю MySQL по всей таблице (95% попало по критерию) чтобы найти наиболее быстрый способ хранения данных (без учета того что повторные запросы быстрее отрабатывают за счет кеширования, причем не базы, а кеширования дисковых операций). С MyISAM я уже определился - если запрос касается выборки только по индексу, и не проверяет другие колонки, можно файл индекса кинуть на NVM / SSD, а файл с данными - на HDD, при больших объемах данных существенная экономия денег выйдет, время выполнения запроса - то же как будто все лежит на SSD. Если же в запросе проверяется какой-то не индекс то что-то среднее получается по времени между чисто HDD и чисто SSD. Но в InnoDB и данные, и индексы лежат в одной братской могиле, её не распилишь. А если у тебя будет скрипт 2 минуты дописывать туда новые данные и блокировать MyISAM таблицу, в это время в очереди висеть запросы, ожидающие UNLOCK, при этом половина из них отваливаться либо по таймауту (автоматические) либо обрубаться CloudFlare по причине бекенд не ответил, это не подходит.

> почему не может упираться в него сильнее, чем в диск?

Если база на NVMe - камень загружается на 96%, Iotop на MySQL показывает 10%. Получается что вот он потолок всего. Но когда на SSD камень загружен только на 70% для InnoDB или 30% на MyISAM / Aria - тут узкое место диск. Правильно понимаю? Чтение с HDD показывало 21% CPU на Isam / Aria и до 64% на Inno, iotop 79 и 42% соответственно.

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

Раньше не встречал. Всегда хостинг на SSD и хостинг на HDD существенно отличался, а тут мало того что скорость, так я еще баранам объясняю что у них раз в 10 минут кто-то на 3 секунды полностью кладет сеть своими кронами (рост пинга на все IP из интервала, включая мой), а они отвечают что специалисты работают над этим вопросом и ответят когда разберутся. И так уже пол года. Переезд уже без вариантов. Сейчас просто пытаюсь сообразить как грамотнее запустить эту базу, но пока самому сообразить не получается.

Metal_Messiah
На сайте с 01.08.2010
Offline
127
#4

Тут вообще пишут что если без WHERE то MyISAM берет count из метаданных, Inno считает.

А если с WHERE то  в обоих движках идет проход по данным и не должно быть существенных различий во времени. Но они есть. На порядок.

Improve InnoDB count(*) performance
Improve InnoDB count(*) performance
  • 2014.03.12
  • NBhatti
  • stackoverflow.com
I got a large table with million of records. I have to do a for a certain criteria and there is no way I can get rid of it. with is very expensive. I have been trying to figure out different configurations for MySQL but all in vain. Can't speed up the...
Andreyka
На сайте с 19.02.2005
Offline
817
#5

Специалисты по высоким нагрузкам говорят:


Разделите вашу таблицу на 2 части. Каждую разместите на своем mysql.

Допишите приложение, что если ему нужна одна часть - оно лезет на один хост мискуля, а если другая - то на другую.

Не стоит плодить сущности без необходимости
Metal_Messiah
На сайте с 01.08.2010
Offline
127
#6

Или так... Продолжаю эксперименты. Взял в цикле на php 10 тестовых выборок SELECT * FROM ... WHERE ... квазиодновременно (1я из каждой таблицы, потом 2я и так далее). Т.е. одинаковые запросы, отличающиеся только значением по ключу, соответственно разное число строк в выборке. Считаю суммарное время. Запрос включает полнотекстовый поиск (LIKE '%___%').

MyISAM дало 20.99 сек, Aria 20.52, InnoDB 75.33 секунд все на NVMe.

Дальше сделал копию InnoDB таблицы где одно из полей (varchar 512) сменил на Char 255 (больше не принималось, видимо однобайтная длина строки). Тест дал 0.04 секунды при тех же 20.70 на Aria. 

Без полнотекстового поиска (в условии WHERE один индекс и один целочисленный не индекс) Aria 18 сек, InnoDB 71.9, InnoDB Char 0.04 сек. Полнотекстовый поиск тут не при чём, основное время занимает обход таблицы с полями переменной длины. При том что судя по докам, в основной таблице хранится только адрес, а значение поля переменной длины хранится где-то в Тмутаракани, или я что-то путаю?

Metal_Messiah
На сайте с 01.08.2010
Offline
127
#7

Отредактировать пост почему-то не могу. Баг был какой-то, таблица с char вместо varchar занимала место на диске, но все выборки из нее выдали 0 записей, пришлось очистить и скопировать ее заново.

Выводы о преимуществах Char перед Varchar неверные. Varchar дает 70 секунд, char 80 секунд и при этом занимает существенно больше места на винте.

Между Varchar(512) и Varchar(255) существенной разницы по времени выполнения нет. По занятому дисковому пространству ровно за счет укорачивания некоторых длинных записей (которых мало) отличия 5%.

Solmyr
На сайте с 10.09.2007
Offline
492
#8
1. Сделайте композитный индекс id1+id2 и перенесите все на noSQL базу.
2. 10 Гб это вообще-то мало. Можно все в памяти держать.
Metal_Messiah
На сайте с 01.08.2010
Offline
127
#9

В памяти держать не вариант, памяти не больше 2 GB. Уже писал.

По поводу id1 + id2 не понял, Вы имеете в виду сделать так чтобы  в WHERE стояла выборка только по индексам? Вторая колонка это timestamp, миллионы разных значений, а не десяток как по другим индексам кроме primary (id). Кто-то когда-то говорил что делать индекс по таким данным нельзя, нецелесообразно или что-то в этом духе. Добавил индекс, +200 мегабайт к размеру таблицы (что составляет где-то по +11.5 байт на каждую строчку, индексируемая колонка - 4-байтный integer). 10 тестовых выборок не ускорились, начинаю думать как такое возможно - и оказывается, что я время уже сделал индексом в прошлый раз, но забыл об этом. Все тесты делались уже по индексам. 

> SELECT SQL_NO_CACHE count(*) FROM table1 WHERE key1=value1 AND date>UNIX_TIMESTAMP()-864000;

44 секунды, почти лям записей

> SELECT SQL_NO_CACHE count(*) FROM table1 WHERE key1=value1 AND date=1594119617;

0.01 сек, 48 записей

> SELECT SQL_NO_CACHE count(*) FROM table1 WHERE key1=value1 AND id>101500000;

1.26 сек, 1.7 млн записей.

(все 3 теста на InnoDB). Получается, что условие WHERE primary_key > some_value работает на порядок быстрее чем WHERE index > some_value? Видимо, буду писать функцию преобразования id (primary) <> date (приближенную, табличную, хз какую раз это быстрее), заменять дату на первичный индекс во всех запросах, и убирать индекс по date для экономии дискового пространства.

M
На сайте с 17.09.2016
Offline
86
#10

Это фича оптимизатора mysql (не движка)

Если при запросе извлекать нужно много данных (по мнению mysql), то индексы не используются

Можно их принудительно подсунуть с помощью опции FORCE INDEX

Ну и в целом (если это возможно) лучше посчитать  UNIX_TIMESTAMP()-864000 в приложении, а в базу ходить с уже рассчитанным значением

12

Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий