Таблица с 80 миллионами записей и добавлением индекса занимает более 18 часов (или навсегда)! Что теперь?

Короткий рассказ о том, что произошло. Я работаю с 71 миллионом записей (не так много по сравнению с миллиардами записей, обработанных другими). В другом потоке кто-то предположил, что текущая настройка моего кластера не подходит для моей потребности. Моя структура таблицы:

CREATE TABLE `IPAddresses` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `ipaddress` bigint(20) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM;

И я добавил 71 миллион записей, а затем сделал:

ALTER TABLE IPAddresses ADD INDEX(ipaddress);

Это было 14 часов, и операция все еще не завершена. После Googling я обнаружил, что для решения этой проблемы существует хорошо известный подход - Разделение. Я понимаю, что мне нужно разбить мою таблицу сейчас на основе ipaddress, но могу ли я сделать это без воссоздания всей таблицы? Я имею в виду, через выражение ALTER? Если да, было одно требование, говорящее, что столбец, который должен быть секционирован, должен быть первичным ключом. Я буду использовать id этого ipaddress при построении другой таблицы, поэтому ipaddress не является моим основным ключом. Как мне разделить мою таблицу с учетом этого сценария?

Ответ 1

Ок получается, что эта проблема была не просто просто создала таблицу, указала ее и забыла проблему:) Вот что я сделал на случай, если кто-то сталкивается с той же проблемой (я использовал пример IP-адреса, но он работает и для других типов данных):

Проблема: у вашей таблицы есть миллионы записей, и вам нужно быстро добавить индекс

Usecase: Рассмотрите возможность хранения миллионов IP-адресов в справочной таблице. Добавление IP-адресов не должно быть большой проблемой, но создание индекса на них занимает более 14 часов.

Решение. Разделите свою таблицу, используя MySQL Partitionin g strategy

Случай №1: Когда требуемая таблица еще не создана

CREATE TABLE IPADDRESSES(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ipaddress BIGINT UNSIGNED,
  PRIMARY KEY(id, ipaddress)
) ENGINE=MYISAM
PARTITION BY HASH(ipaddress)
PARTITIONS 20;

Случай №2: Когда требуемая таблица уже создана. Кажется, есть способ использовать ALTER TABLE для этого, но я еще не нашел правильного решения для этого. Вместо этого существует немного неэффективное решение:

CREATE TABLE IPADDRESSES_TEMP(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ipaddress BIGINT UNSIGNED,
  PRIMARY KEY(id)
) ENGINE=MYISAM;

Вставьте IP-адреса в эту таблицу. А затем создайте фактическую таблицу с разделами:

CREATE TABLE IPADDRESSES(
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ipaddress BIGINT UNSIGNED,
  PRIMARY KEY(id, ipaddress)
) ENGINE=MYISAM
PARTITION BY HASH(ipaddress)
PARTITIONS 20;

И наконец,

INSERT INTO IPADDRESSES(ipaddress) SELECT ipaddress FROM IPADDRESSES_TEMP;
DROP TABLE IPADDRESSES_TEMP;
ALTER TABLE IPADDRESSES ADD INDEX(ipaddress)

И там вы идете... индексирование на новую таблицу заняло у меня около 2 часов на 3,2 ГГц машине с 1 ГБ ОЗУ:) Надеюсь, это поможет.

Ответ 2

Создание индексов с MySQL выполняется медленно, но не так медленно. С 71 миллионом записей, это займет пару минут, а не 14 часов. Возможные проблемы:

вы не настроили размер буфера сортировки и другие параметры конфигурации.

смотрите здесь: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_myisam_sort_buffer_size

Если вы попытаетесь создать индекс 1 ГБ с буфером сортировки 8 МБ, он будет принимать много проходов. Но если буфер больше кэша вашего процессора, он будет медленнее. Поэтому вам нужно проверить и посмотреть, что лучше всего работает.

у кого-то есть блокировка в таблице
ваша система ввода-вывода сосет
ваш сервер заменяет
и т.д.

как обычно, проверьте iostat, vmstat, журналы и т.д. Выпустите LOCK TABLE в своей таблице, чтобы проверить, есть ли у кого-то блокировка.

FYI на моем 64-битном рабочем столе, создающем индекс на 10M случайных BIGINT, занимает 17 секунд...

Ответ 3

У меня возникла проблема, когда я хотел ускорить мой запрос, добавив индекс. В таблице было всего около 300 000 записей, но также слишком долго. Когда я проверил процессы сервера mysql, оказалось, что запрос, который я пытался оптимизировать, все еще работал в фоновом режиме. 4 раза! После того, как я убил эти запросы, индексирование было выполнено в одно мгновение. Возможно, та же проблема относится и к вашей ситуации.

Ответ 4

Вы используете MyISAM, который скоро устареет. Альтернативой будет InnoDB.

"InnoDB - это безопасный для транзакций (ACID) механизм хранения для MySQL, который имеет возможности фиксации, отката и восстановления после сбоя для защиты пользовательских данных. Блокировка на уровне строк InnoDB (без повышения до более крупных блокировок) и Oracle- стильные последовательные неблокирующие чтения увеличивают многопользовательский concurrency и производительность. InnoDB хранит пользовательские данные в кластерных индексах для уменьшения ввода-вывода для общих запросов на основе первичных ключей. Для поддержания целостности данных InnoDB также поддерживает ограничения ссылочной целостности FOREIGN KEY. могут свободно смешивать таблицы InnoDB с таблицами других движков хранения MySQL, даже в пределах одного и того же оператора." \

http://dev.mysql.com/doc/refman/5.0/en/innodb.html

В соответствии с:

http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html

вы должны иметь возможность переключаться между разными двигателями, используя простую команду alter, которая дает вам некоторую гибкость. В нем также указано, что каждая таблица в вашей БД может быть настроена независимо.

Ответ 5

В вашей таблице. вы уже ввели 71 миллиард записей. теперь, если вы хотите создавать разделы в столбце первичного ключа таблицы, вы можете использовать опцию alter table. Пример приведен для вашей справки.

CREATE TABLE t1 (
    id INT,
    year_col INT
);

ALTER TABLE t1
    PARTITION BY HASH(id)
    PARTITIONS 8;