The post has been translated automatically. Original language: Russian
The original is on the main technical channel (Edits and additions are possible).
Development and experimental testing of a set of load profiling functions based on Markov chains with an assessment of the completeness of reference profiles, sensitivity to anomaly detection and resistance to data leaks in the PostgreSQL environment.
GitHub - pg_expecto complex for statistical performance analysis and PostgreSQL database testing
The preface
Ensuring DBMS performance in a dynamically changing workload remains one of the key tasks of database administration. Traditional threshold monitoring methods do not always allow timely detection of precursors of failures, which stimulates the search for probabilistic models capable of detecting subtle changes in the structure of transitions between system states.
This study examines the implementation of the Markov productivity model integrated into the pg_expecto project, which aggregates minute metrics over three time horizons, builds a reference profile of "normal" behavior, and calculates Z-scores to register deviations.
The purpose of the work is to evaluate the functional completeness of the created components, identify limitations associated with insufficient historical data, and suggest ways to increase the reliability of incident forecasting.
Theoretical justification of the work
Markov Chain application for Low incident rate performance/load profiling
Summary analytical report on the implementation of the Markov chain and DBMS load profiling
1. Composition and functionality of implemented components
Markov Chain
It models transitions between performance states, calculates probabilities, and predicts risk. Implemented in markov_chain_functions.sql based on tables: transition_log (transition log), markov_frequencies (accumulated frequencies), markov_probabilities (probability matrix), markov_absorbing (absorbing matrix), critical_states (list of emergency states).
Load profiling
It provides collection and aggregation of profile metrics over three time horizons: operational (60 minutes), daily (24 hours) and weekly (7 days). Implemented by the calculate_profile_metrics, save_operational_profile, save_daily_profile, and save_weekly_profile functions from the markov_chain_profile_functions.sql file.
The reference profile
Builds the "normal" behavior of the system based on historical data, excluding periods of incidents. The build_baseline_profile function saves the results to the profile_baseline table, averaged by hour of the day and day of the week.
Anomaly detection
Compares the current profile with the benchmark using Z-scores for each metric. When the threshold is exceeded, the event is recorded in the anomaly_log table. Implemented by the functions detect_anomaly, check_and_log_anomalyes and log_anomaly.
Additional utilities
The append_performance_history function allows you to incrementally add new data from cluster_stat_median to performance_history without losing old records (unlike fill_performance_history, which performs TRUNCATE).
All components have been successfully tested as part of the experiment, and the main work scenarios have been completed without critical errors.
2. Analysis of experimental data
2.1. Availability and quality of the source data
- The cluster_stat_median table contains data from 2026-06-22 to 2026-07-03 (up to 10:24) with minute discreteness, but with gaps.
- After calling append_performance_history, the performance_history table has expanded to 14,727 records, covering the period from 2026-06-22 to 2026-07-03 10:25. This ensures a continuous history of metrics.
- The transition_log has 13,137 transitions over the same period, which exceeds the threshold for enabling forgetting (5,000).
- The list of critical states (critical_states) contains 23 states filled in based on empirical risks.
Conclusion: The initial data is sufficient for modeling and profiling – the volume and time span allow you to train a Markov chain and build reference profiles.
2.2. Building a reference profile
- Calling build_baseline_profile(..., 'default', 60) filled 99 of the 168 possible slots (hour × day of the week combinations).
- The distribution by day of the week is uneven: the most slots are on Tuesdays and Thursdays (19 each), the least on Fridays (9) and Sundays (10).
- The reason for the incompleteness is the insufficient amount of data in the performance_history for individual hours on certain days, which is either due to omissions in the source data or the exclusion of incident windows.
Conclusion: The benchmark is incomplete, so for many slots the anomaly check is skipped (with a warning). At the stage of data accumulation, this is acceptable, but in the future it is necessary to increase the period of building a benchmark or apply methods of filling in gaps.
2.3. Saving profiles and detecting anomalies
- The functions save_operational_profile, save_daily_profile and save_weekly_profile were executed without errors.
- While maintaining the operational profile, anomalies with very high Z-scores were recorded:Entropy is Z ≈ 23.2 (operational), ≈ 34.3 (daily), ≈ 35.7 (weekly).Self‑loop ratio – Z ≈ 2.26 (operational), ≈ 2.00 (weekly).
- Such high values indicate a strong deviation of the current profiles from the reference ones, however, due to the incompleteness of the reference (standard deviations may be underestimated) these anomalies may be false.
Conclusion: The anomaly detection mechanism works, but requires calibration of thresholds and improvement of the reference to reduce false alarms.
2.4. Resistance to the absence of a reference
- The modified detect_anomaly and check_and_log_anomalies functions correctly handle situations when there is no benchmark for a particular slot – a warning is displayed instead of an error, and the check is skipped.
- This allows the system to continue working even with an incomplete reference, without interrupting the process of saving profiles.
3. Assessment of the quality and maturity of the system
- Functional completeness – ⭐⭐⭐⭐⭐All the stated functions have been implemented and tested in real-world scenarios.
- Error tolerance – ⭐⭐⭐⭐The system correctly handles the absence of a reference, errors are logged in error tables, but no critical failures occur.
- The quality of the benchmark is only 99 out of 168 slots are filled, which limits the accuracy of anomaly detection and leads to false alarms.
- Anomaly detection accuracy – ⭐⭐⭐The high sensitivity (many warnings) is due to an unrepresentative benchmark, not to errors in logic.
- Easy operation – The functions have intuitive interfaces, there are ready-made examples for cron, documentation in the comments.
4. Recommendations for improvement
- Increase the reference period to 30 days to accumulate statistics for each hour and day of the week.
- Add to build_baseline_profile the option to fill gaps with global averages (averaged over all days of the week) for slots without data.
- Increase the Z-score threshold from 2.0 to 3.0 or 3.5 until the benchmark becomes more stable to reduce the number of false positives.
- Set up a weekly automatic recalculation of the benchmark using a sliding window (for example, the last 30 days).
- To develop a report on the quality of the reference, showing which slots are missing data and what are the standard deviations – this will help to assess the reliability of anomaly detection.
5. Final conclusion
The developed load profiling system based on the Markov chain is successfully functioning, collects and stores profiles, and detects deviations from the reference. The main problem is the incompleteness of the reference profile, which reduces the reliability of anomaly detection. With the accumulation of more data and periodic reconstruction of the reference, the system will reach the design level of accuracy.
A general plan for practical configuration and analysis of the DBMS load profile
1. Setting up a regular data collection
- Updating the performance_history with new data should be performed every 5-10 minutes (or once an hour) using a query: SELECT append_performance_history((SELECT MAX(ts) FROM performance_history), now());
- Saving an operational profile – every 5 minutes: SELECT save_operational_profile();
- Saving a daily profile – every hour: SELECT save_daily_profile();
- Saving a weekly profile – once a day, for example, at 01:00: SELECT save_weekly_profile();
2. Building and maintaining a reference profile
- The initial construction of the benchmark should be performed once, using the maximum available historical period (at least 14 days, preferably 30): SELECT build_baseline_profile((SELECT MIN(ts) FROM performance_history),now(),'default',60);
- Periodic updating of the benchmark – once a week or after accumulation of new data (for example, every Sunday): DELETE FROM profile_baseline WHERE baseline_name = 'default';SELECT build_baseline_profile((SELECT MIN(ts) FROM performance_history),now(),'default',60);
- Benchmark completeness control – check the number of filled slots weekly and, if necessary, expand the period or adjust the excluded windows: SELECT COUNT(DISTINCT(hour, dow)) FROM profile_baseline WHERE baseline_name='default';
3. Setting anomaly detection thresholds
- By default, the Z-score threshold is 2.0. If the benchmark is unstable, it is recommended to increase it to 3.0 or 3.5.
- You can change the threshold in calls to the check_and_log_anomalies functions (by passing the second argument) or directly in the code of the save_*_profile functions.
- After improving the benchmark (completeness > 90%), the threshold can be returned to 2.0.
4. Interpretation and response to anomalies
- Viewing the latest anomalies: SELECT * FROM anomaly_log ORDER BY detected_at DESC LIMIT 20;
- Analysis of the affected metrics:Entropy – growth indicates an increase in the diversity of states (possible instability of the system).Self‑loop ratio – growth indicates that you are stuck in one state (slowing down or hanging).Critical ratio – an increase in the proportion of critical conditions signals an approach to incidents.Avg correlation – a drop may indicate a mismatch between speed and expectations.
- Confirmation of an anomaly (if it is real) or a mark as false: UPDATE anomaly_log SET acknowledged = TRUE, knowledged_by = 'admin', knowledged_at = now() WHERE id = <id>;
- Integration with the notification system – configure sending notifications when anomalies with a high anomaly_score appear (for example, > 10).
5. Periodic analysis and adjustment
- Checking the completeness of the benchmark – weekly:SELECT COUNT(DISTINCT(hour, dow)) FROM profile_baseline WHERE baseline_name='default';
- Markov Chain forecast quality assessment – daily:SELECT mchain_quality_report(CURRENT_DATE - 7, CURRENT_DATE - 1);
- Stability trend analysis – weekly:SELECT report_stability_trend(30);
- Optimization of forgetting parameters – when changing the frequency of incidents:CALL optimize_forgetting_params();
- Critical status review – weekly:SELECT refresh_critical_states();
6. Examples of control requests for operational monitoring
- Checking the latest profile and related anomalies: SELECT pa.ts, pa.profile_type, pa.avg_correlation, pa.entropy,al.anomaly_score, al.affected_metricsFROM profile_aggregated paLEFT JOIN anomaly_log al ON al.profile_type = pa.profile_typeAND al.detected_at > pa.ts - INTERVAL '1 minute'ORDER BY pa.ts DESC LIMIT 5;
- Summary of anomalies for the week (by profile type): SELECT profile_type, COUNT(*) AS anomalies, AVG(anomaly_score) AS avg_scoreFROM anomaly_logWHERE detected_at >= CURRENT_DATE - 7GROUP BY profile_type;
7. Roadmap for bringing the system to commercial operation
- Data accumulation – ensure regular replenishment of performance_history for at least 30 days.
- Improving the benchmark is to rebuild the benchmark after accumulating a full cycle (7 days × 24 hours) and achieve filling of > 95% of the slots.
- Threshold setting – based on the collected false alarm statistics, set optimal Z-score thresholds.
- Automation – implement all cron tasks and set up alerts.
- Visualization integration – upload data from profile_aggregated and anomaly_log to Grafana or a similar system for visual monitoring.
- A regular audit is to review critical conditions and forgetting parameters on a monthly basis to adapt to the changing workload.
The report was prepared based on the experimental protocol. test3.txt and the current implementation of profiling functions.
Afterword
The experiment confirmed the operability of all implemented modules: the Markov chain correctly calculates transition probabilities, profiles are saved without errors, and the anomaly mechanism steadily handles the absence of reference slots.
However, the main bottleneck remains the incompleteness of the reference profile – only 99 out of 168 combinations of the hour and day of the week are filled in, which leads to an underestimation of the standard deviations and, as a result, to false high Z-estimates.
For the transition to industrial operation, it is recommended to extend the period of building the benchmark to 30 days, introduce global averages to fill in gaps, raise the detection threshold to 3.0–3.5 sigma, and organize weekly automatic rebuilding of the benchmark using a sliding window.
Further development of the system is seen in integration with visualization panels, adaptive adjustment of forgetting parameters and the creation of reports on the quality of the reference, which will reduce the proportion of false positives and increase confidence in predictive signals.
Оригинал — на основном техническом канале (Возможны правки и дополнения).
Разработка и экспериментальная апробация комплекса функций профилирования нагрузки на базе цепей Маркова с оценкой полноты эталонных профилей, чувствительности детектирования аномалий и устойчивости к пропускам данных в среде PostgreSQL.
Предисловие
Обеспечение производительности СУБД в условиях динамически меняющейся рабочей нагрузки остаётся одной из ключевых задач администрации баз данных. Традиционные пороговые методы мониторинга не всегда позволяют своевременно выявлять предвестники сбоев, что стимулирует поиск вероятностных моделей, способных улавливать тонкие изменения в структуре переходов между состояниями системы.
В настоящем исследовании рассматривается реализация марковской модели производительности, интегрированной в проект pg_expecto, которая агрегирует минутные метрики по трём временным горизонтам, строит эталонный профиль «нормального» поведения и вычисляет Z-оценки для регистрации отклонений.
Цель работы – оценить функциональную полноту созданных компонентов, выявить ограничения, связанные с недостаточностью исторических данных, и предложить пути повышения достоверности прогнозирования инцидентов.
Теоретическое обоснование работ
Применение цепи Маркова для профилирования производительности/нагрузки при низкой частоте инцидентов
Сводный аналитический отчёт по реализации цепи Маркова и профилирования нагрузки СУБД
1. Состав и функциональность реализованных компонентов
Цепь Маркова
Моделирует переходы между состояниями производительности, рассчитывает вероятности и прогнозирует риск. Реализована в markov_chain_functions.sql на основе таблиц: transition_log (журнал переходов), markov_frequencies (накопленные частоты), markov_probabilities (матрица вероятностей), markov_absorbing (поглощающая матрица), critical_states (список аварийных состояний).
Профилирование нагрузки
Обеспечивает сбор и агрегацию метрик профиля за три временных горизонта: оперативный (60 минут), суточный (24 часа) и недельный (7 дней). Реализовано функциями calculate_profile_metrics, save_operational_profile, save_daily_profile, save_weekly_profile из файла markov_chain_profile_functions.sql.
Эталонный профиль
Строит «нормальное» поведение системы по историческим данным, исключая периоды инцидентов. Функция build_baseline_profile сохраняет результаты в таблицу profile_baseline с усреднением по часу дня и дню недели.
Обнаружение аномалий
Сравнивает текущий профиль с эталоном с помощью Z-оценок для каждой метрики. При превышении порога записывает событие в таблицу anomaly_log. Реализовано функциями detect_anomaly, check_and_log_anomalies и log_anomaly.
Дополнительные утилиты
Функция append_performance_history позволяет инкрементально добавлять новые данные из cluster_stat_median в performance_history без потери старых записей (в отличие от fill_performance_history, которая выполняет TRUNCATE).
Все компоненты успешно протестированы в рамках эксперимента, основные сценарии работы выполнены без критических ошибок.
2. Анализ экспериментальных данных
2.1. Наличие и качество исходных данных
- Таблица cluster_stat_median содержит данные с 2026-06-22 по 2026-07-03 (до 10:24) с минутной дискретностью, но с пропусками.
- После вызова append_performance_history таблица performance_history пополнилась до 14 727 записей, покрывая период с 2026-06-22 по 2026-07-03 10:25. Это обеспечивает непрерывную историю метрик.
- Журнал переходов (transition_log) насчитывает 13 137 переходов за тот же период, что превышает порог включения забывания (5 000).
- Список критических состояний (critical_states) содержит 23 состояния, заполненных на основе эмпирических рисков.
Вывод: Исходные данные достаточны для моделирования и профилирования – объём и временной охват позволяют обучать цепь Маркова и строить эталонные профили.
2.2. Построение эталонного профиля
- Вызов build_baseline_profile(..., 'default', 60) заполнил 99 из 168 возможных слотов (комбинаций час × день недели).
- Распределение по дням недели неравномерное: больше всего слотов во вторник и четверг (по 19), меньше всего – в пятницу (9) и воскресенье (10).
- Причина неполноты – недостаточное количество данных в performance_history для отдельных часов в определённые дни, что связано либо с пропусками в исходных данных, либо с исключением инцидентных окон.
Вывод: Эталон неполный, поэтому для многих слотов проверка аномалий пропускается (с предупреждением). На этапе накопления данных это допустимо, но в перспективе требуется увеличение периода построения эталона или применение методов заполнения пропусков.
2.3. Сохранение профилей и обнаружение аномалий
- Функции save_operational_profile, save_daily_profile и save_weekly_profile выполнились без ошибок.
- При сохранении оперативного профиля зафиксированы аномалии с очень высокими Z-оценками:Энтропия – Z ≈ 23.2 (operational), ≈ 34.3 (daily), ≈ 35.7 (weekly).Self‑loop ratio – Z ≈ 2.26 (operational), ≈ 2.00 (weekly).
- Столь высокие значения указывают на сильное отклонение текущих профилей от эталонных, однако из-за неполноты эталона (стандартные отклонения могут быть занижены) эти аномалии могут быть ложными.
Вывод: Механизм обнаружения аномалий работает, но требует калибровки порогов и улучшения эталона для снижения ложных срабатываний.
2.4. Устойчивость к отсутствию эталона
- Модифицированные функции detect_anomaly и check_and_log_anomalies корректно обрабатывают ситуации, когда эталон для конкретного слота отсутствует – вместо ошибки выводится предупреждение, а проверка пропускается.
- Это позволяет системе продолжать работу даже при неполном эталоне, не прерывая процесс сохранения профилей.
3. Оценка качества и зрелости системы
- Функциональная полнота – ⭐⭐⭐⭐⭐Все заявленные функции реализованы и протестированы в реальных сценариях.
- Устойчивость к ошибкам – ⭐⭐⭐⭐Система корректно обрабатывает отсутствие эталона, ошибки логируются в таблицы ошибок, но критических сбоев не возникает.
- Качество эталона – ⭐⭐Заполнено лишь 99 из 168 слотов, что ограничивает точность обнаружения аномалий и приводит к ложным срабатываниям.
- Точность обнаружения аномалий – ⭐⭐⭐Высокая чувствительность (много предупреждений) связана с нерепрезентативным эталоном, а не с ошибками в логике.
- Простота эксплуатации – ⭐⭐⭐⭐Функции имеют интуитивные интерфейсы, есть готовые примеры для cron, документация в комментариях.
4. Рекомендации по улучшению
- Увеличить период построения эталона до 30 дней, чтобы накопить статистику по каждому часу и дню недели.
- Добавить в build_baseline_profile опцию заполнения пропусков глобальными средними значениями (усреднёнными по всем дням недели) для слотов без данных.
- Повысить порог Z-оценки с 2.0 до 3.0 или 3.5, пока эталон не станет более стабильным, чтобы уменьшить количество ложных срабатываний.
- Настроить еженедельный автоматический пересчёт эталона с использованием скользящего окна (например, последние 30 дней).
- Разработать отчёт о качестве эталона, показывающий, для каких слотов нет данных и каковы стандартные отклонения – это поможет оценить надёжность обнаружения аномалий.
5. Итоговое заключение
Разработанная система профилирования нагрузки на основе цепи Маркова успешно функционирует, собирает и сохраняет профили, обнаруживает отклонения от эталона. Основная проблема – неполнота эталонного профиля, что снижает достоверность обнаружения аномалий. При накоплении большего количества данных и периодическом перестроении эталона система выйдет на проектный уровень точности.
Общий план практической настройки и анализа профиля нагрузки СУБД
1. Настройка регулярного сбора данных
- Пополнение performance_history новыми данными – выполнять каждые 5–10 минут (или раз в час) с помощью запроса: SELECT append_performance_history((SELECT MAX(ts) FROM performance_history), now());
- Сохранение оперативного профиля – каждые 5 минут: SELECT save_operational_profile();
- Сохранение суточного профиля – каждый час: SELECT save_daily_profile();
- Сохранение недельного профиля – раз в сутки, например, в 01:00: SELECT save_weekly_profile();
2. Построение и поддержка эталонного профиля
- Первичное построение эталона – выполнить один раз, используя максимальный доступный исторический период (не менее 14 дней, желательно 30): SELECT build_baseline_profile((SELECT MIN(ts) FROM performance_history),now(),'default',60);
- Периодическое обновление эталона – раз в неделю или после накопления новых данных (например, каждое воскресенье): DELETE FROM profile_baseline WHERE baseline_name = 'default';SELECT build_baseline_profile((SELECT MIN(ts) FROM performance_history),now(),'default',60);
- Контроль полноты эталона – еженедельно проверять количество заполненных слотов и, при необходимости, расширять период или корректировать исключаемые окна: SELECT COUNT(DISTINCT (hour, dow)) FROM profile_baseline WHERE baseline_name='default';
3. Настройка порогов обнаружения аномалий
- По умолчанию используется порог Z-оценки = 2.0. При нестабильном эталоне рекомендуется повысить его до 3.0 или 3.5.
- Изменить порог можно в вызовах функций check_and_log_anomalies (передавая второй аргумент) или непосредственно в коде save_*_profile функций.
- После улучшения эталона (полнота > 90%) можно вернуть порог к 2.0.
4. Интерпретация и реагирование на аномалии
- Просмотр последних аномалий: SELECT * FROM anomaly_log ORDER BY detected_at DESC LIMIT 20;
- Анализ затронутых метрик:Энтропия – рост указывает на увеличение разнообразия состояний (возможна нестабильность системы).Self‑loop ratio – рост говорит о зацикливании в одном состоянии (замедление или зависание).Critical ratio – рост доли критических состояний сигнализирует о приближении к инцидентам.Avg correlation – падение может свидетельствовать о рассогласовании скорости и ожиданий.
- Подтверждение аномалии (если она реальна) или отметка как ложной: UPDATE anomaly_log SET acknowledged = TRUE, acknowledged_by = 'admin', acknowledged_at = now() WHERE id = <id>;
- Интеграция с системой оповещения – настроить отправку уведомлений при появлении аномалий с высоким anomaly_score (например, > 10).
5. Периодический анализ и корректировка
- Проверка полноты эталона – еженедельно:SELECT COUNT(DISTINCT (hour, dow)) FROM profile_baseline WHERE baseline_name='default';
- Оценка качества прогнозов цепи Маркова – ежедневно:SELECT mchain_quality_report(CURRENT_DATE - 7, CURRENT_DATE - 1);
- Анализ тренда стабильности – еженедельно:SELECT report_stability_trend(30);
- Оптимизация параметров забывания – при изменении частоты инцидентов:CALL optimize_forgetting_params();
- Пересмотр критических состояний – еженедельно:SELECT refresh_critical_states();
6. Примеры контрольных запросов для оперативного мониторинга
- Проверка последнего профиля и связанных с ним аномалий: SELECT pa.ts, pa.profile_type, pa.avg_correlation, pa.entropy,al.anomaly_score, al.affected_metricsFROM profile_aggregated paLEFT JOIN anomaly_log al ON al.profile_type = pa.profile_typeAND al.detected_at > pa.ts - INTERVAL '1 minute'ORDER BY pa.ts DESC LIMIT 5;
- Сводка по аномалиям за неделю (по типам профилей): SELECT profile_type, COUNT(*) AS anomalies, AVG(anomaly_score) AS avg_scoreFROM anomaly_logWHERE detected_at >= CURRENT_DATE - 7GROUP BY profile_type;
7. Дорожная карта по доведению системы до промышленной эксплуатации
- Накопление данных – обеспечить регулярное пополнение performance_history в течение минимум 30 дней.
- Улучшение эталона – перестроить эталон после накопления полного цикла (7 дней × 24 часа) и добиться заполнения > 95% слотов.
- Настройка порогов – на основе собранной статистики ложных срабатываний установить оптимальные пороги Z-оценок.
- Автоматизация – внедрить все cron-задачи и настроить алертинг.
- Интеграция с визуализацией – выгружать данные из profile_aggregated и anomaly_log в Grafana или аналогичную систему для наглядного мониторинга.
- Регулярный аудит – ежемесячно пересматривать критические состояния и параметры забывания для адаптации к изменяющейся нагрузке.
Отчёт подготовлен на основе протокола эксперимента test3.txt и текущей реализации функций профилирования.
Послесловие
Проведённый эксперимент подтвердил работоспособность всех реализованных модулей: цепь Маркова корректно вычисляет вероятности переходов, профили сохраняются без ошибок, а механизм аномалий устойчиво обрабатывает отсутствие эталонных слотов.
Однако главным узким местом остаётся неполнота эталонного профиля – лишь 99 из 168 комбинаций часа и дня недели заполнены, что приводит к заниженной оценке стандартных отклонений и, как следствие, к ложным высоким Z-оценкам.
Для перехода к промышленной эксплуатации рекомендуется расширить период построения эталона до 30 суток, внедрить восполнение пропусков глобальными средними, повысить порог обнаружения до 3,0–3,5 сигм и организовать еженедельное автоматическое перестроение эталона по скользящему окну.
Дальнейшее развитие системы видится в интеграции с визуализационными панелями, адаптивной настройке параметров забывания и создании отчётности о качестве эталона, что позволит снизить долю ложных срабатываний и повысить доверие к предиктивным сигналам.
Share
