Как при помощи Node.js + MySQL упорядочить 100 Гбайт данных

Эта статья поведает о том, как можно эффективно обрабатывать миллиарды строк, которые занимают сотни гигабайт памяти, при помощи Node.js и MySQL. Вторичная цель статьи — это помочь определиться, подходит ли такое решение для ваших нужд. Код, который будет использоваться в этом примере, можно найти на GitHub.

Почему Node.js и MySQL?

MySQL будет использоваться для хранения информации из Trace — инструмента Node.js для отладки и мониторинга производительности.

Выбор пал на MySQL, потому что на момент принятия решения Postgres не очень хорошо обновлял строки и обновление неизменяемых данных было бы неоправданно сложным. К сожалению, эти решения не соответствуют требованиям ACID, что затрудняет их использование.

Однако при  хорошем индексировании и правильном планировании MySQL, это может быть подходящим решением для поставленной задачи.

MySQL имеет несколько модулей хранения. InnoDB является стандартным, в котором есть большинство функций. Однако следует учитывать, что таблицы InnoDB не изменяемы, что означает, что каждый оператор ALTER TABLE скопирует все данные в новую таблицу. Это ухудшит ситуацию, когда возникнет необходимость в переносе уже существующей базы данных.

Если имеются номинальные значения, каждое из которых имеет множество связанных данных, например, у каждого из пользователей есть множество продуктов, а пользователей довольно много — пожалуй, наиболее оптимальным будет создание отдельных таблиц с именем  <user_id>_<entity_name> для каждой из сущностей. Таким образом можно значительно уменьшить размер конкретной таблицы.

Кроме того, если не разделить пользователей по таблицам, очистка данных пользователя в случае удаления учетной записи будет являться операцией с постоянной временной сложностью алгоритма O(1), когда время выполнения не зависит от размера обрабатываемых данных. Это очень важно, потому что, если нужно удалить большое количество значений из больших таблиц, MySQL может решить использовать неверный индекс или вообще не использовать индексы. Если не создавать отдельных таблиц для каждой сущности, вы не сможете использовать подсказки для DELETE, чтобы указать MySQL, какие именно индексы нужно обрабатывать. Вы также можете захотеть использовать ALTER для таблицы, чтобы удалить данные, но это приведет к копированию каждой строки в новую таблицу, что не является рациональным при использовании одной большой таблицы для всех пользователей.

Создание таблиц для каждого пользователя, на первый взгляд, усложняет задачу, но это может быть единственным решением, когда дело касается удаления пользователей или подобных объектов с огромным количеством связанных данных.

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

Но что если таблицы все еще остались огромными даже после их разделения по пользователями и также нужно удалить устаревшие строки? А данные все еще появляются быстрее, чем происходит их удаление? В этом случае стоит попробовать встроенный в MySQL инструмент для секционирования таблиц. Это удобно, когда нужно разделить данные по постоянным или переменным значениям, например, по времени создания.

Секционирование таблиц с помощью MySQL

С MySQL секционированная таблица будет работать так, как если бы она была несколькими таблицами, но можно использовать тот же привычный интерфейс без изменений в логике приложения. Это также означает, что можно удалять разделы, как если бы происходило удаление таблиц.

Документация хорошая, но довольно многословная (в конце концов, это не простая тема), поэтому просто создадим секционированную таблицу.

			CREATE TABLE IF NOT EXISTS tbl (  
      id INTEGER NOT NULL AUTO_INCREMENT,
      data VARCHAR(255) NOT NULL,
      created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (id, created_at)
    )

PARTITION BY RANGE (TO_DAYS(created_at)) (  
        start        VALUES LESS THAN (0),
        from20170514 VALUES LESS THAN (TO_DAYS('2017-05-15')),
        from20170515 VALUES LESS THAN (TO_DAYS('2017-05-16')),
        from20170516 VALUES LESS THAN (TO_DAYS('2017-05-17')),
        future       VALUES LESS THAN MAXVALUE
    );
		

