[PROPOSAL] timestamp informations to pg_stat_statements
| От | Jun Cheol Gim |
|---|---|
| Тема | [PROPOSAL] timestamp informations to pg_stat_statements |
| Дата | |
| Msg-id | CANb8v8HPc-6=eh84dygWsSd7X6HkJNUywb98_WDzUOioNR+5=w@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: [PROPOSAL] timestamp informations to pg_stat_statements
|
| Список | pgsql-hackers |
Hi hackers!
Following is a proposal to add timestamp informations to `pg_stat_statements`.
# Use case
- If we want to gather list and stats for queries executed at least once last 1 hour, we had to reset a hours ago. There is no way if we didn't.
- If we found some strange query from `pg_stat_statments`, we might want to identify when it ran firstly.
If we have timestamp of first and last executed, we can easily gather thess informations and there are tons of more use cases.
# Implementations
Firstly, I added API version 1.5 to add additional fields and I added two fields to Counters structure. Now it has 25 fields in total.
```
/*
Following is a proposal to add timestamp informations to `pg_stat_statements`.
# Use case
- If we want to gather list and stats for queries executed at least once last 1 hour, we had to reset a hours ago. There is no way if we didn't.
- If we found some strange query from `pg_stat_statments`, we might want to identify when it ran firstly.
If we have timestamp of first and last executed, we can easily gather thess informations and there are tons of more use cases.
# Implementations
Firstly, I added API version 1.5 to add additional fields and I added two fields to Counters structure. Now it has 25 fields in total.
```
@@ -156,6 +158,8 @@ typedef struct Counters
double blk_read_time; /* time spent reading, in msec */
double blk_write_time; /* time spent writing, in msec */
double usage; /* usage factor */
+ TimestampTz created; /* timestamp of created time */
+ TimestampTz last_updated; /* timestamp of last updated */
} Counters;
```
The `created` field is filled at the first time the entry will added to hash table.
```
The `created` field is filled at the first time the entry will added to hash table.
```
@@ -1663,6 +1690,8 @@ entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding,
/* reset the statistics */
memset(&entry->counters, 0, sizeof(Counters));
+ /* set the created timestamp */
+ entry->counters.created = GetCurrentTimestamp();
/* set the appropriate initial usage count */
entry->counters.usage = sticky ? pgss->cur_median_usage : USAGE_INIT;
/* re-initialize the mutex each time ... we assume no one using it */```
The `last_updated` will be updated every time `pgss_store()` updates stats.
```
The `last_updated` will be updated every time `pgss_store()` updates stats.
```
@@ -1251,6 +1256,7 @@ pgss_store(const char *query, uint32 queryId,
e->counters.blk_read_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_read_time);
e->counters.blk_write_time += INSTR_TIME_GET_MILLISEC(bufusage->blk_write_time);
e->counters.usage += USAGE_EXEC(total_time);
+ e->counters.last_updated = GetCurrentTimestamp();
SpinLockRelease(&e->mutex);
}```
The attached is my first implementation.
The attached is my first implementation.
Regards,
Jason Kim.
Вложения
В списке pgsql-hackers по дате отправления: