Sql почему файл данных занимает намного больше чем все таблицы
Обновлено: 04.07.2024
При использовании 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)
Готово. Кол-во освободившегося места будет такой же, как и в 1-ом методе. Т.к. осуществляется разное исполнение одной и той же задачи.
ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)Работа данного сжатия осуществляется за счет перевода фиксированного типа данных SQL в переменный тип данных. Используются следующие действия:
Хранит тип данных CHAR (фиксированной длины), так чтобы система думала, что они являются типами данными, которые имеют переменную длину,
Не применяет сохранение данных, если значения являются 0 и NULL
Пример: Создадим таблицу на 14 500 строк. В целях безопасности данных, буду демонстрировать только результат. Мы видим, что занимаемое пространство данными составляет 9.7 МВ.
Осуществим сжатие по строкам.
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 раз).
Я просто взял резервную копию базы данных SQL Server. Файлы MDF и LDF вместе составляют около 29 ГБ, но файл .bak был всего 23 ГБ, что на 20% меньше.
Мое первое предположение, когда одна версия набора данных меньше, чем другая версия, содержащая одни и те же данные, будет сжатием данных, но сжатие обычно дает гораздо лучшую степень сжатия, чем 20%, особенно для высокоупорядоченных данных (таких как таблицы базы данных.) Кроме того, сжатые данные не могут быть легко сжаты дальше, но я знаю, что файлы .bak могут быть сжаты.
Итак, если данные не сжимаются, и ничто не отбрасывается (потому что весь смысл создания резервной копии - это возможность восстановить ее в идентичное состояние после этого), то что это за 20%, что пропало без вести?
3 ответа
Пространство было отнесено к файлам базы данных, но не использовалось.
Вы можете создать новую базу данных, сделать ее размером 10 ГБ и посмотреть, как файлы выделяют этот объем пространства на диске. Однако, пока вы не поместите данные в базу данных, файл будет по существу пустым, а размер вашего резервного файла будет минимальным.
Для полной резервной копии LDF обычно можно игнорировать
MDF содержит фактические данные
Файл Bak содержит только страницы данных, которые используются внутри mdf. Некоторое пространство не будет использоваться. Это пространство - пользователь верхнего уровня для перестроек индексов, например.
Вполне типично иметь резервную копию 100 ГБ для БД, которая может иметь формат 250 гб. Если мой mdf имеет тот же размер, что и моя резервная копия, это будет красный флаг о неожиданном сжатии DB или нехватке дискового пространства и т. Д.
Когда создается БД, вы можете указать (для производительности), сколько места вы хотите выделить для файлов данных и журналов. Это пространство затем зарезервировано, даже если в таблицах не хранится никаких данных. Резервные копии сохраняются только в экстентах, в которых записаны данные.
В вашем случае общее количество MDF /LDF может быть даже 100 ГБ, но ваша резервная копия будет по-прежнему составлять около 23 ГБ для вашей резервной копии. Если бы было добавлено около 1 ГБ данных, сумма MDF /LDF по-прежнему составляла бы 100 ГБ, но теперь ваша резервная копия будет составлять около 24 ГБ.
Полная резервная копия содержит все экстенты, в которых есть данные, и бит файла журнала. Полная резервная копия содержит все данные с момента окончания задачи резервного копирования, а не только с момента запуска задачи резервного копирования; поэтому требуется также немного файла журнала.
Перенос большой базы данных с эксель на sql
В общем, вопрос может и глупый но поиски и неопытность ни к чему не приводят. Имеется большая база.
Вес базы данных
Вес моей БД 635 мб. Огромное кол-во форм . После того как я реализовал форму , я начинаю.
Как ограничить вес базы данных
Помогите пожалуйста, мне надо ограничить вес бд, как это сделать? (на многих хостингах исползуется.
Создание Большой Базы данных в MS Access
ДОБРОГО ВРЕМЕНИ . Хочу посоветоваться с профи, у которых есть опыт создания больших БД Нужно.
Понять что такое "полная модель восстановления". Решить нужна она вам или нет. Способ борьбы будет зависеть от этого выбора. а реальный размер какой?
на сколько увеличивается при добавлении?
как добавляете? То что данные удаляются не значит что файл будет весить меньше. Вставьте 100 млн записей, а потом удалите, БД будет весить как будто в ней 100 млн, значит вы вствили данных на один гиг, а потом их удалили, место на диске уже зарезервиравано, если столько не нужно сожмите файл до нужных размеров. Но если он у вас так вырос значит для этого была причина, значит он может вырасти вновь. Ой да ладно, ну вылезут другие проблемы, но вещь же неплохая
AutoShrink это зло. Shrink файла это операция РАЗОВАЯ, выполняющяяся по мери необходимости, если вы будете шринковать постоянно файл просто так то он у вас будет поятоянно расти, как итог сильнейшая фрагментация и частый (в зависимости какое значение параметра AutoGrowth у вас стоит) рост файла, который кстати недоступен для всехопераций записи и чтения на время роста. К примеру недавно работал с БД, еженедельный шринк, AG стоит 1mb, надо ли говорить что из за этого у них запросы изм данных оч долго делались (за минуту файл рос 45 раз и был заблокирован 600мс), поэтому руки за AutoShrink надо отрывать.
Добавлено через 2 минуты
А пример когда он полезен просто шринк, вы удалили 100 млн записей из БД и тепеоь в ней до фига свободного места, сжимаем. Или файл логов у вас не секался по какой либо причине (висит открытая транзакция) и вырос прям неприлично, сжимаем. Но это не делается Auto.
Файлы. Создать базу данных. Компоненты базы: Блюдо, вес, цена
Задание: 1. Создать базу данных на 4 строки (по вариантам) и записать в файл №1; 2. Добавить в.
Большой объем базы данных 150 - 250 GB
Добрый вечер коллеги! Столкнулся с проблемой, большой объём базы данных 150 - 250 GB Таблица.
Большой размер базы данных по сравнению с файлом Excel
Добрый день! Есть база, 120 тысяч строк и 65 столбцов. Данные в одном столбце - дата. В паре.
Большой объем базы данных при импорте из Excel
Добрый день. Прошу у форумчан совета или указания ошибки. Сам ввиду ничтожно малого опыта работы с.
Приглашаем также посетить вебинар на тему «Выбор архитектурного стиля». На этом открытом уроке участники вместе с экспертом рассмотрят различия между микросервисным и монолитным подходами, преимущества и недостатки подходов, обсудят принципы выбора архитектурного стиля.
У одного из наших клиентов возникла проблема с большой, постоянно растущей, таблицей в MySQL с более чем 2 миллиардами записей. Без модернизации инфраструктуры была опасность исчерпания дискового пространства, что потенциально могло сломать все приложение. С такой большой таблицей были и другие проблемы: низкая производительность запросов, плохая схема, и, из-за огромного количества записей, не было простого способа анализировать эти данные. Также нам нужно было решить эти проблемы без простоев в работе приложения.
В этом посте я хотел рассказать о нашем подходе к данной проблеме, но сразу хочу оговориться, что это не универсальное решение: каждый случай индивидуален и требует разных подходов. Но, возможно, вы найдете здесь некоторые полезные идеи для себя.
Спасение в облаках
После оценки нескольких альтернативных решений мы решили отправлять данные в какое-нибудь облачное хранилище. И наш выбор пал на Google Big Query. Мы выбрали его, потому что клиент предпочитал облачные решения от Google, а также данные были структурированными, предназначались для аналитики и нам не требовалась низкая задержка передачи данных (low latency). Поэтому BigQuery, казалась, идеальным решением (см. диаграмму ниже).
После тестов, о которых вы можете прочитать в посте Анджея Людвиковски (Andrzej Ludwikowski), мы убедились, что Big Query достаточно хорошее решение, отвечающее потребностям наших клиентов и легко позволяет использовать аналитические инструменты для анализа данных. Но, как вы, возможно, уже знаете, большое количество запросов в BigQuery может привести к увеличению стоимости, поэтому мы хотели избежать запросов в BigQuery напрямую из приложения и использовать его только для аналитики и как что-то вроде резервной копии.
Передача данных в облако
Для передачи потока данных есть много разных способов, но наш выбор был очень прост. Мы использовали Apache Kafka просто потому, что она уже широко использовалась в проекте и не было смысла внедрять другое решение. Использование Kafka дало нам еще одно преимущество — мы могли передавать все данные в Kafka и хранить их там в течение необходимого времени, а затем использовать для миграции в выбранное решение, которое справилось бы со всеми проблемами без большой нагрузки на MySQL. С таким подходом мы подготовили себе запасной вариант в случае проблем с BigQuery, например, слишком высокой стоимости или сложностей и с выполнением необходимых запросов. Как вы увидите ниже, это было важное решение, которое дало нам много преимуществ без каких-то серьезных накладных расходов.
Потоковая передача из MySQL
Итак, когда речь заходит о передаче потока данных из MySQL в Kafka, вы, вероятно, думаете о Debezium или Kafka Connect. Оба решения — отличный выбор, но в нашем случае не было возможности их использовать. Версия сервера MySQL была настолько старой, что Debezium ее не поддерживал, а обновление MySQL было невозможным. Мы также не могли использовать Kafka Connect из-за отсутствия автоинкрементного столбца в таблице, который мог бы использоваться коннектором для запроса новых записей без потери каких-либо из них. Мы знали, что можно использовать timestamp-столбцы, но при этом подходе могли быть потери строк из-за того, что запрос использовал более низкую точность timestamp, чем указано в определении столбца.
Конечно, оба решения хороши, и если нет никаких препятствий для их использования, то я могу рекомендовать их для передачи данных из вашей базы данных в Kafka. В нашем случае нам нужно было разработать простого Kafka Producer, который запрашивал данные без потери каких-либо записей и передавал их в Kafka. И Kafka Consumer, отправляющего данные в BigQuery, как показано на диаграмме ниже.
Отправка данных в BigQuery
Секционирование как способ экономии места
Итак, мы отправили все данные в Kafka (сжимая их для уменьшения полезной нагрузки), а затем — в BigQuery. Это помогло нам решить проблемы с производительностью запросов и быстро анализировать большой объем данных. Но осталась проблема с доступным местом. Мы хотели найти решение с заделом на будущее, которое справилось бы с проблемой сейчас и могло быть легко использовано в будущем. Мы начали с разработки новой таблицы. Мы использовали serial id в качестве первичного ключа и секционирование по месяцам. Секционирование этой большой таблицы дало нам возможность создавать резервные копии старых секций и усекать (truncate) / удалять (drop) их, чтобы освободить место, когда секция больше не нужна. Итак, мы создали новую таблицу с новой схемой и использовали данные из Kafka для ее заполнения. После переноса всех записей мы развернули новую версию приложения, которая для INSERT использовала новую таблицу с секционированием и удалили старую, чтобы освободить место. Конечно, вам понадобится достаточно свободного места для переноса старых данных в новую таблицу, но в нашем случае во время миграции мы постоянно делали резервные копии и удаляли старые разделы, чтобы быть уверенными, что у нас хватит места для новых данных.
Передача данных в секционированную таблицу
Сжатие данных как еще один способ освободить пространство
Как я уже упоминал, после передачи данных в BigQuery мы могли легко анализировать их, и это дало нам возможность проверить несколько новых идей, которые могли бы позволить нам уменьшить пространство, занимаемое таблицей в базе данных.
Одна из идей была посмотреть, как различные данные распределены по таблице. После нескольких запросов выяснилось, что почти 90% данных никому не нужны. Поэтому мы решили их сжать, написав Kafka Consumer, который отфильтровал бы ненужные записи и вставлял только нужные в еще одну таблицу. Назовем ее «сжатой» таблицей (compacted table), что показано на приведенной ниже диаграмме.
После сжатия (строки со значением "A" и "B" в колонке type были отфильтрованы во время миграции).
Передача данных в compacted-таблицу
После этого мы обновили наше приложение и теперь выполняли чтение из новой таблицы (compacted table), а запись делали в секционированную таблицу (partitioned table), из которой мы непрерывно передавали данные с помощью Kafka в сжатую таблицу (compacted table).
Итак, как видите, мы устранили проблемы, с которыми столкнулся наш клиент. Благодаря секционированию была устранена проблема нехватки места. Сжатие и правильное проектирование индексов решили некоторые проблемы с производительностью запросов из приложения, и, наконец, передача всех данных в облако дала нашему клиенту возможность легко анализировать все данные.
Так как мы используем BigQuery только для аналитических запросов, а остальные запросы, отправляемые пользователями через приложение, по-прежнему выполняются в MySQL, то затраты оказались не такие и большие, как можно было бы ожидать. Еще одна важная деталь — все было выполнено без простоев, ни один клиент не пострадал.
Резюме
Итак, подведем итоги. Мы начали с использования Kafka в качестве инструмента для потоковой передачи данных в BigQuery. Но так как все данные были в Kafka, это дало нам возможность легко решить другие проблемы, которые были важны для нашего клиента.
Читайте также: