Речь пойдет об оптимизации в MySQL базе данных.
Это случилось, когда мы делали систему для email рассылок. Наша система должна была высылать десятки млн. писем в день. Отправка письма — задача не из простых, хотя выглядит все довольно примитивно:
Расскажу подробнее о втором пункте:
Микросервис mail-builder занимается подготовкой письма к отправке:
Таким образом, все исходные ссылки будут заменены на uuid-ы, а домен поменяется на наш. При GET запросе по этой ссылке мы проксируем оригинальную картинку или редиректим на исходную ссылку. Сохранение происходит в базу данных MySQL, мы сохраняем сгенерированный uuid вместе с оригинальной ссылкой и с некоторой мета информацией (email пользователя, id рассылки и другими данными). Денормализация помогает нам в 1 запрос получить все необходимые данные для сохранения статистики, или старта какой-то триггерной цепочки.
Генерация uuid-a у нас зависела от timestamp.
Так как рассылки обычно происходят в определенный период времени и запущено много инстансов микросервиса по сборке письма, получалось, что часть uuid-ов была очень схожими.Это давало низкую селективность. UPD: т.к данные получались схожие, работа с би-деревом была не очень эффективная.
Решили эту проблему использованием модуля uuid в питоне, где нет зависимости от времени.
Такая неявная вещь уменьшала скорость работы индексов.
Как происходит хранение?
Структура таблицы была следующая:
На момент создания все выглядело логично:
UUID — primary key, а также является кластерным индексом. Когда мы делаем выборку по этому полю, то мы просто выбираем запись, ведь все значения хранятся тут же. Это было обдуманным решением. Подробнее о кластерном индексе.
Все было замечательно, пока таблица не разрослась.
Если прочитать подробнее про кластерный индекс, можно узнать о таком нюансе:
Решением было использовать другую структуру таблицы.
Так как теперь primary key автоинкрементный, а в mysql хранится кеш последнего места вставки, теперь вставка всегда происходит в конец, т.е Innodb оптимизирован под запись последовательно-возрастающих значений.
Подробности этой оптимизации я находил в исходниках у postgres. В mysql реализована очень похожая оптимизация.
Пришлось, конечно, добавить уникальный ключ, чтобы не было коллизий, но скорость вставки мы увеличили.
С еще большим ростом базы, мы задумались над тем, чтобы удалять старые данные. Использовать DELETE по полю Inserted абсолютно не оптимально — это очень долго, а также место не будет освобождено пока мы не выполним команду optimize table. Кстати, эта операция полностью блокирует таблицу — это нам совсем не подходило.
Поэтому мы решили разбить нашу таблицу на партиции.
1 день — 1 партиция, старые дропаются автоматически, когда приходит время.
Мы получили возможность удалять старые данные, но мы не получили возможности выбирать из нужной партиции, т.к при select`e мы указываем только uuid, mysql не знает в какой партиции нам это искать и ищет во всех.
Решение родилось из Проблемы №1 — добавить timestamp в сгенерированный uuid. Только в этот раз мы поступили немного по-другому: вставили timestamp в случайное место строки, не в начало и не в конец; перед ним и после добавили символ тире, чтобы его можно было получить регулярным выражением.
С помощью этой оптимизации мы смогли получать дату, когда uuid был сгенерирован и уже сделать select с указанием конкретного значения поля Inserted. Теперь мы читаем данные сразу из нужной нам партиции.
Так же, благодаря таким вещам, как ROW_FORMAT=COMPRESSED и сменой кодировки на latin1, мы сэкономили еще больше места на жестком диске.
Это случилось, когда мы делали систему для email рассылок. Наша система должна была высылать десятки млн. писем в день. Отправка письма — задача не из простых, хотя выглядит все довольно примитивно:
- Собрать письмо из html креатива, подставить персонализированные данные.
- Добавить пиксель просмотра письма, заменить все ссылки в письме на свои — чтобы отслеживать клики.
- Проверить перед отправкой, что email не находится в black list-е.
- Отправить письмо на определенный пул.
Расскажу подробнее о втором пункте:
Микросервис mail-builder занимается подготовкой письма к отправке:
- находит все ссылки в письме;
- для каждой ссылки генерируется уникальный 32-ух символьный uuid;
- заменяет исходную ссылку на новую и сохраняет данные в базу.
Таким образом, все исходные ссылки будут заменены на uuid-ы, а домен поменяется на наш. При GET запросе по этой ссылке мы проксируем оригинальную картинку или редиректим на исходную ссылку. Сохранение происходит в базу данных MySQL, мы сохраняем сгенерированный uuid вместе с оригинальной ссылкой и с некоторой мета информацией (email пользователя, id рассылки и другими данными). Денормализация помогает нам в 1 запрос получить все необходимые данные для сохранения статистики, или старта какой-то триггерной цепочки.
Проблема №1
Генерация uuid-a у нас зависела от timestamp.
Так как рассылки обычно происходят в определенный период времени и запущено много инстансов микросервиса по сборке письма, получалось, что часть uuid-ов была очень схожими.
Решили эту проблему использованием модуля uuid в питоне, где нет зависимости от времени.
Такая неявная вещь уменьшала скорость работы индексов.
Как происходит хранение?
Структура таблицы была следующая:
CREATE TABLE IF NOT EXISTS `Messages` (
`UUID` varchar(32) NOT NULL,
`Message` json NOT NULL,
`Inserted` DATE NOT NULL,
PRIMARY KEY (`UUID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;На момент создания все выглядело логично:
UUID — primary key, а также является кластерным индексом. Когда мы делаем выборку по этому полю, то мы просто выбираем запись, ведь все значения хранятся тут же. Это было обдуманным решением. Подробнее о кластерном индексе.
Все было замечательно, пока таблица не разрослась.
Проблема №2
Если прочитать подробнее про кластерный индекс, можно узнать о таком нюансе:
При добавлении новой строки в таблицу, она дописывается не в конец файла, не в конец плоского списка, а в нужную ветку древовидной структуры, соответствующую ей по сортировке.Таким образом, при росте нагрузки время вставки росло.
Решением было использовать другую структуру таблицы.
CREATE TABLE IF NOT EXISTS `Messages` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`UUID` varchar(32) NOT NULL,
`Message` json NOT NULL,
`Inserted` DATE NOT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `UUID` (`UUID`, `Inserted`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;Так как теперь primary key автоинкрементный, а в mysql хранится кеш последнего места вставки, теперь вставка всегда происходит в конец, т.е Innodb оптимизирован под запись последовательно-возрастающих значений.
Подробности этой оптимизации я находил в исходниках у postgres. В mysql реализована очень похожая оптимизация.
Пришлось, конечно, добавить уникальный ключ, чтобы не было коллизий, но скорость вставки мы увеличили.
С еще большим ростом базы, мы задумались над тем, чтобы удалять старые данные. Использовать DELETE по полю Inserted абсолютно не оптимально — это очень долго, а также место не будет освобождено пока мы не выполним команду optimize table. Кстати, эта операция полностью блокирует таблицу — это нам совсем не подходило.
Поэтому мы решили разбить нашу таблицу на партиции.
1 день — 1 партиция, старые дропаются автоматически, когда приходит время.
Проблема №3
Мы получили возможность удалять старые данные, но мы не получили возможности выбирать из нужной партиции, т.к при select`e мы указываем только uuid, mysql не знает в какой партиции нам это искать и ищет во всех.
Решение родилось из Проблемы №1 — добавить timestamp в сгенерированный uuid. Только в этот раз мы поступили немного по-другому: вставили timestamp в случайное место строки, не в начало и не в конец; перед ним и после добавили символ тире, чтобы его можно было получить регулярным выражением.
С помощью этой оптимизации мы смогли получать дату, когда uuid был сгенерирован и уже сделать select с указанием конкретного значения поля Inserted. Теперь мы читаем данные сразу из нужной нам партиции.
Так же, благодаря таким вещам, как ROW_FORMAT=COMPRESSED и сменой кодировки на latin1, мы сэкономили еще больше места на жестком диске.
Комментарии (14)
amaksr
08.05.2019 22:34При нескольких миллионах инсертов в день возможно стоит рассмотреть создание отдельной таблицы на каждый день, генерировать линки вида UUID-DDMM, и при клике из урла вычислить имя таблицы. Таблицы тогда будут маленькими и быстрыми, их легко будет удалять.
На случай, если нужны какие-то отчеты за период можно создать вьюхи или SP, которые будут все таблицы соединять. Создавать вьюхи мог бы тот же скрипт, которые создает и дропает дневные таблицы.kl09 Автор
09.05.2019 05:27Инсертов не несколько миллионов в день, а несколько сотен миллионов в день.
Ваш подоход неправильный, это получается какой то кривой шардинг. Партиции хорошо работают.
Все события по ссылкам пишутся в очередь, оттуда батчами в clickhouse, вот оттуда отчёты и строятся.amaksr
09.05.2019 17:59Ваш подоход неправильный
Такой подход позволяет инсертить в таблицу, в которой вообще нет индексов, т.е. мгновенно, а потом уже построить индекс. Понятно, что на создание индекса(ов) понадобится время, но это будет в разы быстрее, чем сейчас у вас уходит на инсерты.VolCh
10.05.2019 16:47Я так понимаю, что из нескольких сотен миллионов записей основное чтение будет в этот же день процентах на 10, если не меньше, а на остальных 90+ скорее всего не будет уже никогда.
miksoft
09.05.2019 01:27При добавлении новой строки в таблицу, она дописывается не в конец файла, не в конец плоского списка, а в нужную ветку древовидной структуры, соответствующую ей по сортировке.
Пришлось, конечно, добавить уникальный ключ, чтобы не было коллизий, но скорость вставки мы увеличили.
Но запись нового значения в индекс уникального ключа тоже происходит не в конец, а «в нужную ветку древовидной структуры». За счет чего получилось ускорение?kl09 Автор
09.05.2019 05:32Если primary key является монотонно возрастающим, т.е автоинкрементным, то вставка происходит в конец, за счёт кеша в дереве. Я привёл ссылку на исходник postgres, там об этом упоминается, в mysql очень похожая реализация.
VolCh
09.05.2019 07:24Как минимум меньшие объёмы данных нужно двигать, чтобы вставить запись в индекс.
dss_kalika
Может это нюансы MySQL, но селективность зависящая от похожести значений — звучит как то странно. (если мне не изменяет память, она, всё-таки о количестве уникальных значений, а не похожести значений… тем более уидов)
А можно поподробнее о том, куда в уид вставлялся таймстамп? Возможно, если он вставлялся в начало, то и с сортировкой по уидам (кластерный индекс) и с партициями проблем бы не было.
И, да — со скоростью вставки вы расплатились необходимостью KeyLookup каждого значения… правда, опять же, возможно тут есть нюансы того, что это MySQL.
Вообще весь сырбор начался с фразы про селективность, которая достаточно сомнительна)
… а нормализация, обычно, никак не мешает выбрать те же денормализованные данные в один запрос…
Странная статья )
kl09 Автор
Немного не так выразился.
Индекс в mysql — это Би-дерево, поэтому когда uuid-ы содержали timestamp-ы, то это было не очень эффективное хранение.
Чтение происходит в разы реже, чем вставка. Поэтому основной выигрыш, конечно же был с когда мы стали вставлять в конец, а не искать нужное место для вставки. Ну и Key Lookup — в разы проще, чем проходиться по дереву и искать место вставки.
dss_kalika
ммм… так а в бидерево вставляется на основе сортировки элементов. А сортировка по таймстампу в уиде бы как раз и давала бы вам вставку в конец, как Вы и хотели.
Проблема то была как раз в том, что вы использовали чистые уиды, которые непосортируешь. А вот зачем вы это сделали — большой вопрос (хотя в итоге сами же к этому и пришли).
Выстрелили себе в ногу )
kl09 Автор
К сожалению не с timestamp начиналось — он был в середине него. Но да, если бы он был в начале, то да. Но все равно вставка с помощью AUTO_INCREMENT быстрее.
dss_kalika
Ну ок, вставили бы таймстамп в начало уида и построили бы по нему кластерный индекс (раз уж вы всё равно его вставили).
Разницы в скорости вставки не было бы. Зато не надо было бы новое поле вводить и терять на выборе данных.
ЗЫ: а зачем уникальность в разрезе уида и даты вставки? уиды могут дублироваться?