MySQL производительность добавления столбца в большую таблицу

У меня есть MySQL 5.5.37 с InnoDB, установленным локально с apt-get на Ubuntu 13.10. Моя машина-i7-3770 + 32Gb memory + SSD жесткий диск на моем рабочем столе. Для таблицы "mytable", которая содержит всего 1,5 миллиона записей, следующий запрос DDL занимает более 20 минут (!): 

ALTER TABLE mytable ADD some_column CHAR(1) NOT NULL DEFAULT 'N';
Есть ли способ улучшить его? Я проверил 
show processlist;
И это показывало, что он по какой-то причине копирует мою таблицу. Это крайне неудобно. Есть ли способ отключить эту копию? Есть ли другие способы повышения производительности добавления столбца в большую таблицу?

Кроме того, моя БД относительно мала с размером дампа всего 1,3 Гб. Поэтому он должен (теоретически) поместиться на 100% в памяти.

Есть ли настройки, которые могут помочь? Изменит ли что-нибудь для меня миграция в Прекону?

Добавить: у меня есть 

innodb_buffer_pool_size = 134217728
4 8
mysql performance ddl database-performance

4 ответа:

Существуют ли другие способы повысить производительность добавления столбца в большую таблицу?

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

Длинный ответ: ваша настоящая проблема-это не производительность, а время блокировки. Не имеет значения, если он медленный, важно только то, что другие клиенты не могут выполнять запросы до тех пор, пока ваша таблица ALTER все кончено. В этом случае есть несколько вариантов:

  1. Вы можете использоватьpt-online-schema-change из Percona toolkit. Сначала сделайте резервную копию данных! Это самое простое решение, но может сработать не во всех случаях.

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

  3. Если вам легко создавать реплики, например, если вы размещаетесь в Amazon RDS, вы можете создать мастер-мастер-реплику, запустить там alter table, позволить ей вернуться в синхронизацию и переключить экземпляры после завершения.

7

Как насчет онлайн DDL?

Http://www.tocker.ca/2013/11/05/a-closer-look-at-online-ddl-in-mysql-5-6.html

Может быть, вы бы использовали TokuDB вместо:

Http://www.tokutek.com/products/tokudb-for-mysql/

2

MariaDb 10.3, MySQL 8.0 и, вероятно, другие варианты MySQL, которые будут следовать, имеют функцию "мгновенного добавления столбца", при которой большинство столбцов (есть несколько ограничений, см. docs) могут быть добавлены мгновенно без перестроения таблицы.

2

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

Данные таблицы не хранятся в памяти. Индексы могут находиться в памяти.

1,5 миллиона записей-это не так много строк, и 20 минут кажутся довольно длинными, но, возможно, ваши строки большие и у вас много индексов.

Во время копирования таблицы вы все еще можете выбирать строки из таблицы. Однако, если вы попытаетесь сделать какие-либо обновления, они будут заблокированы до завершения ALTER.

0