Інженер — у перекладі латиною — натхненний.
Інженер може все. (С) Р.Дізель.
Епіграф.
Або історія про те, навіщо адміністратору баз даних згадувати своє програмістське минуле.
Передмова
Усі імена змінено. Збіги випадкові. Матеріал є виключно особистою думкою автора.
Відмова від гарантій: у планованому циклі статей нічого очікувати докладного і точного описи використовуваних таблиць і скриптів. Матеріали не вдасться одразу використовувати «AS IS».
По-перше, через великий обсяг матеріалу,
по-друге через заточеність із продакшн базою реального замовника.
Тому в статтях будуть наведені лише ідеї та описи у найзагальнішому вигляді.
Можливо, у майбутньому система доросте рівня викладання на GitHub, і може бути немає. Час покаже.
Початок історії- "
Що вийшло в результаті, у найзагальніших рисах-
Навіщо мені все це?
Ну, по-перше, щоб самому не забути, згадуючи на пенсії славні дєнята.
По-друге, щоб систематизувати написане. Бо вже сам, іноді починаю плутатися та забувати окремі частини.
Ну і найголовніше - раптом може комусь знадобиться і допоможе не винаходити велосипеда і не збирати граблі. Іншими словами, покращити свою карму (не хабрівську). Бо найцінніше в цьому світі це ідеї. Головне знайти ідею. А реалізувати ідею в реальність це вже питання суто технічне.
Отже, почнемо, потихеньку…
Постановка задачі.
Є:
База даних PostgreSQL(10.5), змішаного типу навантаження (OLTP+DSS), середньої-малої завантаженості, розташованої у хмарі AWS.
Моніторинг бази даних відсутній, моніторинг інфраструктури представлений у вигляді штатних засобів AWS в мінімальній конфігурації.
потрібно:
Моніторити продуктивність та стан бази даних, знаходити та мати початкову інформацію для оптимізації важких запитів до БД.
Коротка передмова або аналіз варіантів розв'язання
Для початку спробуємо розібрати варіанти вирішення задачі з погляду порівняльного аналізу вигод та неприємностей для інженера, а користю та збитками менеджменту нехай займаються ті, кому належить за штатним розкладом.
Варіант 1- "Working on demand"
Залишаємо все як є. Якщо замовника не влаштовує щось у працездатності, продуктивності бази даних або програми він сповістить інженерів DBA по e-mail або створивши інцидент у тикетниці.
Інженер, отримавши оповіщення, розбереться в проблемі, запропонує рішення або відкладе проблему в довгу скриньку, сподіваючись, що все само собою розсмокчеться, та й байдуже, скоро все забудеться.
Пряники та пампушки, синці та шишкиПряники та пампушки:
1. Нічого зайвого робити не треба
2. Завжди є можливість відмазатись і сачканути.
3. Купа часу, який можна витратити на власний розсуд.
Синяки та шишки:
1. Рано чи пізно замовник замислиться про сутність буття та вселенську справедливість у цьому світі і в черговий раз поставить собі питання — за що я їм плачу свої гроші? Наслідок завжди один - питання тільки коли замовник нудьгує і махне рукою на прощання. І годівниця спорожніє. Це сумно.
2. Розвиток інженера – нуль.
3. Складності планування роботи та завантаження
Варіант 2- «Танцюємо з бубнами, впарюємо і взиваємо»
Пункт 1-Навіщо нам система моніторингу, ми все отримуватимемо запитами. Запускам купу будь-яких запитів до словника даних і динамічним уявленням, включаємо всякі лічильники, зводимо все у таблиці, періодично аналізуємо списки і таблиці. В результаті маємо красиві чи не дуже графіки, таблиці, звіти. Головне — щоб більше, більше.
Пункт 2-Генеруємо активність-запускаємо аналіз всього цього.
Пункт 3-Готуємо якийсь документ, називаємо цей документ, просто - "як нам облаштувати базу даних".
Пункт 4-Замовник, бачачи всю цю пишність графіків і цифр перебуває в дитячій наївній впевненості — ось тепер у нас все запрацює, скоро. І легко і безболісно розлучається зі своїми грошовими ресурсами. Менеджмент також упевнений - інженери у нас працюють ого-го. Завантаження на максимумі.
Пункт 5-Регулярно повторити пункт 1.
Пряники та пампушки, синці та шишкиПряники та пампушки:
1. Життя менеджерів та інженерів - просте, передбачуване і наповнене активністю. Все дзижчить, усі зайняті.
2. Життя замовника теж непогане - він завжди впевнений, що потрібно ось трохи потерпіти і все налагодиться. Не налагоджується, ну що ж, це світ несправедливий, у наступному житті пощастить.
Синяки та шишки:
1. Рано чи пізно, але знайдеться більш спритний постачальник аналогічної послуги, який робитиме те саме, але трохи дешевше. А якщо в результат той самий, навіщо платити більше. Що знову-таки призведе до зникнення годівниці.
2. Це нудно. Як нудна будь-яка малоосмислена активність.
3. Як і в попередньому варіанті – розвитку жодного. Але для інженера мінус у тому, що на відміну від першого варіанта тут необхідно постійно генерувати ІБД. А це забирає час. Яке можна витратити з користю для себе коханого. Бо сам про себе не подбаєш, усім на тебе барабаном.
Варіант 3-Не потрібно вигадувати велосипед, потрібно його купити та кататися.
Інженери інших компаній не дарма їдять піцу, запиваючи пивом (ех, славні часи Пітер 90-х). Давайте використовувати моніторингові системи, які зроблені, налагоджені та працюють, і приносять взагалі кажучи користь (ну як мінімум їх творцям).
Пряники та пампушки, синці та шишкиПряники та пампушки:
1. Не потрібно витрачати час на вигадування того, що й так вигадано. Бери та користуйся.
2. Системи моніторингу пишуть не дурні і вони, звичайно ж, корисні.
3. Працюючі системи моніторингу зазвичай дають корисну відфільтровану інформацію.
Синяки та шишки:
1. Інженер у разі не інженер, а лише користувач чужого продукта.Або користувач.
2. Замовника треба переконати в необхідності купити щось у чому він розумітися взагалі не хоче, та й не повинен і взагалі бюджет на рік затверджений і не змінюватиметься. Потім необхідно виділити окремий ресурс, налаштувати під конкретну систему. Тобто. спочатку потрібно платити, платити та ще раз заплатити. А замовник скупий. Це норма цього життя.
Що ж робити-Чернишевський? Твоє питання дуже доречне. (с)
У даному конкретному випадку і ситуації можна зробити трохи по-іншому. а давайте зробимо власну систему моніторингу.
Ну не систему звичайно, в повному розумінні слова, це надто голосно сказано і самовпевнено, але хоч якось полегшити собі завдання та зібрати більше інформації для вирішення інцидентів продуктивності. Щоб не опинятися в ситуації - «Піди туди не знаю куди, знайди те, не знаю що».
Які ж плюси та мінуси цього варіанту:
Плюси:
1. Це цікаво. Ну як мінімум цікавіше, ніж постійні "shrink datafile, alter tablespace, etc."
2. Це нові скелі та новий розвиток. Що в перспективі рано чи пізно дасть заслужені пряники та пампушки.
Мінуси:
1. Доведеться працювати. Працюватиме багато.
2. Доведеться регулярно пояснювати зміст та перспективи всієї активності.
3. Чимось доведеться пожертвувати, бо єдиний доступний інженеру ресурс-час обмежений Всесвіту.
4. Найстрашніше і найнеприємніше — в результаті може вийде фігня типу «Не мишеня, не жаба, а невідоме звірятко».
Хто не ризикує, не п'є шампанське.
Отже – починається найцікавіше.
Загальна ідея - схематично
(Ілюстрацію взято зі статті «
Пояснення:
- У цільовій базі встановлюється стандартне розширення PostgreSQL - "pg_stat_statements".
- У базі даних моніторингу створюємо набір сервісних таблиць для зберігання історії pg_stat_statements на початковому етапі та налаштування метрик і моніторингу надалі
- На хості моніторингу створюємо набір bash-скриптів, у тому числі для генерації інцидентів у тикетній системі.
Сервісні таблиці
Для початку схематично спрощена ERD, що ж вийшло в результаті:
Короткий опис таблицькінцева точка - хост, точка підключення до інстансу
база даних - Параметри бази даних
pg_stat_history - Історична таблиця для зберігання тимчасових знімків подання pg_stat_statements цільової бази даних
metric_glossary - словник метрик продуктивності
metric_config - Конфігурація окремих метрик
метрика — конкретна метрика для запиту, який моніториться
metric_alert_history - Історія попереджень продуктивності
log_query — службова таблиця для зберігання розібраних записів з log файлу PostgreSQL, що завантажується з AWS
базова лінія — параметри тимчасового періоду, що використовується як базовий.
КПП - Конфігурація метрик перевірки стану бази даних
checkpoint_alert_history історія попереджень метрик перевірки стану бази даних
pg_stat_db_queries - службова таблиця активних запитів
activity_log - службова таблиця журналу активності
trap_oid - службова таблиця конфігурації trap
Етап 1 — збираємо статистичну інформацію про продуктивність та отримуємо звіти
Для зберігання статистичної інформації служить таблиця pg_stat_history
Структура таблиці pg_stat_history
Table "public.pg_stat_history" Column | Тип | Modifiers ---------------------+--------------------------- --+------------------------------------------- id | integer | not null default nextval('pg_stat_history_id_seq'::regclass) 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 | baseline_id | integer | Indexes: "pg_stat_history_pkey" PRIMARY KEY, btree (id) "database_idx" btree (database_id) "queryid_idx" btree (queryid) "snapshot_timestamp_idx" btree (snapshot_timestamp) Foreign-key constra _id) REFERENCES database(id ) ON DELETE CASCADE
Як видно, таблиця є лише кумулятивними даними уявлення. pg_stat_statements у цільовій базі даних.
Використання цієї таблиці дуже просте
pg_stat_history являтиме собою накопичену статистику виконання запитів за кожну годину. На початку кожної години після заповнення таблиці статистика pg_stat_statements скидається за допомогою pg_stat_statements_reset().
Примітка: Статистика збирається для запитів, з тривалістю виконання більше 1 секунди.
Заповнення таблиці pg_stat_history
--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
endpoint_rec record ;
database_rec record ;
pg_stat_snapshot record ;
current_snapshot_timestamp timestamp without time zone;
BEGIN
current_snapshot_timestamp = date_trunc('minute',now());
FOR endpoint_rec IN SELECT * FROM endpoint
LOOP
FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
LOOP
RAISE NOTICE 'NEW SHAPSHOT IS CREATING';
--Connect to the target DB
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
RAISE NOTICE 'host % and dbname % ',endpoint_rec.host,database_rec.name;
RAISE NOTICE 'Creating snapshot of pg_stat_statements for database %',database_rec.name;
SELECT
*
INTO
pg_stat_snapshot
FROM dblink('LINK1',
'SELECT
dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) ,
SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() )
GROUP BY dbid
'
)
AS t
( dbid oid , calls bigint ,
total_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
);
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid , calls ,total_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
RAISE NOTICE 'Creating snapshot of pg_stat_statements for queries with min_time more than 1000ms';
FOR pg_stat_snapshot IN
--All queries with max_time greater than 1000 ms
SELECT
*
FROM dblink('LINK1',
'SELECT
dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,
local_blks_hit , local_blks_read , local_blks_dirtied ,
local_blks_written , temp_blks_read , temp_blks_written , blk_read_time ,
blk_write_time
FROM pg_stat_statements
WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 )
'
)
AS t
( 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
)
LOOP
INSERT INTO pg_stat_history
(
snapshot_timestamp ,database_id ,
dbid ,userid , queryid , query , calls ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
rows ,shared_blks_hit ,shared_blks_read ,shared_blks_dirtied ,shared_blks_written ,local_blks_hit ,
local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written,
blk_read_time, blk_write_time
)
VALUES
(
current_snapshot_timestamp ,
database_rec.id ,
pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written ,
pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written ,
pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time
);
END LOOP;
PERFORM dblink_disconnect('LINK1');
END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id
END LOOP;
RETURN TRUE;
END
$$ LANGUAGE plpgsql;
В результаті, після деякого періоду часу в таблиці pg_stat_history у нас буде набір знімків вмісту таблиці pg_stat_statements цільової бази даних.
Власне репортинг
Використовуючи прості запити, можна отримати цілком корисні та цікаві звіти.
Агреговані дані за проміжок часу
Запит
SELECT
database_id ,
SUM(calls) AS calls ,SUM(total_time) AS total_time ,
SUM(rows) AS rows , SUM(shared_blks_hit) AS shared_blks_hit,
SUM(shared_blks_read) AS shared_blks_read ,
SUM(shared_blks_dirtied) AS shared_blks_dirtied,
SUM(shared_blks_written) AS shared_blks_written ,
SUM(local_blks_hit) AS local_blks_hit ,
SUM(local_blks_read) AS local_blks_read ,
SUM(local_blks_dirtied) AS local_blks_dirtied ,
SUM(local_blks_written) AS local_blks_written,
SUM(temp_blks_read) AS temp_blks_read,
SUM(temp_blks_written) temp_blks_written ,
SUM(blk_read_time) AS blk_read_time ,
SUM(blk_write_time) AS blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;
DB Time
to_char(interval '1 millisecond' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')
I/O Time
to_char(interval '1 millisecond' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')
TOP10 SQL by total_time
Запит
SELECT
queryid ,
SUM(calls) AS calls ,
SUM(total_time) AS total_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid
ORDER BY 3 DESC
LIMIT 10
-------------------------------------------------- ----------------------------------- | TOP10 SQL BY TOTAL EXECUTION TIME | #| queryid | calls| calls %| total_time (ms) | dbtime % +----+-----------+-----------+-----------+------ --------------------------+---------- | 1| 821760255 | 2| .00001 | 00:03:23.141 (203141.681 ms.) | 5.42 | 2| 4152624390 | 2| .00001|00:03:13.929(193929.215 ms.)| 5.17 | 3| 1484454471 | 4| .00001 | 00:02:09.129 (129129.057 ms.) | 3.44 | 4| 655729273 | 1| .00000|00:02:01.869(121869.981 ms.)| 3.25 | 5| 2460318461 | 1| .00000|00:01:33.113(93113.835 ms.)| 2.48 | 6| 2194493487 | 4| .00001|00:00:17.377( 17377.868 ms.)| .46 | 7| 1053044345 | 1| .00000 | 00:00:06.156 (6156.352 ms.) | .16 | 8| 3644780286 | 1| .00000|00:00:01.063(1063.830 ms.)| .03
TOP10 SQL за підсумками I/O time
Запит
SELECT
queryid ,
SUM(calls) AS calls ,
SUM(blk_read_time + blk_write_time) AS io_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid
ORDER BY 3 DESC
LIMIT 10
-------------------------------------------------- -------------------------------------- | TOP10 SQL BY TOTAL I/O TIME | #| queryid | calls| calls %| I/O time (ms)|db I/O time % +----+-----------+-----------+------ -----+--------------------------------+----------- - | 1| 4152624390 | 2| .00001 | 00:08:31.616 (511616.592 ms.) | 31.06 | 2| 821760255 | 2| .00001 | 00:08:27.099 (507099.036 ms.) | 30.78 | 3| 655729273 | 1| .00000 | 00:05:02.209 (302209.137 ms.) | 18.35 | 4| 2460318461 | 1| .00000|00:04:05.981(245981.117 ms.)| 14.93 | 5| 1484454471 | 4| .00001|00:00:39.144(39144.221 ms.)| 2.38 | 6| 2194493487 | 4| .00001 | 00:00: 18.182 (18182.816 ms.) | 1.10 | 7| 1053044345 | 1| .00000|00:00:16.611(16611.722 ms.)| 1.01 | 8| 3644780286 | 1| .00000 | 00:00:00.436 (436.205 ms.) | .03
TOP10 SQL by max time of execution
Запит
SELECT
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
max_time
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 4 DESC
LIMIT 10
-------------------------------------------------- --------------------------------------- | TOP10 SQL BY MAX EXECUTION TIME | #| snapshot | snapshotID| queryid | max_time (ms) +----+------------------+-----------+--------- --+---------------------------------------- | 1| 05.04.2019 01:03 | 4169 | 655729273 | 00:02:01.869( 121869.981 ms.) | 2| 04.04.2019 17:00 | 4153 | 821760255 | 00:01:41.570( 101570.841 ms.) | 3| 04.04.2019 16:00 | 4146 | 821760255 | 00:01:41.570( 101570.841 ms.) | 4| 04.04.2019 16:00 | 4144 | 4152624390 | 00:01:36.964( 96964.607 ms.) | 5| 04.04.2019 17:00 | 4151 | 4152624390 | 00:01:36.964( 96964.607 ms.) | 6| 05.04.2019 10:00 | 4188 | 1484454471 | 00:01:33.452( 93452.150 ms.) | 7| 04.04.2019 17:00 | 4150 | 2460318461 | 00:01:33.113( 93113.835 ms.) | 8| 04.04.2019 15:00 | 4140 | 1484454471 | 00:00:11.892( 11892.302 ms.) | 9| 04.04.2019 16:00 | 4145 | 1484454471 | 00:00:11.892( 11892.302 ms.) | 10 | 04.04.2019 17:00 | 4152 | 1484454471 | 00:00:11.892( 11892.302 ms.)
TOP10 SQL by SHARED buffer read/write
Запит
SELECT
id AS snapshotid ,
queryid ,
snapshot_timestamp ,
shared_blks_read ,
shared_blks_written
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC , 5 DESC
LIMIT 10
-------------------------------------------------- ------------------------------------------ | TOP10 SQL BY SHARED BUFFER READ/WRITE | #| snapshot | snapshotID| queryid | shared blocks read| shared blocks write +----+----------------------+-----------+---------- -+---------------------+--------------------- | 1| 04.04.2019 17:00 | 4153 | 821760255 | 797308 | 0 | 2| 04.04.2019 16:00 | 4146 | 821760255 | 797308 | 0 | 3| 05.04.2019 01:03 | 4169 | 655729273 | 797158 | 0 | 4| 04.04.2019 16:00 | 4144 | 4152624390 | 756514 | 0 | 5| 04.04.2019 17:00 | 4151 | 4152624390 | 756514 | 0 | 6| 04.04.2019 17:00 | 4150 | 2460318461 | 734117 | 0 | 7| 04.04.2019 17:00 | 4155 | 3644780286 | 52973 | 0 | 8| 05.04.2019 01:03 | 4168 | 1053044345 | 52818 | 0 | 9| 04.04.2019 15:00 | 4141 | 2194493487 | 52813 | 0 | 10 | 04.04.2019 16:00 | 4147 | 2194493487 | 52813 | 0 ------------------------------------------------- -------------------------------------------
Гістограма розподілу запитів за максимальним часом виконання
запити
SELECT
MIN(max_time) AS hist_min ,
MAX(max_time) AS hist_max ,
(( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;
SELECT
SUM(calls) AS calls
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id =DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( max_time >= hist_current_min AND max_time < hist_current_max ) ;
|------------------------------------------------- ---------------------------------------------- | MAX_TIME HISTOGRAM | TOTAL CALLS : 33851920 | MIN TIME : 00:00:01.063 | MAX TIME : 00:02:01.869 ------------------------------------------ --------------------------------------- | min duration| max duration| calls +----------------------------------+------------- ---------------------+---------- | 00:00:01.063( 1063.830 ms.) | 00:00:13.144( 13144.445 ms.) | 9 | 00:00:13.144( 13144.445 ms.) | 00:00:25.225( 25225.060 ms.) | 0 | 00:00:25.225( 25225.060 ms.) | 00:00:37.305( 37305.675 ms.) | 0 | 00:00:37.305( 37305.675 ms.) | 00:00:49.386( 49386.290 ms.) | 0 | 00:00:49.386( 49386.290 ms.) | 00:01:01.466( 61466.906 ms.) | 0 | 00:01:01.466( 61466.906 ms.) | 00:01:13.547( 73547.521 ms.) | 0 | 00:01:13.547( 73547.521 ms.) | 00:01:25.628( 85628.136 ms.) | 0 | 00:01:25.628( 85628.136 ms.) | 00:01:37.708( 97708.751 ms.) | 4 | 00:01:37.708( 97708.751 ms.) | 00:01:49.789( 109789.366 ms.) | 2 | 00:01:49.789( 109789.366 ms.) | 00:02:01.869( 121869.981 ms.) | 0
TOP10 Snapshots by Query per Second
запити
--pg_qps.sql
--Calculate Query Per Second
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
pg_stat_history_rec record ;
prev_pg_stat_history_id integer ;
prev_pg_stat_history_rec record;
total_seconds double precision ;
result double precision;
BEGIN
result = 0 ;
SELECT *
INTO pg_stat_history_rec
FROM
pg_stat_history
WHERE id = pg_stat_history_id ;
IF pg_stat_history_rec.snapshot_timestamp IS NULL
THEN
RAISE EXCEPTION 'ERROR - Not found pg_stat_history for id = %',pg_stat_history_id;
END IF ;
--RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id ,
pg_stat_history_rec.snapshot_timestamp ;
SELECT
MAX(id)
INTO
prev_pg_stat_history_id
FROM
pg_stat_history
WHERE
database_id = pg_stat_history_rec.database_id AND
queryid IS NULL AND
id < pg_stat_history_rec.id ;
IF prev_pg_stat_history_id IS NULL
THEN
RAISE NOTICE 'Not found previous pg_stat_history shapshot for id = %',pg_stat_history_id;
RETURN NULL ;
END IF;
SELECT *
INTO prev_pg_stat_history_rec
FROM
pg_stat_history
WHERE id = prev_pg_stat_history_id ;
--RAISE NOTICE 'prev_pg_stat_history_id = % , prev_snapshot_timestamp = %', prev_pg_stat_history_id , prev_pg_stat_history_rec.snapshot_timestamp ;
total_seconds = extract(epoch from ( pg_stat_history_rec.snapshot_timestamp - prev_pg_stat_history_rec.snapshot_timestamp ));
--RAISE NOTICE 'total_seconds = % ', total_seconds ;
--RAISE NOTICE 'calls = % ', pg_stat_history_rec.calls ;
IF total_seconds > 0
THEN
result = pg_stat_history_rec.calls / total_seconds ;
ELSE
result = 0 ;
END IF;
RETURN result ;
END
$$ LANGUAGE plpgsql;
SELECT
id ,
snapshot_timestamp ,
calls ,
total_time ,
( select pg_qps( id )) AS QPS ,
blk_read_time ,
blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
( select pg_qps( id )) IS NOT NULL
ORDER BY 5 DESC
LIMIT 10
|------------------------------------------------- ---------------------------------------------- | TOP10 Snapshots ordered by QueryPerSeconds numbers -------------------------------------------- -------------------------------------------------- ------------------------------------------------- | #| snapshot | snapshotID| calls| total dbtime | QPS| I/O time| I/O time % +-----+------------------+-----------+------- ----+----------------------------------+---------- -+----------------------------------+----------- | 1| 04.04.2019 20:04 | 4161 | 5758631 | 00:06:30.513( 390513.926 ms.)| 1573.396 | 00:00:01.470( 1470.110 ms.)| .376 | 2| 04.04.2019 17:00 | 4149 | 3529197 | 00:11:48.830( 708830.618 ms.)| 980.332 | 00:12:47.834( 767834.052 ms.)| 108.324 | 3| 04.04.2019 16:00 | 4143 | 3525360 | 00:10:13.492( 613492.351 ms.)| 979.267 | 00:08:41.396( 521396.555 ms.)| 84.988 | 4| 04.04.2019 21:03 | 4163 | 2781536 | 00:03:06.470( 186470.979 ms.)| 785.745 | 00:00:00.249( 249.865 ms.)| .134 | 5| 04.04.2019 19:03 | 4159 | 2890362 | 00:03:16.784( 196784.755 ms.)| 776.979 | 00:00:01.441( 1441.386 ms.)| .732 | 6| 04.04.2019 14:00 | 4137 | 2397326 | 00:04:43.033( 283033.854 ms.)| 665.924 | 00:00:00.024( 24.505 ms.)| .009 | 7| 04.04.2019 15:00 | 4139 | 2394416 | 00:04:51.435( 291435.010 ms.)| 665.116 | 00:00:12.025( 12025.895 ms.)| 4.126 | 8| 04.04.2019 13:00 | 4135 | 2373043 | 00:04:26.791( 266791.988 ms.)| 659.179 | 00:00:00.064( 64.261 ms.)| .024 | 9| 05.04.2019 01:03 | 4167 | 4387191 | 00:06:51.380( 411380.293 ms.)| 609.332 | 00:05:18.847( 318847.407 ms.)| 77.507 | 10 | 04.04.2019 18:01 | 4157 | 1145596 | 00:01:19.217( 79217.372 ms.)| 313.004 | 00:00:01.319( 1319.676 ms.)| 1.666
Hourly Execution History with QueryPerSeconds and I/O Time
Запит
SELECT
id ,
snapshot_timestamp ,
calls ,
total_time ,
( select pg_qps( id )) AS QPS ,
blk_read_time ,
blk_write_time
FROM
pg_stat_history
WHERE
queryid IS NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2
|----------------------------------------------------------------------------------------------- | HOURLY EXECUTION HISTORY WITH QueryPerSeconds and I/O Time ----------------------------------------------------------------------------------------------------------------------------------------------- | QUERY PER SECOND HISTORY | #| snapshot| snapshotID| calls| total dbtime| QPS| I/O time| I/O time % +-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+----------- | 1| 04.04.2019 11:00| 4131| 3747| 00:00:00.835( 835.374 ms.)| 1.041| 00:00:00.000( .000 ms.)| .000 | 2| 04.04.2019 12:00| 4133| 1002722| 00:01:52.419( 112419.376 ms.)| 278.534| 00:00:00.149( 149.105 ms.)| .133 | 3| 04.04.2019 13:00| 4135| 2373043| 00:04:26.791( 266791.988 ms.)| 659.179| 00:00:00.064( 64.261 ms.)| .024 | 4| 04.04.2019 14:00| 4137| 2397326| 00:04:43.033( 283033.854 ms.)| 665.924| 00:00:00.024( 24.505 ms.)| .009 | 5| 04.04.2019 15:00| 4139| 2394416| 00:04:51.435( 291435.010 ms.)| 665.116| 00:00:12.025( 12025.895 ms.)| 4.126 | 6| 04.04.2019 16:00| 4143| 3525360| 00:10:13.492( 613492.351 ms.)| 979.267| 00:08:41.396( 521396.555 ms.)| 84.988 | 7| 04.04.2019 17:00| 4149| 3529197| 00:11:48.830( 708830.618 ms.)| 980.332| 00:12:47.834( 767834.052 ms.)| 108.324 | 8| 04.04.2019 18:01| 4157| 1145596| 00:01:19.217( 79217.372 ms.)| 313.004| 00:00:01.319( 1319.676 ms.)| 1.666 | 9| 04.04.2019 19:03| 4159| 2890362| 00:03:16.784( 196784.755 ms.)| 776.979| 00:00:01.441( 1441.386 ms.)| .732 | 10| 04.04.2019 20:04| 4161| 5758631| 00:06:30.513( 390513.926 ms.)| 1573.396| 00:00:01.470( 1470.110 ms.)| .376 | 11| 04.04.2019 21:03| 4163| 2781536| 00:03:06.470( 186470.979 ms.)| 785.745| 00:00:00.249( 249.865 ms.)| .134 | 12| 04.04.2019 23:03| 4165| 1443155| 00:01:34.467( 94467.539 ms.)| 200.438| 00:00:00.015( 15.287 ms.)| .016 | 13| 05.04.2019 01:03| 4167| 4387191| 00:06:51.380( 411380.293 ms.)| 609.332| 00:05:18.847( 318847.407 ms.)| 77.507 | 14| 05.04.2019 02:03| 4171| 189852| 00:00:10.989( 10989.899 ms.)| 52.737| 00:00:00.539( 539.110 ms.)| 4.906 | 15| 05.04.2019 03:01| 4173| 3627| 00:00:00.103( 103.000 ms.)| 1.042| 00:00:00.004( 4.131 ms.)| 4.010 | 16| 05.04.2019 04:00| 4175| 3627| 00:00:00.085( 85.235 ms.)| 1.025| 00:00:00.003( 3.811 ms.)| 4.471 | 17| 05.04.2019 05:00| 4177| 3747| 00:00:00.849( 849.454 ms.)| 1.041| 00:00:00.006( 6.124 ms.)| .721 | 18| 05.04.2019 06:00| 4179| 3747| 00:00:00.849( 849.561 ms.)| 1.041| 00:00:00.000( .051 ms.)| .006 | 19| 05.04.2019 07:00| 4181| 3747| 00:00:00.839( 839.416 ms.)| 1.041| 00:00:00.000( .062 ms.)| .007 | 20| 05.04.2019 08:00| 4183| 3747| 00:00:00.846( 846.382 ms.)| 1.041| 00:00:00.000( .007 ms.)| .001 | 21| 05.04.2019 09:00| 4185| 3747| 00:00:00.855( 855.426 ms.)| 1.041| 00:00:00.000( .065 ms.)| .008 | 22| 05.04.2019 10:00| 4187| 3797| 00:01:40.150( 100150.165 ms.)| 1.055| 00:00:21.845( 21845.217 ms.)| 21.812
Text of all SQL-selects
Запит
SELECT
queryid ,
query
FROM
pg_stat_history
WHERE
queryid IS NOT NULL AND
database_id = DATABASE_ID AND
snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid , query
Підсумок
Як видно, досить простими засобами, можна отримати чимало корисної інформації про завантаженість та стан бази.
Примітка:Якщо в запитах фіксувати queryid, то отримаємо історію за окремим запитом (з метою економії місця звіти за окремим запитом опущені).
Отже, статистичні дані про продуктивність запитів є і збираються.
Перший етап «збір статистичних даних» – завершено.
Можна переходити до другого етапу-налаштування метрик продуктивності.
Але це вже зовсім інша історія.
Далі буде ...
Джерело: habr.com