Конвертация таблицы в 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
Вообще, данной утилитой можно выполнять многие другие трудоемкие задачи.