Конвертация таблицы в InnoDB с помощью pt-online-schema-change

Конвертация таблицы в InnoDB с помощью pt-online-schema-change

На одном нашем старом, но довольно мощном сервере баз данных большая часть таблиц использовала движок MyIsam, потому что так  исторически сложилось. Со временем нагрузка возросла, объемы данных тоже — и сервер начал медленно отвечать на запросы. Основной причиной была высокая нагрузка на диск.  Для снижения нагрузки на диски было решено перевести таблицы на движок InnoDB, увеличив пул в памяти (благо, сервер это позволял). Выигрыш в производительности был так же за счет построчной блокировки таблиц в InnoDB.

Обычно смена движка производится командой:

ALTER TABLE NAMETABLE01 ENGINE=innodb

Это происходит в консоли базы. Для небольших таблиц эта команда срабатывает довольно-таки быстро. А вот с большими таблицами  возникают проблемы. Так как конвертация будет выполняться долго, таблица будет заблокирована, и все операции с ней станут невозможными, что непременно скажется на оказании услуг/сервисов. Для решения этой проблемы нам поможет утилита pt-online-schema-change из комплекта percona-toolkit. Ставится эта утилита из репозитариев, как на шляпах, так и в дебианах:

sam@debian:~:14/10/14-09:37$ sudo aptitude show percona-toolkit
Пакет: percona-toolkit
Новый: да
Состояние: установлен
Автоматически установлен: нет
Версия: 2.2.11
Приоритет: необязательный
Раздел: utils
Сопровождающий: Percona Toolkit Developers <toolkit-dev@percona.com>
Архитектура: all
Размер в распакованном виде: 5 746 k
Зависимости: perl, libdbi-perl (>= 1.13), libdbd-mysql-perl | libdbd-mysql-5.1-perl, libterm-readkey-perl (>= 2.10), libio-socket-ssl-perl
Описание: Advanced MySQL and system command-line tools
 Percona Toolkit is a collection of advanced command-line tools used by Percona (http://www.percona.com/) support staff to perform a variety of MySQL and system tasks that are too difficult
 or complex to perform manually.

 These tools are ideal alternatives to private or "one-off" scripts because they are professionally developed, formally tested, and fully documented. They are also fully self-contained, so
 installation is quick and easy and no libraries are installed.

 Percona Toolkit is developed and supported by Percona.  For more information and other free, open-source software developed by Percona, visit http://www.percona.com/software/.
Сайт: http://www.percona.com/software/percona-toolkit/

Имеется таблица:

(root@localhost) [(none)]>show create table database.NAMETABLE01;
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                    | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| NAMETABLE01 | CREATE TABLE `NAMETABLE01` (
  `WblablaID` int(11) NOT NULL AUTO_INCREMENT,
  `Username` char(11) DEFAULT NULL,
  `MessageID` bigint(20) DEFAULT NULL,
  `DaunTownID` bigint(20) NOT NULL,
  `Speeeed` decimal(10,2) NOT NULL,
  `Perconaaaa` decimal(10,2) NOT NULL,
  `Hmmm` decimal(10,2) DEFAULT '0.00',
  `TM` datetime DEFAULT NULL,
  `Descriptions` varchar(255) DEFAULT NULL,
  `DWblaID` int(11) NOT NULL DEFAULT '1',
  `SblablaID` bigint(20) unsigned DEFAULT '1',
  PRIMARY KEY (`WblablaID`),
  KEY `i_unum` (`Username`)
) ENGINE=MyISAM AUTO_INCREMENT=602846553 DEFAULT CHARSET=cp1251 |
+--------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,00 sec)

(root@localhost) [(none)]>
(root@localhost) [(none)]>select count(*) from database.NAMETABLE01;
+-----------+
| count(*)  |
+-----------+
| 517632699 |
+-----------+
1 row in set (0,00 sec)

50 миллионов записей) не слабо)

Важно! Необходимо, чтобы у конвертируемой таблицы был или первичный (PRIMARY), или уникальный (UNIQUE) ключ, иначе выдаст ошибку, например такую:

Cannot chunk the original table `database`.`NAMETABLE01_NOKEY`: There is no good index and the table is oversized. at /usr/bin/pt-online-schema-change line 5442.

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

pt-online-schema-change -uuser -ppassword -h 127.0.0.1 --alter "ENGINE=InnoDB" D=database,t=NAMETABLE01 --execute --statistics --charset cp1251 --set-vars wait_timeout=10000,innodb_lock_wait_timeout=10,lock_wait_timeout=180

Параметр —alter указывает на проводимую операцию, —execute указывает на необходимость ее конкретного выполнения, для выведения статистики используется параметр —statistics и так далее. Для InnoDB используются некоторые свои параметры, указанные в параметре —set-vars. Имя базы данных и таблицы указывается так: D=<база данных>, t=<таблица>.