В коде нет ничего необычного до PARTITION BY RANGE.

В MySQL таблицы можно разделить на RANGE, LIST, COLUMN, HASH и KEY, о которых можно прочитать в документации. Стоит обратить внимание, что ключ секционирования должен быть частью первичного ключа или любых уникальных индексов.

Все, что начинается с <date>, должно быть понятным. Каждый раздел содержит значения, для которых столбец created_at меньше даты следующего дня. Это также означает, что from20120414 содержит все данные, которые старше 2012-04-15, так что этот раздел будет удален при выполнении очистки.

Разделы start и future нуждаются в некотором разъяснении: future содержит данные для дат, которые еще не включены в правила секционирования. Если невозможно выполнить секционирование вовремя, все данные, поступившие 2017-05-17 и позже, будут в этом разделе, и мы ничего не потеряем. Раздел start является неким защитным механизмом. Ожидается, что все строки имеют значение DATETIME created_at, однако нужно подготовиться к возможным ошибкам. Если по какой-то причине в строке в этом поле будет NULL, она окажется в разделеstart. Это укажет нам на ошибки, и, значит, нужно разбираться.

При использовании секционирования MySQL сохранит эти данные на отдельных частях диска, как если бы они были отдельными таблицами, и автоматически упорядочит все данные на основании ключа секционирования.

Однако есть некоторые ограничения при использовании секционирования, которые необходимо принять во внимание:

• кэш запросов не поддерживается;
• внешние ключи не поддерживаются для секционированных таблиц InnoDB;
• секционированные таблицы не поддерживают индексы или поисковые запросы FULLTEXT.

Ограничений гораздо больше, это лишь основные.

Если нужно создать новый раздел, то необходимо реорганизовать существующий и разбить его в соответствии с потребностями:

			ALTER TABLE tbl  
    REORGANIZE PARTITION future INTO (
        from20170517 VALUES LESS THAN (TO_DAYS('2017-05-18')),
        from20170518 VALUES LESS THAN (TO_DAYS('2017-05-19')),
        PARTITION future VALUES LESS THAN MAXVALUE
);
		

Удаление разделов происходит с помощью ALTER TABLE, поскольку выполняется аналогично удалению таблиц:

			ALTER TABLE tbl  
    DROP PARTITION from20170517, from20170518;
		

Как можно увидеть, нужно включать фактические имена и описания разделов в операторах.  Они не могут быть динамически генерированы MySQL, поэтому придется обрабатывать их в логике приложения. Это будет рассмотрено дальше.

Пример секционирования при помощи Node.js и MySQL

Рассмотрим практическое решение. Для примера здесь используется knex — инструмент запросов для JavaScript. Для тех, кто знаком с SQL, понимание кода не составит труда.

Сначала создадим таблицу:

			const dedent = require('dedent')  
const _ = require('lodash')  
const moment = require('moment')

const MAX_DATA_RETENTION = 7  
const PARTITION_NAME_DATE_FORMAT = 'YYYYMMDD'

Table.create = function () {  
  return knex.raw(dedent`
    CREATE TABLE IF NOT EXISTS \`${tableName}\` (
      \`id\` INTEGER NOT NULL AUTO_INCREMENT,
      \`data\` VARCHAR(255) NOT NULL,
      \`created_at\` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (\`id\`, \`created_at\`)
    )
    PARTITION BY RANGE ( TO_DAYS(\`created_at\`)) (
      PARTITION \`start\` VALUES LESS THAN (0),
      ${Table.getPartitionStrings()}
      PARTITION \`future\` VALUES LESS THAN MAXVALUE
    );
  `)
}

Table.getPartitionStrings = function () {  
  const days = _.range(MAX_DATA_RETENTION - 2, -2, -1)
  const partitions = days.map((day) => {
    const tomorrow = moment().subtract(day, 'day').format('YYYY-MM-DD')
    const today = moment().subtract(day + 1, 'day').format(PARTITION_NAME_DATE_FORMAT)
    return `PARTITION \`from${today}\` VALUES LESS THAN (TO_DAYS('${tomorrow}')),`
  })
  return partitions.join('\n')
}
		

