08.05.2019 13:03
kl09 14 3100 Источник
Речь пойдет об оптимизации в MySQL базе данных.

Это случилось, когда мы делали систему для email рассылок. Наша система должна была высылать десятки млн. писем в день. Отправка письма — задача не из простых, хотя выглядит все довольно примитивно:

  1. Собрать письмо из html креатива, подставить персонализированные данные.
  2. Добавить пиксель просмотра письма, заменить все ссылки в письме на свои — чтобы отслеживать клики.
  3. Проверить перед отправкой, что email не находится в black list-е.
  4. Отправить письмо на определенный пул.

Расскажу подробнее о втором пункте:
Микросервис mail-builder занимается подготовкой письма к отправке:

  • находит все ссылки в письме;
  • для каждой ссылки генерируется уникальный 32-ух символьный uuid;
  • заменяет исходную ссылку на новую и сохраняет данные в базу.

Таким образом, все исходные ссылки будут заменены на uuid-ы, а домен поменяется на наш. При GET запросе по этой ссылке мы проксируем оригинальную картинку или редиректим на исходную ссылку. Сохранение происходит в базу данных MySQL, мы сохраняем сгенерированный uuid вместе с оригинальной ссылкой и с некоторой мета информацией (email пользователя, id рассылки и другими данными). Денормализация помогает нам в 1 запрос получить все необходимые данные для сохранения статистики, или старта какой-то триггерной цепочки.

Проблема №1


Генерация uuid-a у нас зависела от timestamp.

Так как рассылки обычно происходят в определенный период времени и запущено много инстансов микросервиса по сборке письма, получалось, что часть uuid-ов была очень схожими. Это давало низкую селективность. UPD: т.к данные получались схожие, работа с би-деревом была не очень эффективная.

Решили эту проблему использованием модуля 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)


  1. dss_kalika
    08.05.2019 16:14
    #20129496
    -2

    Может это нюансы MySQL, но селективность зависящая от похожести значений — звучит как то странно. (если мне не изменяет память, она, всё-таки о количестве уникальных значений, а не похожести значений… тем более уидов)
    А можно поподробнее о том, куда в уид вставлялся таймстамп? Возможно, если он вставлялся в начало, то и с сортировкой по уидам (кластерный индекс) и с партициями проблем бы не было.

    И, да — со скоростью вставки вы расплатились необходимостью KeyLookup каждого значения… правда, опять же, возможно тут есть нюансы того, что это MySQL.

    Вообще весь сырбор начался с фразы про селективность, которая достаточно сомнительна)
    … а нормализация, обычно, никак не мешает выбрать те же денормализованные данные в один запрос…

    Странная статья )


    1. kl09 Автор
      08.05.2019 16:41
      #20129684

      Немного не так выразился.
      Индекс в mysql — это Би-дерево, поэтому когда uuid-ы содержали timestamp-ы, то это было не очень эффективное хранение.
      Чтение происходит в разы реже, чем вставка. Поэтому основной выигрыш, конечно же был с когда мы стали вставлять в конец, а не искать нужное место для вставки. Ну и Key Lookup — в разы проще, чем проходиться по дереву и искать место вставки.


      1. dss_kalika
        08.05.2019 16:45
        #20129716

        ммм… так а в бидерево вставляется на основе сортировки элементов. А сортировка по таймстампу в уиде бы как раз и давала бы вам вставку в конец, как Вы и хотели.

        Проблема то была как раз в том, что вы использовали чистые уиды, которые непосортируешь. А вот зачем вы это сделали — большой вопрос (хотя в итоге сами же к этому и пришли).

        Выстрелили себе в ногу )


        1. kl09 Автор
          08.05.2019 16:57
          #20129818

          К сожалению не с timestamp начиналось — он был в середине него. Но да, если бы он был в начале, то да. Но все равно вставка с помощью AUTO_INCREMENT быстрее.


          1. dss_kalika
            08.05.2019 17:00
            #20129840

            Ну ок, вставили бы таймстамп в начало уида и построили бы по нему кластерный индекс (раз уж вы всё равно его вставили).
            Разницы в скорости вставки не было бы. Зато не надо было бы новое поле вводить и терять на выборе данных.

            ЗЫ: а зачем уникальность в разрезе уида и даты вставки? уиды могут дублироваться?


  1. amaksr
    08.05.2019 22:34
    #20131216

    При нескольких миллионах инсертов в день возможно стоит рассмотреть создание отдельной таблицы на каждый день, генерировать линки вида UUID-DDMM, и при клике из урла вычислить имя таблицы. Таблицы тогда будут маленькими и быстрыми, их легко будет удалять.
    На случай, если нужны какие-то отчеты за период можно создать вьюхи или SP, которые будут все таблицы соединять. Создавать вьюхи мог бы тот же скрипт, которые создает и дропает дневные таблицы.


    1. kl09 Автор
      09.05.2019 05:27
      #20132032

      Инсертов не несколько миллионов в день, а несколько сотен миллионов в день.
      Ваш подоход неправильный, это получается какой то кривой шардинг. Партиции хорошо работают.
      Все события по ссылкам пишутся в очередь, оттуда батчами в clickhouse, вот оттуда отчёты и строятся.


      1. amaksr
        09.05.2019 17:59
        #20133684

        Ваш подоход неправильный
        Такой подход позволяет инсертить в таблицу, в которой вообще нет индексов, т.е. мгновенно, а потом уже построить индекс. Понятно, что на создание индекса(ов) понадобится время, но это будет в разы быстрее, чем сейчас у вас уходит на инсерты.


        1. VolCh
          10.05.2019 16:47
          #20136356

          Я так понимаю, что из нескольких сотен миллионов записей основное чтение будет в этот же день процентах на 10, если не меньше, а на остальных 90+ скорее всего не будет уже никогда.


  1. miksoft
    09.05.2019 01:23
    #20131750

    `UUID` varchar(32)
    А почему не BINARY(16)?
    Дало бы экономию 17 байт на запись.


    1. kl09 Автор
      09.05.2019 05:40
      #20132046

      А вместо json ещё можно использовать blob, но на практике это совершенно неудобно.


  1. miksoft
    09.05.2019 01:27
    #20131766

    При добавлении новой строки в таблицу, она дописывается не в конец файла, не в конец плоского списка, а в нужную ветку древовидной структуры, соответствующую ей по сортировке.
    Пришлось, конечно, добавить уникальный ключ, чтобы не было коллизий, но скорость вставки мы увеличили.
    Но запись нового значения в индекс уникального ключа тоже происходит не в конец, а «в нужную ветку древовидной структуры». За счет чего получилось ускорение?


    1. kl09 Автор
      09.05.2019 05:32
      #20132036

      Если primary key является монотонно возрастающим, т.е автоинкрементным, то вставка происходит в конец, за счёт кеша в дереве. Я привёл ссылку на исходник postgres, там об этом упоминается, в mysql очень похожая реализация.


    1. VolCh
      09.05.2019 07:24
      #20132164

      Как минимум меньшие объёмы данных нужно двигать, чтобы вставить запись в индекс.