Работает это, в целом, просто:

1) создаются триггеры, типа:

(root@localhost) [database]>show triggers\G;
*************************** 1. row ***************************
             Trigger: pt_osc_database_NAMETABLE01_ins
               Event: INSERT
               Table: NAMETABLE01
           Statement: REPLACE INTO `database`.`_NAMETABLE01_new` (...)
              Timing: AFTER
             Created: NULL
            sql_mode: NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@127.0.0.1
character_set_client: cp1251
collation_connection: cp1251_general_ci
  Database Collation: cp1251_general_ci
*************************** 2. row ***************************
             Trigger: pt_osc_database_NAMETABLE01_upd
               Event: UPDATE
               Table: NAMETABLE01
           Statement: REPLACE INTO `database`.`_NAMETABLE01_new` (...)
              Timing: AFTER
             Created: NULL
            sql_mode: NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@127.0.0.1
character_set_client: cp1251
collation_connection: cp1251_general_ci
  Database Collation: cp1251_general_ci
*************************** 3. row ***************************
             Trigger: pt_osc_database_NAMETABLE01_del
               Event: DELETE
               Table: NAMETABLE01
           Statement: DELETE IGNORE FROM `database`.`_NAMETABLE01_new` WHERE ...
              Timing: AFTER
             Created: NULL
            sql_mode: NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION
             Definer: root@127.0.0.1
character_set_client: cp1251
collation_connection: cp1251_general_ci
  Database Collation: cp1

2) создается временная таблица, с именем как «_NAMETABLE01_new»

3) по окончании преобразования таблица переименовывается в исходную, старая таблица удаляется, так же удаляются триггеры.

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

sam@debian:~:14/10/14-09:48$ pt-online-schema-change -uuser -ppassword -h 127.0.0.1 --alter "ENGINE=InnoDB" D=database,t=NAMETABLE01 --execute --statistics --charset cp1251 --set-vars wait_timeout=10000,innodb_lock_wait_timeout=10,lock_wait_timeout=180
No slaves found.  See --recursion-method if host debian has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `database`.`NAMETABLE01`...
Creating new table...
Created new table database._NAMETABLE01_new OK.
Altering new table...
Altered `database`.`_NAMETABLE01_new` OK.
2014-10-14T09:48:33 Creating triggers...
2014-10-14T09:48:33 Created triggers OK.
2014-10-14T09:48:33 Copying approximately 517632699 rows...
Copying `database`.`NAMETABLE01`:   0% 03:05:39 remain
Copying `database`.`NAMETABLE01`:   0% 02:59:26 remain
Copying `database`.`NAMETABLE01`:   0% 02:58:53 remain
Copying `database`.`NAMETABLE01`:   1% 03:00:16 remain
Copying `database`.`NAMETABLE01`:   1% 03:00:28 remain
Copying `database`.`NAMETABLE01`:   1% 03:00:11 remain
Copying `database`.`NAMETABLE01`:   1% 03:00:10 remain
Copying `database`.`NAMETABLE01`:   2% 02:59:54 remain
Copying `database`.`NAMETABLE01`:   2% 02:59:43 remain
Copying `database`.`NAMETABLE01`:   2% 02:59:28 remain
Copying `database`.`NAMETABLE01`:   2% 02:58:45 remain
Copying `database`.`NAMETABLE01`:   3% 02:58:08 remain
Copying `database`.`NAMETABLE01`:   3% 02:57:42 remain
Copying `database`.`NAMETABLE01`:   3% 02:57:10 remain
Copying `database`.`NAMETABLE01`:   4% 02:56:47 remain
Copying `database`.`NAMETABLE01`:   4% 03:44:05 remain
2014-10-14T09:58:43 Dropping triggers...
2014-10-14T09:58:43 Dropped triggers OK.
2014-10-14T09:58:43 Dropping new table...
2014-10-14T09:58:45 Dropped new table OK.
# Event  Count
# ====== =====
# INSERT   913
`database`.`NAMETABLE01` was not altered.
        (in cleanup) 2014-10-14T09:58:43 Error copying rows from `database`.`NAMETABLE01` to `database`.`_NAMETABLE01_new`: Threads_running=58 exceeds its critical threshold 50

2014-10-14T09:58:45 Dropping triggers...
2014-10-14T09:58:45 Dropped triggers OK.
# Event  Count
# ====== =====
# INSERT   913
`database`.`NAMETABLE01` was not altered.

Это поведение настраивается, и за это отвечают следующие параметры:

--critical-load
--max-load

Так же рекомендуется задать «шаг»с помощью параметра:

--chunk-size

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

http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html

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