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

Как сжать/снизить размеры базы данных в MS SQL?

При использовании MS SQL появляется проблема, когда размеры расположенных баз данных на физическом носителе увеличиваются до огромных объемов.

Одно из решений — это покупка нового жесткого диска с большим объемом памяти. Но тот же самый MS SQL Server предлагает более экономичное решение (бесплатное) — свои собственные функции (как сжатие). Ниже представлены четыре основных метода по решению данной проблемы.

Метод 1: Использование SQL Server Management Studio

Шаг 1: Правая кнопка мыши по названию БД → Задачи (Tasks) → Сжать (Shrink) → База данных (Database)

Шаг 2: Нажимаем на «ОК»

Готово. Мы видим, что доступное свободное место можно освободить (сжать) на 0.69 МВ (11%).

Метод 2: Использование Transact SQL Command

Метод 1: Использование SQL Server Management Studio

Шаг 1: Открываем наш SQL Server Management Studio

Шаг 2: Подключаемся к необходимой Базе данных

Шаг 3: Нажимаем на «Создать запрос» (New Query)

Шаг 4: После чего в открывшемся окне прописываем соответствующую команду (ниже) и жмем кнопку «Выполнить» (Execute)

DBCC SHRINKDATABASE (test, 10); GO

Готово. Кол-во освободившегося места будет такой же, как и в 1-ом методе. Т.к. осуществляется разное исполнение одной и той же задачи.

Метод 3: Сжатие на уровне строк
ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)

Работа данного сжатия осуществляется за счет перевода фиксированного типа данных SQL в переменный тип данных. Используются следующие действия:

• Хранит тип данных CHAR (фиксированной длины), так чтобы система думала, что они являются типами данными, которые имеют переменную длину,
• Не применяет сохранение данных, если значения являются 0 и NULL

Пример: Создадим таблицу на 14 500 строк. В целях безопасности данных, буду демонстрировать только результат. Мы видим, что занимаемое пространство данными составляет 9.7 МВ.

Осуществим сжатие по строкам.

Метод 4: Сжатие на уровне страниц
ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=PAGE)

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

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

Пример: используем ту же самую таблицу на 14 500 строк.

Осуществим сжатие по страницам.

Результат: занимаемое пространство данными уменьшилось до 2МВ.

Различия между сжатием на уровне страниц и строк

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

Если вам известно, что БД использует огромное количество повторяющихся значений, то лучше использовать «Сжатие на уровне страниц» (Метод 4), т.к. система хранит ссылки на эти значения, а не дублирует данные. В остальных случаях лучше использовать «Сжатие на уровне рядов» (Метод 3). Первые 2 метода используются по желанию.

Негативные факторы при использовании сжатия:

• Частое сжатие Базы Данных не рекомендуется, т.к. сжатие приводит к фрагментации таблиц.
• Размер базы данных никаким образом нельзя сделать меньше,чем минимальный размер этой БД. Пример: если базу данных создали с размером 5 МВ и она увеличилась до 50 МВ, то ее можно сжать только до изначального созданного размера в 5МВ (даже с пустыми столбцами и строками).
• Чтобы достичь наибольшего эффекта от сжатия, ее нужно применять после операций, которые после своего применения создают большое количество неиспользуемого пространства в БД (удаление таблиц).
• Происходит увеличение загрузки процессора.

Сжатие таблицы в MS SQL позволяет существенно сэкономить дисковое пространство. Помимо экономии места, повышается производительность запросов, т.к. уменьшается количество обрабатываемых строк. При правильном выборе метода, мы можем увидеть значительное освобождение места для записи новых данных. Таблица на 14 500 строк это доказала (уменьшение размера в 2 и в 5 раз).

Тюнинг MySQL-сервера: сжатие таблиц InnoDB

Jul 8, 2015 22:21 · 293 words · 2 minute read mysql innodb

В больших проектах со временем размер баз данных разрастается до множества гигабайт (терабайт), поэтому всегда остается открытым вопрос как с этим бороться.

Известно несколько вариантов решения данной проблемы — уменьшение объема данных (грубо говоря, удаление старых записей), разделение одной базы данных на несколько, шардирование, приобретение нового оборудования (новых серверов или жестких дисков большего объема).

В большинстве случаев, уменьшение объема данных невозможно, так как база данных увеличивается за счет активного использования. Более того, если движок базы данных InnoDB, то недостаточно просто удалить записи из таблиц. Иногда архитектура проекта не позволяет разделить одну базу данных на несколько, а шардирование хорошо реализуется только при условии отсутствия (или минимального количества) связей между таблицами. Покупка нового оборудования стоит денег и требует времени на его установку и настройку.

