Как сжать базу mysql

Сжатие, дефрагментация и оптимизация базы данных MariaDB/MySQL

В этой статье мы будем исследовать некоторые методы сжатия таблицы / базы данных и дефрагментации в MySQL / MariaDB, что поможет вам сэкономить место на диске , база данных расположена на.

Базы данных крупных проектов со временем безмерно разрастаются, и всегда возникает вопрос, что с ними делать. Есть несколько способов решить проблему. Вы можете уменьшить объем данных в базе данных, удалив старую информацию, разделив базу данных на более мелкие, увеличив размер диска на сервере или сжав / сжав таблицы.

Еще один важный аспект функционирования базы данных — необходимость время от времени дефрагментировать таблицы и базы данных для повышения их производительности.

Сжатие и оптимизация таблиц InnoDB

Файлы ibdata1 и ib_log

Большинство проектов с таблицами InnoDB имеют проблемы с большими файлами ibdata1 и ib_log. В большинстве случаев это связано с неправильной конфигурацией MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого само не используется. Я предпочитаю хранить данные таблицы в отдельных файлах ibd*. Для этого добавьте в my.cnf следующую строку:

Если ваш сервер настроен и у вас есть продуктивные базы данных с таблицами InnoDB, сделайте следующее:

1. Сделайте резервную копию всех баз данных на вашем сервере (кроме mysql и performance_schema). Вы можете получить дамп базы данных с помощью этой команды:
   # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
2. После создания резервной копии базы данных остановите сервер mysql/mariadb;
3. Измените настройки в my.cfg;
4. Удалите файлы ibdata1 и ib_log;
5. Запустите демон mysql/mariadb;
6. Восстановить все базы из резервной копии:
   # mysql -u [username] –p[password] [database_name] < [dump_file.sql]

После этого все таблицы InnoDB будут храниться в отдельных файлах, и ibdata1 перестанет экспоненциально расти.

Сжатие таблиц InnoDB

Вы можете сжимать таблицы с текстовыми данными / данными BLOB и экономить довольно много места на диске.

У меня есть база данных innodb_test, содержащая таблицы, которые потенциально могут быть сжаты, и поэтому я могу освободить место на диске. Прежде чем что-либо делать, я рекомендую сделать резервную копию всех баз данных. Подключитесь к серверу mysql:

Выберите нужную базу данных в консоли mysql:

Чтобы отобразить список таблиц и их размеры, используйте следующий запрос:

Где innodb_test — имя вашей базы данных.

Некоторые таблицы могут быть сжаты. Возьмем для примера таблицу b_crm_event_relations. Запустите этот запрос:

После его запуска вы можете увидеть, что размер таблицы уменьшился с 26 МБ до 11 МБ из-за сжатия.

Сжимая таблицы, вы можете сэкономить много дискового пространства на вашем хосте. Однако при работе со сжатыми таблицами нагрузка на процессор возрастает. Используйте сжатие для таблиц db, если у вас нет проблем с ресурсами процессора, но есть проблема с дисковым пространством.

Сжатие таблиц MyISAM в MySQL / MariDB

Для сжатия таблиц Myisam используйте специальный запрос в консоли сервера вместо консоли mysql. Чтобы сжать таблицу, запустите следующее:

Где /var/lib/mysql/test/modx_session — это путь к вашей таблице. К сожалению, у меня не было большой таблицы и пришлось сжимать маленькие, но результат все равно можно было увидеть (файл был сжат с 25 МБ до 18 МБ):

Compressing /var/lib/mysql/test/modx_session.MYD: (4933 records)
— Calculating statistics
— Compressing file
29.84%
Remember to run myisamchk -rq on compressed tables

Я использовал в команде ключ -b. Когда вы добавляете его, таблица создается перед сжатием и помечается меткой OLD:

Оптимизация таблиц и баз данных в MySQL и MariaDB

