Речь пойдет об оптимизации в 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, мы сэкономили еще больше места на жестком диске.