Моя подборка полезных команд для MySQL.
Узнать размер базы данных
SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" FROM information_schema.tables GROUP BY table_schema ORDER BY Round(Sum(data_length + index_length) / 1024 / 1024, 1) DESC;
Выводим 15 наиболее фрагментированных таблиц
SELECT CONCAT(TABLE_SCHEMA, ":",TABLE_NAME) AS TABLENAME, ROUND(DATA_FREE * 100/ DATA_LENGTH,1) AS FRAG_PERCENTAGE FROM information_schema.tables ORDER BY FRAG_PERCENTAGE DESC LIMIT 15;
где FRAG_PERCENTAGE - соотношение свободного места в таблице к занятному.
Стоит заметить, что этот метод не работает, когда вы используете InnoDB таблицы без включенной опции innodb_file_per_table. Когда эта опция выключена, все таблицы innodb хранятся в одном файле: /var/lib/mysql/ibdata1. В таком случае результат будет одинаков для всех таблиц. Дефрагментация в данном случае тоже поможет, все таблицы и индексы будут упорядочены в этом файле, однако файл ibdata1 всё равно будет занимать столько же места, сколько и до дефрагментации (просто свободное место в нём будет упорядочено и аккуратно размечено:) ).
Чтобы можно было получать адекватные значение по каждой InnoDB таблице, надо выполнить следующее:
- Сделать дамп всех баз
- Включить опцию innodb_file_per_table
- Удалить файл /var/lib/mysql/ibdata1
- Залить обратно дамп
Ниже более интересная команда:
Выводим 15 наиболее фрагментированных таблиц, их размер и движок
SELECT CONCAT(TABLE_SCHEMA, ":",TABLE_NAME) AS TABLENAME, ROUND(DATA_FREE * 100/ DATA_LENGTH,1) AS FRAG_PERCENTAGE, ENGINE , (DATA_LENGTH+INDEX_LENGTH)/(1024*1024) AS "SIZE (MB)" FROM information_schema.tables ORDER BY FRAG_PERCENTAGE DESC LIMIT 15;
Дефрагментируем таблицы
Для этого достаточно выполнить команду:
OPTIMIZE TABLE tablenameгде tablename - название дефрагментированной таблицы. Стоит заметить, что для InnoDB таблиц OPTIMIZE TABLE - всего лишь обертка для ALTER TABLE tablename ENGINE="INNODB", которая перестраивает таблицу и оптимизирует индексы.