Разработчики проекта Basecamp для своей базы данных использовали сжатие таблиц InnoDB (InnoDB compression) — давайте разберемся с таким подходом!

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

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

Что нужно для сжатия таблиц InnoDB:

• тип движка базы данных (storage engine) должен быть InnoDB;

show variables like 'storage_engine'\G; *************************** 1. row *************************** Variable_name: storage_engine  Value: InnoDB 1 row in set (0.00 sec) 

• параметр innodb_file_per_table должен быть включен (как включить данную опцию читайте в этой статье);

show variables like 'innodb_file_per_table'\G; *************************** 1. row *************************** Variable_name: innodb_file_per_table  Value: ON 1 row in set (0.00 sec) 

• параметр innodb_file_format = Barracuda (подробнее об этом параметре читайте здесь);

show variables like 'innodb_file_format'\G; *************************** 1. row *************************** Variable_name: innodb_file_format  Value: Barracuda 1 row in set (0.00 sec) 

• помните, что лучше сжимаются таблицы с текстовыми полями.

Как сжать таблицу:

• подключаемся к MySQL-серверу:

mysql -u -p 

• выбираем базу данных:

• выполняем запрос:

ALTER TABLE имя_таблицы> ROW_FORMAT=COMPRESSED; 

Read more

© Copyright 2023 Yevhen Lebid

Сжатие и дефрагментация базы данных в 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_file_per_table

innodb_file_per_table=1

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

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

После выполнения этой процедуры, все таблицы 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;

Query OK, 0 rows affected (3.27 sec) Records: 0 Duplicates: 0 Warnings: 0

После выполнения, можно увидеть что за счет сжатия размер таблицы уменьшился с 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

25M modx_session.MYD

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

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

# du -sh modx_session.MYD

18M modx_session.MYD

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

# ls -la modx_session.OLD

-rw-r----- 1 mysql mysql 25550000 Dec 17 15:20 modx_session.OLD

# du -sh modx_session.OLD

25M 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 Мб неиспользуемого пространства:

+-------------------------------+----------------+--------------+ | TABLE_NAME | data_length_mb | data_free_mb | +-------------------------------+----------------+--------------+ | b_disk_deleted_log_v2 | 402 | 64 | | b_crm_timeline_bind | 827 | 150 | | b_disk_object_path | 980 | 72 |

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

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

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

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

-rw-r----- 1 mysql mysql 402M Dec 17 15:43 b_disk_deleted_log_v2.MYD -rw-r----- 1 mysql mysql 828M Dec 17 14:52 b_crm_timeline_bind.MYD -rw-r----- 1 mysql mysql 981M Dec 17 15:45 b_disk_object_path.MYD

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

# OPTIMIZE TABLE b_disk_deleted_log_v2, b_disk_object_path, b_crm_timeline_bind;

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

+-------------------------------+----------------+--------------+ | TABLE_NAME | data_length_mb | data_free_mb | +-------------------------------+----------------+--------------+ | b_disk_deleted_log_v2 | 74 | 0 | | b_crm_timeline_bind | 115 | 0 | | b_disk_object_path | 201 | 0 |

Как видите, 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

Enter password: innodb_test.b_admin_notify note : Table does not support optimize, doing recreate + analyze instead status : OK innodb_test.b_admin_notify_lang note : Table does not support optimize, doing recreate + analyze instead status : OK innodb_test.b_adv_banner note : Table does not support optimize, doing recreate + analyze instead status : OK ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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

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

Как уменьшить физический размер базы данных mysql (innodb)?

Хочу оптимизировать размер БД, удалив устаревшие данные (например, историю состояния хостов). Что нужно сделать после запроса на удаление, чтобы физический размер БД сократился в соответствии с удаленными данными? Mysql настроен так:

[mysqld] long_query_time = 2 key_buffer_size = 512M max_allowed_packet = 2M sort_buffer_size = 16M read_buffer_size = 16M read_rnd_buffer_size = 32M myisam_sort_buffer_size = 256M thread_cache_size = 16 query_cache_size = 256M query_cache_type=1 query_cache_limit=1048576 max_connections = 2048 tmp_table_size=256M max_heap_table_size=128M innodb_file_per_table=0 innodb_buffer_pool_size = 32G innodb_log_buffer_size = 32M innodb_flush_method=O_DIRECT innodb_doublewrite = false innodb_flush_log_at_trx_commit = 0 innodb_log_file_size = 512M innodb_buffer_pool_instances = 8 sync_binlog = 0 character-set-server=utf8 collation-server=utf8_bin default-storage-engine=INNODB max_allowed_packet=100M