Хабр Курсы для всех
РЕКЛАМА
Практикум, Хекслет, SkyPro, авторские курсы — собрали всех и попросили скидки. Осталось выбрать!
Хабр доступен 24/7 благодаря поддержке друзей
Комментарии 59
На эту тему врят-ли стоило изобретать велосипед, можно было взять например вот это: https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Статья интересная. Однако, позволяет ли данное решение постоянно по одному индексу дефрагментировать, а не все сразу? Я понял на примерах, что каждые 5-30 минут запускать задание, которое вызывает хранимку, которая берет нужный индекс (анализирует) и дефрагментирует, в данном решении не реализовано. Может я и ошибаюсь, но данный метод похож на те, что я читал ранее. Оно направлено на дефрагментацию или перестройку всех индексов во время простоя системы. Я же пишу о системе, у которой времени простоя не бывает или оно настолько ничтожное по длительности в сутках, что им можно пренебречь.
Хорошая статья.
Какой размер индекса в Ваших БД? Вопрос к тому, что большинство БД 24*7 имеют полную модель восстановления и при реорганизации больших индексов потребляется большой объем журнала транзакций, что может привести к проблемам в случае нехватки дискового пространства, т.е. приходится фильтровать по размеру индекса.
Какой размер индекса в Ваших БД? Вопрос к тому, что большинство БД 24*7 имеют полную модель восстановления и при реорганизации больших индексов потребляется большой объем журнала транзакций, что может привести к проблемам в случае нехватки дискового пространства, т.е. приходится фильтровать по размеру индекса.
Ребилд индексов онлайн разве вызовет простой системы?
Конечно нет, но был опыт, когда после такого данные были повреждены (проверка CHECKDB)
Автор иммел ввиду, что ребилд отдельных секций индекса вызывает блокировку, но так было до 2014 SQL Server, в 2014 можно ребилдить секции онлайн.
А как происходит это онлайн? Наверняка используется временная БД типа tempDB. И все это не освобождает от риска повреждений.
А если не онлайн и не блокируется, то я в это не поверю, т к пока он перестраивает структуру индекса, то либо системе нужно поместить куда-то эту структуру, чтобы не было блокировок, а затем переключить на измененную. И вот в эти два момента блокировка будет. Да и еще-нужно куда-то изменения складывать пока перестройка идет. Важно понять как это происходит.
А если не онлайн и не блокируется, то я в это не поверю, т к пока он перестраивает структуру индекса, то либо системе нужно поместить куда-то эту структуру, чтобы не было блокировок, а затем переключить на измененную. И вот в эти два момента блокировка будет. Да и еще-нужно куда-то изменения складывать пока перестройка идет. Важно понять как это происходит.
Все верно, но эти блокировки краковременные — они не настолько существенны.
TempDB не используется, при ребилде новая структура создается прямо в файле данных, TempDB только для сортировок при перестроении.
Если в том же файле данных, то идет нагрузка на носитель информации, т е тоже не есть хорошо. Делали оценку? Поделитесь результатами
В документации про онлайн перестроение тоже не было, что данные могут повредиться.
Вы сами пробовали, когда индекс весит много (порядка 1 ГБ) и занимает много страниц и у него несколько уровней? Сколько времени занимает?
Вы сами пробовали, когда индекс весит много (порядка 1 ГБ) и занимает много страниц и у него несколько уровней? Сколько времени занимает?
По поводу повреждения данных, то скорее всего это баг, например https://support.microsoft.com/ru-ru/kb/2969896
Есть БД, которая крутится на 2005 сервере. На конкретной таблице индекс 1.5 ГБ. За последние 9 лет такой проблемы не было ни разу, запись туда каждую минуту, при этом раз в 15 минут данных сильно больше (автоматика) + пользователи хаотично ее мучают. Индекс успевает перестроиться за эти самые 15 минут, потерь данных замечено не было. Возможно это был реально баг? Надо сделать для себя пометку, покопать этот вопрос, не хотел бы я на такое напороться.
Увы, здесь Вы не правы. Я раньше применял рекомендацию от Microsoft. Однако, из практики оказалось, что все сразу индексы дефрагментировать хуже раз в день. Лучше делать это постоянно по одному индексу каждые 5-30 минут. Перестроение вообще показало, что в момент перестроения запросы значительно дольше выполняются, т к эта операция блокирует таблицу или секцию. При постоянной дефрагментации уходит потребность в перестроении индексов. Они будут равномерно и несильно фрагментированы всегда.
Не согласен. При больших объемах мощности могут не потянуть «универсализм». Да и на мощности у заказчиков деньги ограничены. Данный подход, изложенный в статье тоже универсальный. А если Вы хотите на разные СУБД универсальный метод, то он есть, но будет проигрывать любому методу, который будет заточен для конкретной СУБД
Во многих системах можно дефрагментировать и даже перестраивать индексы все и сразу (по рекомендациям от Microsoft). Я лишь привел пример того, как размазать дефрагментацию в течении всего времени эксплуатации системы, у которой нет времени простоя. И да, я сначала тоже все индексы дефрагментировал и это сказалось на запросах. Так что Ваш подход к моей системе не подходит. Думаю есть и еще системы, где такой подход не подойдет. А если и подойдет, то будут небольшие зависания, о которых пользователи просто промолчат. И все будут считать, что все хорошо.
Значит Вам не попадались такие проблемы)
У нас самая большая база данных, которая постоянно эксплуатируется для изменения и сбора данных-до 100 ГБ, т к все старое мы складываем в хранилище, а из базы удаляем. Т. е. записи по истечении более года не доступны для редактирования. И в данном рабочем процессе это правильно.
И если у Вас не было проблем, то никто и не просит использовать опубликованный метод.
Я лишь привел свой, который подошел, т к стандартные решения не подходили. Т. е. подходили, но существенно влияли на производительность.
У нас самая большая база данных, которая постоянно эксплуатируется для изменения и сбора данных-до 100 ГБ, т к все старое мы складываем в хранилище, а из базы удаляем. Т. е. записи по истечении более года не доступны для редактирования. И в данном рабочем процессе это правильно.
И если у Вас не было проблем, то никто и не просит использовать опубликованный метод.
Я лишь привел свой, который подошел, т к стандартные решения не подходили. Т. е. подходили, но существенно влияли на производительность.
Не сильно понимаю ограничения на редактирование, оно ни как не влияет на производительность
Еще как влияет-объемы, в таблицах, которые используются и для модификации данных, объемы всегда будут за последние 1-2 года, а не за все время существования рабочих процессов. У нас это аж до 2000 года.
Таким образом у Вас всегда хранятся данные за последние 1-2 года, которые имеет смысл редактировать, а остальные отправляются в хранилище и уже не редактируются.
под хранилищем я понимаю отдельную базу данных или кубы. В моем случае первое.
Чем меньше данных в таблицах, тем быстрее идут операции на изменения данных. Это очевидно. Не понимаю, что тут доказывать нужно.
Не понятно зачем заказчику менять данные проводок или документов, которые были проведены или закрыты более 1-2 лет назад. Может стоит его убедить в нетрогании старых проводок и документов, а не идти на поводу?
Чем меньше данных в таблицах, тем быстрее идут операции на изменения данных. Это очевидно. Не понимаю, что тут доказывать нужно.
Не понятно зачем заказчику менять данные проводок или документов, которые были проведены или закрыты более 1-2 лет назад. Может стоит его убедить в нетрогании старых проводок и документов, а не идти на поводу?
Нет, я рассуждаю из реляционной алгебры. Чем больше записей в секции (в данном случае таблица как одна секция), тем больше времени потребуется для ее нахождения. Это очевидный факт. Даже если по индексу-все равно будет узлы перебирать. Больше данных-больше узлов, и значит логарифм по основанию кол-во ветвей в дереве все равно будет расти, немного, но расти.
Вы все как-то пытаетесь спихнуть на мощное железо. Нет у наших заказчиков мощного железа. Эта розовая реальность. По теории как раз должно незначительно время увеличиваться с ростом данных. Я же вижу на практике, что с каждым +100 000 строк, а уж тем более + 1 млн строк вообще говоря время на изменение меняется уже в +0,01 сек и более
Вы опять смотрите все через розовые очки. Часто у крупных заказчиков нет денег-Вы не поверите, нет и все. Нравится-работай за хорошую ЗП, не нравится-найдут другого. И по-моему, чем крупнее фирма или Заказчик, тем больше как это сказать покультурнее выкрунтасов. Ну чего мне Вам говорить. Думаю сами знаете. Не слабое железо, а вполне распространенная проблема-чего, все богатые что ли?)
Вспомнил еще важный аргумент не хранить все в одной базе данных.
Вы потом что из такой огромной БД будете резервную копию делать? А потом если понадобится ее куда скопировать? Чем больше бэкап, тем больше вероятности, что она еще и поврежденная будет. А вообще нужно разделять: оперативные данные (в самой БД)-те данные, которые пользователь может изменить (обычно это данные не старее 1-2 лет) и исторические данные-идут в хранилище данных и доступны только для чтения.
Такой подход позволяет не раздувать базы данных на серверах. Их объем всегда будет от нескольких ГБ до нескольких 10-ов ГБ. Макс=100 ГБ.
Что позволит делать быстро резервные копии и т. д. Как говорится меньше объема=меньше проблем)
А вот хранилище нужно продумать основательно, т к там будет храниться данные с самого начала сотворения рабочих процессов и до конца света)
Нужно продумать именно как быстро вытаскивать данные, а не как их менять. У нас в хранилище данные поступают каждую ночь. Как только проводке или документу с даты проведения (закрытия) прошел год. Такие данные удаляются из баз данных и остаются только в хранилище данных.
Я думаю в большинстве случаев так можно поступить, т. к. сомневаюсь, что вдруг приспичит кому-то редактировать геологически старые данные. К тому же эти данные уже были поданы в разные органы и т. д.
Вы потом что из такой огромной БД будете резервную копию делать? А потом если понадобится ее куда скопировать? Чем больше бэкап, тем больше вероятности, что она еще и поврежденная будет. А вообще нужно разделять: оперативные данные (в самой БД)-те данные, которые пользователь может изменить (обычно это данные не старее 1-2 лет) и исторические данные-идут в хранилище данных и доступны только для чтения.
Такой подход позволяет не раздувать базы данных на серверах. Их объем всегда будет от нескольких ГБ до нескольких 10-ов ГБ. Макс=100 ГБ.
Что позволит делать быстро резервные копии и т. д. Как говорится меньше объема=меньше проблем)
А вот хранилище нужно продумать основательно, т к там будет храниться данные с самого начала сотворения рабочих процессов и до конца света)
Нужно продумать именно как быстро вытаскивать данные, а не как их менять. У нас в хранилище данные поступают каждую ночь. Как только проводке или документу с даты проведения (закрытия) прошел год. Такие данные удаляются из баз данных и остаются только в хранилище данных.
Я думаю в большинстве случаев так можно поступить, т. к. сомневаюсь, что вдруг приспичит кому-то редактировать геологически старые данные. К тому же эти данные уже были поданы в разные органы и т. д.
Я в последнее время все больше замечаю следующую тенденцию-разрабатывают софт и БД на скорую руку, думая что железо справится и забывая математику. Скажу я Вам нет, я всегда склонен делать все максимально оптимально, т е мое решение пойдет и на проблемном железе. И более того на крупных предприятиях всегда есть 1 суперкомп, но он один. А серверов остальных много. Даже со времен 2000 года и то до сих пор работают. И Вы можете что-то там про ФОТ им говорить и что угодно. НО жизнь реальна. И алгоритмы нужно писать не с рассчетом на мощное железо, а с расчетом на минимальную нагрузку
Ко мне никто не приходил, я сам проанализировал с помощью следующей статьи. Повторюсь, возможно у Вас так можно делать и пользователи не ощущают замедление запросов, т к последние возможно незначительно стали медленнее в виду особенностей Вашей системы.
Вместо такого:
Ну а по тексту вместо «дефрагментации» почти везде следует читать «фрагментации».
USE [Название_Базы_Данных]USE [<имя параметра, тип, значение по-умолчанию>]Ну а по тексту вместо «дефрагментации» почти везде следует читать «фрагментации».
Обычно делал это джобой или в плане обслуживания (что почти одно и то же). Расписание: когда загрузка процессора не выше определенного порога или не реже одного раза в n дней в определенное время (количество дней и время под проект). Там же скрипт проверки на «захламлённость» индекса и выполнения его реорганизации\ребилда в зависимости от ситуации. Конечно, там был еще ряд проверок на схему таблицы (от этого зависело, например, как используется таблица, чем и как часто), размер + еще пару специфичных проверок.
По сути, схема та же: смотрим на индексы, ищем те, которые нужно переделать, если ничего не мешает — делаем что нужно. Только не было никаких вьюх в продакшен базе, всё в джобе (хранимки всё-таки создавались, если проверка занимала больше 20-30 строк). Правда, я пока не сталкивался с системами, где равномерная высокая нагрузка на одни и те же модули системы в течение суток, поэтому почти все проверки базировались на времени суток.
По сути, схема та же: смотрим на индексы, ищем те, которые нужно переделать, если ничего не мешает — делаем что нужно. Только не было никаких вьюх в продакшен базе, всё в джобе (хранимки всё-таки создавались, если проверка занимала больше 20-30 строк). Правда, я пока не сталкивался с системами, где равномерная высокая нагрузка на одни и те же модули системы в течение суток, поэтому почти все проверки базировались на времени суток.
А были ли попытки оценить эффект от этой постоянной дефрагментации? И оправдывает ли он дополнительную нагрузку на систему?
И второй вопрос. Не пробовали ли решить проблему настройкой индексов, чтобы уменьшить фрагментацию?
И второй вопрос. Не пробовали ли решить проблему настройкой индексов, чтобы уменьшить фрагментацию?
И второй вопрос. Не пробовали ли решить проблему настройкой индексов, чтобы уменьшить фрагментацию?
Как раз с этого и начинали. И стандартные методы применяли, причем даже до меня делали.
А были ли попытки оценить эффект от этой постоянной дефрагментации?
Как раз этим и занимались статья. Результаты были хорошие, т е, как я и писал выше, всегда индексы были умеренно фрагментированы. А до этого фрагментированы одни хуже других и т. д.
И оправдывает ли он дополнительную нагрузку на систему?
Неправильный вопрос. Такой подход как раз убирает нагрузку, чем все и сразу дефрагментировать. Попробуйте сами-хуже не будет.
Зарегистрируйтесь на Хабре, чтобы оставить комментарий
Автоматизация дефрагментации индексов в базе данных MS SQL Server