Секционирование таблиц – зло или благо?

Меня зовут Антон Дорошкевич, я из города Новосибирска, Франчайзинговая сеть ИнфоСофт.

Расскажу про секционирование таблиц. Попытаемся с вами разобраться – зло это или благо.

Секционирование таблиц в 1С редко кто использует. Но иногда становится настолько больно, что без этого обойтись нельзя.

На слайде представлены последние данные статистики – объем генерируемых в год данных в зетабайтах:

• в 2020 году было сгенерировано 40 зетабайт данных;

• в 2021 году – 60 зетабайт;

• к 2025 планируется, что в год будет сгенериться 160 зетабайт.

Казалось бы, да и пусть генерятся – все же у себя в смартфонах и на компьютерах кучу файлов хранят. На самом деле нет – 60% объема всех этих данных хранят корпорации.

В России большое количество данных хранится в том числе в 1С. Поэтому тема секционирования, даже если кого-то до сих пор не затрагивало, скоро затронет всех, скоро будет невозможно ворочать эту одну большую таблицу или 2 или 3. И к этому надо хотя бы попытаться подготовиться, заглянуть за горизонт – что там бывает, на что рассчитывать, на что не рассчитывать, что делать, что не делать…

Типичное распределение размеров таблиц в базе 1С

Классическая база 1С по распределению объемов выглядит примерно так. Множество таблиц – тут далеко не все, тут топ 200. И из них 3-4 таблицы огромадные, которые занимают в общей сложности 80% всего объема.

Табличка, выделенная синим цветом, скорее всего, вообще проблемная. Конкретно в этой базе эта таблица имеет 4 миллиарда записей. И ничего сделать с этим нельзя, удалить записи из этой таблицы нельзя.

При этом вам нужно понимать, что все ваше секционирование (или партицирование, что то же самое), полностью уничтожит реструктуризация 1С. Как только вы запустили либо обновление с изменением структуры данных, либо просто реструктуризацию в конфигураторе, 1С создаст простую несекционированную табличку. За этим нужно следить.

Поэтому, как только вы начинаете использовать секционирование, все структурные обновления 1С либо какие-то регламентные работы из конфигуратора нужно согласовывать с DBA-шниками – вырабатывать планы что как делать.

Казалось бы, есть большая таблица, в ней много данных – хороший такой набор. Ну и ладно. Находим то, что нам надо, достаем. Почему это вдруг стало проблемой?

Дисков добавили – никакой проблемы нет.

Но зачем-то кто-то когда-то придумал индексы на базе данных.

И вот тут по номеркам уже будет гораздо проще найти те данные, которые нам нужны.

Индексы таблиц базы данных в 1С

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

Но из-за особенностей 1С индексы огромные.

Особенности построения индексов для таблиц баз данных 1С описаны на ИТС.

Например, возьмем периодический регистр сведений. Как в периодическом регистре без периода? Никак, туда обязательно помещаются хронологические данные, плюс еще и наш любимый момент времени, который представляет собой уникальную совокупность даты и ссылки на документ, не повторяется внутри миллисекунд и так далее.

Это значит что на каждую строчку таблицы, у вас есть строчка в индексе.

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

Хорошо, если период – день, а если секунда, а если момент времени? Получается, что индекс у вас растет и поиск по нему не так эффективен как мог бы быть.

Т.е. для вас он полезный, вам с ним получить данные быстрее. Но для самой базы данных он почти ничего в итоге не ускоряет.

Потом начинаем удивляться, а чего это у нас планировщик выбрал Table Scan (SeqScan) вместо того, чтобы в индексе поискать.

Самый быстрый доступ к данным в базе – это Index Seek (Index Only Scan). Чтобы этого это добиться, нужно в запросе полностью повторить состав индекса, который описан на ИТС.

Если вы хотя бы одно поле сюда не укажете, допустим, при запросе к таблице оборотов регистра укажете в запросе не все измерения, а только период и пару измерений, при том, что у вас 8 измерений, будет Index Scan, а не Index Seek (Index Only Scan).

Будет сканироваться индекс. Индекс огромный. Чтобы что-то в нем найти, движок СУБД потратит много времени, а потом еще и полезет в базу, чтобы достать оттуда данные. Только тогда вы получите результат.

Проблемы больших объемов

В итоге мы вместо нашей всегда быстрой 1С получаем большую толстую черепаху – данных несет много, очень много чего хранит, но еле-еле шевелится.

Основные проблемы больших объемов – это регламентные операции. Это – основная вещь, которая болит у админов. Это не болит у пользователей, но болит у админов.

Перестроение индекса. Когда-нибудь вы вырастите такую таблицу, для которой никогда не сможете сделать rebuild индекса в MS SQL (или reindex в PostgreSQL). Он может идти неделями. Такое технологическое окно ни один бизнес не даст. Если в базе много данных, они явно нужны, а тут они неделю должны отсутствовать.

Обновление статистики. Очень часто затягивается процесс обновления статистики, особенно на MS SQL (на PostgreSQL оно идет намного быстрее). В итоге обновить статистику на MS SQL становится невозможно. А сделать полное обновление статистики в режиме Full Scan, как рекомендует фирма 1С, – невозможно в два раза больше)). Соответственно, планировщик знает вообще старое распределение данных в статистике. Он вообще не в курсе, что там у вас с данными. Генерятся отвратительные планы запросов. Тормозим.

В PostgreSQL получаем проблемы на вакууме. На огромных таблицах vacuum туда зайдет и не выйдет – он будет пытаться ее провакуумить несколько дней. Неважно, autovacuum вы настроите или просто vacuum руками запустите. Не дай Бог, vacuum full запустите. Это полезная операция, но на такой таблице она может длиться крайне долго, так как происходит в один поток. А это еще и блокирующая операция – по факту все, база встала колом.

Очень большая проблема с распуханием (BLOAT) таблиц в СУБД PostgreSQL. Эта проблема сказывается в разы сильнее. База пухнет. Вместе с ней пухнут индексы. Несмотря на то, что в Postgres с распуханием индексов пытаются бороться – там апдейты по-другому сделали, дедубликацию сделали. Но это пока не сильно помогает. Индексы в 1С все равно очень сильно пухнут. Мы в 1С, к сожалению, почти никогда не используем апдейт. У нас, когда перепроводится документ, который должен отразить движение в регистре сведений или накопления, платформа delete записи, а потом insert. Поэтому нововведение PostgreSQL 14 про облегченный апдейт в 1С не всегда будет работать. Распухание таблиц – это очень больная тема. И вы не сможете с ним побороться, потому что единственный действенный способ борьбы с распуханием – это vacuum full, а он “никогда не закончится” на большой таблице. Из-за распухания база работать будет все медленнее и медленнее, потому что СУБД будет поднимать все больше и больше данных, поднимая все версии, все это анализируя. Вы как бы ничего сделать не можете.

Следующий момент – INDEX SCAN. Я уже проговорил до этого, что Index Seek (Index Only Scan) – крайне редкая операция. Если вы анализируете планы запросов из техжурнала 1С, ну или из СУБД, вы крайне редко увидите в длительном запросе Index Seek (Index Only Scan). Там чаще всего Index Scan. Index Scan будет очень длительный, огромный по времени, очень трудозатратный. Админ 1С будет кричать: «Дайте мне еще мощности СХД», а админ СХД будет ему отвечать: «Ничего не знаю, у нас тут рядом SAP крутится, в нем все быстро, а ваша 1С тормозит». Вечная борьба.

И неочевидная вещь – вставка записи (INSERT) будет тормозить. И тормозить существенно. Будет очень сильно подтормаживать операция, где мы просто вставляем запись. Но как вставляется запись? Она же не сама по себе живет. Запись появилась, нужно обновить индекс. А индексы у нас еще и распухшие – нужно все версии поднять, заново все сделать, все обновить. А в MS SQL это еще и кластерный индекс. То есть он еще и попытается вставить запись, чтобы все было по порядку. Поэтому при определенном размере таблицы скорость insert будет резко падать. Нельзя сказать точно, при каком объеме это произойдет, но после каких-то миллионов записей упадет. Это зависит от железа, настроек, типа нагрузки на СУБД. Предсказать конкретную цифру невозможно, но будет такая неочевидная проблема.

Следующий момент – это удаление по периоду из этих записей. Мы пробовали удалять записи из такой огромной таблицы – это проблема. Клиенты часто просят обрезать базу: «Оставьте там три года только». Мы делали эксперимент. Таблица была полтора миллиарда записей, надо было срезать миллиард двести и оставить 300 миллионов. Оказалось, что срезка 1,2 млрд в 1,5 млрд таблице занимает месяц в MS SQL. И вы ничего с этим сделать не можете. Там дешевле оказалось создать рядом новую таблицу и скопировать в нее 300 миллионов записей. Но так не всегда можно делать, не всегда бизнес-процесс вам это позволит. Когда-то нужно будет удалить старые записи хронологические, а вы не сможете это сделать. Вообще не сможете, база будет просто колом стоять. А еще и журналы транзакций – главный грех админов – будут настолько расти, что еще и место на диске кончится. И все колом станет.

Поиск по периоду тоже резко начнет ухудшаться. Про этом уже выше все было сказано – Index Scan будет плохо работать, Table Scan еще хуже будет работать, в Index Seek (Index Only Scan) вы скорее всего никогда не попадете. Поиск по периоду будет все медленнее, медленнее, хотя, казалось бы, индексированное поле. Запрос идеальный, а будет все хуже, хуже и хуже.

