{"draftJsState":{"blocks":[{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"70qcg"},{"data":{"image":{"id":"694a487ebf1b0901441fc6a5"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Когда данные задыхаются в очередях: как разгрузить диск, снять блокировки и вернуть скорость системе","type":"atomic:image","key":"a5oe0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"fnmtn"},{"data":{},"depth":0,"entityRanges":[{"key":0,"length":9,"offset":0},{"key":1,"length":110,"offset":9}],"inlineStyleRanges":[{"length":9,"offset":0,"style":"BOLD"}],"text":"GitHub - Комплекс pg_expecto для статистического анализа производительности и нагрузочного тестирования СУБД PostgreSQL","type":"unstyled","key":"3akdp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":11,"offset":0,"style":"BOLD"}],"text":"Предисловие","type":"header-two","key":"eplis"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Статья посвящена комплексному анализу производительности СУБД PostgreSQL, основанному на данных мониторинга и статистики. В ходе анализа выявлены ключевые проблемы: перегруженность дисковой подсистемы, contention блокировок, неоптимальное использование памяти и конфигурационные ошибки. В итоге сформированы практические рекомендации, сгруппированные по приоритетам и срокам внедрения, которые помогут стабилизировать и значительно улучшить работу базы данных.","type":"unstyled","key":"4lrqk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Исходные данные","type":"header-two","key":"agnei"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Регрессионный и корреляционный анализ ","type":"header-three","key":"dfka3"},{"data":{"image":{"id":"694a30b67d3c3333c97f2344"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"5o6jr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Операционная скорость ","type":"header-three","key":"9hmn5"},{"data":{"image":{"id":"694a310a8be99719b1a681f2"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"График изменения операционной скорости в течении часа до инцидента","type":"atomic:image","key":"3vln1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Ожидания СУБД","type":"header-three","key":"57thj"},{"data":{"image":{"id":"694a31738be99719b1a6eae7"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"График изменения ожиданий СУБД в течении часа до инцидента","type":"atomic:image","key":"1fs66"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Ожидания типа IO","type":"unstyled","key":"7p9f5"},{"data":{"image":{"id":"694a31d2a9a9f95b4b231261"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"График изменения ожиданий типа IO в течении часа до инцидента","type":"atomic:image","key":"a6ch0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":17,"offset":0,"style":"BOLD"}],"text":"Ожидания типа IPC","type":"unstyled","key":"cm9kh"},{"data":{"image":{"id":"694a3225b2debd7a8b80d1e6"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"График изменения ожиданий типа IOC в течении часа до инцидента","type":"atomic:image","key":"40crr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Ожидания типа Lock","type":"unstyled","key":"agrdt"},{"data":{"image":{"id":"694a327b64be014777db5903"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"График изменения ожиданий типа Lock в течении часа до инцидента","type":"atomic:image","key":"6skmr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Ожидания типа LWLock","type":"unstyled","key":"9njnp"},{"data":{"image":{"id":"694a32c969c9a35b7ffa2c19"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"График изменения ожиданий типа LWLock в течении часа до инцидента","type":"atomic:image","key":"7oilb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"7vlmd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":44,"offset":0,"style":"BOLD"}],"text":"1. Сводный анализ проблем производительности","type":"header-two","key":"c4mt3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"1a71j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":5,"style":"BOLD"}],"text":"1.1. ТОП-3 ПРОБЛЕМЫ ПРОИЗВОДИТЕЛЬНОСТИ","type":"header-two","key":"c1fc4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":54,"offset":2,"style":"BOLD"}],"text":"1.1.1. Критическая нагрузка на дисковую подсистему /data","type":"header-three","key":"4bd9e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Метрики: Утилизация дисков vdh/vdi/vdj/vdk >78-85% (ALARM: 100% наблюдений >50%)","type":"unordered-list-item","key":"afgfi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":0,"style":"BOLD"}],"text":"Глубина очереди: aqu_sz > 1.6-2.0 (ALARM: 100% наблюдений >1)","type":"unordered-list-item","key":"e9s36"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Корреляция: WAITINGS-IO = 0.7540 (высокая), IO-wa = 0.2156 (средняя)","type":"unordered-list-item","key":"rk4i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Причина: Чрезмерные операции чтения (10-12k r/s) и записи (600-735 w/s) на диск","type":"unordered-list-item","key":"cukpu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":53,"offset":2,"style":"BOLD"}],"text":"1.1.2. Экстремальная конкуренция за блокировки (LWLock)","type":"header-three","key":"hs2r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Метрики: Корреляция WAITINGS-LWLOCK = 0.9215 (очень высокая)","type":"unordered-list-item","key":"31bn7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Основные события: BufferMapping (12.26%), LockManager (4.68%), MultiXactGen (3.75%)","type":"unordered-list-item","key":"b7ect"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Причина: Высокая конкурентная нагрузка на общие структуры данных СУБД","type":"unordered-list-item","key":"7o003"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":67,"offset":2,"style":"BOLD"}],"text":"1.1.3. Недостаточное использование оперативной памяти для кэширования","type":"header-three","key":"d39hm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Метрики: Свободная RAM <5% (ALARM: 100% наблюдений)","type":"unordered-list-item","key":"2tamr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":0,"style":"BOLD"}],"text":"Проблема: memory_cache ~707 ГБ, но корреляция cache-r/s = 0.8581 (очень высокая)","type":"unordered-list-item","key":"7d5gj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Причина: Кэш ОС неэффективно снижает нагрузку на диск","type":"unordered-list-item","key":"7tncq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":38,"offset":5,"style":"BOLD"}],"text":"1.2. КОРРЕЛЯЦИОННЫЙ АНАЛИЗ ВЛИЯНИЯ НА SPEED","type":"header-two","key":"73tao"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":49,"offset":0,"style":"BOLD"}],"text":"Отрицательная корреляция SPEED-WAITINGS = -0.8148 (при росте ожиданий скорость падает)","type":"unstyled","key":"2ifa8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Наиболее влияющие типы ожиданий (по силе корреляции с WAITINGS):","type":"unstyled","key":"e5g1p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":6,"offset":0,"style":"BOLD"}],"text":"LWLock: 0.9215 (критическое влияние) — конкуренция за внутренние блокировки","type":"ordered-list-item","key":"8onl4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":3,"offset":0,"style":"BOLD"}],"text":"IPC: 0.8181 (высокое влияние) — межпроцессное взаимодействие","type":"ordered-list-item","key":"6aufe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":4,"offset":0,"style":"BOLD"}],"text":"Lock: 0.7546 (высокое влияние) — блокировки объектов БД","type":"ordered-list-item","key":"318jj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":2,"offset":0,"style":"BOLD"}],"text":"IO: 0.7540 (высокое влияние) — операции ввода/вывода","type":"ordered-list-item","key":"fkmd1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"},{"length":18,"offset":21,"style":"BOLD"}],"text":"BUFFERPIN (0.4369) и EXTENSION (0.2841) имеют умеренное влияние.","type":"unstyled","key":"3qsvj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":5,"style":"BOLD"}],"text":"1.3. ИНФРАСТРУКТУРНЫЕ ОГРАНИЧЕНИЯ","type":"header-two","key":"nmkn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":50,"offset":2,"style":"BOLD"}],"text":"1.3.1. Дисковая подсистема — КРИТИЧЕСКОЕ ОГРАНИЧЕНИЕ","type":"header-three","key":"b3107"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"/data (vdh/vdi/vdj/vdk): утилизация 78-85%, очередь 1.6-2.0","type":"unordered-list-item","key":"djgjm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Пропускная способность: чтение 82-112 MB/s, запись 5.3-5.9 MB/s","type":"unordered-list-item","key":"d581v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":0,"style":"BOLD"}],"text":"Проблема: Диски не справляются с нагрузкой, вызывая каскадные проблемы","type":"unordered-list-item","key":"fbjc7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":50,"offset":2,"style":"BOLD"}],"text":"1.3.2. Оперативная память — СУЩЕСТВЕННОЕ ОГРАНИЧЕНИЕ","type":"header-three","key":"svja"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Использование: 1007 ГБ RAM, но свободно <14 ГБ (<1.4%)","type":"unordered-list-item","key":"8oqsa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"},{"length":8,"offset":65,"style":"BOLD"}],"text":"Распределение:\nКэш ОС: ~707 ГБ (70% RAM)\nБуферы: ~1.5 ГБ (0.15%)\nПроблема: Неоптимальное распределение между кэшем ОС и буферами PostgreSQL","type":"unordered-list-item","key":"8hdbe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":2,"style":"BOLD"}],"text":"1.3.3. CPU — НЕТ ЯВНЫХ ОГРАНИЧЕНИЙ","type":"header-three","key":"ccqdj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Использование: user 30%, system 6%, I/O wait 2-3%","type":"unordered-list-item","key":"b31r5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Очередь: 69-71 процессов при 192 ядрах (36-37% загрузки)","type":"unordered-list-item","key":"e0rj3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":6,"offset":0,"style":"BOLD"}],"text":"Резерв: Значительный запас мощности процессора","type":"unordered-list-item","key":"1b2lk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":36,"offset":5,"style":"BOLD"}],"text":"1.4. КОНФИГУРАЦИОННЫЕ ПРОБЛЕМЫ POSTGRESQL","type":"header-two","key":"cjbr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":47,"offset":0,"style":"BOLD"}],"text":"Критические параметры, требующие корректировки:","type":"header-three","key":"4dvk6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"random_page_cost = 1.1 — слишком низко для дисков (даже для SSD)","type":"ordered-list-item","key":"4tdmq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"effective_io_concurrency = 300 — чрезмерно высокое для текущей дисковой подсистемы","type":"ordered-list-item","key":"4ibb4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"max_parallel_workers_per_gather = 0 — отключен параллелизм при 192 ядрах","type":"ordered-list-item","key":"4h25q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"work_mem = 1GB — опасно высокое при многих одновременных сессиях","type":"ordered-list-item","key":"9kul3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"shared_buffers = 251807MB (~246GB) — возможно чрезмерно для данной нагрузки","type":"ordered-list-item","key":"bop0b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"autovacuum_vacuum_scale_factor = 0.001 — слишком агрессивный автовакуум","type":"ordered-list-item","key":"64j62"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"commit_delay = 1000 — очень высокое значение, может увеличивать задержки","type":"ordered-list-item","key":"jbrp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":38,"offset":0,"style":"BOLD"}],"text":"Потенциально проблемные настройки WAL:","type":"header-three","key":"56u1i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"wal_keep_size = 500GB — огромный объем WAL файлов","type":"unordered-list-item","key":"djqvb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"checkpoint_timeout = 15min + max_wal_size = 8GB — возможна интенсивная запись WAL","type":"unordered-list-item","key":"a1l6i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":39,"offset":5,"style":"BOLD"}],"text":"1.5. РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ (ПРИОРИТЕТ)","type":"header-two","key":"fmst5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"}],"text":"ВЫСОКИЙ ПРИОРИТЕТ (1-2 дня):","type":"header-three","key":"aehhf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Оптимизация дисковой подсистемы:\nПроверить RAID-конфигурацию дисков /data\nОптимизировать файловую систему (noatime, nobarrier)\nРассмотреть разделение hot/cold данных","type":"ordered-list-item","key":"dqcs7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":43,"offset":0,"style":"BOLD"},{"length":20,"offset":217,"style":"ITALIC"}],"text":"Коррекция критических параметров PostgreSQL:sqlALTER SYSTEM SET random_page_cost = 2.0;\nALTER SYSTEM SET effective_io_concurrency = 32;\nALTER SYSTEM SET work_mem = '128MB';\nALTER SYSTEM SET shared_buffers = '128GB'; -- уменьшить с 246GB\nALTER SYSTEM SET max_parallel_workers_per_gather = 4;\n","type":"ordered-list-item","key":"7o7se"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"Оптимизация памяти ОС:\nНастроить vm.swappiness = 1\nУвеличить vm.dirty_background_ratio = 5\nУменьшить vm.dirty_ratio = 10","type":"ordered-list-item","key":"6e5nh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"СРЕДНИЙ ПРИОРИТЕТ (1-2 недели):","type":"header-three","key":"4s5oo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Оптимизация проблемных запросов:\nСфокусироваться на топ-20 QUERYID из Pareto-анализа\nОсобое внимание запросам с QUERYID: 940651181422038149, -1757223094415174739, 3548304247151540986","type":"ordered-list-item","key":"82mvh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"Настройка автовакуума:sqlALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;\nALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.1;\nALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms';\n","type":"ordered-list-item","key":"5s6a9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"Мониторинг блокировок:\nВнедрить мониторинг pg_locks и pg_stat_activity\nНастроить alert при длительных блокировках","type":"ordered-list-item","key":"ep3s4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"НИЗКИЙ ПРИОРИТЕТ (1 месяц):","type":"header-three","key":"3nt1t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"Архитектурные улучшения:\nРассмотреть partitioning для крупных таблиц\nОптимизация индексов (возможно, избыточные/неиспользуемые)","type":"ordered-list-item","key":"esuuu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Аппаратные улучшения:\nРассмотреть NVMe диски для /data\nУвеличить RAM для лучшего кэширования","type":"ordered-list-item","key":"adpbg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"},{"length":21,"offset":168,"style":"ITALIC"}],"text":"Настройка расширенных параметров:sqlALTER SYSTEM SET commit_delay = 100;\nALTER SYSTEM SET checkpoint_completion_target = 0.7;\nALTER SYSTEM SET wal_keep_size = '64GB'; -- уменьшить с 500GB\n","type":"ordered-list-item","key":"9rnjd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"МОНИТОРИНГ ПОСЛЕ ИЗМЕНЕНИЙ:","type":"header-three","key":"5vi1e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"BOLD"}],"text":"Ключевые метрики для отслеживания:","type":"unstyled","key":"54a1h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Утилизация дисков (%util) — целевое <70%","type":"ordered-list-item","key":"8la91"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Длина очереди (aqu_sz) — целевое <1","type":"ordered-list-item","key":"duqb0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WAITINGS-LWLOCK корреляция — целевое <0.7","type":"ordered-list-item","key":"4n1km"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Свободная RAM — целевое >5%","type":"ordered-list-item","key":"c9mmq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":14,"offset":0,"style":"BOLD"}],"text":"Предупреждение: Изменения вносить постепенно, с мониторингом после каждого шага. Начать с оптимизации дисковой подсистемы и корректировки random_page_cost/effective_io_concurrency, так как это даст наиболее быстрый эффект.","type":"unstyled","key":"5096b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"dsd6m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":42,"offset":0,"style":"BOLD"}],"text":"2. Анализ дисковой подсистемы и IO-проблем","type":"header-two","key":"5k25i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"2778j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"2.1. ПРОБЛЕМНЫЕ ДИСКИ","type":"header-three","key":"cpbh"},{"data":{"image":{"id":"694a37368970911ed46c9646"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"34atd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":5,"offset":0,"style":"BOLD"},{"length":21,"offset":54,"style":"BOLD"}],"text":"ВЫВОД: Все 4 диска файловой системы /data находятся в критическом состоянии:","type":"unstyled","key":"dufio"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Утилизация постоянно >75% (ALARM: 100% наблюдений >50%)","type":"unordered-list-item","key":"am4of"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Длина очереди >1.5 (ALARM: 100% наблюдений >1)","type":"unordered-list-item","key":"9q70d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Высокая корреляция с WAITINGS IO (0.7540)","type":"unordered-list-item","key":"ear25"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":35,"offset":0,"style":"BOLD"}],"text":"WAL диск (vdg) в отличном состоянии:","type":"unstyled","key":"lii6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Утилизация <10%","type":"unordered-list-item","key":"9uspm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Очередь 0.1","type":"unordered-list-item","key":"4o80j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Специализированный WAL диск работает оптимально","type":"unordered-list-item","key":"ac8v"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"85t1c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"}],"text":"2.2. ПАТТЕРНЫ ДОСТУПА К ДИСКАМ","type":"header-three","key":"cptsl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Для дисков /data (vdh-vdk):","type":"header-three","key":"5cf66"},{"data":{"image":{"id":"694a37a269c9a35b7fff5060"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"4ea8j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"rn2m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Для WAL диска (vdg):","type":"header-three","key":"7vi1u"},{"data":{"image":{"id":"694a37d2e1c22307245c476d"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"dfdd9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"ar8oc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"КЛЮЧЕВЫЕ НАБЛЮДЕНИЯ:","type":"header-three","key":"1kpiq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":39,"offset":0,"style":"BOLD"},{"length":48,"offset":84,"style":"BOLD"}],"text":"Аномально низкий r_await (0.15-0.16 мс) при высокой утилизации диска - указывает на эффективное кэширование на уровне контроллера/ОС, но не снимает проблему очередей","type":"ordered-list-item","key":"1eck9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":26,"offset":0,"style":"BOLD"}],"text":"Высокий w_await (17-19 мс) - дисковая подсистема не справляется с синхронной записью","type":"ordered-list-item","key":"6qnoi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"},{"length":48,"offset":47,"style":"BOLD"}],"text":"Мелкие операции чтения (8К) - типичный паттерн случайного доступа к индексам/небольшим таблицам","type":"ordered-list-item","key":"deb89"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Операции записи по 22К - типичный размер 2-3 страниц PostgreSQL (8К каждая)","type":"ordered-list-item","key":"dbana"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"79d40"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":49,"offset":0,"style":"BOLD"}],"text":"2.3. ВЛИЯНИЕ ПАРАМЕТРОВ POSTGRESQL НА IO-НАГРУЗКУ","type":"header-three","key":"bs67t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"Проблемные настройки:","type":"header-three","key":"dghis"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":23,"style":"BOLD"},{"length":9,"offset":95,"style":"BOLD"},{"length":10,"offset":172,"style":"BOLD"}],"text":"random_page_cost = 1.1\nТекущее влияние: Планировщик считает случайные чтения почти бесплатными\nРезультат: Чрезмерное использование индексных сканов вместо последовательных\nСвязь с IO: Высокий r/s (9-12k операций чтения в секунду)","type":"ordered-list-item","key":"ct3bl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":31,"style":"BOLD"},{"length":9,"offset":107,"style":"BOLD"},{"length":10,"offset":181,"style":"BOLD"}],"text":"effective_io_concurrency = 300\nТекущее влияние: PostgreSQL пытается выполнять 300 параллельных IO-операций\nРезультат: Перегрузка дисковой подсистемы, большие очереди (aqu_sz > 1.5)\nСвязь с IO: Высокая утилизация дисков (>80%)","type":"ordered-list-item","key":"bvd0p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":35,"style":"BOLD"},{"length":9,"offset":95,"style":"BOLD"},{"length":10,"offset":188,"style":"BOLD"}],"text":"shared_buffers = 251807MB (~246GB)\nТекущее влияние: 25% от 1ТБ RAM выделено под shared buffers\nРезультат: Недостаток памяти для кэша ОС, что видно по высокой корреляции cache-r/s (0.8581)\nСвязь с IO: Кэш ОС неэффективен, много операций идут на диск","type":"ordered-list-item","key":"dg3f7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":15,"style":"BOLD"},{"length":9,"offset":97,"style":"BOLD"},{"length":10,"offset":162,"style":"BOLD"}],"text":"work_mem = 1GB\nТекущее влияние: Каждая операция сортировки/хеширования может использовать до 1ГБ\nРезультат: Потенциальное использование дисковых временных файлов\nСвязь с IO: Может увеличивать нагрузку на запись","type":"ordered-list-item","key":"bd383"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":35,"style":"BOLD"},{"length":9,"offset":101,"style":"BOLD"},{"length":10,"offset":160,"style":"BOLD"}],"text":"checkpoint_completion_target = 0.9\nТекущее влияние: Контрольные точки растягиваются на 90% интервала\nРезультат: Более равномерная, но постоянная фоновая запись\nСвязь с IO: Поддерживает постоянную нагрузку на запись","type":"ordered-list-item","key":"fcet9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"Дополнительные факторы:","type":"header-three","key":"631rm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":3,"offset":18,"style":"BOLD"}],"text":"max_wal_size = 8GB + checkpoint_timeout = 15min - частые контрольные точки","type":"unordered-list-item","key":"6kah"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"wal_keep_size = 500GB - огромный объем WAL файлов на диске","type":"unordered-list-item","key":"eide5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"autovacuum_vacuum_scale_factor = 0.001 - очень агрессивный автовакуум","type":"unordered-list-item","key":"a34i2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"euf3c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"2.4. ОПТИМИЗАЦИИ","type":"header-three","key":"8533r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":56,"offset":0,"style":"BOLD"}],"text":"2.4.1. Настройки PostgreSQL для снижения IO (немедленно)","type":"header-three","key":"dpiuf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"ITALIC"},{"length":45,"offset":83,"style":"ITALIC"},{"length":21,"offset":178,"style":"ITALIC"},{"length":29,"offset":244,"style":"ITALIC"},{"length":35,"offset":275,"style":"ITALIC"},{"length":18,"offset":349,"style":"ITALIC"},{"length":39,"offset":422,"style":"ITALIC"},{"length":39,"offset":503,"style":"ITALIC"},{"length":20,"offset":585,"style":"ITALIC"},{"length":29,"offset":607,"style":"ITALIC"},{"length":33,"offset":687,"style":"ITALIC"},{"length":20,"offset":817,"style":"ITALIC"}],"text":"-- КРИТИЧЕСКИЕ ИЗМЕНЕНИЯ (1-й приоритет)\nALTER SYSTEM SET random_page_cost = 4.0; -- Более реалистично для перегруженных дисков\nALTER SYSTEM SET effective_io_concurrency = 32; -- Уменьшить в 10 раз\nALTER SYSTEM SET shared_buffers = '128GB'; -- Уменьшить с 246GB до 128GB\n\n-- ВАЖНЫЕ ИЗМЕНЕНИЯ (2-й приоритет)\nALTER SYSTEM SET work_mem = '128MB'; -- Уменьшить с 1GB\nALTER SYSTEM SET checkpoint_completion_target = 0.7; -- Меньше растягивать контрольные точки\nALTER SYSTEM SET max_wal_size = '16GB'; -- Увеличить для реже контрольных точек\nALTER SYSTEM SET wal_keep_size = '64GB'; -- Уменьшить с 500GB\n\n-- ДОПОЛНИТЕЛЬНЫЕ ОПТИМИЗАЦИИ\nALTER SYSTEM SET effective_cache_size = '600GB'; -- Более реалистично (из 1ТБ RAM)\nALTER SYSTEM SET min_wal_size = '4GB';\nALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05; -- Менее агрессивный","type":"unstyled","key":"f9mlt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":52,"offset":0,"style":"BOLD"}],"text":"2.4.2. Рекомендации по файловой системе/монтированию","type":"header-three","key":"25op9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Для /data (vdh-vdk):","type":"unstyled","key":"f6cq7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"ITALIC"},{"length":15,"offset":80,"style":"ITALIC"}],"text":"# В /etc/fstab добавить опции:\nnoatime,nodiratime,data=writeback,barrier=0,nobh\n# ИЛИ для ext4:\nnoatime,nodiratime,data=writeback,barrier=0","type":"unstyled","key":"13n11"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":0,"style":"BOLD"}],"text":"Для /wal (vdg):","type":"unstyled","key":"d669n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":47,"offset":0,"style":"ITALIC"}],"text":"# WAL диск уже хорошо работает, можно добавить:\nnoatime,nodiratime","type":"unstyled","key":"92ln8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"Настройки ядра Linux:","type":"unstyled","key":"c6dbl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"ITALIC"}],"text":"# В /etc/sysctl.conf\nvm.dirty_background_ratio = 5\nvm.dirty_ratio = 10\nvm.dirty_expire_centisecs = 3000\nvm.dirty_writeback_centisecs = 500\nvm.swappiness = 1\nvm.vfs_cache_pressure = 50","type":"unstyled","key":"es6mp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"2.4.3. Аппаратные улучшения","type":"header-three","key":"b7499"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Краткосрочные (1-3 месяца):","type":"unstyled","key":"a953h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"BOLD"}],"text":"Добавить диски в RAID массив /data - увеличить IOPS и пропускную способность","type":"ordered-list-item","key":"3154k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Рассчитать текущие требования:\nТекущий IOPS: ~12,000 r/s + ~700 w/s = ~12,700 IOPS\nЦелевой IOPS с запасом: 20,000+ IOPS","type":"ordered-list-item","key":"60qmd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"BOLD"}],"text":"Оптимизировать RAID конфигурацию:\nЕсли RAID 10: добавить 2-4 диска\nЕсли RAID 5/6: рассмотреть переход на RAID 10","type":"ordered-list-item","key":"8q21t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"}],"text":"Среднесрочные (3-6 месяцев):","type":"unstyled","key":"r4ev"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"Переход на NVMe диски для /data\nSamsung PM9A3, Intel P5510 или аналоги\nIOPS: 500,000-1,000,000 (увеличение в 40-80 раз)","type":"ordered-list-item","key":"cl0at"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":44,"offset":0,"style":"BOLD"}],"text":"Выделить отдельный диск для temp_tablespaces","type":"ordered-list-item","key":"7j7bk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":52,"offset":0,"style":"BOLD"}],"text":"Рассмотреть RAM диски для частоиспользуемых индексов","type":"ordered-list-item","key":"35a3d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"}],"text":"Долгосрочные (6-12 месяцев):","type":"unstyled","key":"1sku6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"}],"text":"Внедрить кэширующий контроллер с SSD/NVMe кэшем","type":"ordered-list-item","key":"2um4m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":36,"offset":0,"style":"BOLD"}],"text":"Рассмотреть распределенное хранилище (Ceph, GlusterFS)","type":"ordered-list-item","key":"ekd23"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"Архитектурные изменения - шардинг, partitioning","type":"ordered-list-item","key":"frcmg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":54,"offset":0,"style":"BOLD"}],"text":"2.4.4. Оптимизация запросов (на основе Pareto-анализа)","type":"header-three","key":"1ri50"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Топ-3 проблемных запроса по IO:","type":"unstyled","key":"9amss"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"},{"length":11,"offset":73,"style":"BOLD"}],"text":"QUERYID: -5038981907002478858\nCALLS: 103,910,315\nWAITINGS: 3,214 (2.37%)\nОптимизация: Индексирование, переписывание запроса","type":"ordered-list-item","key":"99kb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"},{"length":11,"offset":72,"style":"BOLD"}],"text":"QUERYID: -4280293605113329019\nCALLS: 32,134,168\nWAITINGS: 3,141 (2.32%)\nОптимизация: Кэширование результатов, материализованные представления","type":"ordered-list-item","key":"8pl12"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"},{"length":11,"offset":68,"style":"BOLD"}],"text":"QUERYID: 940651181422038149\nCALLS: 1,938,049\nWAITINGS: 3,805 (2.8%)\nОптимизация: Оптимизация JOIN, покрывающие индексы","type":"ordered-list-item","key":"6cql9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"2.4.5. План внедрения изменений","type":"header-three","key":"ds51q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Фаза 1 (Неделя 1):","type":"unstyled","key":"6c7j7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Изменить параметры PostgreSQL: random_page_cost, effective_io_concurrency","type":"ordered-list-item","key":"62s8j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить параметры ядра Linux","type":"ordered-list-item","key":"enpe7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Мониторинг изменений","type":"ordered-list-item","key":"12qsd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Фаза 2 (Неделя 2):","type":"unstyled","key":"60ne1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Оптимизировать топ-10 проблемных запросов","type":"ordered-list-item","key":"5c6i6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить файловую систему","type":"ordered-list-item","key":"ei3m3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Уменьшить shared_buffers до 128GB","type":"ordered-list-item","key":"2c0hv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Фаза 3 (Неделя 3-4):","type":"unstyled","key":"96bh6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Внедрить аппаратные улучшения (RAID расширение)","type":"ordered-list-item","key":"3cgkh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить мониторинг ключевых метрик","type":"ordered-list-item","key":"5klqp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Регулярный анализ производительности","type":"ordered-list-item","key":"7md62"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":49,"offset":0,"style":"BOLD"}],"text":"Ключевые метрики для мониторинга после изменений:","type":"unstyled","key":"46rqc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Утилизация дисков: цель <70%","type":"unordered-list-item","key":"csh3l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Длина очереди: цель <1","type":"unordered-list-item","key":"9blni"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Время отклика записи: цель <5 мс","type":"unordered-list-item","key":"1tcsi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"IOPS: снижение на 30-50%","type":"unordered-list-item","key":"7cib1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"cecfn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":48,"offset":0,"style":"BOLD"}],"text":"3. Анализ блокировок и contention (LWLock, Lock)","type":"header-two","key":"dlhkj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"ao3bd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":42,"offset":0,"style":"BOLD"}],"text":"3.1. ТИПЫ БЛОКИРОВОК С НАИБОЛЬШИМ ВЛИЯНИЕМ","type":"header-three","key":"9cobk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":49,"offset":0,"style":"BOLD"}],"text":"LWLock (Lightweight Locks) - КРИТИЧЕСКАЯ ПРОБЛЕМА","type":"header-three","key":"5le7o"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":35,"offset":0,"style":"BOLD"}],"text":"Корреляция WAITINGS-LWLOCK = 0.9215 (самая высокая из всех типов ожиданий)","type":"unstyled","key":"4hbqt"},{"data":{"image":{"id":"694a395f24a5f50e9c3da0b4"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"2mtta"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"fj6uq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Влияние на SPEED: Высокая конкуренция за внутренние структуры данных PostgreSQL замедляет все операции.","type":"unstyled","key":"145b9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":46,"offset":0,"style":"BOLD"}],"text":"Lock (Обычные блокировки) - СЕРЬЕЗНАЯ ПРОБЛЕМА","type":"header-three","key":"6vk2t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"BOLD"}],"text":"Корреляция WAITINGS-LOCK = 0.7546","type":"unstyled","key":"750b8"},{"data":{"image":{"id":"694a39c3a9a9f95b4b2b27c9"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"d1ohg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Влияние на SPEED: Конкуренция за объекты БД приводит к очередям и ожиданиям.","type":"unstyled","key":"eivvv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"f74pu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"3.2. ПРОБЛЕМНЫЕ QUERYID","type":"header-three","key":"8cg4k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":50,"offset":0,"style":"BOLD"}],"text":"Топ-10 запросов по суммарным ожиданиям блокировок:","type":"header-three","key":"8e58i"},{"data":{"image":{"id":"694a3a172b72d27cf5c4046e"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"3snu9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"5n37d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":41,"offset":0,"style":"BOLD"}],"text":"Анализ соотношения CALLS/ROWS к WAITINGS:","type":"header-three","key":"749k8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"},{"length":11,"offset":95,"style":"BOLD"},{"length":8,"offset":136,"style":"BOLD"}],"text":"QUERYID -5038981907002478858:\nCALLS: 103,910,315 (103 млн!)\nROWS: 103,910,315\nWAITINGS: 10,878\nСоотношение: 9,551 вызовов на 1 ожидание\nПроблема: Экстремально высокая частота вызовов создает постоянную конкуренцию","type":"ordered-list-item","key":"1oqu4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"},{"length":11,"offset":91,"style":"BOLD"},{"length":8,"offset":132,"style":"BOLD"}],"text":"QUERYID -4280293605113329019:\nCALLS: 32,134,168 (32 млн)\nROWS: 32,134,168\nWAITINGS: 10,639\nСоотношение: 3,020 вызовов на 1 ожидание\nПроблема: Высокая частота, возможно, тривиальный запрос вызывается слишком часто","type":"ordered-list-item","key":"2gua"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":26,"offset":0,"style":"BOLD"},{"length":11,"offset":78,"style":"BOLD"},{"length":8,"offset":117,"style":"BOLD"}],"text":"QUERYID 940651181422038149:\nCALLS: 1,938,049\nROWS: 1,938,049\nWAITINGS: 12,723\nСоотношение: 152 вызовов на 1 ожидание\nПроблема: Высокие ожидания на относительно небольшое количество вызовов","type":"ordered-list-item","key":"8u9b9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"BOLD"}],"text":"Классификация проблемных запросов:","type":"header-three","key":"3kh5b"},{"data":{"image":{"id":"694a3a58c6ba3d6cfd45daea"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"2hkcm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"k4en"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":0,"style":"BOLD"}],"text":"3.3. КОНФИГУРАЦИОННЫЕ ФАКТОРЫ","type":"header-three","key":"ckm3k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"BOLD"}],"text":"Проблемные настройки и их влияние:","type":"header-three","key":"68f3b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":5,"offset":25,"style":"BOLD"},{"length":8,"offset":66,"style":"BOLD"},{"length":7,"offset":128,"style":"BOLD"},{"length":21,"offset":202,"style":"BOLD"},{"length":12,"offset":273,"style":"BOLD"}],"text":"max_parallel_workers = 20 при max_parallel_workers_per_gather = 0\nПроблема: Параллельные запросы отключены, но воркеры выделены\nВлияние: Ресурсы простаивают, тяжелые запросы выполняются последовательно\nВлияние на блокировки: Длительные запросы дольше удерживают блокировки\nРекомендация: Включить параллелизм для тяжелых запросов","type":"ordered-list-item","key":"fn34q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":28,"style":"BOLD"},{"length":21,"offset":87,"style":"BOLD"},{"length":12,"offset":166,"style":"BOLD"}],"text":"maintenance_work_mem = 16GB\nПроблема: Очень высокое значение для операций обслуживания\nВлияние на блокировки: VACUUM и CREATE INDEX могут блокировать объекты надолго\nРекомендация: Уменьшить до 1-2GB","type":"ordered-list-item","key":"fomn2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":27,"style":"BOLD"},{"length":21,"offset":96,"style":"BOLD"},{"length":12,"offset":177,"style":"BOLD"}],"text":"autovacuum_max_workers = 4\nПроблема: При высокой нагрузке UPDATE/DELETE может быть недостаточно\nВлияние на блокировки: Накопление мертвых строк → длительные VACUUM → блокировки\nРекомендация: Увеличить до 6-8, но с осторожностью","type":"ordered-list-item","key":"7obef"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":22,"style":"BOLD"},{"length":21,"offset":67,"style":"BOLD"},{"length":12,"offset":141,"style":"BOLD"}],"text":"wal_keep_size = 500GB\nПроблема: Чрезмерно большой объем WAL файлов\nВлияние на блокировки: Не напрямую, но может влиять на операции архивации\nРекомендация: Уменьшить до разумного значения (64-128GB)","type":"ordered-list-item","key":"8qfi8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":36,"offset":0,"style":"BOLD"}],"text":"Дополнительные проблемные настройки:","type":"header-three","key":"3hhib"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"idle_in_transaction_session_timeout = 1h - слишком долго, транзакции могут висеть","type":"unordered-list-item","key":"783ov"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"statement_timeout = 64h - экстремально высокое значение, запросы могут блокировать надолго","type":"unordered-list-item","key":"flprp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"commit_delay = 1000 - высокое значение может увеличивать время удержания блокировок","type":"unordered-list-item","key":"9epha"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"77j1a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":17,"offset":0,"style":"BOLD"}],"text":"3.4. РЕКОМЕНДАЦИИ","type":"header-three","key":"b1khv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":47,"offset":0,"style":"BOLD"}],"text":"3.4.1. Оптимизация запросов (Высокий приоритет)","type":"header-three","key":"1k5tt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":0,"style":"BOLD"}],"text":"Для топ-5 проблемных QUERYID:","type":"unstyled","key":"a5tjo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":43,"offset":0,"style":"BOLD"},{"length":44,"offset":44,"style":"ITALIC"},{"length":46,"offset":89,"style":"ITALIC"},{"length":34,"offset":136,"style":"ITALIC"}],"text":"QUERYID -5038981907002478858 (103M вызовов):-- Внедрить кэширование на уровне приложения\n-- Рассмотреть материализованные представления\n-- Объединить с другими запросами\n","type":"ordered-list-item","key":"4156p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":45,"offset":0,"style":"BOLD"},{"length":53,"offset":46,"style":"ITALIC"},{"length":37,"offset":100,"style":"ITALIC"},{"length":50,"offset":138,"style":"ITALIC"}],"text":"QUERYID 940651181422038149 (высокие ожидания):-- Добавить индексы для уменьшения времени блокировок\n-- Разбить на более мелкие транзакции\n-- Использовать SELECT ... FOR UPDATE SKIP LOCKED\n","type":"ordered-list-item","key":"ftvmk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"}],"text":"Оптимизация шаблонов доступа:\nВнедрить row-level lock timeouts:sqlSET lock_timeout = '5s';\n\nИспользовать оптимистичные блокировки вместо пессимистичных","type":"ordered-list-item","key":"asdjs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":51,"offset":0,"style":"BOLD"}],"text":"3.4.2. Настройки PostgreSQL для снижения contention","type":"header-three","key":"2mt24"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"ITALIC"},{"length":30,"offset":366,"style":"ITALIC"},{"length":22,"offset":448,"style":"ITALIC"}],"text":"-- НЕМЕДЛЕННЫЕ ИЗМЕНЕНИЯ:\nALTER SYSTEM SET max_parallel_workers_per_gather = 2;\nALTER SYSTEM SET maintenance_work_mem = '2GB';\nALTER SYSTEM SET autovacuum_max_workers = 6;\nALTER SYSTEM SET deadlock_timeout = '500ms';\nALTER SYSTEM SET lock_timeout = '30s';\nALTER SYSTEM SET statement_timeout = '10min';\nALTER SYSTEM SET idle_in_transaction_session_timeout = '5min';\n\n-- ДОПОЛНИТЕЛЬНЫЕ ОПТИМИЗАЦИИ:\nALTER SYSTEM SET max_locks_per_transaction = 256; -- Если много объектов\nALTER SYSTEM SET max_pred_locks_per_transaction = 256;\nALTER SYSTEM SET commit_delay = 10;\nALTER SYSTEM SET wal_keep_size = '64GB';","type":"unstyled","key":"1tud0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"BOLD"}],"text":"3.4.3. Мониторинг проблемных объектов БД","type":"header-three","key":"bm055"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"Скрипты для мониторинга:","type":"unstyled","key":"d96om"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"BOLD"}],"text":"Поиск таблиц с высоким contention:","type":"ordered-list-item","key":"eadqp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"sql","type":"unstyled","key":"ettg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT \n relname, \n n_tup_upd + n_tup_del + n_tup_ins as total_changes,\n n_dead_tup,\n autovacuum_count,\n last_autovacuum\nFROM pg_stat_user_tables \nWHERE n_dead_tup > 1000\nORDER BY n_dead_tup DESC\nLIMIT 20;","type":"unstyled","key":"ebq42"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"BOLD"}],"text":"Мониторинг блокировок в реальном времени:","type":"ordered-list-item","key":"5krvb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"sql","type":"unstyled","key":"350ej"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT \n blocked_locks.pid AS blocked_pid,\n blocking_locks.pid AS blocking_pid,\n blocked_activity.query AS blocked_query,\n blocking_activity.query AS blocking_query,\n blocked_activity.wait_event_type,\n blocked_activity.wait_event,\n age(now(), blocked_activity.query_start) AS blocked_duration,\n age(now(), blocking_activity.query_start) AS blocking_duration\nFROM pg_catalog.pg_locks blocked_locks\nJOIN pg_catalog.pg_stat_activity blocked_activity \n ON blocked_activity.pid = blocked_locks.pid\nJOIN pg_catalog.pg_locks blocking_locks \n ON blocking_locks.locktype = blocked_locks.locktype\n AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE\n AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation\n AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page\n AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple\n AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid\n AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid\n AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid\n AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid\n AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid\n AND blocking_locks.pid != blocked_locks.pid\nJOIN pg_catalog.pg_stat_activity blocking_activity \n ON blocking_activity.pid = blocking_locks.pid\nWHERE NOT blocked_locks.granted;","type":"unstyled","key":"b41vv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Топ объектов по блокировкам:","type":"ordered-list-item","key":"1pirl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SELECT \n relation::regclass AS table_name,\n mode,\n COUNT(*) AS lock_count,\n MAX(age(now(), pg_stat_activity.query_start)) AS max_query_age\nFROM pg_locks\nJOIN pg_stat_activity ON pg_locks.pid = pg_stat_activity.pid\nWHERE relation IS NOT NULL\n AND NOT granted\nGROUP BY relation, mode\nORDER BY lock_count DESC\nLIMIT 10;","type":"unstyled","key":"aeh92"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"}],"text":"3.4.4. Архитектурные изменения","type":"header-three","key":"plg5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Шардинг горячих таблиц - разделить нагрузку","type":"ordered-list-item","key":"1124s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Оптимистичная модель данных - использовать версионирование вместо блокировок","type":"ordered-list-item","key":"4687u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":17,"offset":0,"style":"BOLD"}],"text":"Очереди сообщений - для обработки фоновых задач","type":"ordered-list-item","key":"8jiqb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Read Replicas - разделить нагрузку чтения/записи","type":"ordered-list-item","key":"3q5u7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"3.4.5. План внедрения","type":"header-three","key":"6evdn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Фаза 1 (День 1-2):","type":"unstyled","key":"pm44"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить мониторинг блокировок","type":"ordered-list-item","key":"19gso"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Изменить lock_timeout, statement_timeout","type":"ordered-list-item","key":"2sjti"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Внедрить кэширование для высокочастотных запросов","type":"ordered-list-item","key":"ej6a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Фаза 2 (День 3-7):","type":"unstyled","key":"eajof"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Оптимизировать топ-3 проблемных запроса","type":"ordered-list-item","key":"5333t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить автовакуум: autovacuum_max_workers = 6","type":"ordered-list-item","key":"309hf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Уменьшить maintenance_work_mem = 2GB","type":"ordered-list-item","key":"2hsq4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Фаза 3 (Неделя 2):","type":"unstyled","key":"36kg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Включить параллелизм: max_parallel_workers_per_gather = 2","type":"ordered-list-item","key":"46ru6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить индексы для проблемных таблиц","type":"ordered-list-item","key":"f6k8t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Внедрить скрипты мониторинга","type":"ordered-list-item","key":"23hh9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Фаза 4 (Неделя 3-4):","type":"unstyled","key":"831uo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Архитектурные изменения (если необходимо)","type":"ordered-list-item","key":"cr3ck"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настройка реплик для чтения","type":"ordered-list-item","key":"28vpq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Регулярный аудит производительности","type":"ordered-list-item","key":"bn396"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"BOLD"}],"text":"3.4.6. Ключевые метрики для отслеживания","type":"header-three","key":"3rlrj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":17,"offset":0,"style":"BOLD"}],"text":"LWLock contention: цель < 0.7 корреляции","type":"ordered-list-item","key":"dcbkk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"}],"text":"Количество deadlocks в сутки: цель < 10","type":"ordered-list-item","key":"4jon9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"BOLD"}],"text":"Среднее время ожидания блокировок: цель < 100мс","type":"ordered-list-item","key":"93omi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"}],"text":"Количество активных блокировок: цель < 50","type":"ordered-list-item","key":"74h29"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"BOLD"}],"text":"Эффективность автовакуума: доля мертвых строк < 10%","type":"ordered-list-item","key":"c2qns"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":5,"offset":0,"style":"BOLD"}],"text":"ВАЖНО: Изменения вносить постепенно, начиная с наименее рискованных (таймауты, мониторинг), затем переходить к оптимизации запросов и настройкам.","type":"unstyled","key":"9tuqm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"2805f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":44,"offset":0,"style":"BOLD"}],"text":"4. Анализ использования памяти и IPC-проблем","type":"header-two","key":"9j6ag"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"1n8dm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"4.1. ИСПОЛЬЗОВАНИЕ RAM","type":"header-three","key":"79gu6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":42,"offset":0,"style":"BOLD"}],"text":"Критическая проблема: свободная память <5%","type":"header-three","key":"6dcq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":17,"offset":0,"style":"BOLD"}],"text":"Текущее состояние: 13,572-13,914 MB свободно из 1,031,766 MB (1.3-1.35%)","type":"unordered-list-item","key":"7ln42"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":5,"offset":0,"style":"BOLD"}],"text":"ALARM: 100% наблюдений показывают <5% свободной памяти","type":"unordered-list-item","key":"e6j55"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":0,"style":"BOLD"}],"text":"Проблема: Система работает на пределе, любое увеличение нагрузки может вызвать OOM или свопинг","type":"unordered-list-item","key":"fmfjc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"BOLD"}],"text":"Распределение памяти (в среднем):","type":"header-three","key":"de09j"},{"data":{"image":{"id":"694a3cb60bcee331d63a0ba4"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"2d93a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"bkv47"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Корреляция с IO-операциями:","type":"header-three","key":"5umh2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Для дисков /data (vdh-vdk):","type":"unstyled","key":"fmuia"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"},{"length":13,"offset":42,"style":"BOLD"},{"length":8,"offset":103,"style":"BOLD"}],"text":"buff - r/s: 0.8775-0.8788 (ОЧЕНЬ ВЫСОКАЯ)\nИнтерпретация: Увеличение буферов не снижает операций чтения\nПроблема: Буферы ОС неэффективны для снижения IO","type":"ordered-list-item","key":"3ph0l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"},{"length":13,"offset":44,"style":"BOLD"},{"length":8,"offset":102,"style":"BOLD"}],"text":"buff - rMB/s: 0.8879-0.8886 (ОЧЕНЬ ВЫСОКАЯ)\nИнтерпретация: Объем чтения сильно коррелирует с буферами\nПроблема: Данные не кэшируются эффективно","type":"ordered-list-item","key":"45f01"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":11,"offset":0,"style":"BOLD"},{"length":13,"offset":48,"style":"BOLD"},{"length":12,"offset":105,"style":"BOLD"}],"text":"cache - r/s: -0.6642 до -0.6621 (ОТРИЦАТЕЛЬНАЯ)\nИнтерпретация: Кэш ОС фактически снижает операции чтения\nПоложительно: Кэш работает как должен","type":"ordered-list-item","key":"78fps"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":11,"offset":0,"style":"BOLD"},{"length":13,"offset":54,"style":"BOLD"}],"text":"cache - w/s: -0.3070 до -0.2714 (СЛАБО ОТРИЦАТЕЛЬНАЯ)\nИнтерпретация: Кэш слабо влияет на операции записи","type":"ordered-list-item","key":"9q2fr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":5,"offset":0,"style":"BOLD"},{"length":70,"offset":134,"style":"BOLD"}],"text":"Вывод: Кэш ОС (707GB) работает правильно (отрицательная корреляция с чтением), но буферы ОС (1.5GB) неэффективны. Основная проблема - неоптимальное распределение между shared_buffers PostgreSQL и кэшем ОС.","type":"unstyled","key":"5hu5p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"bta8m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":17,"offset":0,"style":"BOLD"}],"text":"4.2. IPC-ПРОБЛЕМЫ","type":"header-three","key":"4v4l4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Основные типы ожиданий IPC:","type":"header-three","key":"f7leo"},{"data":{"image":{"id":"694a3d077911811a53503d9a"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"2t4vo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"6p587"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"}],"text":"Корреляция WAITINGS-IPC = 0.8181 (высокая)","type":"unstyled","key":"8c6bp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":0,"style":"BOLD"}],"text":"QUERYID с наибольшими IPC-ожиданиями:","type":"header-three","key":"1fi88"},{"data":{"image":{"id":"694a3d3e1db5da6923b0a743"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"ecv5d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"rnu1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Соотношение CALLS/IPC WAIT:","type":"unstyled","key":"et2o4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Наилучшее: 940651181422038149 - 854 вызовов на 1 IPC ожидание","type":"unordered-list-item","key":"cmise"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Наихудшее: -5038981907002478858 - 54,920 вызовов на 1 IPC ожидание (но CALLS=103M!)","type":"unordered-list-item","key":"e0il1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"55qro"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":35,"offset":0,"style":"BOLD"}],"text":"4.3. КОНФИГУРАЦИЯ ПАМЯТИ POSTGRESQL","type":"header-three","key":"6g6ga"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":0,"style":"BOLD"}],"text":"Оценка адекватности текущих настроек:","type":"header-three","key":"9dfu5"},{"data":{"image":{"id":"694a3d8e64be014777e7cfeb"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"4pm6t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"dp3t9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"BOLD"}],"text":"Риски из-за текущей конфигурации:","type":"header-three","key":"309fq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"},{"length":6,"offset":80,"style":"BOLD"},{"length":6,"offset":131,"style":"BOLD"}],"text":"Двойное кэширование: 246GB в shared_buffers + 707GB в кэше ОС = 953GB (92% RAM)\nЭффект: Одна и та же данные хранятся в двух местах\nПотеря: До 246GB памяти используются неэффективно","type":"ordered-list-item","key":"c83o5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":14,"offset":0,"style":"BOLD"},{"length":4,"offset":16,"style":"BOLD"},{"length":28,"offset":79,"style":"BOLD"},{"length":19,"offset":118,"style":"BOLD"}],"text":"work_mem = 1GB:\nРиск: Один сложный запрос с сортировкой может использовать 1GB\nПри 10 параллельных запросах: 10GB RAM\nПри 100 соединениях: потенциально 100GB RAM","type":"ordered-list-item","key":"a6k6g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"},{"length":8,"offset":24,"style":"BOLD"},{"length":14,"offset":48,"style":"BOLD"},{"length":4,"offset":91,"style":"BOLD"}],"text":"Нехватка памяти для ОС:\nСвободно: 13.5GB (1.3%)\nПотребности ОС: Ядро, сетевые буферы, slab\nРиск: При пиковой нагрузке - OOM killer или свопинг","type":"ordered-list-item","key":"cce0n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"},{"length":5,"offset":117,"style":"BOLD"}],"text":"Автовакуум конкуренция:\n4 воркера × 2GB = 8GB максимально для автовакуума\nПлюс 16GB для ручных операций обслуживания\nИтого: До 24GB могут быть заняты фоновыми операциями","type":"ordered-list-item","key":"aj5c9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"62d6s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"4.4. СИСТЕМНЫЕ НАСТРОЙКИ","type":"header-three","key":"b0mjq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":51,"offset":0,"style":"BOLD"}],"text":"Параметры ядра (отсутствуют в отчете, но критичны):","type":"header-three","key":"dfjcn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":0,"style":"BOLD"}],"text":"Текущее состояние (предположительно):","type":"unstyled","key":"89j63"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"vm.swappiness: вероятно 60 (default)","type":"unordered-list-item","key":"3bsps"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"vm.dirty_background_ratio: вероятно 10 (default)","type":"unordered-list-item","key":"all1q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"vm.dirty_ratio: вероятно 20 (default)","type":"unordered-list-item","key":"finm2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"vm.dirty_expire_centisecs: вероятно 3000 (30 seconds)","type":"unordered-list-item","key":"eplgv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"vm.dirty_writeback_centisecs: вероятно 500 (5 seconds)","type":"unordered-list-item","key":"1thc2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Проблемы с default настройками:","type":"unstyled","key":"btk4m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"swappiness=60: Слишком агрессивный свопинг при нехватке памяти","type":"ordered-list-item","key":"vusr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"BOLD"}],"text":"dirty_background_ratio=10: 100GB \"грязных\" страниц перед фоновой записью","type":"ordered-list-item","key":"2gaov"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":14,"offset":0,"style":"BOLD"}],"text":"dirty_ratio=20: 200GB \"грязных\" страниц перед принудительной синхронной записью","type":"ordered-list-item","key":"2mlu2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"BOLD"}],"text":"Настройки huge_pages = on","type":"header-three","key":"63i2s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Преимущества:","type":"unstyled","key":"do187"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Уменьшает overhead на page tables","type":"unordered-list-item","key":"1c5en"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Улучшает производительность TLB","type":"unordered-list-item","key":"8nrfs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Экономит память","type":"unordered-list-item","key":"4re63"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":0,"style":"BOLD"}],"text":"Проблемы:","type":"unstyled","key":"ibfd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"BOLD"},{"length":51,"offset":35,"style":"ITALIC"},{"length":34,"offset":87,"style":"ITALIC"},{"length":37,"offset":122,"style":"ITALIC"}],"text":"Требует предварительного выделения:# Для 246GB shared_buffers с размером страницы 2MB:\n# 246GB / 2MB = 126,000 huge pages\n# На практике нужно ~128,000 страниц\n","type":"ordered-list-item","key":"edbfr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"Недостаток памяти для ОС, если выделено неправильно","type":"ordered-list-item","key":"f4pcg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Сложность управления при изменении shared_buffers","type":"ordered-list-item","key":"7732s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Текущая ситуация: Скорее всего, huge_pages настроены неправильно или не выделены, что может вызывать проблемы с запуском PostgreSQL.","type":"unstyled","key":"9fhuc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"e2euu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":39,"offset":0,"style":"BOLD"}],"text":"4.5. РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ ПАМЯТИ","type":"header-three","key":"bri6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":48,"offset":0,"style":"BOLD"}],"text":"4.5.1. Немедленные изменения (высокий приоритет)","type":"header-three","key":"7cijk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"A. Настройки PostgreSQL:","type":"unstyled","key":"b2kj3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":0,"style":"ITALIC"},{"length":31,"offset":81,"style":"ITALIC"},{"length":28,"offset":155,"style":"ITALIC"},{"length":19,"offset":232,"style":"ITALIC"},{"length":18,"offset":300,"style":"ITALIC"},{"length":29,"offset":320,"style":"ITALIC"},{"length":27,"offset":400,"style":"ITALIC"},{"length":19,"offset":469,"style":"ITALIC"}],"text":"-- КРИТИЧЕСКИЕ ИЗМЕНЕНИЯ (немедленно)\nALTER SYSTEM SET shared_buffers = '64GB'; -- Уменьшить с 246GB (в 4 раза)\nALTER SYSTEM SET work_mem = '128MB'; -- Уменьшить с 1GB (в 8 раз)\nALTER SYSTEM SET maintenance_work_mem = '2GB'; -- Уменьшить с 16GB\nALTER SYSTEM SET autovacuum_work_mem = '512MB'; -- Уменьшить с 2GB\n\n-- ДОПОЛНИТЕЛЬНЫЕ ОПТИМИЗАЦИИ\nALTER SYSTEM SET effective_cache_size = '700GB'; -- Отразить реальный кэш ОС\nALTER SYSTEM SET temp_buffers = '32MB'; -- Уменьшить с 64MB","type":"unstyled","key":"cnskm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":45,"offset":0,"style":"BOLD"}],"text":"B. Настройки ядра Linux (в /etc/sysctl.conf):","type":"unstyled","key":"78m8s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"ITALIC"},{"length":24,"offset":62,"style":"ITALIC"},{"length":40,"offset":125,"style":"ITALIC"},{"length":48,"offset":204,"style":"ITALIC"},{"length":23,"offset":291,"style":"ITALIC"},{"length":22,"offset":353,"style":"ITALIC"},{"length":20,"offset":377,"style":"ITALIC"},{"length":29,"offset":436,"style":"ITALIC"},{"length":30,"offset":504,"style":"ITALIC"},{"length":28,"offset":573,"style":"ITALIC"},{"length":48,"offset":640,"style":"ITALIC"},{"length":32,"offset":690,"style":"ITALIC"},{"length":42,"offset":761,"style":"ITALIC"},{"length":23,"offset":842,"style":"ITALIC"}],"text":"# КРИТИЧЕСКИЕ ПАРАМЕТРЫ\nvm.swappiness = 1 # Минимизировать свопинг\nvm.dirty_background_ratio = 5 # 50GB для фоновой записи (вместо 100GB)\nvm.dirty_ratio = 10 # 100GB для принудительной записи (вместо 200GB)\nvm.dirty_expire_centisecs = 3000 # 30 секунд (нормально)\nvm.dirty_writeback_centisecs = 500 # 5 секунд (нормально)\n\n# ОПТИМИЗАЦИЯ ПАМЯТИ\nvm.overcommit_memory = 2 # Строгая политика overcommit\nvm.overcommit_ratio = 90 # Разрешить overcommit 90% RAM\nvm.vfs_cache_pressure = 50 # Меньше давления на кэш VFS\nvm.min_free_kbytes = 1048576 # 1GB минимальной свободной памяти (0.1% от 1TB)\n\n# HUGE PAGES (если используются)\nvm.nr_hugepages = 32768 # 32,768 × 2MB = 64GB (для shared_buffers)\nvm.hugetlb_shm_group = 999 # GID группы PostgreSQL","type":"unstyled","key":"38bgh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Применить настройки:","type":"unstyled","key":"dam90"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"sysctl -p","type":"unstyled","key":"bdp1t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":50,"offset":0,"style":"BOLD"}],"text":"4.5.2. Оптимизация IPC проблем (средний приоритет)","type":"header-three","key":"d0pn8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":41,"offset":0,"style":"BOLD"}],"text":"A. Настройка PostgreSQL для снижения IPC:","type":"unstyled","key":"5o92f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"ITALIC"},{"length":19,"offset":79,"style":"ITALIC"},{"length":18,"offset":147,"style":"ITALIC"},{"length":59,"offset":167,"style":"ITALIC"},{"length":18,"offset":284,"style":"ITALIC"},{"length":19,"offset":360,"style":"ITALIC"},{"length":36,"offset":381,"style":"ITALIC"},{"length":34,"offset":469,"style":"ITALIC"}],"text":"-- Уменьшить конкуренцию за буферы\nALTER SYSTEM SET bgwriter_delay = '200ms'; -- Увеличить с 10ms\nALTER SYSTEM SET bgwriter_lru_maxpages = 1000; -- Увеличить с 400\n\n-- Оптимизация autovacuum для снижения ProcArray contention\nALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms'; -- Увеличить с 2ms\nALTER SYSTEM SET autovacuum_vacuum_cost_limit = 2000; -- Уменьшить с 5600\n\n-- Улучшение управления транзакциями\nALTER SYSTEM SET max_prepared_transactions = 100; -- Если не используется, уменьшить","type":"unstyled","key":"5tbkh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":35,"offset":0,"style":"BOLD"}],"text":"B. Оптимизация проблемных запросов:","type":"unstyled","key":"abd1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":50,"offset":0,"style":"ITALIC"},{"length":44,"offset":51,"style":"ITALIC"},{"length":46,"offset":96,"style":"ITALIC"},{"length":43,"offset":143,"style":"ITALIC"},{"length":41,"offset":188,"style":"ITALIC"},{"length":42,"offset":272,"style":"ITALIC"},{"length":21,"offset":346,"style":"ITALIC"}],"text":"-- Для QUERYID -5038981907002478858 (103M вызовов)\n-- Внедрить кэширование на уровне приложения\n-- Рассмотреть материализованные представления\n-- Увеличить interval для частых обновлений\n\n-- Для запросов с высокими IPC ожиданиями\nSET max_parallel_workers_per_gather = 0; -- Отключить параллелизм для этих запросов\nSET synchronous_commit = off; -- Для batch операций","type":"unstyled","key":"4dvl3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":47,"offset":0,"style":"BOLD"}],"text":"4.5.3. Настройка Huge Pages (высокий приоритет)","type":"header-three","key":"640b8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"}],"text":"Проверка текущего состояния:","type":"unstyled","key":"eigs1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"ITALIC"},{"length":47,"offset":59,"style":"ITALIC"}],"text":"# Проверить выделенные huge pages\ngrep Huge /proc/meminfo\n\n# Проверить использование huge pages PostgreSQL\ncat $PGDATA/postmaster.pid | head -1 | xargs grep -a 'Huge'","type":"unstyled","key":"67kmt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":45,"offset":0,"style":"BOLD"}],"text":"Настройка huge pages для 64GB shared_buffers:","type":"unstyled","key":"2uv6f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":46,"offset":0,"style":"ITALIC"},{"length":29,"offset":47,"style":"ITALIC"},{"length":32,"offset":78,"style":"ITALIC"},{"length":39,"offset":136,"style":"ITALIC"},{"length":55,"offset":244,"style":"ITALIC"},{"length":28,"offset":437,"style":"ITALIC"}],"text":"# 1. Рассчитать необходимое количество страниц\n# 64GB / 2MB = 32,768 страниц\n\n# 2. Добавить в /etc/sysctl.conf\nvm.nr_hugepages = 32768\n\n# 3. Настроить группу для shared memory\nsudo groupadd -g 999 postgres\nsudo usermod -a -G postgres postgres\n\n# 4. Установить лимиты памяти для пользователя postgres\necho \"postgres soft memlock unlimited\" >> /etc/security/limits.conf\necho \"postgres hard memlock unlimited\" >> /etc/security/limits.conf\n\n# 5. Перезагрузить настройки\nsysctl -p","type":"unstyled","key":"76v23"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"4.5.4. Мониторинг и диагностика","type":"header-three","key":"5sg4r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Скрипты для мониторинга памяти:","type":"unstyled","key":"8alkg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Общее использование памяти:","type":"ordered-list-item","key":"au5b0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"#!/bin/bash\necho \"=== Memory Usage ===\"\nfree -h\necho \"\"\necho \"=== Huge Pages ===\"\ngrep -E 'HugePages_Total|HugePages_Free|HugePages_Rsvd|HugePages_Surp' /proc/meminfo\necho \"\"\necho \"=== PostgreSQL Memory ===\"\nps aux | grep postgres | grep -v grep | awk '{sum += $6} END {print \"Total RSS: \" sum/1024 \" MB\"}'","type":"unstyled","key":"en9l3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"}],"text":"Мониторинг IPC в PostgreSQL:","type":"ordered-list-item","key":"7mcuo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":17,"offset":0,"style":"ITALIC"},{"length":25,"offset":171,"style":"ITALIC"}],"text":"-- IPC статистика\nSELECT \n datname,\n count(*),\n sum(backend_start) as total_backends,\n sum(xact_start) as total_transactions\nFROM pg_stat_activity \nGROUP BY datname;\n\n-- Buffer cache hit ratio\nSELECT \n sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as buffer_hit_ratio\nFROM pg_statio_user_tables;","type":"unstyled","key":"2fsa5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":35,"offset":0,"style":"BOLD"}],"text":"Оповещения при проблемах с памятью:","type":"ordered-list-item","key":"bimvg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":11,"offset":0,"style":"ITALIC"}],"text":"# В crontab\n*/5 * * * * /usr/bin/free | grep Mem | awk '{if ($7/$2 * 100 < 5) print \"WARNING: Free memory less than 5%\"}' | mail -s \"Memory Alert\" admin@example.com","type":"unstyled","key":"5blh6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"4.5.5. План внедрения","type":"header-three","key":"5i8tm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Фаза 1 (День 1):","type":"unstyled","key":"9rf7b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Изменить настройки ядра: vm.swappiness=1, vm.dirty_*","type":"ordered-list-item","key":"3lpcn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Уменьшить work_mem до 128MB","type":"ordered-list-item","key":"al0kk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить мониторинг памяти","type":"ordered-list-item","key":"627rl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Фаза 2 (День 2):","type":"unstyled","key":"f2dp4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Уменьшить shared_buffers до 64GB","type":"ordered-list-item","key":"de6ic"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить huge pages для нового размера","type":"ordered-list-item","key":"bsdrh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Перезапустить PostgreSQL с новыми настройками","type":"ordered-list-item","key":"fsgdp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Фаза 3 (День 3-5):","type":"unstyled","key":"fsgh3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Оптимизировать топ-3 запроса по IPC","type":"ordered-list-item","key":"3nqlu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить автовакуум параметры","type":"ordered-list-item","key":"56djj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Внедрить скрипты мониторинга","type":"ordered-list-item","key":"b4ten"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Фаза 4 (День 6-7):","type":"unstyled","key":"e6i9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Анализ эффективности изменений","type":"ordered-list-item","key":"119ud"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Дополнительная тонкая настройка","type":"ordered-list-item","key":"flcvl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Документирование конфигурации","type":"ordered-list-item","key":"48bf9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"BOLD"}],"text":"4.5.6. Ключевые метрики для отслеживания","type":"header-three","key":"f134m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Свободная память: Цель >5% (50GB+)","type":"ordered-list-item","key":"4iu3d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Buffer cache hit ratio: Цель >99%","type":"ordered-list-item","key":"dmh4k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"IPC ожидания: Цель снижение на 30-50%","type":"ordered-list-item","key":"91qus"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Время отклика запросов: Цель улучшение на 20%","type":"ordered-list-item","key":"dd285"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"}],"text":"Отсутствие свопинга: swap_si/swap_so = 0","type":"ordered-list-item","key":"653sg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"}],"text":"4.5.7. Долгосрочные рекомендации","type":"header-three","key":"boku9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Аппаратное улучшение:\nУвеличить RAM до 1.5-2TB для лучшего кэширования\nРассмотреть NVMe диски для снижения потребности в кэше","type":"ordered-list-item","key":"7ipao"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"Архитектурные изменения:\nВнедрить read replicas для распределения нагрузки\nРассмотреть partitioning для больших таблиц\nВнедрить connection pooling (pgbouncer)","type":"ordered-list-item","key":"1gs0j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Проактивный мониторинг:\nВнедрить Prometheus + Grafana для мониторинга памяти\nНастроить автоматические алерты при проблемах\nРегулярный анализ тенденций использования памяти","type":"ordered-list-item","key":"86l1r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":5,"offset":0,"style":"BOLD"}],"text":"ВАЖНО: Все изменения вносить постепенно, с обязательным мониторингом после каждого шага. Начинать с наименее рискованных изменений (настройки ядра), затем переходить к настройкам PostgreSQL, и только потом к оптимизации запросов.","type":"unstyled","key":"5fvrh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"bfgoo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"BOLD"}],"text":"5. Анализ запросов (Парето-анализ)","type":"header-two","key":"ec5hq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"2bh0t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":0,"style":"BOLD"}],"text":"5.1. ТОП-10 QUERYID ПО ТИПАМ ОЖИДАНИЙ","type":"header-three","key":"6jjo7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":42,"offset":0,"style":"BOLD"}],"text":"5.1.1. IO-bound запросы (нагрузка на диск)","type":"header-three","key":"1piv5"},{"data":{"image":{"id":"694a407dd247e17d30126bd1"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"1p1d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"38r8t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Общий паттерн IO: Все запросы имеют одинаковые WAIT_EVENT:","type":"unstyled","key":"7j1th"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DataFileRead - чтение данных с диска","type":"unordered-list-item","key":"17vtf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WALWrite - запись в WAL","type":"unordered-list-item","key":"eudc1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"DataFileWrite - запись данных на диск","type":"unordered-list-item","key":"72b0b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"SLRURead/SLRUWrite - доступ к SLRU кэшам (subtransaction, multixact)","type":"unordered-list-item","key":"67ull"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":55,"offset":0,"style":"BOLD"}],"text":"5.1.2. IPC-bound запросы (межпроцессное взаимодействие)","type":"header-three","key":"2c6pe"},{"data":{"image":{"id":"694a40b5626f45202304bf0e"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"aguld"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"4uk0r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":17,"offset":0,"style":"BOLD"}],"text":"Общий паттерн IPC:","type":"unstyled","key":"aqi2g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"BufferIO - операции ввода/вывода в буферный кэш","type":"unordered-list-item","key":"ab4aj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"ProcArrayGroupUpdate - обновление массива процессов (высокая конкурентность)","type":"unordered-list-item","key":"estjd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":51,"offset":0,"style":"BOLD"}],"text":"5.1.3. LWLock-bound запросы (внутренние блокировки)","type":"header-three","key":"d9i8"},{"data":{"image":{"id":"694a40f88433a617aa7822b5"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"31ppa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"chsdv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Общий паттерн LWLock:","type":"unstyled","key":"9k99c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"BufferMapping - конкуренция за хэш-таблицу буферов (самая частая проблема)","type":"unordered-list-item","key":"82pvv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LockManager - конкуренция за менеджер блокировок","type":"unordered-list-item","key":"bjl79"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WALInsert - конкуренция за вставку в WAL","type":"unordered-list-item","key":"3qb4c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":47,"offset":0,"style":"BOLD"}],"text":"5.1.4. Lock-bound запросы (блокировки объектов)","type":"header-three","key":"88v88"},{"data":{"image":{"id":"694a41307911811a53554f55"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"en8v6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"c8vdg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Общий паттерн Lock:","type":"unstyled","key":"7r9p1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"transactionid - блокировки по ID транзакции (взаимоблокировки)","type":"unordered-list-item","key":"7bph2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"extend - блокировки расширения файлов (активный рост таблиц)","type":"unordered-list-item","key":"17jbg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"tuple - блокировки строк (конкуренция за обновление одних строк)","type":"unordered-list-item","key":"f8b3p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":39,"offset":0,"style":"BOLD"}],"text":"5.2. ХАРАКТЕРИСТИКИ ПРОБЛЕМНЫХ ЗАПРОСОВ","type":"header-three","key":"ogld"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":44,"offset":0,"style":"BOLD"}],"text":"5.2.1. Топ-5 запросов по суммарным ожиданиям","type":"header-three","key":"388u3"},{"data":{"image":{"id":"694a417ca9a9f95b4b341678"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"frj18"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"2fthk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":48,"offset":0,"style":"BOLD"}],"text":"5.2.2. Запросы с экстремальными характеристиками","type":"header-three","key":"cgqqm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"}],"text":"По CALLS (частота выполнения):","type":"unstyled","key":"20uqv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"-5038981907002478858: 103,910,315 вызовов (103 млн!)","type":"ordered-list-item","key":"15g7l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"-4280293605113329019: 32,134,168 вызовов (32 млн)","type":"ordered-list-item","key":"7l67c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"-1757223094415174739: 10,514,634 вызовов (10 млн)","type":"ordered-list-item","key":"534bi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":46,"offset":0,"style":"BOLD"}],"text":"По WAITINGS на вызов (интенсивность ожиданий):","type":"unstyled","key":"dddtp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"940651181422038149: 0.00656 ожиданий/вызов","type":"ordered-list-item","key":"1i44p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"}],"text":"6300411387403852926: 0.00653 ожиданий/вызов","type":"ordered-list-item","key":"4dlhj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"-1738298181192624679: 0.00642 ожиданий/вызов","type":"ordered-list-item","key":"8dnih"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":55,"offset":0,"style":"BOLD"},{"length":18,"offset":144,"style":"BOLD"}],"text":"По комплексности проблем (присутствие во всех списках):\nВсе топ-10 запросов из IO списка присутствуют во всех четырех списках, что указывает на системные проблемы, а не на проблемы отдельных запросов.","type":"unstyled","key":"41c9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"ekr12"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":26,"offset":0,"style":"BOLD"}],"text":"5.3. КЛАССИФИКАЦИЯ ПРОБЛЕМ","type":"header-three","key":"7iog6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":67,"offset":0,"style":"BOLD"}],"text":"5.3.1. Категория A: Запросы с экстремально высокой частотой вызовов","type":"header-three","key":"36ji6"},{"data":{"image":{"id":"694a41cc7d3c3333c9916db6"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"fb91"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"8fo59"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"Рекомендация: Кэширование на уровне приложения, объединение запросов, материализованные представления.","type":"unstyled","key":"9d7ev"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":58,"offset":0,"style":"BOLD"}],"text":"5.3.2. Категория B: Запросы с высокими ожиданиями на вызов","type":"header-three","key":"bq94p"},{"data":{"image":{"id":"694a42177c8a142b0c346f43"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"bo2gj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"5ec5q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"Рекомендация: Оптимизация запросов, индексирование, уменьшение времени транзакций.","type":"unstyled","key":"c5v3k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":53,"offset":0,"style":"BOLD"}],"text":"5.3.3. Категория C: Запросы с комплексными проблемами","type":"header-three","key":"9ftvg"},{"data":{"image":{"id":"694a4250317d9d69e313e43a"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"9fip8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"e4oba"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"Рекомендация: Системная оптимизация (параметры БД, инфраструктура).","type":"unstyled","key":"7iena"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":74,"offset":0,"style":"BOLD"}],"text":"5.3.4. Категория D: Запросы с потенциальными проблемами производительности","type":"header-three","key":"3c4pj"},{"data":{"image":{"id":"694a42810bcee331d64129c6"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"a7dfi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"Рекомендация: Проверить необходимость такого количества вызовов, возможно объединение.","type":"unstyled","key":"fb9jt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"4vtpb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"}],"text":"5.4. РЕКОМЕНДАЦИИ ПО ОПТИМИЗАЦИИ","type":"header-three","key":"74nm5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":41,"offset":0,"style":"BOLD"}],"text":"5.4.1. Индексирование (Высокий приоритет)","type":"header-three","key":"e7b49"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":0,"style":"BOLD"}],"text":"Для высокочастотных запросов:","type":"unstyled","key":"9ccp4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":45,"offset":0,"style":"ITALIC"},{"length":39,"offset":46,"style":"ITALIC"},{"length":44,"offset":188,"style":"ITALIC"},{"length":38,"offset":233,"style":"ITALIC"},{"length":54,"offset":384,"style":"ITALIC"},{"length":43,"offset":439,"style":"ITALIC"}],"text":"-- 1. Для -5038981907002478858 (103M вызовов)\n-- Предполагая, что это поиск по ключу:\nCREATE INDEX CONCURRENTLY idx_query_5038 \nON your_table (search_column) \nINCLUDE (returned_columns);\n\n-- 2. Для -4280293605113329019 (32M вызовов)\n-- Возможно, нужен покрывающий индекс:\nCREATE INDEX CONCURRENTLY idx_query_4280 \nON your_table (col1, col2, col3) \nWHERE condition_column = constant;\n\n-- 3. Для запросов с высокими ожиданиями Lock (extend)\n-- Индексы для уменьшения full table scans:\nCREATE INDEX CONCURRENTLY idx_reduce_scans \nON large_table (frequently_filtered_column);","type":"unstyled","key":"8mqm7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"BOLD"}],"text":"Рекомендации по индексам:","type":"unstyled","key":"3v9s1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Использовать CREATE INDEX CONCURRENTLY чтобы не блокировать запись","type":"ordered-list-item","key":"d3epr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Рассмотреть BRIN индексы для временных рядов","type":"ordered-list-item","key":"7mbrf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Регулярно выполнять REINDEX CONCURRENTLY для борьбы с bloat","type":"ordered-list-item","key":"218kp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Использовать pg_stat_all_indexes для мониторинга эффективности","type":"ordered-list-item","key":"1ph6e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":49,"offset":0,"style":"BOLD"}],"text":"5.4.2. Переписывание запросов (Средний приоритет)","type":"header-three","key":"9o3m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"BOLD"}],"text":"Для -5038981907002478858 (103M вызовов):","type":"unstyled","key":"cc43b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"ITALIC"},{"length":50,"offset":67,"style":"ITALIC"},{"length":55,"offset":118,"style":"ITALIC"},{"length":36,"offset":175,"style":"ITALIC"},{"length":34,"offset":212,"style":"ITALIC"},{"length":49,"offset":302,"style":"ITALIC"},{"length":27,"offset":453,"style":"ITALIC"}],"text":"-- Текущий (предположительно):\nSELECT * FROM table WHERE id = $1;\n\n-- Оптимизация 1: Кэширование на уровне приложения\n-- Внедрить Redis/Memcached для кэширования результатов\n\n-- Оптимизация 2: Пакетная обработка\n-- Вместо 103M отдельных запросов:\nSELECT * FROM table WHERE id IN ($1, $2, ..., $1000);\n\n-- Оптимизация 3: Материализованное представление\nCREATE MATERIALIZED VIEW mv_cache AS\nSELECT id, data, updated_at \nFROM table \nWHERE some_condition;\n\n-- Обновление по расписанию\nREFRESH MATERIALIZED VIEW CONCURRENTLY mv_cache;","type":"unstyled","key":"ee4tg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":42,"offset":0,"style":"BOLD"}],"text":"Для запросов с высокими ожиданиями LWLock:","type":"unstyled","key":"6leia"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"ITALIC"},{"length":50,"offset":84,"style":"ITALIC"},{"length":50,"offset":135,"style":"ITALIC"},{"length":35,"offset":186,"style":"ITALIC"}],"text":"-- Текущий (с проблемами BufferMapping):\nUPDATE table SET col = val WHERE id = $1;\n\n-- Оптимизация: Уменьшение конкурентных обновлений\n-- Использовать очередь сообщений для сериализации\n-- Или использовать advisory locks:\n\nBEGIN;\nSELECT pg_advisory_xact_lock(id) FROM table WHERE id = $1;\nUPDATE table SET col = val WHERE id = $1;\nCOMMIT;","type":"unstyled","key":"fb1m4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":54,"offset":0,"style":"BOLD"}],"text":"5.4.3. Изменение паттернов доступа (Высокий приоритет)","type":"header-three","key":"fcvn1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"1. Connection Pooling:","type":"unstyled","key":"8e7rv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"yaml","type":"unstyled","key":"3gv5e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":0,"style":"ITALIC"}],"text":"# pgbouncer.ini\n[databases]\nmydb = host=localhost dbname=mydb pool_size=100\n\n[pgbouncer]\npool_mode = transaction\nmax_client_conn = 1000\ndefault_pool_size = 100","type":"unstyled","key":"4k9ef"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"BOLD"}],"text":"2. Read/Write разделение:","type":"unstyled","key":"be01p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"ITALIC"},{"length":44,"offset":79,"style":"ITALIC"},{"length":16,"offset":124,"style":"ITALIC"},{"length":22,"offset":141,"style":"ITALIC"},{"length":23,"offset":164,"style":"ITALIC"}],"text":"-- Настройка hot_standby_feedback\nALTER SYSTEM SET hot_standby_feedback = on;\n\n-- Направление read-only запросов на реплики\n-- В приложении:\n-- writes -> primary\n-- reads -> replicas","type":"unstyled","key":"b52eo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":49,"offset":0,"style":"BOLD"}],"text":"3. Batch processing для высокочастотных операций:","type":"unstyled","key":"2uidj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"python","type":"unstyled","key":"etp85"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":0,"style":"ITALIC"},{"length":15,"offset":84,"style":"ITALIC"}],"text":"# Вместо:\nfor item in items:\n db.execute(\"INSERT INTO table VALUES (%s)\", item)\n\n# Использовать:\nfrom psycopg2.extras import execute_batch\nexecute_batch(cur, \"INSERT INTO table VALUES (%s)\", items, page_size=1000)","type":"unstyled","key":"ekafg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":46,"offset":0,"style":"BOLD"}],"text":"5.4.4. Настройки параметров на уровне запросов","type":"header-three","key":"5tnjq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"Для проблемных запросов:","type":"unstyled","key":"2loqn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":65,"offset":0,"style":"ITALIC"},{"length":65,"offset":98,"style":"ITALIC"},{"length":51,"offset":195,"style":"ITALIC"},{"length":39,"offset":295,"style":"ITALIC"},{"length":39,"offset":412,"style":"ITALIC"},{"length":23,"offset":519,"style":"ITALIC"}],"text":"-- 1. Уменьшение lock_timeout для избежания длительных блокировок\nSET LOCAL lock_timeout = '2s';\n\n-- 2. Увеличение work_mem для сортировок/хеширования (осторожно!)\nSET LOCAL work_mem = '256MB';\n\n-- 3. Отключение параллелизма для коротких запросов\nSET LOCAL max_parallel_workers_per_gather = 0;\n\n-- 4. Изменение параметров планировщика\nSET LOCAL random_page_cost = 1.5;\nSET LOCAL effective_cache_size = '700GB';\n\n-- 5. Управление WAL для batch операций\nSET LOCAL synchronous_commit = off;\nSET LOCAL wal_level = minimal; -- для временных данных","type":"unstyled","key":"kkmg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"BOLD"}],"text":"Шаблоны для разных типов запросов:","type":"unstyled","key":"3vc4h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":42,"offset":0,"style":"ITALIC"},{"length":35,"offset":164,"style":"ITALIC"},{"length":22,"offset":344,"style":"ITALIC"},{"length":19,"offset":450,"style":"ITALIC"}],"text":"-- Для высокочастотных read-only запросов:\nBEGIN;\nSET LOCAL statement_timeout = '1s';\nSET LOCAL lock_timeout = '100ms';\nSELECT * FROM table WHERE id = $1;\nCOMMIT;\n\n-- Для update интенсивных операций:\nBEGIN;\nSET LOCAL lock_timeout = '5s';\nSET LOCAL idle_in_transaction_session_timeout = '30s';\nUPDATE table SET col = val WHERE id = $1;\nCOMMIT;\n\n-- Для batch операций:\nBEGIN;\nSET LOCAL synchronous_commit = off;\nSET LOCAL maintenance_work_mem = '2GB';\n-- batch operations\nCOMMIT;","type":"unstyled","key":"c0962"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":38,"offset":0,"style":"BOLD"}],"text":"5.4.5. План оптимизации по приоритетам","type":"header-three","key":"ami3k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"Неделя 1: Быстрые победы","type":"unstyled","key":"8couv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Внедрить connection pooling (pgbouncer)","type":"ordered-list-item","key":"d9ul4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Добавить покрывающие индексы для топ-3 высокочастотных запросов","type":"ordered-list-item","key":"8e1a5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить statement_timeout = '30s' на уровне БД","type":"ordered-list-item","key":"9rnif"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"}],"text":"Неделя 2: Оптимизация запросов","type":"unstyled","key":"2fata"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Переписать -5038981907002478858 с использованием кэширования","type":"ordered-list-item","key":"ck81k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Внедрить batch processing для INSERT/UPDATE операций","type":"ordered-list-item","key":"bflgu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Оптимизировать JOIN в 940651181422038149","type":"ordered-list-item","key":"bakg3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"BOLD"}],"text":"Неделя 3: Архитектурные изменения","type":"unstyled","key":"agq7f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Внедрить read replicas для распределения нагрузки","type":"ordered-list-item","key":"b9pm7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить partitioning для горячих таблиц","type":"ordered-list-item","key":"cir3j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Внедрить очередь сообщений для фоновой обработки","type":"ordered-list-item","key":"dufjn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":39,"offset":0,"style":"BOLD"}],"text":"Неделя 4: Мониторинг и тонкая настройка","type":"unstyled","key":"d0kml"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Настроить автоматический сбор статистики","type":"ordered-list-item","key":"phsa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Внедрить alerting при проблемах с запросами","type":"ordered-list-item","key":"5kj25"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Регулярный ревью и оптимизация запросов","type":"ordered-list-item","key":"dneb1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"BOLD"}],"text":"5.4.6. Ключевые метрики для отслеживания","type":"header-three","key":"4leif"},{"data":{"image":{"id":"694a43418433a617aa7af1f9"}},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"atomic:image","key":"dfaqr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":6,"offset":0,"style":"BOLD"}],"text":"Резюме: ","type":"header-three","key":"bnopm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Проблемы носят системный характер - почти все топ-запросы страдают от всех типов ожиданий. Это указывает на необходимость комплексной оптимизации: начиная с инфраструктуры (диски, память), через настройки PostgreSQL, и заканчивая оптимизацией конкретных запросов и архитектурными изменениями.","type":"unstyled","key":"bth2i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"29uhi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":43,"offset":0,"style":"BOLD"}],"text":"6. Комплексные рекомендации и план действий","type":"header-two","key":"e2isk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"aos7t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"BOLD"}],"text":"6.1. КРАТКОСРОЧНЫЕ МЕРЫ (1-2 дня)","type":"header-three","key":"5cs82"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":52,"offset":0,"style":"BOLD"}],"text":"6.1.1. Критические изменения конфигурации PostgreSQL","type":"header-three","key":"p4t7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":44,"offset":0,"style":"BOLD"}],"text":"Приоритет 1: Оптимизация дисковой подсистемы (даст эффект сразу)","type":"unstyled","key":"73rl8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":35,"offset":0,"style":"ITALIC"},{"length":50,"offset":87,"style":"ITALIC"},{"length":32,"offset":189,"style":"ITALIC"},{"length":27,"offset":273,"style":"ITALIC"}],"text":"-- Изменения в postgresql.auto.conf\nALTER SYSTEM SET random_page_cost = 4.0; -- Было 1.1 (реалистично для перегруженных дисков)\nALTER SYSTEM SET effective_io_concurrency = 32; -- Было 300 (уменьшить в 10 раз)\nALTER SYSTEM SET effective_cache_size = '700GB'; -- Отразить реальный кэш ОС","type":"unstyled","key":"cs0v5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"BOLD"}],"text":"Приоритет 2: Снижение нагрузки на память","type":"unstyled","key":"ceqa0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":51,"style":"ITALIC"},{"length":34,"offset":134,"style":"ITALIC"},{"length":28,"offset":220,"style":"ITALIC"}],"text":"ALTER SYSTEM SET work_mem = '128MB'; -- Было 1GB (уменьшить в 8 раз)\nALTER SYSTEM SET lock_timeout = '30s'; -- Предотвратить вечные блокировки\nALTER SYSTEM SET statement_timeout = '10min'; -- Было 64h (разумный лимит)","type":"unstyled","key":"qafb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"BOLD"}],"text":"Приоритет 3: Настройка мониторинга","type":"unstyled","key":"jp8b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"ITALIC"}],"text":"-- Включить дополнительные метрики\nALTER SYSTEM SET track_io_timing = on;\nALTER SYSTEM SET track_activities = on;\nALTER SYSTEM SET track_counts = on;","type":"unstyled","key":"3muut"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":44,"offset":0,"style":"BOLD"}],"text":"6.1.2. Оптимизация самых проблемных запросов","type":"header-three","key":"9dbrq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"Немедленные действия:","type":"unstyled","key":"calj9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":47,"offset":0,"style":"BOLD"},{"length":63,"offset":48,"style":"ITALIC"},{"length":27,"offset":211,"style":"ITALIC"}],"text":"Для QUERYID -5038981907002478858 (103M вызовов):-- Внедрить временный кэш через материализованное представление\nCREATE MATERIALIZED VIEW IF NOT EXISTS cache_5038 AS\nSELECT * FROM source_table WHERE conditions;\n\n-- Обновлять каждые 5 минут\nREFRESH MATERIALIZED VIEW CONCURRENTLY cache_5038;\n","type":"ordered-list-item","key":"cjvmp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":41,"offset":0,"style":"BOLD"},{"length":54,"offset":45,"style":"ITALIC"}],"text":"Для запросов с высокими LWLock ожиданиями:sql-- Добавить индекс для уменьшения времени сканирования\nCREATE INDEX CONCURRENTLY idx_urgent ON problem_table(problem_column);\n","type":"ordered-list-item","key":"2ik50"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":41,"offset":0,"style":"BOLD"}],"text":"Внедрить ограничения на уровне приложения:\nОграничить максимальное количество одновременных запросов\nВнедрить circuit breaker для проблемных эндпоинтов","type":"ordered-list-item","key":"c79j0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"BOLD"}],"text":"6.1.3. Мониторинг ключевых метрик","type":"header-three","key":"58nen"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"}],"text":"Дашборд для первых 48 часов:","type":"unstyled","key":"57h9p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":43,"offset":0,"style":"ITALIC"},{"length":11,"offset":44,"style":"ITALIC"}],"text":"# Скрипт для мониторинга в реальном времени\n#!/bin/bash\nwatch -n 5 \"\necho '=== DISK IO ==='\niostat -dx /dev/vdh /dev/vdi /dev/vdj /dev/vdk 2 1 | tail -n +7\necho ''\necho '=== MEMORY ==='\nfree -h\necho ''\necho '=== POSTGRESQL WAITS ==='\npsql -c \\\"SELECT wait_event_type, count(*) FROM pg_stat_activity WHERE wait_event_type IS NOT NULL GROUP BY 1 ORDER BY 2 DESC;\\\"\necho ''\necho '=== TOP QUERIES ==='\npsql -c \\\"SELECT query, calls, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;\\\"\n\"","type":"unstyled","key":"oc36"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":36,"offset":0,"style":"BOLD"}],"text":"Ключевые метрики (целевые значения):","type":"unstyled","key":"55jkt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Дисковая утилизация: < 70% (сейчас 78-86%)","type":"unordered-list-item","key":"aboqk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Очередь дисков: < 1 (сейчас 1.6-2.0)","type":"unordered-list-item","key":"fjbqo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Свободная память: > 5% (сейчас 1.3%)","type":"unordered-list-item","key":"6einq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"LWLock ожидания: < 100/сек (сейчас ~500/сек)","type":"unordered-list-item","key":"ahj30"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":36,"offset":0,"style":"BOLD"}],"text":"6.2. СРЕДНЕСРОЧНЫЕ МЕРЫ (1-2 недели)","type":"header-three","key":"234b7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":38,"offset":0,"style":"BOLD"}],"text":"6.2.1. Оптимизация схемы БД и индексов","type":"header-three","key":"4kjjt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Неделя 1: Анализ и планирование","type":"unstyled","key":"aa0td"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"ITALIC"},{"length":25,"offset":199,"style":"ITALIC"},{"length":18,"offset":279,"style":"ITALIC"}],"text":"-- 1. Анализ индексов\nSELECT \n schemaname,\n tablename,\n indexname,\n pg_size_pretty(pg_relation_size(indexrelid)) as index_size,\n idx_scan as scans\nFROM pg_stat_user_indexes \nWHERE idx_scan = 0 -- Неиспользуемые индексы\nORDER BY pg_relation_size(indexrelid) DESC\nLIMIT 20;\n\n-- 2. Анализ bloat\nSELECT \n schemaname,\n tablename,\n pg_size_pretty(pg_total_relation_size(relid)) as total_size,\n n_dead_tup,\n n_live_tup,\n round(n_dead_tup::numeric / (n_live_tup + n_dead_tup) * 100, 2) as dead_percent\nFROM pg_stat_user_tables \nWHERE n_dead_tup > 1000\nORDER BY n_dead_tup DESC\nLIMIT 10;","type":"unstyled","key":"2g3mb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"}],"text":"Неделя 2: Реализация оптимизаций","type":"unstyled","key":"2992d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"}],"text":"Удаление неиспользуемых индексов (экономия места, ускорение записи)","type":"ordered-list-item","key":"40i3n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":0,"style":"BOLD"}],"text":"Создание покрывающих индексов для топ-10 проблемных запросов","type":"ordered-list-item","key":"94qeh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"Оптимизация partitioning для больших таблиц","type":"ordered-list-item","key":"b8plg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Настройка fillfactor для таблиц с частыми обновлениями","type":"ordered-list-item","key":"1f6c0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":41,"offset":0,"style":"BOLD"}],"text":"6.2.2. Настройка Vacuum/Analyze стратегии","type":"header-three","key":"78ca9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":49,"offset":0,"style":"ITALIC"},{"length":24,"offset":325,"style":"ITALIC"}],"text":"-- Для таблиц с высокой активностью UPDATE/DELETE\nALTER TABLE problem_table SET (\n autovacuum_vacuum_scale_factor = 0.05,\n autovacuum_vacuum_threshold = 1000,\n autovacuum_analyze_scale_factor = 0.02,\n autovacuum_analyze_threshold = 1000,\n autovacuum_vacuum_cost_limit = 2000,\n autovacuum_vacuum_cost_delay = '10ms'\n);\n\n-- Ежедневный мониторинг\nCREATE TABLE vacuum_monitor AS\nSELECT \n schemaname,\n tablename,\n last_vacuum,\n last_autovacuum,\n last_analyze,\n last_autoanalyze,\n n_dead_tup\nFROM pg_stat_user_tables;","type":"unstyled","key":"lfca"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":47,"offset":0,"style":"BOLD"}],"text":"6.2.3. Анализ и переработка проблемных запросов","type":"header-three","key":"hqs5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"Методология:","type":"unstyled","key":"41iqp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":0,"style":"BOLD"}],"text":"Неделя 1: Топ-5 запросов по WAITINGS","type":"ordered-list-item","key":"5v774"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":0,"style":"BOLD"}],"text":"Неделя 2: Топ-10 запросов по общему времени выполнения","type":"ordered-list-item","key":"escdj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"Инструменты:","type":"unstyled","key":"d02rc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"ITALIC"},{"length":41,"offset":117,"style":"ITALIC"}],"text":"-- Подробный анализ запроса\nEXPLAIN (ANALYZE, BUFFERS, VERBOSE, WAL) \nSELECT * FROM problem_table WHERE conditions;\n\n-- Поиск альтернативных планов выполнения\nSET enable_hashjoin = off;\nEXPLAIN SELECT * FROM problem_table WHERE conditions;\nSET enable_hashjoin = on;\nSET enable_mergejoin = off;\nEXPLAIN SELECT * FROM problem_table WHERE conditions;\nSET enable_mergejoin = on;","type":"unstyled","key":"9s1ed"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":49,"offset":0,"style":"BOLD"}],"text":"6.2.4. Настройка файловой системы и параметров ОС","type":"header-three","key":"6as8k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":0,"style":"BOLD"}],"text":"Оптимизация /etc/sysctl.conf:","type":"unstyled","key":"a7fuk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"ITALIC"},{"length":5,"offset":214,"style":"ITALIC"},{"length":46,"offset":221,"style":"ITALIC"}],"text":"# Оптимизация памяти\nvm.swappiness = 1\nvm.dirty_background_ratio = 5\nvm.dirty_ratio = 10\nvm.dirty_expire_centisecs = 3000\nvm.dirty_writeback_centisecs = 500\nvm.vfs_cache_pressure = 50\nvm.min_free_kbytes = 1048576 # 1GB\n\n# Оптимизация сети (для распределенных систем)\nnet.core.rmem_max = 134217728\nnet.core.wmem_max = 134217728\nnet.ipv4.tcp_rmem = 4096 87380 134217728\nnet.ipv4.tcp_wmem = 4096 65536 134217728","type":"unstyled","key":"ck29g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"}],"text":"Оптимизация монтирования дисков:","type":"unstyled","key":"ehc0p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"bash","type":"unstyled","key":"fiefp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"ITALIC"},{"length":10,"offset":104,"style":"ITALIC"}],"text":"# В /etc/fstab для /data\nUUID=xxxx /data ext4 defaults,noatime,nodiratime,data=writeback,barrier=0 0 0\n\n# Для /wal\nUUID=yyyy /wal ext4 defaults,noatime,nodiratime 0 0","type":"unstyled","key":"392n8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":35,"offset":0,"style":"BOLD"}],"text":"6.3. ДОЛГОСРОЧНЫЕ МЕРЫ (1-3 месяца)","type":"header-three","key":"adcn0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"6.3.1. Аппаратные улучшения","type":"header-three","key":"fg910"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":35,"offset":0,"style":"BOLD"}],"text":"Месяц 1: Диагностика и планирование","type":"unstyled","key":"5fuk0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"BOLD"}],"text":"Анализ текущей производительности дисков:bashfio --filename=/dev/vdh --rw=randread --ioengine=libaio --direct=1 \\\n --name=test --bs=8k --iodepth=64 --runtime=60 --numjobs=4 \\\n --group_reporting --output=fio_results.txt\n","type":"ordered-list-item","key":"b0st6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":17,"offset":0,"style":"BOLD"}],"text":"Расчет требований:\nТекущий IOPS: ~12,700\nЦелевой IOPS с запасом: 20,000+\nРекомендуемое решение: NVMe диски или расширение RAID массива","type":"ordered-list-item","key":"f6e9e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"}],"text":"Текущие расширения:","type":"header-three","key":"eb8i5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":0,"style":"BOLD"}],"text":"Вариант A (если бюджет ограничен):\nДобавить 4 диска в RAID 10 массив /data\nОбщий размер: +14TB, IOPS: +8,000","type":"ordered-list-item","key":"d0llr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":9,"offset":0,"style":"BOLD"}],"text":"Вариант B (оптимальный):\nПереход на NVMe диски Samsung PM9A3\nIOPS: 500,000+ (увеличение в 40 раз)\nЗадержка: < 100µs (улучшение в 100 раз)","type":"ordered-list-item","key":"e0gm7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Месяц 3: Память и процессор","type":"unstyled","key":"cce41"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Увеличение RAM до 1.5TB для лучшего кэширования","type":"unordered-list-item","key":"c3c6h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Оптимизация NUMA для 4-х сокетной системы","type":"unordered-list-item","key":"bvkit"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"}],"text":"6.3.2. Архитектурные изменения","type":"header-three","key":"5je8q"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":0,"style":"BOLD"}],"text":"Микроархитектурные улучшения:","type":"unstyled","key":"7d21n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"},{"length":22,"offset":55,"style":"ITALIC"}],"text":"Внедрение read replicas для распределения нагрузки yaml# Конфигурация реплики\nprimary_conninfo = 'host=primary port=5432 user=replicator'\nhot_standby = on\nhot_standby_feedback = on\nmax_standby_streaming_delay = 30s\n","type":"ordered-list-item","key":"2hinc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"},{"length":19,"offset":26,"style":"ITALIC"},{"length":10,"offset":157,"style":"ITALIC"}],"text":"Шардинг больших таблиц:sql-- По диапазону дат\nCREATE TABLE measurements_2024_01 PARTITION OF measurements\nFOR VALUES FROM ('2024-01-01') TO ('2024-02-01');\n\n-- По хэшу\nCREATE TABLE users_0 PARTITION OF users\nFOR VALUES WITH (MODULUS 4, REMAINDER 0);\n","type":"ordered-list-item","key":"dvppt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":26,"offset":0,"style":"BOLD"},{"length":40,"offset":33,"style":"ITALIC"},{"length":9,"offset":379,"style":"ITALIC"}],"text":"Внедрение кэширующего слоя:python# Redis кэш для высокочастотных запросов\nimport redis\nimport pickle\n\ndef cached_query(query_id, params):\n cache_key = f\"query:{query_id}:{hash(params)}\"\n cached = redis.get(cache_key)\n if cached:\n return pickle.loads(cached)\n \n result = execute_query(query_id, params)\n redis.setex(cache_key, 300, pickle.dumps(result)) # 5 минут\n return result\n","type":"ordered-list-item","key":"3501p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":53,"offset":0,"style":"BOLD"}],"text":"6.3.3. Внедрение расширенных инструментов мониторинга","type":"header-three","key":"9u7l2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":17,"offset":0,"style":"BOLD"}],"text":"Стек мониторинга:","type":"unstyled","key":"5ns3r"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Prometheus + Grafana для сбора и визуализации метрик","type":"ordered-list-item","key":"a67q8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":0,"style":"BOLD"}],"text":"pg_stat_monitor для расширенной статистики запросов","type":"ordered-list-item","key":"aefns"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":12,"offset":0,"style":"BOLD"}],"text":"pg_qualstats для анализа условий WHERE","type":"ordered-list-item","key":"f6ejr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"pg_wait_sampling для детального анализа ожиданий","type":"ordered-list-item","key":"8td07"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"}],"text":"Кастомные дашборды:","type":"unstyled","key":"1po0a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Disk Performance Dashboard","type":"unordered-list-item","key":"18onh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Query Performance Trends","type":"unordered-list-item","key":"146bu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Lock Contention Monitor","type":"unordered-list-item","key":"95tkn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Memory Usage Analytics","type":"unordered-list-item","key":"fcahi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Автоматические алерты:","type":"unstyled","key":"2sgrk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"yaml","type":"unstyled","key":"194vd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"ITALIC"}],"text":"# Alertmanager конфигурация\ngroups:\n - name: postgresql_alerts\n rules:\n - alert: HighDiskUtilization\n expr: disk_utilization > 80\n for: 5m\n annotations:\n description: 'Disk {{ $labels.device }} is {{ $value }}% utilized'\n \n - alert: HighLockContention\n expr: rate(pg_lock_wait_seconds_total[5m]) > 10\n for: 2m\n annotations:\n description: 'High lock contention detected'","type":"unstyled","key":"8g725"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"6.4. ПРИОРИТИЗАЦИЯ","type":"header-three","key":"dga4e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":51,"offset":0,"style":"BOLD"}],"text":"6.4.1. Наибольший эффект при минимальных изменениях","type":"header-three","key":"ce5db"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"BOLD"}],"text":"Топ-5 low-hanging fruits:","type":"unstyled","key":"dqrlt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"random_page_cost = 4.0 - немедленное улучшение планов запросов","type":"ordered-list-item","key":"aamhr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"effective_io_concurrency = 32 - снижение очередей на дисках","type":"ordered-list-item","key":"d543g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"}],"text":"Внедрение pgbouncer - уменьшение нагрузки на соединения","type":"ordered-list-item","key":"6fcps"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Индекс для -5038981907002478858 - самый частый запрос","type":"ordered-list-item","key":"676o9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Настройка vm.swappiness = 1 - предотвращение свопинга","type":"ordered-list-item","key":"b7t0h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Ожидаемый эффект: 30-50% улучшение производительности","type":"unstyled","key":"4isnt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":0,"style":"BOLD"}],"text":"6.4.2. Наиболее рискованные изменения","type":"header-three","key":"9k216"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Высокий риск:","type":"unstyled","key":"8f8mm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"Изменение shared_buffers - может вызвать OOM или деградацию","type":"ordered-list-item","key":"3g14g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":28,"offset":0,"style":"BOLD"}],"text":"Изменение схемы partitioning - требует остановки приложения","type":"ordered-list-item","key":"d7gqe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"Миграция на новые диски - риск потери данных","type":"ordered-list-item","key":"akl1a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Стратегия митигации:","type":"unstyled","key":"cc3t5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Тестирование на staging окружении","type":"ordered-list-item","key":"c7269"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Постепенный rollout (canary deployment)","type":"ordered-list-item","key":"3pk8e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Подготовленные планы отката","type":"ordered-list-item","key":"8o1ha"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":46,"offset":0,"style":"BOLD"}],"text":"6.4.3. Измерение эффекта от каждой оптимизации","type":"header-three","key":"83srs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Метрика эффективности:","type":"unstyled","key":"4nphq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"sql","type":"unstyled","key":"6us5j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":0,"style":"ITALIC"},{"length":20,"offset":259,"style":"ITALIC"}],"text":"-- Таблица для отслеживания изменений\nCREATE TABLE optimization_metrics (\n id serial PRIMARY KEY,\n change_name text,\n change_date timestamp DEFAULT now(),\n metric_name text,\n before_value numeric,\n after_value numeric,\n improvement_percent numeric\n);\n\n-- Пример заполнения\nINSERT INTO optimization_metrics (change_name, metric_name, before_value, after_value)\nVALUES \n ('random_page_cost change', 'avg_query_time', 150.5, 120.3),\n ('effective_io_concurrency change', 'disk_queue_length', 1.8, 1.2);","type":"unstyled","key":"27ogi"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"BOLD"}],"text":"KPI для измерения успеха:","type":"unstyled","key":"cveit"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Disk utilization: < 70% (сейчас 85%)","type":"ordered-list-item","key":"96of4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Query response time (p95): < 100ms (улучшение на 50%)","type":"ordered-list-item","key":"1hqa0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Throughput: увеличение на 30%","type":"ordered-list-item","key":"bl1de"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Error rate: снижение на 90%","type":"ordered-list-item","key":"9lc5e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"6.5. МОНИТОРИНГ ПОСЛЕ ИЗМЕНЕНИЙ","type":"header-three","key":"l0gg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"BOLD"}],"text":"6.5.1. Ключевые метрики для отслеживания","type":"header-three","key":"9ffba"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":38,"offset":0,"style":"BOLD"}],"text":"Обязательные метрики (каждые 5 минут):","type":"unstyled","key":"6gctt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"sql","type":"unstyled","key":"ann44"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"ITALIC"},{"length":25,"offset":136,"style":"ITALIC"},{"length":33,"offset":346,"style":"ITALIC"}],"text":"-- 1. Производительность дисков\nSELECT device, util, aqu_sz, await \nFROM iostat_metrics \nWHERE device IN ('vdh', 'vdi', 'vdj', 'vdk');\n\n-- 2. Ожидания PostgreSQL\nSELECT wait_event_type, count(*), \n round(100.0 * count(*) / sum(count(*)) OVER (), 2) as pct\nFROM pg_stat_activity \nWHERE wait_event_type IS NOT NULL\nGROUP BY wait_event_type;\n\n-- 3. Производительность запросов\nSELECT queryid, calls, mean_time, \n (mean_time * calls) as total_time\nFROM pg_stat_statements \nORDER BY total_time DESC \nLIMIT 10;","type":"unstyled","key":"fe8aa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"BOLD"}],"text":"Расширенные метрики (каждый час):","type":"unstyled","key":"cgacn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Buffer cache hit ratio","type":"unordered-list-item","key":"57bj8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Index usage statistics","type":"unordered-list-item","key":"3qgtb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Lock contention by object","type":"unordered-list-item","key":"a7mo9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"WAL generation rate","type":"unordered-list-item","key":"bp604"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":38,"offset":0,"style":"BOLD"}],"text":"6.5.2. Пороговые значения для alerting","type":"header-three","key":"2kdc2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":35,"offset":0,"style":"BOLD"}],"text":"Критические алерты (PagerDuty/SMS):","type":"unstyled","key":"bu068"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Disk utilization > 90% более 5 минут","type":"ordered-list-item","key":"4aqbv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"No free memory (OOM imminent)","type":"ordered-list-item","key":"acffa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Database not accepting connections","type":"ordered-list-item","key":"djh50"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Replication lag > 5 minutes","type":"ordered-list-item","key":"56um4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":0,"style":"BOLD"}],"text":"Предупреждения (Email/Slack):","type":"unstyled","key":"5j8ol"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Disk utilization > 80% более 15 минут","type":"ordered-list-item","key":"6knui"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Lock contention > 100 locks/minute","type":"ordered-list-item","key":"6aobm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Query timeout rate > 5%","type":"ordered-list-item","key":"e43up"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Connection count > 80% of max_connections","type":"ordered-list-item","key":"7ojvr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":39,"offset":0,"style":"BOLD"}],"text":"Информационные алерты (Dashboard only):","type":"unstyled","key":"5eihr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Performance degradation > 20%","type":"ordered-list-item","key":"c3cnf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Index bloat > 30%","type":"ordered-list-item","key":"dddlc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Autovacuum falling behind","type":"ordered-list-item","key":"3favc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"}],"text":"6.5.3. План отката при проблемах","type":"header-three","key":"cc7ga"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Процедура отката (Runbook):","type":"unstyled","key":"f5b9o"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"## Экстренный откат изменений PostgreSQL\n\n### Шаг 1: Оценка ситуации (первые 5 минут)\n1. Проверить доступность БД\n2. Собрать основные метрики\n3. Определить scope проблемы\n\n### Шаг 2: Быстрый откат (5-15 минут)\nЕсли проблема в конфигурации:\n```bash\n# Восстановить backup конфигурации\ncp /var/lib/pgsql/data/postgresql.conf.backup /var/lib/pgsql/data/postgresql.conf\n\n# Перезапустить PostgreSQL\nsystemctl restart postgresql-14\n\n# Проверить доступность\npg_isready -h localhost -p 5432","type":"unstyled","key":"784so"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Шаг 3: Откат изменений схемы (15-60 минут)","type":"header-three","key":"efjkn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"sql","type":"unstyled","key":"4krot"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":54,"offset":0,"style":"ITALIC"},{"length":16,"offset":62,"style":"ITALIC"},{"length":26,"offset":128,"style":"ITALIC"}],"text":"-- Для каждого изменения схемы иметь обратную миграцию\nBEGIN;\n-- Откат индекса\nDROP INDEX CONCURRENTLY IF EXISTS problem_index;\n-- Откат изменения таблицы\nALTER TABLE problem_table DROP COLUMN IF EXISTS new_column;\nCOMMIT;","type":"unstyled","key":"9ngqs"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Шаг 4: Коммуникация","type":"header-three","key":"2c8nt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Уведомить команду разработки","type":"ordered-list-item","key":"9q41a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Обновить статус-страницу","type":"ordered-list-item","key":"el4oa"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Запланировать пост-мортем","type":"ordered-list-item","key":"a8dr9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Шаг 5: Пост-мортем анализ","type":"header-three","key":"qvi4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Собрать все логи и метрики","type":"ordered-list-item","key":"ecst2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Провести root cause анализ","type":"ordered-list-item","key":"37j0l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Обновить runbook","type":"ordered-list-item","key":"6l55t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Создать задачи по предотвращению","type":"ordered-list-item","key":"dtajb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"text","type":"unstyled","key":"5hvgf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"**Checklist для безопасного деплоя:**\n- [ ] Backup конфигурации\n- [ ] Backup данных\n- [ ] Проверка на staging\n- [ ] Постепенный rollout\n- [ ] Мониторинг после деплоя\n- [ ] План отката готов\n\n**Автоматизированные тесты после изменений:**\n```bash\n#!/bin/bash\n# Health check script\npg_isready -h localhost -p 5432 || exit 1\npsql -c \"SELECT 1\" || exit 1\npsql -c \"SELECT pg_is_in_recovery()\" | grep -q f || exit 1\npsql -c \"SELECT count(*) FROM pg_stat_activity WHERE state = 'active'\" || exit 1\necho \"All checks passed\"","type":"unstyled","key":"5395d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"ИТОГОВАЯ ДОРОЖНАЯ КАРТА","type":"header-three","key":"6a74i"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Неделя 1: Стабилизация","type":"header-three","key":"akj01"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Day 1-2: Конфигурационные изменения","type":"ordered-list-item","key":"44bvt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Day 3-4: Мониторинг и базовая оптимизация","type":"ordered-list-item","key":"2r3sr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Day 5-7: Анализ результатов и корректировка","type":"ordered-list-item","key":"4fv7a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"Неделя 2-3: Оптимизация","type":"header-three","key":"9ljid"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Индексы и схемы","type":"ordered-list-item","key":"45q3h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Запросы и паттерны доступа","type":"ordered-list-item","key":"9u8go"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Файловая система и ОС","type":"ordered-list-item","key":"b6c38"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"}],"text":"Месяц 2: Архитектурные улучшения","type":"header-three","key":"a8ikh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Read replicas","type":"ordered-list-item","key":"6mafe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Кэширование","type":"ordered-list-item","key":"8hr8u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Connection pooling","type":"ordered-list-item","key":"1s0gl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":0,"style":"BOLD"}],"text":"Месяц 3: Аппаратные улучшения","type":"header-three","key":"1qesh"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Дисковая подсистема","type":"ordered-list-item","key":"f69tf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Память","type":"ordered-list-item","key":"9vujm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Расширенный мониторинг","type":"ordered-list-item","key":"20bv6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Критерии успеха через 3 месяца:","type":"header-three","key":"7gtj3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Disk utilization: < 70% (улучшение с 85%)","type":"ordered-list-item","key":"9r7u9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Query p95 latency: < 100ms (улучшение на 50%)","type":"ordered-list-item","key":"a5guo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"System stability: 99.9% uptime","type":"ordered-list-item","key":"e16bl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Team productivity: 40% меньше времени на firefighting","type":"ordered-list-item","key":"2tkkg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"Финансовое обоснование:","type":"header-three","key":"1lob5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":14,"offset":0,"style":"BOLD"}],"text":"Текущие потери: 2 часа downtime в месяц × $500/час = $1,000/мес","type":"unordered-list-item","key":"bcql3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Затраты на оптимизацию: $5,000 (время команды + инструменты)","type":"unordered-list-item","key":"8ra6u"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":3,"offset":0,"style":"BOLD"}],"text":"ROI: 5 месяцев (без учета улучшения производительности)","type":"unordered-list-item","key":"7smmo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"Дополнительная выгода: Улучшение user experience, снижение рисков","type":"unordered-list-item","key":"56gcp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":6,"offset":0,"style":"BOLD"}],"text":"Резюме: План начинается с быстрых, безопасных изменений, которые дадут немедленный эффект. Затем постепенно внедряются более сложные оптимизации, каждая из которых измеряется и валидируется. Ключевой принцип - итеративный подход с постоянным мониторингом и корректировкой.","type":"unstyled","key":"4b9ne"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":47,"offset":0,"style":"BOLD"}],"text":"7. Диагностика специфичных проблем конфигурации","type":"header-two","key":"31frf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"1ncam"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":26,"offset":0,"style":"BOLD"}],"text":"7.1. НАСТРОЙКИ АВТОВАКУУМА","type":"header-three","key":"2c8rg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Текущие настройки:","type":"header-three","key":"b6ji4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"autovacuum_vacuum_scale_factor = 0.001","type":"unordered-list-item","key":"j4dl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"autovacuum_analyze_scale_factor = 0.005","type":"unordered-list-item","key":"8qgu2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"autovacuum_vacuum_cost_delay = 2ms","type":"unordered-list-item","key":"7abff"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"autovacuum_max_workers = 4","type":"unordered-list-item","key":"1n47h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"autovacuum_work_mem = 2GB","type":"unordered-list-item","key":"r8si"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"}],"text":"Влияние на производительность:","type":"header-three","key":"bjji7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":52,"offset":0,"style":"BOLD"}],"text":"Критическая проблема: Слишком агрессивный автовакуум","type":"unstyled","key":"5043j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":77,"style":"BOLD"},{"length":6,"offset":149,"style":"BOLD"},{"length":9,"offset":216,"style":"BOLD"}],"text":"autovacuum_vacuum_scale_factor = 0.001 (0.1%) - экстремально низкое значение\nПроблема: Автовакуум запускается при изменении всего 0.1% строк таблицы\nПример: Для таблицы с 10 млн строк → вакуум после 10,000 изменений\nРезультат: Постоянные запуски автовакуума, конкуренция с пользовательскими запросами","type":"unordered-list-item","key":"e2hc6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":71,"style":"BOLD"},{"length":9,"offset":110,"style":"BOLD"}],"text":"autovacuum_analyze_scale_factor = 0.005 (0.5%) - очень низкое значение\nПроблема: Частые обновления статистики\nРезультат: Дополнительная нагрузка на CPU и блокировки таблиц","type":"unordered-list-item","key":"dm3l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Проблема с производительностью:","type":"unstyled","key":"esbps"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":63,"style":"BOLD"},{"length":9,"offset":108,"style":"BOLD"}],"text":"autovacuum_vacuum_cost_delay = 2ms - слишком короткая задержка\nПроблема: Автовакуум работает почти без пауз\nРезультат: Постоянная фоновая нагрузка на IO (видно по высокой записи на диск)","type":"unordered-list-item","key":"7jc1h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":53,"style":"BOLD"},{"length":9,"offset":118,"style":"BOLD"}],"text":"autovacuum_max_workers = 4 - может быть недостаточно\nПроблема: При высокой нагрузке UPDATE/DELETE воркеры не успевают\nРезультат: Накопление мертвых строк (видно по bloat в статистике)","type":"unordered-list-item","key":"auiq8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":52,"style":"BOLD"},{"length":9,"offset":102,"style":"BOLD"}],"text":"autovacuum_work_mem = 2GB - опасно высокое значение\nПроблема: Каждый воркер может использовать до 2GB\nРезультат: При 4 воркерах → до 8GB памяти, что может вызвать OOM","type":"unordered-list-item","key":"bmk9o"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Рекомендации:","type":"header-three","key":"dgnor"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":36,"offset":0,"style":"ITALIC"},{"length":17,"offset":94,"style":"ITALIC"},{"length":18,"offset":169,"style":"ITALIC"},{"length":20,"offset":245,"style":"ITALIC"},{"length":36,"offset":267,"style":"ITALIC"},{"length":21,"offset":353,"style":"ITALIC"},{"length":19,"offset":423,"style":"ITALIC"}],"text":"-- Безопасные изменения (немедленно)\nALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05; -- 5% вместо 0.1%\nALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.1; -- 10% вместо 0.5%\nALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms'; -- Увеличить в 5 раз\n\n-- Оптимизация памяти и параллелизма\nALTER SYSTEM SET autovacuum_work_mem = '512MB'; -- Уменьшить в 4 раза\nALTER SYSTEM SET autovacuum_max_workers = 6; -- Увеличить на 50%","type":"unstyled","key":"1oe46"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"}],"text":"7.2. НАСТРОЙКИ КОНТРОЛЬНЫХ ТОЧЕК","type":"header-three","key":"2h0uf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Текущие настройки:","type":"header-three","key":"4v3v5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"checkpoint_timeout = 15min","type":"unordered-list-item","key":"1vfig"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"checkpoint_completion_target = 0.9","type":"unordered-list-item","key":"1eub6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"max_wal_size = 8GB","type":"unordered-list-item","key":"25una"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"min_wal_size = 2GB","type":"unordered-list-item","key":"5ba7c"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":35,"offset":0,"style":"BOLD"}],"text":"Связь с наблюдаемыми IO-проблемами:","type":"header-three","key":"6mhk8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":44,"offset":0,"style":"BOLD"}],"text":"Проблема 1: Слишком частые контрольные точки","type":"unstyled","key":"281o0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":53,"style":"BOLD"},{"length":8,"offset":128,"style":"BOLD"},{"length":9,"offset":205,"style":"BOLD"}],"text":"checkpoint_timeout = 15min - очень короткий интервал\nРасчет WAL генерации: При записи 10MB/s → за 15 минут генерируется 9GB WAL\nПроблема: max_wal_size = 8GB меньше, чем потенциальная генерация за 15 минут\nРезультат: Контрольные точки срабатывают по размеру, а не по времени","type":"unordered-list-item","key":"478r8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":34,"offset":0,"style":"BOLD"}],"text":"Проблема 2: Высокая фоновая запись","type":"unstyled","key":"cbjph"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":60,"style":"BOLD"},{"length":8,"offset":160,"style":"BOLD"},{"length":9,"offset":236,"style":"BOLD"}],"text":"checkpoint_completion_target = 0.9 - очень высокое значение\nМеханизм: PostgreSQL растягивает запись грязных страниц на 90% интервала между контрольными точками\nПроблема: При 15-минутных интервалах → 13.5 минут постоянной фоновой записи\nРезультат: Постоянная нагрузка на диск (видно по steady-state записи на диски /data)","type":"unordered-list-item","key":"7ilk7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":42,"offset":0,"style":"BOLD"}],"text":"Проблема 3: Несбалансированные размеры WAL","type":"unstyled","key":"30dnl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":39,"style":"BOLD"},{"length":9,"offset":86,"style":"BOLD"}],"text":"min_wal_size = 2GB, max_wal_size = 8GB\nПроблема: Маленький разброс (всего 4x разница)\nРезультат: Частые переключения WAL сегментов и контрольные точки","type":"unordered-list-item","key":"7soou"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Рекомендации:","type":"header-three","key":"2uh5a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"ITALIC"},{"length":19,"offset":80,"style":"ITALIC"},{"length":21,"offset":148,"style":"ITALIC"},{"length":21,"offset":218,"style":"ITALIC"},{"length":29,"offset":241,"style":"ITALIC"},{"length":21,"offset":325,"style":"ITALIC"},{"length":19,"offset":400,"style":"ITALIC"},{"length":18,"offset":473,"style":"ITALIC"}],"text":"-- Увеличить интервалы и объемы\nALTER SYSTEM SET checkpoint_timeout = '30min'; -- Удвоить интервал\nALTER SYSTEM SET max_wal_size = '32GB'; -- Увеличить в 4 раза\nALTER SYSTEM SET min_wal_size = '8GB'; -- Увеличить в 4 раза\n\n-- Оптимизация фоновой записи\nALTER SYSTEM SET checkpoint_completion_target = 0.7; -- Уменьшить нагрузку\nALTER SYSTEM SET bgwriter_delay = '200ms'; -- Увеличить с 10ms\nALTER SYSTEM SET bgwriter_lru_maxpages = 1000; -- Увеличить с 400","type":"unstyled","key":"3lsja"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"7.3. ПАРАМЕТРЫ ПАРАЛЛЕЛИЗМА","type":"header-three","key":"e2pcr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Текущие настройки:","type":"header-three","key":"b6mvb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"max_parallel_workers = 20","type":"unordered-list-item","key":"l8re"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":38,"style":"BOLD"}],"text":"max_parallel_workers_per_gather = 0 ← ПРОБЛЕМА","type":"unordered-list-item","key":"76it3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"max_parallel_maintenance_workers = 20","type":"unordered-list-item","key":"chmhr"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":38,"offset":0,"style":"BOLD"}],"text":"Почему параллельные запросы отключены?","type":"header-three","key":"247ph"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Анализ ситуации:","type":"unstyled","key":"48vf9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"max_parallel_workers_per_gather = 0 полностью отключает параллельное выполнение запросов","type":"ordered-list-item","key":"bg217"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"При этом max_parallel_workers = 20 и max_parallel_maintenance_workers = 20 настроены","type":"ordered-list-item","key":"1hbgj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":0,"style":"BOLD"}],"text":"Парадокс: Параллелизм доступен, но не используется для запросов","type":"ordered-list-item","key":"68p61"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Возможные причины:","type":"unstyled","key":"bmgtt"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":0,"style":"BOLD"}],"text":"Историческое решение: Возможно, в прошлом параллельные запросы вызывали проблемы","type":"ordered-list-item","key":"atcnu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"Консервативная настройка: Боязнь contention при 192 ядрах","type":"ordered-list-item","key":"bfb85"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"}],"text":"Ошибка конфигурации: Несогласованность настроек","type":"ordered-list-item","key":"askvn"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":39,"offset":0,"style":"BOLD"}],"text":"Проблемы из-за отключения параллелизма:","type":"unstyled","key":"82m5j"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"BOLD"}],"text":"Неэффективное использование CPU: 192 ядра, но тяжелые запросы выполняются одним процессом","type":"ordered-list-item","key":"7cbbm"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"}],"text":"Длительные операции: Full table scans, большие JOIN выполняются последовательно","type":"ordered-list-item","key":"4e65h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":0,"style":"BOLD"}],"text":"Увеличение времени блокировок: Длительные запросы дольше удерживают блокировки","type":"ordered-list-item","key":"bkqfl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Рекомендации:","type":"header-three","key":"5ah2p"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":0,"style":"ITALIC"},{"length":18,"offset":94,"style":"ITALIC"},{"length":18,"offset":169,"style":"ITALIC"},{"length":22,"offset":244,"style":"ITALIC"},{"length":22,"offset":323,"style":"ITALIC"},{"length":27,"offset":347,"style":"ITALIC"},{"length":17,"offset":431,"style":"ITALIC"}],"text":"-- Постепенное включение параллелизма\nALTER SYSTEM SET max_parallel_workers_per_gather = 2; -- Начать с малого\nALTER SYSTEM SET max_parallel_workers = 32; -- 25% от 192 ядер\nALTER SYSTEM SET parallel_setup_cost = 10; -- Уменьшить threshold\nALTER SYSTEM SET parallel_tuple_cost = 0.001; -- Уменьшить стоимость\n\n-- Для maintenance операций\nALTER SYSTEM SET max_parallel_maintenance_workers = 8; -- Уменьшить с 20","type":"unstyled","key":"cll58"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":21,"offset":0,"style":"BOLD"}],"text":"7.4. НАСТРОЙКИ ПАМЯТИ","type":"header-three","key":"80jko"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Текущие настройки:","type":"header-three","key":"dtqmq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":20,"offset":17,"style":"BOLD"}],"text":"work_mem = 1GB ← КРИТИЧЕСКАЯ ПРОБЛЕМА","type":"unordered-list-item","key":"7lnni"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"shared_buffers = 251807MB (~246GB)","type":"unordered-list-item","key":"d8hs6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"temp_buffers = 64MB","type":"unordered-list-item","key":"clva4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"maintenance_work_mem = 16GB","type":"unordered-list-item","key":"d3g7f"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":32,"offset":0,"style":"BOLD"}],"text":"Риски и возможности оптимизации:","type":"header-three","key":"eui41"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Критическая проблема: work_mem = 1GB","type":"unstyled","key":"7pkn6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":4,"offset":0,"style":"BOLD"}],"text":"Риск: Каждая операция сортировки/хеширования может использовать до 1GB","type":"unordered-list-item","key":"2fpfd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":6,"offset":0,"style":"BOLD"}],"text":"Пример: 100 одновременных запросов с сортировкой → потенциально 100GB RAM","type":"unordered-list-item","key":"5ml2g"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Реальность: При наблюдаемой нагрузке это может вызвать OOM killer","type":"unordered-list-item","key":"fs91l"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"},{"length":17,"offset":32,"style":"BOLD"}],"text":"Проблема: shared_buffers = 246GB (24% от 1ТБ RAM)","type":"unstyled","key":"5a16h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":0,"style":"BOLD"}],"text":"Текущее влияние: Двойное кэширование (shared_buffers + OS cache)","type":"unordered-list-item","key":"3trp3"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Эффективность: 707GB в кэше ОС показывает, что данные хорошо кэшируются","type":"unordered-list-item","key":"f1uor"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":0,"style":"BOLD"}],"text":"Проблема: 246GB в shared_buffers могут быть избыточными","type":"unordered-list-item","key":"bmvfo"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Проблема: maintenance_work_mem = 16GB","type":"unstyled","key":"1epqe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":4,"offset":0,"style":"BOLD"}],"text":"Риск: Операции VACUUM, CREATE INDEX могут использовать много памяти","type":"unordered-list-item","key":"bj5lg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":11,"offset":0,"style":"BOLD"}],"text":"Конкуренция: Может конфликтовать с autovacuum_work_mem = 2GB","type":"unordered-list-item","key":"buf6m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Проблема: temp_buffers = 64MB","type":"unstyled","key":"5jvmc"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":0,"style":"BOLD"}],"text":"Контекст: Используется для временных таблиц в сессии","type":"unordered-list-item","key":"856ks"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":4,"offset":0,"style":"BOLD"}],"text":"Риск: Может быть недостаточно для сложных запросов с временными таблицами","type":"unordered-list-item","key":"db8b"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Рекомендации:","type":"header-three","key":"1j31d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":33,"offset":0,"style":"ITALIC"},{"length":20,"offset":72,"style":"ITALIC"},{"length":20,"offset":138,"style":"ITALIC"},{"length":29,"offset":160,"style":"ITALIC"},{"length":21,"offset":234,"style":"ITALIC"},{"length":18,"offset":306,"style":"ITALIC"},{"length":29,"offset":326,"style":"ITALIC"},{"length":33,"offset":398,"style":"ITALIC"},{"length":19,"offset":480,"style":"ITALIC"},{"length":45,"offset":501,"style":"ITALIC"}],"text":"-- Безопасное уменьшение work_mem\nALTER SYSTEM SET work_mem = '128MB'; -- Уменьшить в 8 раз\nALTER SYSTEM SET hash_mem_multiplier = 2.0; -- Для hash операций\n\n-- Оптимизация shared_buffers\nALTER SYSTEM SET shared_buffers = '128GB'; -- Уменьшить в 2 раза\nALTER SYSTEM SET effective_cache_size = '700GB'; -- Реальный кэш ОС\n\n-- Настройка временной памяти\nALTER SYSTEM SET temp_buffers = '256MB'; -- Увеличить для сложных запросов\nALTER SYSTEM SET maintenance_work_mem = '4GB'; -- Уменьшить с 16GB\n\n-- Для больших сортировок во временных файлах\nALTER SYSTEM SET temp_file_limit = '20GB';","type":"unstyled","key":"86jr7"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"7.5. РАСШИРЕНИЯ И МОДУЛИ","type":"header-three","key":"5blf9"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"shared_preload_libraries = 'plpgsql, dblink, pgpro_scheduler, postgres_fdw, \n uuid-ossp, pg_hint_plan, pg_wait_sampling, pgpro_stats'","type":"unstyled","key":"c3tu6"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":30,"offset":0,"style":"BOLD"}],"text":"Влияние на производительность:","type":"header-three","key":"dqs3s"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":14,"offset":0,"style":"BOLD"}],"text":"1. pgpro_stats","type":"unstyled","key":"3gv6n"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Назначение: Сбор расширенной статистики (Postgres Pro)","type":"unordered-list-item","key":"ds8c2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":8,"offset":0,"style":"BOLD"}],"text":"Параметр: pgpro_stats.max = 20000","type":"unordered-list-item","key":"28uen"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"},{"length":10,"offset":9,"style":"BOLD"},{"length":10,"offset":54,"style":"BOLD"},{"length":8,"offset":106,"style":"BOLD"}],"text":"Влияние:\nПозитивное: Детальная статистика для анализа\nНегативное: Дополнительная нагрузка на CPU и память\nПроблема: Может вызывать дополнительные LWLock ожидания (видно в статистике)","type":"unordered-list-item","key":"29q8d"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":0,"style":"BOLD"}],"text":"2. pg_hint_plan","type":"unstyled","key":"fa41h"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Назначение: Подсказки для оптимизатора запросов","type":"unordered-list-item","key":"jdvp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"},{"length":10,"offset":9,"style":"BOLD"},{"length":10,"offset":60,"style":"BOLD"},{"length":10,"offset":129,"style":"BOLD"}],"text":"Влияние:\nПозитивное: Возможность фиксировать планы запросов\nНегативное: Неправильные подсказки могут ухудшить производительность\nСтатистика: Использование подсказок не видно в отчете","type":"unordered-list-item","key":"7rgqd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"}],"text":"3. pg_wait_sampling","type":"unstyled","key":"18tkk"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Назначение: Сбор статистики по ожиданиям","type":"unordered-list-item","key":"a0ecp"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"},{"length":10,"offset":9,"style":"BOLD"},{"length":10,"offset":52,"style":"BOLD"},{"length":8,"offset":102,"style":"BOLD"}],"text":"Влияние:\nПозитивное: Детальная диагностика ожиданий\nНегативное: Дополнительная нагрузка (минимальная)\nВажность: Критически важен для анализа текущих проблем","type":"unordered-list-item","key":"7hb2t"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"4. pgpro_scheduler","type":"unstyled","key":"cv08a"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Назначение: Планировщик задач Postgres Pro","type":"unordered-list-item","key":"1e4s1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Влияние: Дополнительные фоновые процессы, может создавать contention","type":"unordered-list-item","key":"91vpg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":0,"style":"BOLD"}],"text":"5. postgres_fdw","type":"unstyled","key":"a7u0e"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Назначение: Foreign data wrappers","type":"unordered-list-item","key":"59hrb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":7,"offset":0,"style":"BOLD"}],"text":"Влияние: Если не используется - бесполезная нагрузка","type":"unordered-list-item","key":"3p2tu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":13,"offset":0,"style":"BOLD"}],"text":"Рекомендации:","type":"header-three","key":"55qg0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":29,"offset":0,"style":"ITALIC"},{"length":20,"offset":72,"style":"ITALIC"},{"length":40,"offset":94,"style":"ITALIC"},{"length":41,"offset":211,"style":"ITALIC"},{"length":37,"offset":253,"style":"ITALIC"},{"length":82,"offset":291,"style":"ITALIC"},{"length":105,"offset":374,"style":"ITALIC"},{"length":35,"offset":481,"style":"ITALIC"}],"text":"-- 1. Оптимизация pgpro_stats\nALTER SYSTEM SET pgpro_stats.max = 5000; -- Уменьшить с 20000\n\n-- 2. Проверить использование расширений\nSELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL;\n\n-- 3. Отключить неиспользуемые расширения\n-- Если postgres_fdw не используется:\n-- ALTER SYSTEM SET shared_preload_libraries = 'plpgsql, dblink, pgpro_scheduler, \n-- uuid-ossp, pg_hint_plan, pg_wait_sampling, pgpro_stats';\n\n-- 4. Мониторинг влияния расширений\nCREATE VIEW extension_impact AS\nSELECT \n name,\n settings,\n pg_size_pretty(pg_total_relation_size(oid)) as size\nFROM pg_extension;","type":"unstyled","key":"8dhtj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":19,"offset":0,"style":"BOLD"}],"text":"СИНТЕЗ ВЗАИМОСВЯЗЕЙ","type":"header-three","key":"7d4pu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":41,"offset":0,"style":"BOLD"}],"text":"Как настройки создают каскадные проблемы:","type":"header-three","key":"57ain"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Цепочка проблем:","type":"unstyled","key":"9l1b5"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Агрессивный автовакуум → Частые запуски → Конкуренция за IO и блокировки","type":"ordered-list-item","key":"5iq27"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"Частые контрольные точки → Постоянная фоновая запись → Высокая утилизация дисков","type":"ordered-list-item","key":"3tjnb"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":23,"offset":0,"style":"BOLD"}],"text":"Отключенный параллелизм → Длительные запросы → Увеличение времени блокировок","type":"ordered-list-item","key":"21ufq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":16,"offset":0,"style":"BOLD"}],"text":"Большой work_mem → Риск OOM → Потенциальный свопинг","type":"ordered-list-item","key":"8l0nl"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"BOLD"}],"text":"Избыточные shared_buffers → Двойное кэширование → Неэффективное использование памяти","type":"ordered-list-item","key":"dpij4"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":36,"offset":0,"style":"BOLD"}],"text":"Корреляция с наблюдаемыми метриками:","type":"unstyled","key":"14aim"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Высокая запись на диск (w/s 600-735) ↔ Частые контрольные точки","type":"unordered-list-item","key":"m529"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Высокие LWLock ожидания ↔ Конкуренция от автовакуума и расширений","type":"unordered-list-item","key":"f0scd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Длительные блокировки ↔ Отсутствие параллелизма для тяжелых запросов","type":"unordered-list-item","key":"50bnv"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":22,"offset":0,"style":"BOLD"}],"text":"Приоритет исправлений:","type":"header-three","key":"9seia"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":37,"offset":0,"style":"BOLD"}],"text":"Высокий риск/высокая отдача (День 1):","type":"unstyled","key":"bog3k"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":36,"offset":0,"style":"ITALIC"},{"length":41,"offset":75,"style":"ITALIC"},{"length":34,"offset":230,"style":"ITALIC"}],"text":"-- 1. Исправить критический work_mem\nALTER SYSTEM SET work_mem = '128MB';\n\n-- 2. Уменьшить агрессивность автовакуума\nALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.05;\nALTER SYSTEM SET autovacuum_vacuum_cost_delay = '10ms';\n\n-- 3. Включить базовый параллелизм\nALTER SYSTEM SET max_parallel_workers_per_gather = 2;","type":"unstyled","key":"fbbsd"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":24,"offset":0,"style":"BOLD"}],"text":"Средний риск (День 2-3):","type":"unstyled","key":"baimg"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":38,"offset":0,"style":"ITALIC"},{"length":35,"offset":180,"style":"ITALIC"}],"text":"-- 4. Оптимизировать контрольные точки\nALTER SYSTEM SET checkpoint_timeout = '30min';\nALTER SYSTEM SET max_wal_size = '32GB';\nALTER SYSTEM SET checkpoint_completion_target = 0.7;\n\n-- 5. Оптимизировать shared_buffers\nALTER SYSTEM SET shared_buffers = '128GB';","type":"unstyled","key":"5h6kf"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":40,"offset":0,"style":"BOLD"}],"text":"Низкий риск/тонкая настройка (Неделя 1):","type":"unstyled","key":"2hka2"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":31,"offset":0,"style":"ITALIC"},{"length":37,"offset":74,"style":"ITALIC"},{"length":32,"offset":201,"style":"ITALIC"}],"text":"-- 6. Оптимизировать расширения\nALTER SYSTEM SET pgpro_stats.max = 5000;\n\n-- 7. Оптимизировать временную память\nALTER SYSTEM SET temp_buffers = '256MB';\nALTER SYSTEM SET maintenance_work_mem = '4GB';\n\n-- 8. Оптимизировать параллелизм\nALTER SYSTEM SET max_parallel_workers = 32;\nALTER SYSTEM SET parallel_setup_cost = 10;","type":"unstyled","key":"173qe"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"BOLD"}],"text":"План валидации изменений:","type":"header-three","key":"qtf8"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":27,"offset":0,"style":"BOLD"}],"text":"Для каждой группы настроек:","type":"unstyled","key":"2o6ts"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":25,"offset":0,"style":"ITALIC"},{"length":31,"offset":193,"style":"ITALIC"},{"length":26,"offset":367,"style":"ITALIC"}],"text":"-- Мониторинг автовакуума\nSELECT \n schemaname,\n relname,\n last_autovacuum,\n n_dead_tup,\n autovacuum_count\nFROM pg_stat_user_tables\nWHERE n_dead_tup > 0\nORDER BY n_dead_tup DESC\nLIMIT 10;\n\n-- Мониторинг контрольных точек\nSELECT \n checkpoints_timed,\n checkpoints_req,\n checkpoint_write_time,\n checkpoint_sync_time,\n buffers_checkpoint\nFROM pg_stat_bgwriter;\n\n-- Мониторинг параллелизма\nSELECT \n query,\n plans,\n total_plan_time,\n executions,\n workers_planned,\n workers_launched\nFROM pg_stat_statements\nWHERE workers_planned > 0;","type":"unstyled","key":"7nabu"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":15,"offset":0,"style":"BOLD"}],"text":"Метрики успеха:","type":"unstyled","key":"68cv0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Снижение IO записи на 20-30%","type":"ordered-list-item","key":"aqt77"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Уменьшение LWLock ожиданий на 30-40%","type":"ordered-list-item","key":"avin0"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Улучшение времени отклика запросов на 15-25%","type":"ordered-list-item","key":"4tea"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"Стабильное использование памяти (< 90% от RAM)","type":"ordered-list-item","key":"ct3me"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":18,"offset":0,"style":"BOLD"}],"text":"Риски и митигация:","type":"unstyled","key":"7ir57"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":4,"offset":0,"style":"BOLD"},{"length":9,"offset":66,"style":"BOLD"}],"text":"Риск: Слишком резкое уменьшение work_mem → ошибки \"out of memory\"\nМитигация: Постепенное уменьшение: 1GB → 512MB → 256MB → 128MB","type":"ordered-list-item","key":"3k2e1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":4,"offset":0,"style":"BOLD"},{"length":9,"offset":54,"style":"BOLD"}],"text":"Риск: Увеличение max_wal_size → больше места на диске\nМитигация: Мониторинг использования WAL, наличие 2x свободного места","type":"ordered-list-item","key":"740vq"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":4,"offset":0,"style":"BOLD"},{"length":9,"offset":58,"style":"BOLD"}],"text":"Риск: Включение параллелизма → увеличение нагрузки на CPU\nМитигация: Начинать с max_parallel_workers_per_gather = 2, мониторить CPU usage","type":"ordered-list-item","key":"3ec5m"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[{"length":10,"offset":0,"style":"BOLD"}],"text":"Заключение:","type":"header-three","key":"2tbte"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":" Настройки PostgreSQL явно неоптимальны и создают каскадные проблемы. Начинать нужно с наиболее критичных (work_mem, автовакуум), затем оптимизировать контрольные точки и память, и только потом включать параллелизм и тонкую настройку расширений.","type":"unstyled","key":"35qg1"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"unstyled","key":"8uvnj"},{"data":{},"depth":0,"entityRanges":[],"inlineStyleRanges":[],"text":"","type":"marker","key":"QmVHXZ7jQ7YX0iBsQy5J"}],"entityMap":{"0":{"data":{"href":"https://dzen.ru/away?to=https%3A%2F%2Fgithub.com%2Fpg-expecto%2Fpg_expecto","url":"https://dzen.ru/away?to=https%3A%2F%2Fgithub.com%2Fpg-expecto%2Fpg_expecto"},"mutability":"MUTABLE","type":"LINK"},"1":{"data":{"href":"https://dzen.ru/away?to=https%3A%2F%2Fgithub.com%2Fpg-expecto%2Fpg_expecto","url":"https://dzen.ru/away?to=https%3A%2F%2Fgithub.com%2Fpg-expecto%2Fpg_expecto"},"mutability":"MUTABLE","type":"LINK"}}},"mentions":[]}