digoal
2020-11-15
PostgreSQL , PostgreSQL 等待事件 及 等待采样统计(pg_wait_sampling)
https://pgxn.org/dist/pg_wait_sampling
《PostgreSQL 等待事件 及 等待采样统计(pg_wait_sampling)》
新版本支持pid, query级别等待时间快照, profile统计. 对于全貌和围观(到query和pid级)都有非常好的分析辅助.
PostgreSQL 9.6+ provides an information about current wait event of particular
process. However, in order to gather descriptive statistics of server
behavior user have to sample current wait event multiple times.
pg_wait_sampling is an extension for collecting sampling statistics of wait
events.
The module must be loaded by adding pg_wait_sampling to
shared_preload_libraries in postgresql.conf, because it requires additional
shared memory and launches background worker. This means that a server restart
is needed to add or remove the module.
When pg_wait_sampling is enabled, it collects two kinds of statistics.
- History of waits events. It's implemented as in-memory ring buffer where
samples of each process wait events are written with given (configurable)
period. Therefore, for each running process user can see some number of
recent samples depending on history size (configurable). Assuming there is
a client who periodically read this history and dump it somewhere, user
can have continuous history. - Waits profile. It's implemented as in-memory hash table where count
of samples are accumulated per each process and each wait event
(and each query withpg_stat_statements). This hash
table can be reset by user request. Assuming there is a client who
periodically dumps profile and resets it, user can have statistics of
intensivity of wait events among time.
In combination with pg_stat_statements this extension can also provide
per query statistics.
pg_wait_sampling launches special background worker for gathering the
statistics above.
pg_wait_sampling is implemented as an extension and not available in default
PostgreSQL installation. It is available from
github
under the same license as
PostgreSQL
and supports PostgreSQL 9.6+.
pg_wait_sampling is PostgreSQL extension which requires PostgreSQL 9.6 or
higher. Before build and install you should ensure following:
- PostgreSQL version is 9.6 or higher.
- You have development package of PostgreSQL installed or you built
PostgreSQL from source. - Your PATH variable is configured so that
pg_configcommand available, or
set PG_CONFIG variable.
Typical installation procedure may look like this:
$ git clone https://github.com/postgrespro/pg_wait_sampling.git
$ cd pg_wait_sampling
$ make USE_PGXS=1
$ sudo make USE_PGXS=1 install
$ make USE_PGXS=1 installcheck
$ psql DB -c "CREATE EXTENSION pg_wait_sampling;"
Compilation on Windows is not supported, since the extension uses symbols from PostgreSQL
that are not exported.
pg_wait_sampling interacts with user by set of views and functions.
pg_wait_sampling_current view – information about current wait events for
all processed including background workers.
| Column name | Column type | Description |
|---|---|---|
| pid | int4 | Id of process |
| event_type | text | Name of wait event type |
| event | text | Name of wait event |
| queryid | int8 | Id of query |
pg_wait_sampling_get_current(pid int4) returns the same table for single given
process.
pg_wait_sampling_history view – history of wait events obtained by sampling into
in-memory ring buffer.
| Column name | Column type | Description |
|---|---|---|
| pid | int4 | Id of process |
| ts | timestamptz | Sample timestamp |
| event_type | text | Name of wait event type |
| event | text | Name of wait event |
| queryid | int8 | Id of query |
pg_wait_sampling_profile view – profile of wait events obtained by sampling into
in-memory hash table.
| Column name | Column type | Description |
|---|---|---|
| pid | int4 | Id of process |
| event_type | text | Name of wait event type |
| event | text | Name of wait event |
| queryid | int8 | Id of query |
| count | text | Count of samples |
pg_wait_sampling_reset_profile() function resets the profile.
The work of wait event statistics collector worker is controlled by following
GUCs.
| Parameter name | Data type | Description | Default value |
|---|---|---|---|
| pg_wait_sampling.history_size | int4 | Size of history in-memory ring buffer | 5000 |
| pg_wait_sampling.history_period | int4 | Period for history sampling in milliseconds | 10 |
| pg_wait_sampling.profile_period | int4 | Period for profile sampling in milliseconds | 10 |
| pg_wait_sampling.profile_pid | bool | Whether profile should be per pid | true |
| pg_wait_sampling.profile_queries | bool | Whether profile should be per query | false |
If pg_wait_sampling.profile_pid is set to false, sampling profile wouldn't be
collected in per-process manner. In this case the value of pid could would
be always zero and corresponding row contain samples among all the processes.
While pg_wait_sampling.profile_queries is set to false queryid field in
views will be zero.
These GUCs are allowed to be changed by superuser. Also, they are placed into
shared memory. Thus, they could be changed from any backend and affects worker
runtime.
See
PostgreSQL documentation
for list of possible wait events.
Please, notice, that pg_wait_sampling is still under development and while
it's stable and tested, it may contains some bugs. Don't hesitate to raise
issues at github with
your bug reports.
If you're lacking of some functionality in pg_wait_sampling and feeling power
to implement it then you're welcome to make pull requests.
- Alexander Korotkov a.korotkov@postgrespro.ru, Postgres Professional,
Moscow, Russia - Ildus Kurbangaliev i.kurbangaliev@gmail.com, Postgres Professional,
Moscow, Russia
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