Это практически то же выражение, что было выше, но в задаче необходимо динамически создавать имена и описания разделов. Вот почему был создан метод getPartitionStrings.

Первая строка:

			const days = _.range(MAX_DATA_RETENTION - 2, -2, -1)
		

MAX_DATA_RETENTION - 2 = 5 создает последовательность от 5 до -2 (последнее число не входит в последовательность) → [ 5, 4, 3, 2, 1, 0, -1 ], после этого вычитаются  значения из текущего времени и создается раздел today и его предел tomorrow. Порядок очень важен, поскольку MySQL выдаст ошибку, если значения для разделения находятся не в возрастающем порядке.

Пример удаления больших масштабов данных при помощи MySQL и Node.js

Теперь можно шаг за шагом разобраться с удалением данных. Полный код можно посмотреть здесь.

Первый метод, removeExpired, получает список текущих разделов, затем передает его в repartition.

			const _ = require('lodash')

Table.removeExpired = function (dataRetention) {  
  return Table.getPartitions()
    .then((currentPartitions) => Table.repartition(dataRetention, currentPartitions))
}

Table.getPartitions = function () {  
  return knex('information_schema.partitions')
    .select(knex.raw('partition_name as name'), knex.raw('partition_description as description')) // в описании находится день партиции в формате mysql
    .where('table_schema', dbName)
    .andWhere('partition_name', 'not in', [ 'start', 'future' ])
    .then((partitions) => partitions.map((partition) => ({
      name: partition.name,
      description: partition.description === 'MAX_VALUE' ? 'MAX_VALUE' : parseInt(partition.description)
    })))
}

Table.repartition = function (dataRetention, currentPartitions) {  
  const partitionsThatShouldExist = Table.getPartitionsThatShouldExist(dataRetention, currentPartitions)

  const partitionsToBeCreated = _.differenceWith(partitionsThatShouldExist, currentPartitions, (a, b) => a.description === b.description)
  const partitionsToBeDropped = _.differenceWith(currentPartitions, partitionsThatShouldExist, (a, b) => a.description === b.description)

  const statement = dedent
    `${Table.reorganizeFuturePartition(partitionsToBeCreated)}
    ${Table.dropOldPartitions(partitionsToBeDropped)}`

  return knex.raw(statement)
}
		

Сначала выбираются все существующие в настоящее время разделы из таблицы information_schema.partitions, которая управляется MySQL.

Затем создаются все разделы, которые должны существовать для таблицы. Если A — это множество разделов, которые существуют, а B — множество разделов, которые должны существовать, тогда:

partitionsToBeCreated = B \ A

partitionsToBeDropped = A \ B

getPartitionsThatShouldExist creates set B.

			Table.getPartitionsThatShouldExist = function (dataRetention, currentPartitions) {  
  const days = _.range(dataRetention - 2, -2, -1)
  const oldestPartition = Math.min(...currentPartitions.map((partition) => partition.description))
  return days.map((day) => {
    const tomorrow = moment().subtract(day, 'day')
    const today = moment().subtract(day + 1, 'day')
    if (Table.getMysqlDay(today) < oldestPartition) {
      return null
    }

    return {
      name: `from${today.format(PARTITION_NAME_DATE_FORMAT)}`,
      description: Table.getMysqlDay(tomorrow)
    }
  }).filter((partition) => !!partition)
}

Table.getMysqlDay = function (momentDate) {  
  return momentDate.diff(moment([ 0, 0, 1 ]), 'days') // даты в mysql исчисляются с с 00:00:00 01/01/0000
}
		

