Как сжать/снизить размеры базы данных в MS SQL
Готово. Мы видим, что доступное свободное место можно освободить (сжать) на 0.69 МВ (11%).
Метод 2: Использование Transact SQL Command
Следуем за шагами ниже, чтобы уменьшить размер базы данных SQL.
Шаг 1: Открываем наш SQL Server Management Studio
Шаг 2: Подключаемся к необходимой Базе данных
Шаг 3: Нажимаем на «Создать запрос» (NewQuery)
Шаг 4: После чего в открывшемся окне прописываем соответствующую команду (ниже) и жмем кнопку «Выполнить» (Execute)
DBCC SHRINKDATABASE (test, 10); GO Готово. Кол-во освободившегося места будет такой же, как и в 1-ом методе. Т.к. осуществляется разное исполнение одной и той же задачи.
Метод 3: Сжатие на уровне строк
ALTER TABLE tableName REBUILD (DATA_COMPRESSION=ROW)Работа данного сжатия осуществляется за счет перевода фиксированного типа данных SQL в переменный тип данных. Используются следующие действия:
- Хранит тип данных CHAR (фиксированной длины), так чтобы система думала, что они являются типами данными, которые имеют переменную длину.
- Не применяет сохранение данных, если значения являются 0 и NULL.
Пример: Создадим таблицу на 14 500 строк. В целях безопасности данных, буду демонстрировать только результат. Мы видим, что занимаемое пространство данными составляет 9.7 МВ.
Осуществим сжатие по строкам.
Результат: занимаемое пространство данными уменьшилось до 5.1 МВ.
Метод 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 раз).
abills.net.ua
Подскажите, пожалуйста, каким лучше способом уменьшить размер базы данных MySQL биллинга?
Какие таблицы можно почистить, а какие вовсе удалить, если они не нужны?
Анализируя структуру БД, вижу, что статистика по правилам ротации раз в месяц складывается в отдельную таблицу с именем ‘s_detail_$yyyy-$mm-$lastdaymonth’. Могу ли я из БД удалить таблицы со статистикой за старые (ненужные) периоды?
Также есть таблица dv_logs, которая тоже содержит немалое количество строк и данных, можно ли каким-то образом ее ротировать и почистить?
Буду очень признателен, если вы дадите другие возможные способы по оптимизации размера БД.
~AsmodeuS~ Site Admin Сообщения: 5744 Зарегистрирован: Пт янв 28, 2005 3:11 pm Контактная информация:
Re: Как уменьшить размер базы данных?
Сообщение ~AsmodeuS~ » Пн дек 18, 2017 1:47 pm
посморите в SQL commander
самые большите таблицы и сообщите возможно нужно сделать ротацию этих таблиц
также можно воспользоваться misc/mysql/clear_db.pl
femistoklus Сообщения: 24 Зарегистрирован: Чт фев 05, 2015 6:53 am
Re: Как уменьшить размер базы данных?
Сообщение femistoklus » Пт дек 22, 2017 8:14 am
Ниже привожу список таиблац, которые имеют наибольший размер и количество строк:
Table name Rows Size dv_log ~503,742 76.1 MiB s_detail_2015_03_31 431,680 62.1 MiB s_detail_2016_03_31 396,489 58.1 MiB s_detail_2015_10_31 379,817 55.1 MiB s_detail_2016_05_31 375,718 54.1 MiB s_detail_2016_01_31 369,296 54.1 MiB s_detail_2015_12_31 377,513 54.1 MiB s_detail_2015_09_30 367,796 54.1 MiB s_detail_2016_04_30 374,420 54.1 MiB s_detail_2016_02_29 358,462 53.1 MiB s_detail_2015_11_30 364,367 53.1 MiB s_detail_2017_06_30 364,433 53.1 MiB s_detail_2017_04_30 361,774 53.1 MiB s_detail_2015_02_28 363,515 53.1 MiB s_detail_2017_05_31 367,648 53.1 MiB s_detail_2017_11_30 358,944 51.1 MiB s_detail_2017_01_31 355,551 51.1 MiB s_detail_2017_03_31 351,937 51.1 MiB s_detail_2017_10_31 356,046 51.1 MiB s_detail_2015_07_31 355,553 51.1 MiB s_detail_2016_12_31 356,587 51.1 MiB s_detail_2015_08_31 353,206 51.1 MiB s_detail_2016_11_30 344,644 50.1 MiB s_detail_2017_09_30 342,179 50.1 MiB s_detail_2016_10_31 345,192 50.1 MiB s_detail_2016_06_30 339,862 49.1 MiB s_detail_2016_09_30 335,540 49.1 MiB s_detail_2015_06_30 327,579 48.1 MiB s_detail_2016_08_31 332,702 48.1 MiB s_detail_2015_05_31 328,286 48.1 MiB s_detail_2017_02_28 332,597 48.1 MiB s_detail_2016_07_31 321,644 47.1 MiB s_detail_2017_08_31 322,395 47.1 MiB s_detail_2017_07_31 325,596 47.1 MiB s_detail_2015_04_30 299,456 44.1 MiB s_detail_2015_01_31 188,702 28.1 MiB s_detail 137,280 21 MiB fees 36,651 7.7 MiB Собственно остается вопрос, могу ли я удалить, к примеру, таблицы s_detail за старые периоды, которые больше не нужны. Например удалить таблицы за 2015-2016 гг?
И таблицу dv_logs наверное тоже мне не мешало бы каким-то способом почистить?))
~AsmodeuS~ Site Admin Сообщения: 5744 Зарегистрирован: Пт янв 28, 2005 3:11 pm Контактная информация:
Сжатие и дефрагментация базы данных в 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, нужно выполнить следующее:
- Сделайте бэкап всех БД на своем сервере (кроме mysql и performance_schema). Дамп баз можно снять следующей командой: # mysqldump -u [username] –p[password] [database_name] > [dump_file.sql]
- После создания резервной копии БД остановите сервер mysql/mariadb;
- Измените настройки в файле 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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Таким образом для экономии места на сервере, вы можете периодически оптимизировать и сжимать ваши таблицы и БД. Повторюсь, перед проведением любых работ по оптимизации, создавайте резервную копию БД.
Предыдущая статья Следующая статья 
Руководство по установке и администрированию Symantec Endpoint Protection (14.3 RU1 MP1) — Russian － Russian Federation
Для базы данных Microsoft SQL Server Express по умолчанию установлен максимальный размер 10 ГБ для файлов данных и данных журнала. Файлы данных включают такие элементы, как пакеты установки, определения вирусов, политики, оповещения и полученные приложения. При обновлении из встроенной базы данных (14.3 MP1 и более ранних версий) размером более 10 ГБ невозможно продолжить процесс обновления до SQL Server Express. Если мастер настройки сервера управления обнаружит, что размер базы данных слишком велик, отобразится следующее сообщение:
Объем резервной копии превышает объем, допустимый в SQL Server Express: 10 ГБ. Сначала необходимо перенастроить сервер управления, чтобы уменьшить объем импортируемых данных. Затем выполните восстановление повторно.
Примерный объем данных во встроенной базе данных превышает максимальный размер в 10 ГБ, установленный для Microsoft SQL Server Express. Чтобы продолжить обновление, мастер должен сначала уменьшить объем данных до менее 10 ГБ.
Сначала необходимо уменьшить пространство существующей встроенной базы данных, используя одно из следующих действий:
Продолжить
, чтобы мастер настройки сервера управления уменьшил размер базы данных в начале обновления.
Отмените мастер настройки сервера управления, уменьшите размер базы данных и перезапустите мастер, выполнив следующие шаги.