Для оптимизации таблиц и баз данных рекомендуется их дефрагментировать. Убедитесь, что в базе данных есть таблицы, требующие дефрагментации.

Откройте консоль MySQL, выберите базу данных и выполните этот запрос:

Таким образом, вы отобразите все таблицы с не менее 50 МБ неиспользуемого пространства:

data_length_mb — общий размер стола

data_free_mb — неиспользуемое место в столе

Это таблицы, которые мы можем дефрагментировать. Проверьте, сколько места они занимают на диске:

Чтобы оптимизировать эти таблицы, выполните следующую команду в консоли mysql:

После успешной дефрагментации вы увидите следующий результат:

Как видите, data_free_mb теперь равно 0, а размер таблицы значительно уменьшился (в 3-4 раза).

Вы также можете запустить дефрагментацию, используя mysqlcheck в консоли сервера:

Где innodb_test ваша база данных и b_workflow_file название таблицы.

Чтобы оптимизировать все таблицы в базе данных, запустите эту команду в консоли сервера:

Где innodb_test — имя базы данных

Или запустите оптимизацию всех баз на сервере:

Если вы проверите размер базы данных до и после оптимизации, вы увидите, что общий размер уменьшился:

Таким образом, чтобы сэкономить место на вашем сервере, вы можете время от времени оптимизировать и сжимать свои таблицы и базы данных MySQL/MariDB. Не забудьте создать резервную копию базы данных перед выполнением любой работы по оптимизации.

Compress, Defrag and Optimize MariaDB/MySQL Database

In this article we will explore some methods of table/database compression and defragmentation in MySQL/MariaDB, that will help you to save space on a disk a database is located on.

Databases of large projects grow immensely with time and a question always arises what to do with it. There are several ways to solve the problem. You can reduce the amount of data in a database by deleting old information, dividing a database into smaller ones, increasing the disk size on a server or compressing/shrinking tables.

Another important aspect of database functioning is the need to defragment tables and databases from time to time to improve their performance.

InnoDB Tables Compression & Optimization

ibdata1 and ib_log Files

Most projects with InnoDB tables have a problem of large ibdata1 and ib_log files. In most cases, it is related to a wrong MySQL/MariaDB configuration or a DB architecture. All information from InnoDB tables is stored in ibdata1 file, the space of which is not reclaimed by itself. I prefer to store table data in separate ibd* files. To do it, add the following line to my.cnf:

If your server is configured and you have some productive databases with InnoDB tables, do the following:

1. Back up all databases on your server (except mysql and performance_schema). You can get a database dump using this command: # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
2. After creating a database backup, stop your mysql/mariadb server;
3. Change the settings in my.cfg;
4. Delete ibdata1 and ib_log files;
5. Start the mysql/mariadb daemon;
6. Restore all databases from the backup: # mysql -u [username] –p[password] [database_name] < [dump_file.sql]

After doing it, all InnoDB tables will be stored in separate files and ibdata1 will stop growing exponentially.

InnoDB Table Compression

You can compress tables with text/BLOB data and save quite a lot of disk space.

I have an innodb_test database containing tables that can potentially be compressed and thus I can free some disk space. Prior to doing anything, I recommend to backup all databases. Connect to a mysql server:

Select the database you need in your mysql console:

To display the list of tables and their sizes, use the following query:

SELECT table_name AS «Table»,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS «Size in (MB)»
FROM information_schema.TABLES
WHERE table_schema = «innodb_test»
ORDER BY (data_length + index_length) DESC;

Where innodb_test is the name of your database.

Some tables may be compressed. Let’s take the b_crm_event_relations table as an example. Run this query:

mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;

After running it, you can see that the size of the table has reduced from 26 MB to 11 MB due to the compression.

By compressing the tables, you can save much disk space on your host. However, when working with the compressed tables, the CPU load grows. Use compression for db tables if you have no problems with CPU resources, but have a disk space issue.

