Sql почему файл данных занимает намного больше чем все таблицы
В какой момент база данных MySQL начинает терять производительность?
- Имеет ли значение физический размер базы данных?
- Имеет ли значение количество записей?
- Является ли снижение производительности линейным или экспоненциальным?
У меня есть то, что я считаю большой базой данных, с примерно 15 миллионами записей, которые занимают почти 2 ГБ. Исходя из этих цифр, есть ли у меня какой-либо стимул для очистки данных или я могу позволить им продолжать масштабирование еще на несколько лет?
Физический размер базы данных не имеет значения. Количество записей не имеет значения.
По моему опыту, самая большая проблема, с которой вы столкнетесь, - это не размер, а количество запросов, которые вы можете обрабатывать за раз. Скорее всего, вам придется перейти к конфигурации «ведущий / ведомый», чтобы запросы на чтение могли выполняться к ведомым, а запросы на запись - к ведущему. Однако, если вы еще не готовы к этому, вы всегда можете настроить свои индексы для выполняемых запросов, чтобы ускорить время ответа. Также есть много настроек, которые можно сделать с сетевым стеком и ядром в Linux, что поможет.
У меня было до 10 ГБ, только с небольшим количеством соединений, и он обрабатывал запросы очень хорошо.
Сначала я сконцентрируюсь на ваших индексах, а затем попрослю администратора сервера взглянуть на вашу ОС, и, если все это не поможет, возможно, пришло время реализовать конфигурацию master / slave.
Я работаю над проектом, который имеет базу данных MySQL с почти 1 ТБ данных. Наиболее важным фактором масштабируемости является оперативная память. Если индексы ваших таблиц помещаются в память и ваши запросы высоко оптимизированы, вы можете обслуживать разумное количество запросов на среднем компьютере.
Количество записей имеет значение, в зависимости от того, как выглядят ваши таблицы. Разница в том, чтобы иметь много полей varchar или только пару целых или длинных целых.
Физический размер базы данных также имеет значение: подумайте о резервных копиях, например. В зависимости от вашего движка ваши физические файлы БД растут, но не сжимаются, например, с помощью innodb. Поэтому удаление большого количества строк не поможет уменьшить ваши физические файлы.
В этом много вопросов, и, как и во многих случаях, дьявол кроется в деталях.
Размер базы данных имеет значение . Если у вас более одной таблицы с более чем миллионом записей, производительность действительно начинает падать. Количество записей, конечно, влияет на производительность: MySQL может работать медленно с большими таблицами . Если вы нажмете на миллион записей, вы получите проблемы с производительностью, если индексы не установлены правильно (например, нет индексов для полей в «выражениях WHERE» или «условиях ON» в соединениях). Если вы наберете 10 миллионов записей, у вас начнутся проблемы с производительностью, даже если у вас все ваши индексы правильные. Модернизация оборудования - добавление дополнительной памяти и большей мощности процессора, особенно памяти - часто помогает уменьшить самые серьезные проблемы, снова увеличивая производительность, по крайней мере, до некоторой степени. Например37 сигналов прошли путь от 32 ГБ ОЗУ до 128 ГБ ОЗУ для сервера базы данных Basecamp.
Вначале я бы сосредоточился на ваших индексах, а не на том, чтобы администратор сервера смотрел на вашу ОС, и, если все, что не помогло, это может быть время для конфигурации master / slave.
Это правда. Другая вещь, которая обычно работает, - это просто уменьшить количество данных, с которыми неоднократно работали. Если у вас есть «старые данные» и «новые данные» и 99% ваших запросов работают с новыми данными, просто переместите все старые данные в другую таблицу - и не смотрите на это;)
2ГБ и около 15М записей - это очень маленькая база данных - я использую гораздо большие базы данных на Pentium III (!), И все еще работает довольно быстро. один.
Говорить о «производительности базы данных» бессмысленно, здесь термин «производительность запросов» лучше. И ответ таков: это зависит от запроса, данных, с которыми он работает, индексов, оборудования и т. Д. Вы можете получить представление о том, сколько строк будет сканироваться и какие индексы будут использоваться с синтаксисом EXPLAIN.
2ГБ на самом деле не считается «большой» базой данных - она больше среднего размера.
В настоящее время я управляю базой данных MySQL в облачной инфраструктуре Amazon, которая выросла до 160 ГБ. Выполнение запросов в порядке. Кошмар превратился в резервное копирование, восстановление, добавление подчиненных устройств или что-то еще, что связано со всем набором данных, или даже с DDL на больших таблицах. Получение чистого импорта файла дампа стало проблематичным. Для того чтобы сделать процесс достаточно стабильным для автоматизации, необходимо было сделать различные выборы, чтобы установить приоритет стабильности над производительностью. Если бы нам когда-нибудь пришлось восстанавливаться после аварии, используя резервную копию SQL, мы бы не работали в течение нескольких дней.
Горизонтальное масштабирование SQL также довольно болезненно, и в большинстве случаев приводит к его использованию способами, которые вы, вероятно, не предполагали, когда решали сначала поместить свои данные в SQL. Осколки, чтение ведомых, multi-master и др., Все они - действительно дерьмовые решения, которые усложняют все, что вы когда-либо делаете с БД, и ни одно из них не решает проблему; только смягчает это в некоторых отношениях. Я настоятельно рекомендую рассмотреть вопрос о переносе некоторых ваших данных из MySQL (или вообще любого SQL), когда вы начнете приближаться к набору данных такого размера, когда эти типы вещей становятся проблемой.
В нереляционное хранилище данных. Реляционные базы данных принципиально не масштабируются без простоя или нарушения реляционной модели. Если вы собираетесь сломать реляционную модель, лучше отказаться от использования реляционной БД. Вместо этого создайте специально созданные документы и поместите их в механизм хранения документов, например, CouchDB или какую-либо другую систему.
Также следите за сложными соединениями. Сложность транзакции может быть важным фактором в дополнение к объему транзакции.
Рефакторинг тяжелых запросов иногда дает большой прирост производительности.
Сразу скажу что я не 1С-к я системный администратор. 1С-ники на аутсорте говорят что просто много
данных. База ведется с 2015 г., но внятно объяснить почему такая разница между SQL и dt-шкой они не могут
Понять почему база стала такой большой.
База весит 164 Гб. При выгрузке в dt файл 5 Гб
Что было проверено:
1. Выгрузка в dt-шку и загрузка в чистую SQL базу результатов не дала, размер базы остался таким же.
2. В SQL-ке к этой базе ежедневно применяются:
a. Перестроение индекса
Серверная версия SQL 1С:Предприятие 8.3 (8.3.16.1814) Управление торговлей, редакция 11 (11.4.13.103)
(0) Нормальное сжатие SQL базы, если там нет картинок 1 к 10. То есть 164 Гб база, должна весить примерно 15-17Гб.
Скорее всего не настроено shrink в SQL, данные мусорные копятся.
(0) А что мешает посмотреть размер таблиц в SQL.
С учетом "выгрузка в dt-шку и загрузка в чистую SQL базу результатов не дала" главный подозреваемый - итоги регистров. Но 170ГБ это очень много
В MSQL SMS не дает сжать базу через интерфейс:
Модель восстановления - простая
Да нифига не надо сжимать, если кушать не просит. А без предварительного анализа на потенциал "сжатия" - и подавно.
(0) А в чем проблема с размером? База плохо работает или что?
А разница между SQL и dt объясняется тем, что dt сжатый.
Размер данных 10 429 512
Размер индекса 8 376 680
Общий размер 18 806 512
Количество строк данных 49 540 167
(18) Хм. Странно.
Да, много занимают итоги регистров. И из них много занимают их индексы (возможно есть "лишние").
Причем мне недавно убедительно доказывали, что выгрузка в dt происходит вместе с итогами. Что косвенно подтверждает тот факт, что загрузка dt в пустую базу результатов не дала.
Получается, что просто хорошо жмется все это дело при выгрузке в dt. Можно попробовать полный пересчет итогов, но неизвестно сколько он займет и вряд ли будет такой уж большой эффект от удаления лишних записей таблиц итогов. Короче от тебя как от админа тут мало что зависит. Нужно анализировать ситуацию из прикладной части.
(0) Покажи свойства FILES системной базы model
(25)Посмотри регистр вручную, с сортировкой по самой ранней даты - может там типа дата 1800 .
Сразу скажу что я не 1С-к я системный администратор. 1С-ники на аутсорте говорят что просто много
данных. База ведется с 2015 г., но внятно объяснить почему такая разница между SQL и dt-шкой они не могут
Понять почему база стала такой большой.
База весит 164 Гб. При выгрузке в dt файл 5 Гб
Что было проверено:
1. Выгрузка в dt-шку и загрузка в чистую SQL базу результатов не дала, размер базы остался таким же.
2. В SQL-ке к этой базе ежедневно применяются:
a. Перестроение индекса
Серверная версия SQL 1С:Предприятие 8.3 (8.3.16.1814) Управление торговлей, редакция 11 (11.4.13.103)
(0) Нормальное сжатие SQL базы, если там нет картинок 1 к 10. То есть 164 Гб база, должна весить примерно 15-17Гб.
Скорее всего не настроено shrink в SQL, данные мусорные копятся.
(0) А что мешает посмотреть размер таблиц в SQL.
С учетом "выгрузка в dt-шку и загрузка в чистую SQL базу результатов не дала" главный подозреваемый - итоги регистров. Но 170ГБ это очень много
В MSQL SMS не дает сжать базу через интерфейс:
Модель восстановления - простая
Да нифига не надо сжимать, если кушать не просит. А без предварительного анализа на потенциал "сжатия" - и подавно.
(0) А в чем проблема с размером? База плохо работает или что?
А разница между SQL и dt объясняется тем, что dt сжатый.
Размер данных 10 429 512
Размер индекса 8 376 680
Общий размер 18 806 512
Количество строк данных 49 540 167
(18) Хм. Странно.
Да, много занимают итоги регистров. И из них много занимают их индексы (возможно есть "лишние").
Причем мне недавно убедительно доказывали, что выгрузка в dt происходит вместе с итогами. Что косвенно подтверждает тот факт, что загрузка dt в пустую базу результатов не дала.
Получается, что просто хорошо жмется все это дело при выгрузке в dt. Можно попробовать полный пересчет итогов, но неизвестно сколько он займет и вряд ли будет такой уж большой эффект от удаления лишних записей таблиц итогов. Короче от тебя как от админа тут мало что зависит. Нужно анализировать ситуацию из прикладной части.
(0) Покажи свойства FILES системной базы model
(25)Посмотри регистр вручную, с сортировкой по самой ранней даты - может там типа дата 1800 .
При использовании MS SQL появляется проблема, когда размеры расположенных баз данных на физическом носителе увеличиваются до огромных объемов.
Одно из решений — это покупка нового жесткого диска с большим объемом памяти. Но тот же самый MS SQL Server предлагает более экономичное решение (бесплатное) — свои собственные функции (как сжатие). Ниже представлены четыре основных метода по решению данной проблемы.
Шаг 1: Правая кнопка мыши по названию БД → Задачи (Tasks) → Сжать (Shrink) → База данных (Database)
Шаг 2: Нажимаем на «ОК»
Готово. Мы видим, что доступное свободное место можно освободить (сжать) на 0.69 МВ (11%).
Метод 2: Использование Transact SQL Command
Шаг 1: Открываем наш SQL Server Management Studio
Шаг 2: Подключаемся к необходимой Базе данных
Шаг 3: Нажимаем на «Создать запрос» (New Query)
Шаг 4: После чего в открывшемся окне прописываем соответствующую команду (ниже) и жмем кнопку «Выполнить» (Execute)
Готово. Кол-во освободившегося места будет такой же, как и в 1-ом методе. Т.к. осуществляется разное исполнение одной и той же задачи.
Работа данного сжатия осуществляется за счет перевода фиксированного типа данных SQL в переменный тип данных. Используются следующие действия:
Хранит тип данных CHAR (фиксированной длины), так чтобы система думала, что они являются типами данными, которые имеют переменную длину,
Не применяет сохранение данных, если значения являются 0 и NULL
Пример: Создадим таблицу на 14 500 строк. В целях безопасности данных, буду демонстрировать только результат. Мы видим, что занимаемое пространство данными составляет 9.7 МВ.
Осуществим сжатие по строкам.
Алгоритмы действия данного сжатия заключается в том, что система проходит по всей таблице. Если видит повторяющиеся значения, то вместо копирования этих данных, система создает ссылки на них. Аналогично осуществляется с общими префиксами.
Данное сжатие позволяет максимизировать кол-во строк, которые хранятся на странице,
Повторы данных заменяются ссылками, если происходит сжатие по префиксу.
Пример: используем ту же самую таблицу на 14 500 строк.
Осуществим сжатие по страницам.
Результат: занимаемое пространство данными уменьшилось до 2МВ.
Различия между сжатием на уровне страниц и строк
Если кратко резюмировать выше описанные способы, то главное различие между 3 и 4 способом – это данные которые используются в самой базе данных.
Если вам известно, что БД использует огромное количество повторяющихся значений, то лучше использовать «Сжатие на уровне страниц» (Метод 4), т.к. система хранит ссылки на эти значения, а не дублирует данные. В остальных случаях лучше использовать «Сжатие на уровне рядов» (Метод 3). Первые 2 метода используются по желанию.
Негативные факторы при использовании сжатия:
Частое сжатие Базы Данных не рекомендуется, т.к. сжатие приводит к фрагментации таблиц.
Размер базы данных никаким образом нельзя сделать меньше, чем минимальный размер этой БД. Пример: если базу данных создали с размером 5 МВ и она увеличилась до 50 МВ, то ее можно сжать только до изначального созданного размера в 5МВ (даже с пустыми столбцами и строками).
Чтобы достичь наибольшего эффекта от сжатия, ее нужно применять после операций, которые после своего применения создают большое количество неиспользуемого пространства в БД (удаление таблиц).
Сжатие таблицы в MS SQL позволяет существенно сэкономить дисковое пространство. Помимо экономии места, повышается производительность запросов, т.к. уменьшается количество обрабатываемых строк. При правильном выборе метода, мы можем увидеть значительное освобождение места для записи новых данных. Таблица на 14 500 строк это доказала (уменьшение размера в 2 и в 5 раз).
Этот вопрос был перенесен из Super User, поскольку на него можно ответить в Exchange Stack Exchange для администраторов баз данных. Мигрировал 7 лет назад .
Я плохо разбираюсь в SQL, но у меня есть база данных для обслуживания.
Для этого почти не осталось места, поэтому я решил удалить все данные, скажем, за 2008 год. После выполнения запроса на удаление (было очищено около 10 000 000 строк) и очистки журнала транзакций я обнаружил, что мой действия не влияли на размер базы данных. Есть ли что-то еще, что я должен сделать?
Хотя сокращение действительно опасно по причинам, указанным здесь. Между ответом Джимбо и ответом Джона есть хорошая середина . Вы всегда должны серьезно задуматься, хотите ли вы уменьшить свою базу данных.
В идеальном мире - вы бы создали свою БД с большим количеством свободного пространства для роста. Я называю это "Правильный размер" вашей базы данных. Вы бы позволили этому свободному пространству быть там и не стремились его вернуть и сохранили ваш общий размер прямо на вашем использованном размере. Почему? Потому что ваша база данных со временем снова вырастет . Затем вы снова сократитесь . И вы застрянете в этой ужасной схеме бесполезных сокращений, сопровождаемых ростом - и все время, как указали немногие, вы будете увеличивая вашу фрагментацию индекса.
Я написал в блоге об этом, где я увещевал людей: « Не прикасайтесь к этой термоусадочной кнопке! », Но иногда . Иногда вам нужно. Если у вас есть большая база данных, вы только что освободили значительное пространство и не рассчитываете на ее дальнейшее увеличение - хорошо, тогда можно считать сжатие однократной операцией, если впоследствии вы сможете позаботиться о фрагментации индекса с помощью перестройки их. Операция сжатия может занимать много времени, поэтому вам нужно запланировать ее на время, когда вы можете заплатить эту цену за работу сокращения. Подход к созданию пустой БД и копированию в нее данных работает, но это может стать очень трудным с большими базами данных и большим количеством данных.
Если вы планируете добавить это пространство обратно в БД с помощью обычного использования и моделей роста в будущем, то вы можете просто оставить это место там.
Также Вы сказали, что «очистили» свой журнал транзакций. Мне было бы любопытно узнать, как вы это сделали, но, прочитав пост, которым я поделился, и другие статьи этой серии, вы увидите несколько советов по управлению журналом транзакций. Но вкратце - если вы находитесь в режиме полного восстановления, вы должны регулярно делать резервные копии журналов, чтобы журнал сам себя использовал повторно. В противном случае - без резервного копирования журнала в полноэкранном режиме - файл журнала продолжает расти, расти и расти и всегда сохраняет то, что вы сделали, потому что вы сказали SQL, что вы не просто хотите поддерживать этот журнал для восстановления после сбоя, но хотите сохранить его ручное резервное копирование для воспроизведения транзакций / отмены транзакций для восстановления к определенному моменту времени для целей восстановления . Если у вас все просто и вы видите, что журнал чрезмерно растет, BEGIN TRAN . do work. COMMIT TRAN или вы просто сделали одно большое DELETE заявление и удалили целый беспорядок данных в одной неявной транзакции.)
Я также предполагаю, что вы ищете это свободное место в вашей файловой системе. Если вы ищете его в SQL и в том большом файле, который у вас есть - возможно, вы ожидаете завершения очистки ghost, если ищете сразу после своей операции. Пол Рэндал пишет о Ghost Cleanup .
Читайте также: