{"draftJsState":{"blocks":[{"data":{"image":{"id":"6488577cbfaf86243ef51838"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Естественное состояние в СУБД Postgres","type":"atomic:image","key":"46hdo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Autovacuum в Postgres – конкурентное преимущество или неизбежное зло?","type":"unordered-list-item","key":"9cj7f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Зачем копаться в мусоре если Autovacuum все делает сам?","type":"unordered-list-item","key":"51fsl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Основное правило уборки мусора – быть в нужное время в нужном месте, ловить момент.","type":"unordered-list-item","key":"3mgkn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Как сосредоточиться на бизнес логике, когда вокруг …","type":"unordered-list-item","key":"132os"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Человечество, мой друг, делится на две половины: одни мусорят, а другие убирают. (Из фильма «Первый троллейбус» 1963 г.)","type":"blockquote","key":"dqjed"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Autovacuum в Postgres – конкурентное преимущество или неизбежное зло?","type":"header-two","key":"cghi5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В ИТ вроде есть разделение труда и в теории команда из программиста, администратора СУБД, системного администратора - может решить любые проблемы мозговым штурмом. Вот только в проектировании архитектуры Highload решений это уже не работает, потому что Вам заранее нужно знать, еще до возникновения проблем:","type":"unstyled","key":"2s03b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":2,"style":"ITALIC"}],"text":"· Где будут узкие места Вашего решения?","type":"unstyled","key":"cf3hl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":56,"offset":2,"style":"ITALIC"}],"text":"· Какие слабые места и пределы выбранной платформы и СУБД?","type":"unstyled","key":"e2c5a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"А если Вы заложили архитектурный изъян – никакой agile software development не спасет. Просто попробуйте переделать фундамент под построенным домом. Особенно это актуально для 1С, где rapid application development не располагает задумываться о последствиях. С прототипом долго играться не дадут, поэтому слабые места нужно знать заранее, бить в точку, а значит осознавать весь стек разработки и только тогда будет гештальт.","type":"unstyled","key":"l4hs"},{"data":{},"depth":0,"entityRanges":[{"key":0,"length":19,"offset":188}],"inlineStyleRanges":[],"text":"Если кто-то думает, что в 1С можно ограничится знанием уровня ORM (языка 1С) и не лезть в СУБД , поверьте, Вы даже с администратором СУБД не сможете разговаривать. Один метод .Записать() создает десяток DML и их понимание принципиально влияет на Highload в 1С.","type":"unstyled","key":"eq7li"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Тем у кого 1С на Postgres рано и ли поздно придется тонко настраивать такую странную вещь как Автовакуум.","type":"unstyled","key":"ac270"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Почувствовать настройки Автовакуума в 1С очень просто – подвигайте итоги назад и вперед","type":"unstyled","key":"14a1c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Пример из двухтерабайтной базы 1С Бухгалтерии (регистр Бухгалтерии) двигаем несколько раз назад и вперед, при настройках Autovacuum по умолчанию .","type":"unstyled","key":"83fcs"},{"data":{"image":{"id":"648857fd8db8a36014dd3b51"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Деградация при сдвиге итогов","type":"atomic:image","key":"3m2mn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В основной таблице регистра бухгалтерии «_AccRg – таблица движений регистра бухгалтерии.» всего лишь 177 270 973 сто семьдесят миллионов записей (за пару лет). С ней связано несколько таблиц с итогами.","type":"unstyled","key":"44lsb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"«_AccRgAT0 – таблица остатков и оборотов по счетам и субсчетам.","type":"blockquote","key":"fnfeg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"_AccRgAT – таблица остатков и оборотов по счетам, субсчетам и субконто. Эти таблицы создаются в том случае, если регистр бухгалтерии ссылается на план счетов, у которого максимальное количество субконто больше нуля. Номер i изменяется от 1 до максимального количества субконто.","type":"blockquote","key":"1an4g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"_AccRgCT – таблица итогов оборотов между счетами. Эта таблица создается только для регистра бухгалтерии, поддерживающего корреспонденцию.","type":"blockquote","key":"cd9ic"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"_AccRgOpt – таблица настроек хранения итогов. Эта таблица создается одна на все регистры бухгалтерии.","type":"blockquote","key":"c3vvq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"_AccRgED – таблица значений субконто регистра бухгалтерии. Эта таблица создается в том случае, если регистр бухгалтерии ссылается на план счетов, у которого максимальное количество субконто больше нуля.»","type":"blockquote","key":"b3rmr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В самой большой таблице с итогами по субконто _AccRgAT0 89 927 298 записей – всего в два раза меньше","type":"unstyled","key":"fa7fq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Алгоритм пересчета простой –","type":"unstyled","key":"eq88k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· удаляются старые итоги из _AccRgAT0 _AccRgAT _AccRgCT,","type":"unstyled","key":"2es0a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· берутся итоги за предыдущий период (например март).","type":"unstyled","key":"2slj5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· добавляются суммарные данные из _AccRg (за апрель) и все это записывается в таблицы итогов.","type":"unstyled","key":"pcsq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Все это идеально подходит для возникновения мертвых кортежей в таблицах итогов. А поскольку по таблице итогов делаются запросы за предыдущий период , то важна и актуальность статистики.","type":"unstyled","key":"6v1qu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Деградация производительности идет если двигать итоги за три месяца (13% от объема данной таблицы)","type":"unstyled","key":"8l2ir"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Впечатляет? А это только начало погружения. Как только это увидел, я поправил в Postgres.conf параметры со значений по умолчанию на","type":"unstyled","key":"6um2p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· \"autovacuum_vacuum_scale_factor\" = 0.01","type":"unstyled","key":"d891m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· \"autovacuum_vacuum_threshold\"=50","type":"unstyled","key":"7skl3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· \"autovacuum_naptime\" оставил 20 sec","type":"unstyled","key":"cpclg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"и обрадовался, что деградация прекратилась. Автоваккум запускается каждые 20 секунд, но стал срабатывать при update or delete 1% записей, ранее было 10% . Дальше можно не читать?","type":"unstyled","key":"9pe3s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Если хорошо подумать - В 1С Бухгалтерии больше тысячи таблиц, плюс метаданные, которые отражают специфику компании. Эти параметры в Postgres.conf выглядят как антибиотик с неприятными побочными эффектами, а не как систематическое лечение – о чем будет рассказано ниже.","type":"unstyled","key":"8peg1"},{"data":{},"depth":0,"entityRanges":[{"key":1,"length":103,"offset":244}],"inlineStyleRanges":[{"length":142,"offset":66,"style":"ITALIC"}],"text":"После работы с MS SQL или Oracle database, невольно удивляешься – почему в Postgres так много внимания уделяют очистке таблиц от мертвых кортежей (dead tuples ) и заодно пересбору статистики через Autovacuum? Конечно в руководстве все написано Postgres Pro Enterprise : Документация: 15: 24.1. Регламентная очистка : Компания Postgres Professional , кто-то даже оставит настройки срабатывания по умолчанию, до первого звонка. А когда сравниваешь архитектуры Postgres с MS SQL или Oracle – вопросов становится больше","type":"unstyled","key":"bjukn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Документация объясняет нам необходимость AUTOVACUUM следующим образом","type":"unstyled","key":"2g820"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"«В Postgres Pro команды UPDATE или DELETE не вызывают немедленного удаления старой версии изменяемых строк. Этот подход необходим для реализации эффективного многоверсионного управления конкурентным доступом (MVCC, см. Главу 13): версия строки не должна удаляться до тех пор, пока она остаётся потенциально видимой для других транзакций. Однако в конце концов устаревшая или удалённая версия строки оказывается не нужна ни одной из транзакций. После этого занимаемое ей место должно быть освобождено и может быть отдано новым строкам, во избежание неограниченного роста потребности в дисковом пространстве. Это происходит при выполнении команды VACUUM.»","type":"blockquote","key":"ft4kq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Опытные знают, что ,например, в MS SQL есть понятие ghost record и Вы даже можете увидеть после массового удаления записей вот такую картинку","type":"unstyled","key":"6ht4b"},{"data":{"image":{"id":"6488589d96df344165e60015"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Ghost чистит после удаления с wait ","type":"atomic:image","key":"e881v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":", но этот процесс идет по своей логике и его еще нужно поймать.","type":"unstyled","key":"5ugv2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В Oracle я не видел ничего подобного","type":"unstyled","key":"sla4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Конечно в Oracle и MS SQL тоже есть проблемы с фрагментацией после delete\\update.","type":"unstyled","key":"bl3b9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В Oracle например, ее можно снижать:","type":"unstyled","key":"borms"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· C помощью alter tablespace COALESCE (в Oracle) для объединения свободных экстентов в более крупные","type":"unstyled","key":"1pmlq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· Либо перестройка кластерных индексов","type":"unstyled","key":"cubso"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· Либо экспорт импорт таблицы с сортировкой по кластерному индексу","type":"unstyled","key":"fkuai"},{"data":{},"depth":0,"entityRanges":[{"key":2,"length":31,"offset":111}],"inlineStyleRanges":[],"text":"Эти процедуры в Oracle, MS SQL как правило делаются в технологические окна, когда возникают вот такие эффекты Как влияет фрагментация на план, но в Postgres борьба с мертвыми кортежами это не просто мероприятие, а ключевой шаг в Performance and tuning в режиме Online.","type":"unstyled","key":"cpdib"},{"data":{},"depth":0,"entityRanges":[{"key":3,"length":59,"offset":197}],"inlineStyleRanges":[],"text":"Возникает вопрос – в Oracle тоже есть MVCC и очень давно (в отличии от MS SQL, где его реализовали как дополнительный режим), почему там не нужен AUTOVACUUM? На этот вопрос отвечает хорошая статья MVCC в Oracle и PostgreSQL : Компания Postgres Professional","type":"unstyled","key":"31ofk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Если кратко","type":"unstyled","key":"d28b4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"«Внутренние детали реализации в двух системах существенно отличаются. Постгрес хранит в блоке все варианты строк, которые получаются при их изменении и даже удалении. Снимок определяет, какая именно из имеющихся версий строки должна быть видна. Время от времени блоки очищаются от тех версий, которые больше не видны никому. В Оракле в блоке находятся только актуальные на определенный момент строки, а вместо непосредственного хранения предыдущих вариантов формируется журнал отката. Если блок не соответствует снимку, изменения в нем откатываются с помощью журнала, формируя новую версию этого блока.»","type":"blockquote","key":"iv9v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"То есть нам намекают, что в Postgres MVCC, как бы это сказать «самый многоверсионный». Это тот самый случай, когда лучше разобраться в деталях и самому составить собственное мнение. Например, Microsoft догонял Oracle в MVCC и определил для хранения снимков предыдущих версий TempDB , по соседству с временными таблицами. А у Oracle изначально rollback сегмент заложен в архитектуре, и реализация MS SQL на этом фоне выглядит как костыль.","type":"unstyled","key":"68gk3"},{"data":{},"depth":0,"entityRanges":[{"key":4,"length":53,"offset":190}],"inlineStyleRanges":[],"text":"MS SQL галочку MVCC поставил ? Несомненно, зачем платить больше чем за Oracle? А у Postgres вообще бесплатно, но с AUTOVACUUM … Меньше знаешь крепче спишь? Тогда выбирай сердцем либо читай How SQL Server MVCC compares to Oracle and PostgreSQL. А можно путь попроще, чем разбираться этих джунглях системного программирования?","type":"unstyled","key":"7i5vq"},{"data":{},"depth":0,"entityRanges":[{"key":5,"length":103,"offset":94}],"inlineStyleRanges":[],"text":"Можно – синтетические тесты. Возьмем тест записи в оборотный регистр накопления без агрегатов Postgres как предчувствие. Вычисляем процент импортозамещения в режиме Highload от 1С / Хабр (habr.com) . Я слышал много презрительных высказываний – «это всего лишь синтетический тест», «это очередной синтетический тест».","type":"unstyled","key":"b7vrl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В приведенном тесте был обнаружен факт негативного влияния ANLYZE <временная таблица>. Напомню, что в методе .Записать() ANLYZE <временная таблица занимает от 30% времени, делается независимо от объема записываемых данных. Поскольку регистры накопления используются везде и много все это маштабируется по вашему решению на Postgres с понятным результатом.","type":"unstyled","key":"6efbt"},{"data":{},"depth":0,"entityRanges":[{"key":6,"length":58,"offset":354}],"inlineStyleRanges":[],"text":"Современное приложение не похоже на канализацию, где маленькие трубы идут к большим и Вы на 99% защищены от затора в большой трубе, чтобы соответствующий сервис был доступен здесь и сейчас иначе... В современном приложении приложении Вы можете наткнуться на бутылочное горлышко, где какой-то важный процесс просто сидит только на одном ядре процессора 1С + MS SQL против Матрицы виртуализации / Хабр (habr.com) . Поэтому синтетический тест позволяет сосредоточится на узких местах, которые могут поставить колом всю систему при хорошей нагрузке.","type":"unstyled","key":"1j827"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Зачем копаться в мусоре если Autovacuum все делает сам?","type":"header-two","key":"bkdct"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для синтетического теста регистр накопления с оборотами без агрегатов, проще по структуре регистра Бухгалтерии – в нем есть основная таблица и одна таблица итогов по оборотам","type":"unstyled","key":"8imjo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· _AccumRg – таблица движений регистра накопления.","type":"unstyled","key":"6u0ua"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· AccumRgTn – таблица оборотов регистра накопления. Эта таблица создается, если регистр поддерживает обороты.","type":"unstyled","key":"4bqob"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для регулирования процесса autovacuum нам интересны параметры.","type":"unstyled","key":"b0e7f"},{"data":{},"depth":0,"entityRanges":[{"key":7,"length":93,"offset":0}],"inlineStyleRanges":[],"text":"PostgreSQL : Документация: 15: 20.10. Автоматическая очистка : Компания Postgres Professional","type":"unstyled","key":"2970v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· autovacuum_vacuum_threshold","type":"unstyled","key":"dqhhg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· autovacuum_vacuum_scale_factor","type":"unstyled","key":"c8dp4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"· autovacuum_vacuum_insert_threshold","type":"unstyled","key":"bkvsp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"и поскольку запуск автоваккума попутно делает analyze autovacuum_analyze_scale_factor","type":"unstyled","key":"4iega"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Они могут задаваться как для всего экземпляра Postgres там и для отдельных таблиц. Первый вариант не очень хороший поскольку действует на все, особенно для баз 1С, где в типовой конфигурации более 1000 таблиц.","type":"unstyled","key":"atji4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Я не верю, что можно подобрать общие параметры autovacuum оптимальные для всех таблиц субд, даже если указывать это процентом:","type":"unstyled","key":"9hlai"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Во-первых он забирает ресурсы – и чем чаще срабатывает тем больше. Помним это не только очистка мертвых кортежей, но и обновление статистики.","type":"unstyled","key":"9tp5t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Во-вторых в 1С есть бизнес цепочка Документ (включая подчиненные) – Движения в регистры ( как правило несколько регистров) – Обновление итогов по регистрам . Узкое место в каком либо звене может замедлить весь хвост цепочки.","type":"unstyled","key":"cvtoc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В третьих указание процента autovacuum_vacuum_scale_factor = 1% предполагает, что дневные объемы со временем увеличиваются и превосходят 1% , по мере роста объема всей таблицы. Иначе в течении дня автовакуум по таблице не сработает.","type":"unstyled","key":"f6s6a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Поэтому лучше указывать на уровне отдельных больших таблиц и не доверять процентам.","type":"unstyled","key":"f8v4j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Основное правило уборки мусора – быть в нужное время в нужном месте, ловить момент.","type":"header-two","key":"20la"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Что такое autovacuum_vacuum_scale_factor =1%, для таблицы с сотнями миллионов записей? Это несколько миллионов, которые могут появиться в течении дня. А если их будет чуть меньше и Autovacuum не сработает до достижения порога - мы получим торможение дневных отчетов. И чем больше таблица даже с равномерным наполнением, тем больше фактический порог срабатывания.","type":"unstyled","key":"oen5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"К слову, В MS SQL для решения проблем актуализации статистики есть флаг трассировки -T2371 именно по этим причинам.","type":"unstyled","key":"dcj0r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Для таблиц с миллионом записей – один процент это всего лишь 10 000 записей,и при начальном заполнении порог срабатывания будет более частым.","type":"unstyled","key":"911jm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В общем имеем бытовую классику: cлишком рано уберешь мусор - это никто не заметит и много усилий потрачено, слишком поздно - скажут срач развел. А корень зла – в организации пространства и процессов, которые этот мусор провоцируют т.е. в архитектуре СУБД. А у Вас на рабочем столе разве не так?","type":"unstyled","key":"dmrdf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"К счастью, в Postgres есть настройки autovacuum на уровне таблиц. Для 1С их нужно запоминать в скрипт обновления, поскольку при реструктуризации таблиц 1С с большой вероятностью пересоздаст таблицу с настройками по умолчанию и все Ваши труды уйдут с DROP старой TABLE включая распределение по Tablespaces.","type":"unstyled","key":"7pvam"},{"data":{},"depth":0,"entityRanges":[{"key":8,"length":103,"offset":50}],"inlineStyleRanges":[],"text":"Возьмем знакомую нам из нагрузочного тестирования Postgres как предчувствие. Вычисляем процент импортозамещения в режиме Highload от 1С / Хабр (habr.com) запись в оборотный регистр накопления с итогами. Все условия синтетического теста можете посмотреть в этой статье. Здесь контур немного другой, но сопоставимый по параметрам. Postgres на Oracle unix.","type":"unstyled","key":"6o5bb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Сначала выключим Autovacuum, запустим тест несколько раз, и дождемся эффекта деградации – он приходит достаточно быстро. Мы просто моделируем ситуацию, когда автовакуум на таблице не срабатывает вовремя.","type":"unstyled","key":"2tvim"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Это делается командой:","type":"unstyled","key":"b5ukh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ALTER TABLE _accumrg16920 SET ( autovacuum_enabled = off)","type":"unstyled","key":"4km2p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ALTER TABLE _accumrgtn17037 SET (autovacuum_enabled = off)","type":"unstyled","key":"3lav1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Смотрим результат в pg_stat_all_tables","type":"unstyled","key":"7brfe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"В колонке n_dead_tup виден объем проблемы. Не обращайте внимание на значение n_life_tup это не тоже самое, что count(*) и это описано в документации. Так же видно, что автовакуум не срабатывал","type":"unstyled","key":"51er2"},{"data":{"image":{"id":"648858da6d418274f9b7c161"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Автовакуум не срабатывает","type":"atomic:image","key":"5pjha"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Теперь посмотрим на что ушло время. Про проблемы с analyze и fasttruncate было написано в статье выше. Хуже другое некоторые Update поднялись в топ, чего раньше не было. Можно конечно и планы посмотреть и поизучать детально, но лучше всего","type":"unstyled","key":"br33n"},{"data":{"image":{"id":"648858f4b07a1c67104a3fcf"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DML по времени стали медленней и идут в топ","type":"atomic:image","key":"4hk83"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Смотрим загрузку CPU - она зашкаливает за 80% против 40% когда автовакуум срабатывает.","type":"unstyled","key":"eunoo"},{"data":{"image":{"id":"648859096526147a9b45fe94"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Загрузка ЦПУ увеличилась, а производительность уменьшилась","type":"atomic:image","key":"dnkk2"},{"data":{},"depth":0,"entityRanges":[{"key":9,"length":64,"offset":451}],"inlineStyleRanges":[{"length":98,"offset":0,"style":"ITALIC"}],"text":"Есть интересный вопрос – от чего идет деградация? От мертвых кортежей или неактуальной статистики? Ответ не такой очевидный – поскольку в теории оптимизатор планов запросов СУБД должен работать не только по статистике, а в первую очередь по имеющимся индексам. Но современные оптимизаторы слишком пренебрежительно относятся к индексам и как следствие к архитектору,и больше ориентируются на статистику – вот такой сложной стала их логика. Вот пример Селективный индекс от 1С — что выберет MS SQL? / Хабр (habr.com)","type":"unstyled","key":"d5pga"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Поскольку автоваккум одновременно чистит и обновляет статистику нам это сейчас не так важно.","type":"unstyled","key":"6o3ln"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Поставим другие параметры таблиц","type":"unstyled","key":"msvo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ALTER TABLE _accumrg16920 SET ( autovacuum_enabled = on, autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 10000, autovacuum_analyze_scale_factor =0 , autovacuum_analyze_threshold = 10000 );","type":"unstyled","key":"f152l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ALTER TABLE _accumrgtn17037 SET (autovacuum_enabled = on, autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 2000, autovacuum_analyze_scale_factor =0 , autovacuum_analyze_threshold = 2000 );","type":"unstyled","key":"2sfd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Здесь я игнорирую scale_factor чтобы не зависеть от процента, о чем я написал выше.","type":"unstyled","key":"2tree"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Threshold подобрал экспериментально, запуская синтетический тест. Это не значит что автовакумм будет запускаться каждые 10000 или 2000 записей, он будет запускаться согласно autovacuum_naptime (у меня 20 секунд), и срабатывать если превысили порог очистки = базовый порог очистки + коэффициент доли для очистки * количество кортежей","type":"unstyled","key":"908m1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"где базовый порог очистки — значение autovacuum_vacuum_threshold, коэффициент доли — autovacuum_vacuum_scale_factor, а количество кортежей — pg_class.reltuples .","type":"unstyled","key":"1uitb"},{"data":{},"depth":0,"entityRanges":[{"key":10,"length":90,"offset":76}],"inlineStyleRanges":[],"text":"На самом деле там еще больше деталей, которые можно почитать в документации PostgreSQL : Документация: 15: 25.1. Регламентная очистка : Компания Postgres Professional . Можно увеличить кодичество процессов автоочистки, и разный другой тюнинг, но это все приведет к загрузке CPU и как следствие преждевременому исчерпанию ресурсов одного Instance. Напомню, что в Postgres нет возможности маштабировать Instance на несколько серверов как в Oracle Real application cluster.","type":"unstyled","key":"hltr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"А теперь можно посмотреть на результат:","type":"unstyled","key":"bs2n1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Автовакуум срабатывает – мертвых кортежей нет, статистика акуализируется","type":"unstyled","key":"autdm"},{"data":{"image":{"id":"6488593396df344165e72a73"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Автоваккум работает","type":"atomic:image","key":"207ur"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Топ DML операторов изменился, в нем опять Fast truncate и Analyze временных таблиц . Почему 1С делает analyze на временные таблицы пока вопрос риторический.","type":"unstyled","key":"fefcp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Все остальные DML операторы ушли вниз по списку, total_time тоже стал улучшаться","type":"unstyled","key":"cfjcq"},{"data":{"image":{"id":"6488594a9506570f871457a0"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DML стали занимать меньше времени","type":"atomic:image","key":"cnk7o"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Использование CPU уменьшается","type":"unstyled","key":"6h6q7"},{"data":{"image":{"id":"648859636d418274f9b8fb84"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Загрузка ЦПУ стала приемлемой для Postgres","type":"atomic:image","key":"45e2c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Вот так все просто, на отдельно взятой операции. Синтетический тест, синтетическая статистика.","type":"unstyled","key":"14gjd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Как сосредоточиться на бизнес логике, когда вокруг …","type":"header-two","key":"epruf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"За уборкой мусора легко забыть, что вся разработка в 1С делается ради реализации бизнес логики, которая программируется в 1С на уровне языка\\библиотек 1С (фактически ORM) . Текущие дела становятся важными и срочными именно из-за таких кейсов с мусором. И изучение полного стека мера больше вынужденная, поскольку последствия непонимания видны даже на слабом синтетическом тесте. В 1С тысячи таблиц, и даже если устанавливать параметры в паре десятков самых больших, это тоже работа.","type":"unstyled","key":"f9he6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Возможно есть какие-то альтернативные средства в Postgres которые это берут на себя, кто знает?","type":"unstyled","key":"akq6h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":694,"style":"BOLD"}],"text":"Сейчас хайповая тема это GhatGTP и другие темы исскуственного интеллекта, где он он в уборке мусора? Там есть место красивым алгоритмам, но капиталистическая экономика устроена по другому – больше функционала, больше потребителей, больше денег. «Экономика должна быть экономной» - умерло вместе с СССР. Остановить это может только высокая себестоимость поддержки СУБД и специалистов, понимающих , как заставить СУБД эффективно работать. Системные администраторы и программисты 1С не первые в пищевой цепочке, поэтому прихода ИИ в СУБД придется подождать. А пока даже для робота пылесоса Вы должны навести порядок в комнате, так что бы ему было удобно. До новых встреч на нашем телеграмм канале t.me/Chat1CUnlimited , новые материалы ждут очереди, когда их напишут.","type":"unstyled","key":"4eak0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"\n","type":"unstyled","key":"c664h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"marker","key":"7sRTSOjLBJHQtx4yjFZa"}],"entityMap":{"0":{"data":{"href":"https://docs.google.com/spreadsheets/d/1mRpyK4YsdH20kk3ULYW5qOlgh0MXwTC1LQcCaNA83zE/edit?usp=drive_link","url":"https://docs.google.com/spreadsheets/d/1mRpyK4YsdH20kk3ULYW5qOlgh0MXwTC1LQcCaNA83zE/edit?usp=drive_link"},"mutability":"MUTABLE","type":"LINK"},"1":{"data":{"href":"https://postgrespro.ru/docs/enterprise/15/routine-vacuuming","url":"https://postgrespro.ru/docs/enterprise/15/routine-vacuuming"},"mutability":"MUTABLE","type":"LINK"},"2":{"data":{"href":"https://habr.com/ru/articles/691326/#comment_24795074","url":"https://habr.com/ru/articles/691326/#comment_24795074"},"mutability":"MUTABLE","type":"LINK"},"3":{"data":{"href":"https://postgrespro.ru/blog/pgsql/17758?ysclid=li0fmqltz2131804075","url":"https://postgrespro.ru/blog/pgsql/17758?ysclid=li0fmqltz2131804075"},"mutability":"MUTABLE","type":"LINK"},"4":{"data":{"href":"https://www.dbi-services.com/blog/how-sql-server-mvcc-compares-to-oracle-and-postgresql/#:~:text=Microsoft%20SQL%20Server%20has%20implemented,I%20in%20ACID)%20in%20OLTP.","url":"https://www.dbi-services.com/blog/how-sql-server-mvcc-compares-to-oracle-and-postgresql/#:~:text=Microsoft%20SQL%20Server%20has%20implemented,I%20in%20ACID)%20in%20OLTP."},"mutability":"MUTABLE","type":"LINK"},"5":{"data":{"href":"https://habr.com/ru/articles/723642/","url":"https://habr.com/ru/articles/723642/"},"mutability":"MUTABLE","type":"LINK"},"6":{"data":{"href":"https://habr.com/ru/articles/675398/","url":"https://habr.com/ru/articles/675398/"},"mutability":"MUTABLE","type":"LINK"},"7":{"data":{"href":"https://postgrespro.ru/docs/postgresql/15/runtime-config-autovacuum","url":"https://postgrespro.ru/docs/postgresql/15/runtime-config-autovacuum"},"mutability":"MUTABLE","type":"LINK"},"8":{"data":{"href":"https://habr.com/ru/articles/723642/","url":"https://habr.com/ru/articles/723642/"},"mutability":"MUTABLE","type":"LINK"},"9":{"data":{"href":"https://habr.com/ru/articles/691326/","url":"https://habr.com/ru/articles/691326/"},"mutability":"MUTABLE","type":"LINK"},"10":{"data":{"href":"https://postgrespro.ru/docs/postgresql/15/routine-vacuuming","url":"https://postgrespro.ru/docs/postgresql/15/routine-vacuuming"},"mutability":"MUTABLE","type":"LINK"}}},"mentions":[]}