- Поисковые системы
- Практика оптимизации
- Трафик для сайтов
- Монетизация сайтов
- Сайтостроение
- Социальный Маркетинг
- Общение профессионалов
- Биржа и продажа
- Финансовые объявления
- Работа на постоянной основе
- Сайты - покупка, продажа
- Соцсети: страницы, группы, приложения
- Сайты без доменов
- Трафик, тизерная и баннерная реклама
- Продажа, оценка, регистрация доменов
- Ссылки - обмен, покупка, продажа
- Программы и скрипты
- Размещение статей
- Инфопродукты
- Прочие цифровые товары
- Работа и услуги для вебмастера
- Оптимизация, продвижение и аудит
- Ведение рекламных кампаний
- Услуги в области SMM
- Программирование
- Администрирование серверов и сайтов
- Прокси, ВПН, анонимайзеры, IP
- Платное обучение, вебинары
- Регистрация в каталогах
- Копирайтинг, переводы
- Дизайн
- Usability: консультации и аудит
- Изготовление сайтов
- Наполнение сайтов
- Прочие услуги
- Не про работу
Что делать, если ваша email-рассылка попала в спам
10 распространенных причин и решений
Екатерина Ткаченко
Авторизуйтесь или зарегистрируйтесь, чтобы оставить комментарий
Доброго времени суток!
Нужна помощь опытных спецов по высоким нагрузкам.
Пришло время для переезда с одного скурвившегося хостинга (скорость записи на 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 всей таблицы ну совсем не вариант.
Как правило, из таблицы делается выборка запросов по критериям и эти запросы нужно максимально ускорить. Самых часто выполняющихся и самых жирных запросов только двое:
То есть по сути идет везде обход по значениям индекса, во 2 случае в найденном происходит еще отсев по дате. Так, ладно, о чем это я, заговорился.
Для теста я сейчас разместил копию этой самой таблички (хранение InnoDB) + ее клоны на MyISAM и Aria на носителе каждого типа, и меняя расположение данных MySQL (а точнее, MariaDB) с перезапуском БД выполняю несколько однотипных тестовых запросов, критерием является время выполнения.
По сути это должен быть аналог выбора по ключу + отсев по дате, хотя поле not_key2, не являющееся индексом, имеет меньший размер, но вместо выбора пары тысяч строк данных из скольких-то миллионов, происходит подсчет количества строк (а выбранному критерию удовлетворяют порядка 95% данных в таблице, потому по сути полученное время - это время обхода всей таблицы по ключу key1 (и в реале такого никогда не будет). В результате я нифига не получаю тех же отношений, что на скоростях чтения с носителей разного типа.
Зато по каждой колонке вылазит 103% отношение HDD2 / HDD3 (то есть RAID на 3 носителях читается быстрее чем на 2 на 3%), SSD 2 против 3 дают менее заметную разницу, NVMe всего в 3 раза быстрее SSD и в 4 раза быстрее HDD, куда делись цифры 10 и 30? Почему страницы InnoDB настолько медленнее работают, чем MyISAM, при отсутствии параллельной записи? На этом тестовом стенде проводится только чтение, таблица - утренняя копия с рабочей машины.
В общем, прошу прощения за много букв, помогите поднять скорость до потолка.
так... букв действительно много.
может что не заметил или не так понял, но обращу внимание на пару моментов:
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крат на запись - это почти наверняка влияние кэша. т.е. как только он закончится, а работу дальше нужно будет делать, вот тут диск и покажет что он на самом деле может... в плохом смысле.
COUNT(*) убивают любую InnoDB т.к. смертельный запрос по пересчету всех данных.
Он вам точно нужен? Может всё таки на PHP потом посчитать количество записей?
Если работаем с InnoDB, может ну его нафиг этот диск а? Дайте серверу 32 ГБ памяти и выделите 28 ГБ для хранения InnoDB пусть всё лежит в памяти.
Если бы у меня было 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 из интервала, включая мой), а они отвечают что специалисты работают над этим вопросом и ответят когда разберутся. И так уже пол года. Переезд уже без вариантов. Сейчас просто пытаюсь сообразить как грамотнее запустить эту базу, но пока самому сообразить не получается.
Тут вообще пишут что если без WHERE то MyISAM берет count из метаданных, Inno считает.
А если с WHERE то в обоих движках идет проход по данным и не должно быть существенных различий во времени. Но они есть. На порядок.
Специалисты по высоким нагрузкам говорят:
Разделите вашу таблицу на 2 части. Каждую разместите на своем mysql.
Допишите приложение, что если ему нужна одна часть - оно лезет на один хост мискуля, а если другая - то на другую.
Или так... Продолжаю эксперименты. Взял в цикле на 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 сек. Полнотекстовый поиск тут не при чём, основное время занимает обход таблицы с полями переменной длины. При том что судя по докам, в основной таблице хранится только адрес, а значение поля переменной длины хранится где-то в Тмутаракани, или я что-то путаю?
Отредактировать пост почему-то не могу. Баг был какой-то, таблица с char вместо varchar занимала место на диске, но все выборки из нее выдали 0 записей, пришлось очистить и скопировать ее заново.
Выводы о преимуществах Char перед Varchar неверные. Varchar дает 70 секунд, char 80 секунд и при этом занимает существенно больше места на винте.
Между Varchar(512) и Varchar(255) существенной разницы по времени выполнения нет. По занятому дисковому пространству ровно за счет укорачивания некоторых длинных записей (которых мало) отличия 5%.
2. 10 Гб это вообще-то мало. Можно все в памяти держать.
В памяти держать не вариант, памяти не больше 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 для экономии дискового пространства.
Это фича оптимизатора mysql (не движка)
Если при запросе извлекать нужно много данных (по мнению mysql), то индексы не используются
Можно их принудительно подсунуть с помощью опции FORCE INDEX
Ну и в целом (если это возможно) лучше посчитать UNIX_TIMESTAMP()-864000 в приложении, а в базу ходить с уже рассчитанным значением