Секционируй это!

Что делать? Индексировать, секционировать, партиционировать и так далее.

В чем смысл? Мы разложим табличку по полочкам – на несколько маленьких табличек по какому-то разделителю секционирования (чаще всего это период). По месяцу, по дню, по неделе – кому как удобно. Все зависит от того, как вы потом пользуетесь данными из этой таблицы. У вас как потом запросы идут? Вы год собираете, неделю, месяц, два, три? Вам нужно нарезать этот торт на кусочки так, чтобы использование этих данных потом попадало в секции.

Возьмем пример базы, где в хронологическом порядке собирается информация с каких-то устройств. У устройства есть айдишник и несколько параметров с цифрами, которые нужно собрать.

Казалось бы, давайте секционируем таблицу по айдишникам устройств. Давайте. Но таких устройств 32 тысячи – окей, у нас будет 32 тысячи секций.

А пользователь потом почти никогда не заказывает отчет по устройству. Он почти всегда заказывает в 1С отчет, где выводятся показатели за месяц – средние, общие по нескольким устройствам. Получается, что вы не попадаете в разделитель – у вас получаются огромное количество таблиц, вроде секционировали, ничего не поменялось.

И тут сразу оговорочка. Для пользователя ничего и не должно было поменяться – у него ничего не ускорится. Да, оно не будет деградировать, но от того, что вы секционировали, оно не ускорится. Если вы сделали изначально правильную архитектуру, а потом секционировали, быстрее запросы работать не будут.

Дальше пойдут скучные слайды с планами запросов, но без них никак, важное в них выделено жирным.

Сверху запрос – мы из той таблички с четырьмя миллиардами записей выбираем пару полей, специально попадая в индекс (индекс как раз по этим двум полям), и добавляем условия: данные за декабрь 2020-го года плюс отбор по конкретному айдишнику (ссылка на регистратор).

Оригинальная таблица в контексте этого слайда – это не секционированная таблица.

В плане видно, что мы нашли индекс, пробежались по нему через Index Scan, и затем вытащили это все вам в данные. Заняло 11 миллисекунд.

Все хорошо быстро сработало.

Теперь мы взяли эту табличку и разбили на 20 секций – с начала 2020 года по сентябрь 2021 года получилось 20 секций ровно по месяцу.

По плану запроса видно, что система выбрала другой индекс и затем пробежалась только по одной секции – в названии видно y2020m12. Она пробежалась по одному месяцу и получила потом данные из одного месяца. Но время выполнения у нас не поменялось.

Почему? С такими запросами, когда попадает Index Scan, пока наше железо справляется - всё нормально.

Но тут имеется другая хорошая сторона медали. Казалось бы, ну и зачем мы секционировали, ничего не поменялось. Я предупреждал, ничего не поменяется. И это хорошо. Как минимум не стало хуже.

Делаем следующий запрос. Здесь мы берем и увеличиваем период в два раза – отбираем уже не месяц, а два месяца. Получаем 28 миллисекунд.

Теперь все то же самое делаем на секционированной таблице.

Получили 29 миллисекунд. Ой, как все плохо, все стормознулось, стало только хуже.

Нет, не хуже, все нормально – мы видим, что таблица взяла данные всего с двух секций.

Но обратите внимание, это все запросы на равно.

А теперь начинаем издеваться – ставим в этом запросе вместо равно like.

Пользователи же обожают в системе искать. Этот like пока не сильно извращенский – у него впереди процента нет. Этот like - поиск по началу строки.

Поэтому система еще выбирает Index Scan. Она может. А раз может, она это делает. И когда в запросе мы вместо равно поставили like, результат получается тот же – так же выдастся 43 350 значений.

Но вместо 15 миллисекунд, которые у нас были, когда мы запрос за месяц делали, мы теперь получаем 15 секунд – в тысячу раз медленнее.

Это на несекционированной таблице.

На секционированной таблице, если вы помните, у нас поиск по равно проходит 16 миллисекунд. А по like? Смотрим – 850 миллисекунд.

Почему? Потому что просканировать два индекса маленьких двух секций на like в разы быстрее.

Мы здесь выиграли в скорости в 20 раз – из-за того, что секционировали.

Дальше берем период два месяца и опять проверяем запрос с like. Тут я вам планы уже не показываю – они сильно много занимают.

Результаты у нас получились:

• оригинальная таблица ищет результат 17 секунд

• секционированная – 1,7 секунды

В 10 раз выигрыш, когда мы период в два месяца взяли.

Теперь берем период три месяца. Смотрим.

• Оригинальная таблица 28 секунд.