MyISAM Table Compression in MySQL/MariDB

To compress Myisam tables, use a special query in the server console instead of mysql console. To compress a table, run the following:

# myisampack -b /var/lib/mysql/test/modx_session

Where /var/lib/mysql/test/modx_session is the path to your table. Unfortunately, I didn’t have a large table and had to compress small ones, but the result still could be seen (the file was compressed from 25 MB to 18 MB):

# du -sh modx_session.MYD

# myisampack -b /var/lib/mysql/test/modx_session

# du -sh modx_session.MYD

I used the -b key in the command. When you add it, a table is backed up before compression and marked with OLD label:

# ls -la modx_session.OLD

# du -sh modx_session.OLD

Optimizing Tables and Database in MySQL and MariaDB

To optimize tables and databases, it is recommended to defragment them. Make sure if there are any tables in the database that require defragmentation.

Open the MySQL console, select a database and run this query:

select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;

Thus, you will display all tables with at least 50 MB of unused space:

data_length_mb — total size of a table

data_free_mb — unused space in a table

These are the tables we can defragment. Check how much space they occupy on the disk:

# ls -lh /var/lib/mysql/innodb_test/ | grep b_

To optimize these tables, run the following command in the mysql console:

# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;

After successful defragmentation, you will see an output like this:

As you can see, data_free_mb equals to 0 now and the table size has reduced significantly (3 – 4 times).

You can also run defragmentation using mysqlcheck in your server console:

Where innodb_test is your database
And b_workflow_file is the name of the table

To optimize all tables in a database, run this command in your server console:
# mysqlcheck -o innodb_test -u root -p

Where innodb_test is a database name

Or run the optimization of all databases on the server:

# mysqlcheck -o —all-databases -u root -p

If you check the database size before and after the optimization, you will see that the total size has reduced:

# mysqlcheck -o innodb_test -u root -p

Thus, to save space on your server, you can optimize and compress your MySQL/MariDB tables and databases from time to time. Remember to back up a database prior to doing any optimization work.

MySQL: Бэкап базы данных и его сжатие

Продолжу описывать конкретные ситуации, с которыми доводилось сталкиваться при работе с MySQL, и эта статья про резервирование БД на сервере под управлением Windows для проекта Hattrick Portal. Не буду подробно останавливаться на всех существующих инструментах резервирования MySQL, просто вкратце обозначу мои причины выбора утилиты mysqldump:

• я использую движок MySQL InnoDB (а значит mysqlhotcopy не подходит);
• она бесплатна и входит в поставку СУБД («горячее» снятие снэпшотов для InnoDB я не видел в бесплатном варианте, да и нужно оно в основном для быстрого переноса данных);
• я снимаю бэкап всей БД (для отдельных таблиц есть операторы SELECT INTO OUTFILE и LOAD DATA INFILE);
• может работать при работающей СУБД;
• отставание бэкапа на день для меня не принципиально (т.е. инкрементные бэкапы не требуются);
• даёт вполне понятный и разбираемый текстовый файл, который может пригодиться не только для восстановления БД.

Минусы, конечно, тоже есть: самый главный — это блокировка таблиц на запись, что гарантирует нам с большими таблицами в БД задержки транзакций на обновление данных. Для того проекта, который я веду, это не так принципиально и это можно преодолеть только репликацией и снятием бэкапа уже с реплики (но это потребляет еще столько же места на диске, плюс дополнительная нагрузка на диск и СУБД и вообще желателен отдельный сервер для реплики). И инкрементные бэкапы с помощью этой утилиты тоже невозможно реализовать — хотя я для Windows бесплатного ПО и не видел, та же Percona XtraBackup только для Linux.

Я запускаю mysqldump со следующими параметрами:

Все параметры вполне стандартные, кроме парочки:

