File: README.rst

package info (click to toggle)
pg-stat-kcache 2.1.1-2
  • links: PTS, VCS
  • area: main
  • in suites: bullseye, buster, sid
  • size: 144 kB
  • sloc: ansic: 611; sql: 83; makefile: 36; sh: 2
file content (218 lines) | stat: -rw-r--r-- 12,322 bytes parent folder | download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
pg_stat_kcache
==============

Features
--------

Gathers statistics about real reads and writes done by the filesystem layer.
It is provided in the form of an extension for PostgreSQL >= 9.4., and requires
pg_stat_statements extension to be installed. PostgreSQL 9.4 or more is
required as previous version of provided pg_stat_statements didn't expose the
queryid field.

Installation
============

Compiling
---------

The module can be built using the standard PGXS infrastructure. For this to
work, the ``pg_config`` program must be available in your $PATH. Instruction to
install follows::

 git clone https://github.com/powa-team/pg_stat_kcache.git
 cd pg_stat_kcache
 make
 make install

PostgreSQL setup
----------------

The extension is now available. But, as it requires some shared memory to hold
its counters, the module must be loaded at PostgreSQL startup. Thus, you must
add the module to ``shared_preload_libraries`` in your ``postgresql.conf``. You
need a server restart to take the change into account.  As this extension
depends on pg_stat_statements, it also need to be added to
``shared_preload_libraries``.

Add the following parameters into you ``postgresql.conf``::

 # postgresql.conf
 shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'

Once your PostgreSQL cluster is restarted, you can install the extension in
every database where you need to access the statistics::

 mydb=# CREATE EXTENSION pg_stat_kcache;

Usage
=====

pg_stat_kcache create several objects.

pg_stat_kcache view
-------------------

+-------------+-------------------+-----------------------------------------------------+
| Name        | Type              | Description                                         |
+=============+===================+=====================================================+
| datname     | name              | Name of the database                                |
+-------------+-------------------+-----------------------------------------------------+
| user_time   | double precision  | User CPU time used                                  |
+-------------+-------------------+-----------------------------------------------------+
| system_time | double precision  | System CPU time used                                |
+-------------+-------------------+-----------------------------------------------------+
| minflts     | bigint            | Number of page reclaims (soft page faults)          |
+-------------+-------------------+-----------------------------------------------------+
| majflts     | bigint            | Number of page faults (hard page faults)            |
+-------------+-------------------+-----------------------------------------------------+
| nswaps      | bigint            | Number of swaps                                     |
+-------------+-------------------+-----------------------------------------------------+
| reads       | bigint            | Number of bytes read by the filesystem layer        |
+-------------+-------------------+-----------------------------------------------------+
| reads_blks  | bigint            | Number of 8K blocks read by the filesystem layer    |
+-------------+-------------------+-----------------------------------------------------+
| writes      | bigint            | Number of butes written by the filesystem layer     |
+-------------+-------------------+-----------------------------------------------------+
| writes_blks | bigint            | Number of 8K blocks written by the filesystem layer |
+-------------+-------------------+-----------------------------------------------------+
| msgsnds     | bigint            | Number of IPC messages sent                         |
+-------------+-------------------+-----------------------------------------------------+
| msgrcvs     | bigint            | Number of IPC messages received                     |
+-------------+-------------------+-----------------------------------------------------+
| nsignals    | bigint            | Number of signals received                          |
+-------------+-------------------+-----------------------------------------------------+
| nvcsws      | bigint            | Number of voluntary context switches                |
+-------------+-------------------+-----------------------------------------------------+
| nivcsws     | bigint            | Number of involuntary context switches              |
+-------------+-------------------+-----------------------------------------------------+

pg_stat_kcache_detail view
--------------------------