• А секционированная – 2,5 секунд.

Мы вроде увеличиваем период, а выигрыш так и остается в 10 раз практически.

Конечно, если мы с вами сейчас выберем все периоды, которые есть в таблице, выигрыш сводится к нулю. Потому что – какая разница, поднять одну большую таблицу или 20 секций, на которые она разделена? Но такой запрос – редкий. Конечно, финансисты и аналитики и такое могут заказывать, но это редко.

А теперь начинаем совсем издеваться и вводим в начало параметра like процент, чтобы системе стало совсем плохо. По факту это аналог ввода искомой части в поле поиска 1С.

Оригинальная таблица, которая со стандартным запросом с like справлялась у нас за 15 секунд, теперь ищет результат 35 секунд.

Секционированная таблица – 15 секунд. Здесь выигрыш уже, конечно, не в десятки раз, но все равно достаточно большой – почти в два раза.

Это эффект от секционирования чисто при работе пользователей – он будет, когда пользователи что-то такое странное заказывают.

Если у ваших пользователей будет жесткая форма отчета, где только отборы и никаких like нельзя сделать, все проверки только на равно и так далее, выигрыша почти не будет, будет все одно и то же. Но вы выиграете в регламентных операциях над СУБД.

Когда секционирование – это благо? А когда – зло?

Итак, когда секционирование благо?

Когда у вас сотни миллионов, миллиарды записей в таблице – это раз. Если у вас в таблице тысячи строк – секционирование не имеет смысла.

Когда у вас операции обслуживания не успевают за технологическое время. Это на самом деле одна из самых главных причин, по которым делают секционирование. Например, у СУБД PostgreSQL есть форк – timescaldb, как раз она для хранения хронологических данных. У нас сейчас пошла мода хранить логи в СУБД. Знаю многих, кто пытался туда журнал регистрации занести, потом отказались и так далее. Вообще-то СУБД для хранения логов не предназначено, но пытаются сделать. Но вот секционированную СУБД использовать для хранения логов можно достаточно эффективно.

И тут есть такая неочевидная вещь – на самом деле все 1С-ники даже в файловой базе сталкивались с возможностью использовать секционирование. Знаете, что это такое? Это журнал регистрации, разбитый по дням, по часам, по месяцам. Вот вам секции.

Более того, все 1С-программисты, ускоряя какие-нибудь длительные запросы с джойнами, тоже почти всегда используют секционирование. Если у вас есть огромная таблица, которую вам нужно сджойнить с другой таблицей по какому-то отбору (допустим, мы просим показать продажи по какому-то списку номенклатур за месяц), это когда-то начинает работать медленно. Всегда было быстро, я ничего не трогал, стало медленно. Таблица выросла, не успевает СУБД нормально дать.

Как обычно строится оптимизация такого запроса? Продажи за период во временную таблицу вытащим, и потом эту временную таблицу сджойним с этой маленькой табличкой номенклатур – все будет быстрее.

Так вот, что вы сделали? Вы динамически создали секцию только во временной таблице.

Частые поиски по разделителю секционирования. Если при поиске не всегда можно попасть в индекс, потому что поиск не всегда может содержать все поля, то да, секционирование тоже поможет. Вы получите большой выигрыш в скорости.

Замедление вставки из-за роста индексов. Важная вещь, поэтому вывел ее сюда. Еще раз обращу внимание, очень неочевидная штука – когда вдруг тормозит insert, может оказаться, что это из-за того, что индексы большие.

Когда у вас все архивные исторические данные физически лежат в других секционированных табличках, а инсерт идет в маленькую табличку, с которой мы работаем, с маленьким индексом – там не будет замедления, она будет быстро работать.

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

Ну и раз уж мы с вами в СУБД залезли – нарушили все, что можно было нарушить в 1С, то если вам нужно удалить данные месяца, просто секционируйте этот месяц в отдельную таблицу. А потом зайдите, drop таблицы сделайте – у вас за долю секунды удалится месяц данных. Стандартным способом вы на удаление данных этого месяца могли потратить месяц.

Распухание индексов в PostgreSQL – секционирование тоже его побеждает практически полностью. Потому что теперь с архивными данными никто не работает, там индексы не пухнут, мы сделали vacuum full, и все, они пухнуть перестали. У нас распухает только текущая табличка, но и то, через месяц она станет архивной, ну или через день, через неделю – смотря как вы настроили секционирование.

Ну и архивные данные. Помимо того, что вы их можете удалять, вы их можете переместить на другой более медленный диск с помощью table space. Так вы освободите эту бедную СХД, которая за всех отвечает и пытается быстро работать.

Когда это зло?

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

*************

Статья написана по итогам доклада (видео), прочитанного на конференции Infostart Event.