Создание объектов разделов очень похоже на создание выражения CREATE TABLE ... PARTITION BY RANGE. Также важно проверить, является ли раздел, который будет создан, старше текущего самого старого раздела, ведь со временем может понадобиться изменить dataRetention.

Пример одного из возможных сценариев:

Пользователи могут хранить данные в течение 7 дней, но также существует возможность расширить этот диапазон до 10 дней. Сначала у пользователя есть разделы, которые охватывают дни в следующем порядке: [ start, -7, -6, -5, -4, -3, -2, -1, future ]. Вскоре пользователь решает увеличить срок хранения данных. Отсутствующие разделы в этом случае: [ -10, -9, -8, 0 ].При очистке текущий скрипт попытается реорганизовать раздел future для отсутствующих разделов, добавляя их после текущих.Создание дополнительных разделов, которые старше 7 дней, не имеет смысла, потому что эти данные будут заброшены так далеко, что это создаст список разделов, который выглядит так: [ start, -7, -6, -5, -4, -3, -2, -1, -10, -9, -8, 0, future ]. Непоследовательно возрастающий список приведет к ошибке MySQL, и вся операция завершится неудачей.

Функция MySQL TO_DAYS(date) вычисляет количество дней, прошедших с 1 января 2000 года, поэтому мы повторяем это в JavaScript.

			Table.getMysqlDay = function (momentDate) {  
  return momentDate.diff(moment([ 0, 0, 1 ]), 'days')
}
		

Теперь, когда созданы разделы, которые нужно удалить, и разделы, которые должны быть созданы, нужно создать новый раздел для нового дня.

			Table.reorganizeFuturePartition = function (partitionsToBeCreated) {  
  if (!partitionsToBeCreated.length) return '' // Создание должно произойти только при первом прогоне, после чего функция 23 раза в день будет лишь выполнять проверку
  const partitionsString = partitionsToBeCreated.map((partitionDescriptor) => {
    return `PARTITION \`${partitionDescriptor.name}\` VALUES LESS THAN (${partitionDescriptor.description}),`
  }).join('\n')

  return dedent`
    ALTER TABLE \`${tableName}\`
      REORGANIZE PARTITION future INTO (
        ${partitionsString}
        PARTITION \`future\` VALUES LESS THAN MAXVALUE
      );`
}
		

Здесь просто выполняется код для создания новых разделов.

Запуская этот скрипт ежечасно, можно убедиться в том, что все идет по плану. Это также поможет выполнять очистку должным образом, по крайней мере, один раз в день.

Итак, первое, что нужно проверить — это создать любой раздел. Это должно произойти только при первом прогоне, после чего функция 23 раза в день будет лишь выполнять проверку.

Также необходимо удалить устаревшие разделы.

			Table.dropOldPartitions = function (partitionsToBeDropped) {  
  if (!partitionsToBeDropped.length) return ''
  let statement = `ALTER TABLE \`${tableName}\`\nDROP PARTITION\n`
  statement += partitionsToBeDropped.map((partition) => {
    return partition.name
  }).join(',\n')
  return statement + ';'
}
		

Этот код создает тот же самый ALTER TABLE ... DROP PARTITION, который был показан выше.

И, наконец, все готово к реорганизации.

			const statement = dedent
    `${Table.reorganizeFuturePartition(partitionsToBeCreated)}
    ${Table.dropOldPartitions(partitionsToBeDropped)}`

  return knex.raw(statement)
		

Заключение

Как можно видеть, вопреки распространенному мнению, совместимые с ACID решения СУБД, такие как MySQL, могут использоваться, когда обрабатываются большие объемы данных, поэтому необязательно отказываться от функций транзакционных баз данных.

Тем не менее, разделение таблиц имеет довольно много ограничений, а это означает, что невозможно будет использовать все возможности, которые InnoDB предоставляет для обеспечения гарантированного сохранения данных. Также может потребоваться обработать в логике приложения то, что без секционирования было бы реализовано средствами СУБД. Например, ограничения внешнего ключа или полнотекстовый поиск.