+-------------+-------------------+-----------------------------------------------------+
| Name        | Type              | Description                                         |
+=============+===================+=====================================================+
| query       | text              | Query text                                          |
+-------------+-------------------+-----------------------------------------------------+
| datname     | name              | Database name                                       |
+-------------+-------------------+-----------------------------------------------------+
| rolname     | name              | Role name                                           |
+-------------+-------------------+-----------------------------------------------------+
| user_time   | double precision  | User CPU time used                                  |
+-------------+-------------------+-----------------------------------------------------+
| system_time | double precision  | System CPU time used                                |
+-------------+-------------------+-----------------------------------------------------+
| minflts     | bigint            | Number of page reclaims (soft page faults)          |
+-------------+-------------------+-----------------------------------------------------+
| majflts     | bigint            | Number of page faults (hard page faults)            |
+-------------+-------------------+-----------------------------------------------------+
| nswaps      | bigint            | Number of swaps                                     |
+-------------+-------------------+-----------------------------------------------------+
| reads       | bigint            | Number of bytes read by the filesystem layer        |
+-------------+-------------------+-----------------------------------------------------+
| reads_blks  | bigint            | Number of 8K blocks read by the filesystem layer    |
+-------------+-------------------+-----------------------------------------------------+
| writes      | bigint            | Number of bytes written by the filesystem layer     |
+-------------+-------------------+-----------------------------------------------------+
| writes_blks | bigint            | Number of 8K blocks written by the filesystem layer |
+-------------+-------------------+-----------------------------------------------------+
| msgsnds     | bigint            | Number of IPC messages sent                         |
+-------------+-------------------+-----------------------------------------------------+
| msgrcvs     | bigint            | Number of IPC messages received                     |
+-------------+-------------------+-----------------------------------------------------+
| nsignals    | bigint            | Number of signals received                          |
+-------------+-------------------+-----------------------------------------------------+
| nvcsws      | bigint            | Number of voluntary context switches                |
+-------------+-------------------+-----------------------------------------------------+
| nivcsws     | bigint            | Number of involuntary context switches              |
+-------------+-------------------+-----------------------------------------------------+

pg_stat_kcache_reset function
-----------------------------

Resets the statistics gathered by pg_stat_kcache. Can be called by superusers::

 pg_stat_kcache_reset()


pg_stat_kcache function
-----------------------

This function is a set-returning functions that dumps the containt of the counters
of the shared memory structure. This function is used by the pg_stat_kcache view.
The function can be called by any user::

 SELECT * FROM pg_stat_kcache();

It provides the following columns:

+-------------+-------------------+--------------------------------------------------+
| Name        | Type              | Description                                      |
+=============+===================+==================================================+
| queryid     | bigint            | pg_stat_statements' query identifier             |
+-------------+-------------------+--------------------------------------------------+
| userid      | oid               | Database OID                                     |
+-------------+-------------------+--------------------------------------------------+
| dbid        | oid               | Database OID                                     |
+-------------+-------------------+--------------------------------------------------+
| user_time   | double precision  | User CPU time used                               |
+-------------+-------------------+--------------------------------------------------+
| system_time | double precision  | System CPU time use                              |
+-------------+-------------------+--------------------------------------------------+
| minflts     | bigint            | Number of page reclaims (soft page faults)       |
+-------------+-------------------+--------------------------------------------------+
| majflts     | bigint            | Number of page faults (hard page faults)         |
+-------------+-------------------+--------------------------------------------------+
| nswaps      | bigint            | Number of swaps                                  |
+-------------+-------------------+--------------------------------------------------+
| reads       | bigint            | Number of bytes read by the filesystem layer     |
+-------------+-------------------+--------------------------------------------------+
| writes      | bigint            | Number of bytes written by the filesystem layer  |
+-------------+-------------------+--------------------------------------------------+
| msgsnds     | bigint            | Number of IPC messages sent                      |
+-------------+-------------------+--------------------------------------------------+
| msgrcvs     | bigint            | Number of IPC messages received                  |
+-------------+-------------------+--------------------------------------------------+
| nsignals    | bigint            | Number of signals received                       |
+-------------+-------------------+--------------------------------------------------+
| nvcsws      | bigint            | Number of voluntary context switches             |
+-------------+-------------------+--------------------------------------------------+
| nivcsws     | bigint            | Number of involuntary context switches           |
+-------------+-------------------+--------------------------------------------------+

Bugs and limitations
====================

No known bugs.

We assume that a kernel block is 512 bytes. This is true for Linux, but may not
be the case for another Unix implementation.

See: http://lkml.indiana.edu/hypermail/linux/kernel/0703.2/0937.html

On platforms without a native getrusage(2), all fields except `user_time` and
`system_time` will be NULL.

On platforms with a native getrusage(2), some of the fields may not be
maintained.  This is a platform dependent behavior, please refer to your
platform getrusage(2) manual page for more details.

Authors
=======

pg_stat_kcache is an original development from Thomas Reiss, with large
portions of code inspired from pg_stat_plans. Julien Rouhaud also contributed
some parts of the extension.

Thanks goes to Peter Geoghegan for providing much inspiration with
pg_stat_plans so we could write this extension quite straightforward.

License
=======

pg_stat_kcache is free software distributed under the PostgreSQL license.

Copyright (c) 2014-2017, Dalibo
Copyright (c) 2018, The PoWA-team