• —single-transaction — создает дамп в виде одной транзакции.
• —set-gtid-purged=OFF — указывает то, что мы не используем репликацию на основе глобальных идентификаторов GTID.
• —default-character-set=utf8 — лучше на всякий случай указать, в какой кодировке у нас данные, чтобы потом не было мучительно больно.

Целую кучу параметров MySQL использует по умолчанию, например, с версии 4.1 по умолчанию выставлен параметр —opt для оптимизации скорости резервирования данных, поэтому набор ключей —quick —add-drop-table —add-locks —create-options —disable-keys —extended-insert —lock-tables —set-charset уже не надо указывать.

Лично у меня сейчас данные занимают 10 Гб, а их бэкап (снимаемый mysqldymp) — 4 Гб. Жаль, что текстовый файл занимает прилично места и «из коробки» не жмется в архив, хотя для текста это было бы логично. Для обычного виртуального сервера в хостинге любой гигабайт дискового пространства дорог (в денежном выражении). Поэтому когда приближается лимит на дисковое пространство, при превышении которого цена за хостинг начинает расти, начинаешь ценить эти гигабайты свободного места. И становится жаль что бэкап БД занимает столько нужного места, да еще чтобы снять новый бэкап — тоже нужно столько же. Я уж не говорю, что иногда хочется иметь не одну последнюю резервную копию, а хотя бы еще одну-две запасные с разницей в день и неделю.

Тут нам на помощь приходит прекрасный оператор конвейера |, используемый и в *nix и в Windows. Поэтому мы дописываем в конце команды создания бэкапа подобную конструкцию (для использования 7-zip):

Сжатие и дефрагментация базы данных в MySQL и MariaDB

23.12.2019
VyacheslavK
Linux
комментариев 9

В данной статье мы рассмотрим методики сжатия и дефрагментации таблиц и баз данных в MySQL/MariaDB, которые позволят вам сэкономить место на диске с БД.

В крупных проектах со временем базы данных разрастаются до огромных размеров и всегда возникает вопрос, как же с этим бороться. Есть несколько вариантов для решения подобной проблемы. Вы можете уменьшить количество данных в самой базе, путем удаления старой информации, разделить базу на несколько, увеличить объем дискового пространства на сервере или сжать таблицы.

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

Сжатие и оптимизация БД с типом таблиц InnoDB

Файлы ibdata1 и ib_log

На многих проектах с таблицами InnoDB встречается проблема с огромными размерами файлов ibdata1 и ib_log. Причина в большинвсте случае связан с неправильными настройками сервера MySQL/MariaDB или архитектурой БД. Вся информация из таблиц InnoDB хранится в файле ibdata1, пространство которого не высвобождается само по себе. Я предпочитаю хранить данные таблиц в отдельных файлах ibd*. Для этого нужно в конфигурационном файле my.cnf добавить строку:

Если же ваш сервер уже настроен и у вас есть несколько рабочих БД с таблицами InnoDB, нужно выполнить следующее:

1. Сделайте бэкап всех БД на своем сервере (кроме mysql и performance_schema). Дамп баз можно снять следующей командой: # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
2. После создания резервной копии БД остановите сервер mysql/mariadb;
3. Измените настройки в файле my.cfg;
4. Удалите файлы ibdata1 и ib_log файлы;
5. Запустите сервер mysql/mariadb;
6. Восстановите из бэкапа все БД: # mysql -u [username] –p[password] [database_name] < [dump_file.sql]

После выполнения этой процедуры, все таблицы InnoDB будут хранится в отдельных файлах и файл ibdata1 не будет расти в геометрической прогрессии.

Сжатие таблиц InnoDB

Вы можете сжимать таблицы с данными типа text/BLOB. Если у вас есть подобные таблицы, вы можете сэкономить довольном много дискового пространства.

У меня имеется БД innodb_test с таблицами, которые потенциально можно сжать и высвободить дисковое пространство. Перед всеми работами я настоятельно рекомендую выполнить резервное копирование всех ваших БД. Подключаемся к серверу mysql:

В консоли mysql авторизуемся в нужной БД:

Чтобы вывести список таблиц и их размер, используйте запрос:

SELECT table_name AS «Table»,
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS «Size in (MB)»
FROM information_schema.TABLES
WHERE table_schema = «innodb_test»
ORDER BY (data_length + index_length) DESC;

Где innodb_test — это имя вашей БД.

Есть вероятность, что некоторые таблицы можно сжать. Возьмём для примера таблицу b_crm_event_relations. Выполните запрос:

mysql> ALTER TABLE b_crm_event_relations ROW_FORMAT=COMPRESSED;

После выполнения, можно увидеть что за счет сжатия размер таблицы уменьшился с 26 до 11 Мб.

Благодаря сжатию таблиц вы можете сэкономить много дискового пространства на сервере. Но при работе со сжатыми таблицами вырастет нагрузка на процессор. Сжатие в таблицах нужно использовать, если у вас нет проблем с процессорными ресурсами, но есть проблема с местом на диске.

Сжатие таблиц MyISAM в MySQL

Для сжатия таблиц формата Myisam, нужно использовать специальный запрос с консоли сервера, а не в консоли mysql. Чтобы сжать нужную таблицу выполните:

# myisampack -b /var/lib/mysql/test/modx_session

Где /var/lib/mysql/test/modx_session — путь до вашей таблицы. К сожалению, у меня не было раздутой БД и пришлось выполнять сжатие на небольших таблицах, но результат все равно виден (файл сжался с 25 до 18 Мб):

# du -sh modx_session.MYD

# myisampack -b /var/lib/mysql/test/modx_session

# du -sh modx_session.MYD

В запросе, мы указали ключ -b, при его добавлении, перед сжатием создается бэкап таблицы и помечается как OLD:

# ls -la modx_session.OLD

# du -sh modx_session.OLD

Оптимизация таблиц и баз данных в MySQL/MariaDB

Для отптимизации таблиц и базы данных рекомендуется выполнять дефрагментацию. Проверим, есть ли в базе данных таблицы, которые требуют дефрагментации.

Войдем в консоль MySQL, выберем нужную БД и выполним запрос:

select table_name, round(data_length/1024/1024) as data_length_mb, round(data_free/1024/1024) as data_free_mb from information_schema.tables where round(data_free/1024/1024) > 50 order by data_free_mb;

Таким образом мы выведем все таблицы, которые имеют минимум 50 Мб неиспользуемого пространства:

data_length_mb — общий размер таблицы

data_free_mb — неиспользуемое пространство таблицы

Эти таблицы мы можем дефрагментировать. Проверим занимаемое место на диске до:

# ls -lh /var/lib/mysql/innodb_test/ | grep b_

Чтобы оптимизировать эти таблицы, используйте следующую команду в консоли mysql:

# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;

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

Как видите, data_free_mb теперь равен 0 и в целом размеры таблицы значительно уменьшились (в 3-4 раза).

Также можно выполнить дефрагментацию с помощью утилиты mysqlcheck из консоли сервера:

# mysqlcheck -o innodb_test b_workflow_file -u root -p innodb_test.b_workflow_file

Где innodb_test — это ваша БД

А b_workflow_file — имя нужной таблицы

Чтобы оптимизировать все таблицы нужной вам БД, запустите команду в консоли сервера:

# mysqlcheck -o innodb_test -u root -p

Где innodb_test — имя желаемой БД.

Или запустите оптимизацию всех БД на сервере:

# mysqlcheck -o —all-databases -u root -p

Если проверить размеры базы до и после оптимизации, то размер в целом уменьшился:

# mysqlcheck -o innodb_test -u root -p

Таким образом для экономии места на сервере, вы можете периодически оптимизировать и сжимать ваши таблицы и БД. Повторюсь, перед проведением любых работ по оптимизации, создавайте резервную копию БД.

Предыдущая статья Следующая статья