Оптимизация базы данных MySQL при использовании InnoDB

[dalex]

После выхода сборки UTM5x015 с реализованной в ней функцией оптимизации БД начал копать что же она там оптимизирует и выкопал что при использовании типа таблиц InnoDB она не делает OPTIMIZE TABLE так как в данное время эта функция не работает для типа таблиц InnoDB. Пришлось залезть в доки по MySQL и выяснилось, что дефрагментацию таблиц InnoDB можно производить двумя способами - первый - бекап с помощью mysqldump и восстановление ее из дампа, а второй - цитирую "Есть еще один способ произвести дефрагментацию - преобразовать таблицу при помощи команды ALTER в тип MyISAM, а затем обратно в тип InnoDB. Обратите внимание на то, что таблица типа MyISAM должна помещаться в один файл в вашей операционной системе".
Вторым способом я и воспользовался.
Применив
ALTER TABLE discount_transactions_iptraffic_all TYPE = MyISAM;
к таблице discount_transactions_iptraffic_all на тестовой машине, размер которой составлял 186.2 мб, я получил таблицу MyISAM размером в 57 мб. Сделав обратную операцию
ALTER TABLE discount_transactions_iptraffic_all TYPE = InnoDB;
я получил ту же таблицу размером в 96.2 мб. Итого выигрыш составил 186.2-96.2=90 мб, в общем почти в 2 раза.

Оригинал раздела в документации
Покопавшись еще в доке узнал что InnoDB позволяет MySQL в качестве файла данных использовать раздел реального диска.

А еще для InnoDB есть InnoDB Monitor. Льет в лог mysqld следующие данные:
* по блокировкам, ожидающим транзакций;
* по семафорам, ожидающим потоков;
* по файлам, ожидающим ответа на запрос ввода/вывода;
* статистику буферного пула;
* по активности буферов удаления и вставок в основном потоке InnoDB.

[Victor]

Покопавшись еще в доке узнал что InnoDB позволяет MySQL в качестве файла данных использовать раздел реального диска.

Этот способ называют уже неактуальным. Т.к. по бенчаркам современная файловая система vs отдельный раздел не дает прироста в производительности. И дается объяснение этому факту. Работа с разделом напрямую нужна была, когда старые файловые системы не могли обеспечить должой производительности. Новые fs достаточно быстры для хранения данных мускуля.

[Разумовский ДВ]

для чистоты эксперимента проделал следующие манипуляции с тремя проблемными таблицами: в новую базу сделал дамп таблиц: create table test_db.[новое имя] select * from UTM5.[имя таблицы];, затем сделал дампы оригинальных таблиц mysqldump -u root -e UTM5 имя_таблицы, сконвертировал тестовые образы таблиц из MyISAM в InnoDB: alter table test_db.имя_таблицы type = InnoDB; и сделал дампы полученных таблиц: mysqldump -u root -e test_db имя_таблицы. Сравнение показало полное соответствие размеров фактических и тестовых таблиц: 329М, 348М и 319М соответственно.
Так что, похоже есть зависимость от способа получения дампа - режим extended syntax (-e) производит упорядочение записей, имхо.

[dalex]

Так что, похоже есть зависимость от способа получения дампа - режим extended syntax (-e) производит упорядочение записей, имхо.

дамп вне зависимости от режима производит упорядочивание. Им и пользуются для этого в том числе. Надо разобраться что быстрее по времени сделать дамп и залить его в базу обратно или сделать через alter table двойную конвертацию в myisam И обратно

[Разумовский ДВ]

ок!
по логике вещей при конвертации БД остается работоспособной. при заливке дампа будет перерыв в накоплении трафика.
у меня одна таблица конвертировалась приблизительно 2.5 минуты, перезаливку базы проводил очень давно, но время составляло тогда порядка десятка минут. так что сравнимо должно быть по затратам.
кстати, как посмотреть размер конкретной таблицы ИнноДБ в составе базы?

[snoomy]

Оператор ALTER TABLE во время работы создает временную копию исходной таблицы. Требуемое изменение выполняется на копии, затем исходная таблица удаляется, а новая переименовывается. Так делается для того, чтобы в новую таблицу автоматически попадали все обновления кроме неудавшихся. Во время выполнения ALTER TABLE исходная таблица доступна для чтения другими клиентами. Операции обновления и записи в этой таблице приостанавливаются, пока не будет готова новая таблица.

а разве при ALTER не будет перерыва в накоплении трафика???

[dalex]

а разве при ALTER не будет перерыва в накоплении трафика???

видимо да к сожалению

Оператор ALTER TABLE во время работы создает временную копию исходной таблицы. Требуемое изменение выполняется на копии, затем исходная таблица удаляется, а новая переименовывается. Так делается для того, чтобы в новую таблицу автоматически попадали все обновления кроме неудавшихся. Во время выполнения ALTER TABLE исходная таблица доступна для чтения другими клиентами. Операции обновления и записи в этой таблице приостанавливаются, пока не будет готова новая таблица.