Постановка задачи.

 

Построить систему мониторинга быстродействия выбранных запросов.

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

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

Собранная статистика хранится в сервисных таблицах на отдельном сервере вместе с набором bash-скриптов для настройки отчетов и мониторинга.

Сервисные таблицы

 

Целевой хост

CREATE TABLE endpoint

(

id SERIAL ,

host text ,

port integer

);

 

 

Целевая база данных

CREATE TABLE database

(

id SERIAL ,

endpoint_id integer ,

name text ,

is_maintenance boolean default true ,

pg_stat_history_retention integer ,

is_detailed_log boolean default TRUE ,

aliastext

);

is_maintenance-  флаг включения режима отладки, для отключение генерации инцидентов pg_stat_history- период хранения статистической информации

 

Снимок pg_stat_statements для целевой базы данных за час

CREATE TABLE pg_stat_history

(

id SERIAL ,

snapshot_timestamp timestamp without time zone ,

database_id integer ,

dbid oid ,

userid oid ,

queryid bigint ,

query text ,

calls bigint ,

total_time double precision       ,

min_time double precision  ,    

max_time double precision  ,    

mean_time double precision ,

stddev_time double precision      ,    

rows bigint ,      

shared_blks_hit bigint ,

shared_blks_read bigint ,       

shared_blks_dirtied bigint ,            

shared_blks_written bigint ,          

local_blks_hit      bigint ,          

local_blks_read bigint ,         

local_blks_dirtied bigint ,      

local_blks_written bigint ,      

temp_blks_read      bigint ,          

temp_blks_written bigint ,       

blk_read_time double precision ,

blk_write_time double precision

);

snapshot_timestamp- время снимка

database_id        - id целевой базы данных

 

Справочник типов метрик

create table metric_glossary

(

  id SERIAL , 

  name text ,

  description text

);

 

Метрика быстродействия

CREATE TABLE metric

(

id SERIAL ,

database_id integer ,

queryid bigint ,

max_threshold double precision ,

metric_glossary_id integer ,

is_enable boolean default FALSE ,

notice_pct double precision ,

warning_pct double precision

);

queryid - queryid изpg_stat_statements

max_threshold- Значение метрики для мониторинга

notice_pct- Минимальный процент срабатывания , для исключения шума

warning_pct-  Процент срабатывания для уровня инцидента Medium или High

 

Журнал истории появления предупреждений по метрикам быстродействия

CREATE TABLE metric_alert_history

(

id SERIAL ,

metric_id integer ,

description text ,

timepoint timestamp without time zone ,

value double precision

);

 

Метрика проверки условия

CREATE TABLE checkpoint

(

id SERIAL ,

metric_glossary_id integer ,

database_id integer ,

good_state text ,

check_period text ,

point double precision ,

is_enable boolean default FALSE

);

good_state- Условие нормальности (условия проверки также задается в поле metric_glossary.description )

check_period- Периодичностьпроверки(MONTHLY , DAYLY , HOURLY  )

point- точка проверки (день.час , час.минута , минута )

 

Журнал истории появления предупреждений по метрикам проверки условий

CREATE TABLE checkpoint_alert_history

(

id SERIAL ,

checkpoint_id integer ,

pg_stat_history_id integer ,

timepoint timestamp without time zone,

state text

);

 

Архив планов выполнения

CREATE TABLE fixed_execution_plans

(

id SERIAL ,

metric_id integer ,

fixed_time_from timestamp without time zone ,

fixed_time_to timestamp without time zone ,

explained_plan text[] ,

analyzed_plan text[]

);

fixed_time_from-начальная точка периода фиксации

fixed_time_to- конечная точка периода фиксации

explained_plan- Текст плана выполнения запроса по выражению EXPLAIN

analyzed_plan- Текс плана выполнения запроса по выражению EXPLAIN ANALYZE

 

Журнал активности

CREATE TABLE activity_log

(

id SERIAL ,

timepoint timestamp without time zone ,

action text ,

log_module text

);

 

Журнал запросов

CREATE TABLE log_query

(

   id SERIAL ,

   queryid bigint ,

   query_md5hash text not null ,

   database_id integer not null ,  

   timepoint timestamp without time zone not null,

   duration double precision not null ,

   query text not null ,

   explained_plan text[],

   plan_md5hash text

);

 

Упрощенная ERD

 

Продолжение следует …

 

Серия сообщений "PostgreSQL":

PostgreSQL: The World's Most Advanced Open Source Relational Database

Часть 1 - Мониторинг производительность запросов PostgreSQL? Не вопрос. Вопрос - как? ( Часть-1