Использование утилиты pt-online-schema-change для OPTIMIZE TABLE

Feb 1, 2016 17:28 · 487 words · 3 minute read percona percona-toolkit debian

Ранее я уже писал о дефрагментации таблиц InnoDB и упоминал, что при выполнении запрос OPTIMIZE TABLE tbl_name будет блокировать таблицу. Как избежать блокирования таблицы и выполнить запрос — давайте разберемся!

Для выполнения OPTIMIZE TABLE tbl_name будем использовать утилиту pt-online-schema-change, из набора percona-toolkit. Подробнее об установке percona-toolkit в Debian Wheezy можно почитать здесь.

Примечание. На официальном сайте пишут: pt-online-schema-change — ALTER tables without locking them. Отлично, нам это и нужно!

Перед использованием утилиты необходимо:

  • внимательно прочесть документацию;
  • ознакомиться с известными проблемами и ограничениями при использовании;
  • протестировать утилиту на тестовом сервере (не продакшн);
  • сделать резервную копию базы данных продакшн-сервера и проверить ее.

Синтаксис использования следующий:

pt-online-schema-change [OPTIONS] DSN

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

Итак, требуется выполнить OPTIMIZE TABLE на таблице, в которой хранятся сессии пользователей (session_new).

Просмотрим физический размер таблицы перед началом работ:

ls -lah | grep session_new
-rw-rw---- 1 mysql mysql 8.5K Jun  3  2013 session_new.frm
-rw-rw---- 1 mysql mysql 8.8G Dec 24 18:11 session_new.ibd

Из командной строки запускаем:

pt-online-schema-change --critical-load Threads_running=150 --alter "ENGINE=InnoDB" D=work-utf,t=session_new,u=<ПОЛЬЗОВАТЕЛЬ>,p=<ПАРОЛЬ> --execute

Используемые параметры:

  • --critical-load — значение, при котором прерывается выполнение запроса. Берется из SHOW GLOBAL STATUS;
  • --alter — запрос, который необходимо выполнить без ключевых слов ALTER TABLE;
  • D, или --database — база данных;
  • t — таблица базы данных;
  • u, или --user — пользователь базы данных;
  • p, или --password — пароль пользователя базы данных;
  • --execute — подтверждение выполнения операции (говорит о том, что вы прочитали документацию и понимаете, что делаете).

В нашем примере значение параметра --critical-load Threads_running установлено в 150, вместо 50 по умолчанию. Параметр был изменен после того, как при первом запуске команды была получена ошибка:

`work-utf`.`session_new` was not altered.
	(in cleanup) 2015-12-24T18:50:47 Error copying rows from `work-utf`.`session_new` to `work-utf`.`_session_new_new`: Threads_running=55 exceeds its critical threshold 50

Во время выполнения команды в консоли можно увидеть примерно следующее:

No slaves found.  See --recursion-method if host db1 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 `work-utf`.`session_new`...
Creating new table...
Created new table work-utf._session_new_new OK.
Altering new table...
Altered `work-utf`.`_session_new_new` OK.
2015-12-24T19:07:20 Creating triggers...
2015-12-24T19:07:20 Created triggers OK.
2015-12-24T19:07:20 Copying approximately 12224345 rows...
Copying `work-utf`.`session_new`:   0% 02:14:03 remain
Copying `work-utf`.`session_new`:   0% 01:41:11 remain
Copying `work-utf`.`session_new`:   1% 01:38:07 remain
Copying `work-utf`.`session_new`:   2% 01:36:12 remain
...
Copying `work-utf`.`session_new`:  98% 01:13 remain
Copying `work-utf`.`session_new`:  99% 00:23 remain
Copying `work-utf`.`session_new`:  99% 00:03 remain
2015-12-24T20:45:28 Copied rows OK.
2015-12-24T20:45:28 Swapping tables...
2015-12-24T20:45:28 Swapped original and new tables OK.
2015-12-24T20:45:28 Dropping old table...
2015-12-24T20:45:31 Dropped old table `work-utf`.`_session_new_old` OK.
2015-12-24T20:45:31 Dropping triggers...
2015-12-24T20:45:31 Dropped triggers OK.
Successfully altered `work-utf`.`session_new`.

Просмотрим физический размер таблицы после выполнения OPTIMIZE TABLE:

ls -lah | grep session_new
-rw-rw---- 1 mysql mysql 8.5K Dec 24 19:07 session_new.frm
-rw-rw---- 1 mysql mysql 6.6G Dec 25 20:51 session_new.ibd

В данном примере физический размер таблицы после выполнения OPTIMIZE TABLE уменьшился на 2,2 ГБ.

tweet Share