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