{"id":11423,"date":"2018-07-12T22:12:13","date_gmt":"2018-07-12T20:12:13","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/pgsentinel-the-sampling-approach-for-postgresql\/"},"modified":"2018-07-12T22:12:13","modified_gmt":"2018-07-12T20:12:13","slug":"pgsentinel-the-sampling-approach-for-postgresql","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/pgsentinel-the-sampling-approach-for-postgresql\/","title":{"rendered":"pgSentinel: the sampling approach for PostgreSQL"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nHere is the first test I did with the beta of pgSentinel<\/a>. This Active Session History sampling is a new approach to Postgres tuning. For people coming from Oracle, this is something that has made our life a lot easier to optimize database applications. Here is a quick example showing how it links together some information that are missing without this extension.
\n
\nThe installation of the extension is really easy (nore details on Daniel’s post<\/a>):<\/p>\n

\ncp pgsentinel.control \/usr\/pgsql-10\/share\/extension\ncp pgsentinel--1.0.sql \/usr\/pgsql-10\/share\/extension\ncp pgsentinel.so \/usr\/pgsql-10\/lib\n<\/code><\/pre>\n
and declare it in postgresql.conf<\/p>\n
\ngrep -i pgSentinel $PGDATA\/postgresql.conf\n \nshared_preload_libraries = 'pg_stat_statements,pgsentinel'\n#pgsentinel_ash.pull_frequency = 1\n#pgsentinel_ash.max_entries = 1000000\n<\/code><\/pre>\n
and restart:<\/p>\n
\n\/usr\/pgsql-10\/bin\/pg_ctl restart\n<\/code><\/pre>\n
Then create the views in psql:<\/p>\n
\nCREATE EXTENSION pgsentinel;\n<\/code><\/pre>\n
I was running PGIO (the SLOB method for PostgreSQL from Kevin Closson https:\/\/kevinclosson.net\/<\/a>)<\/p>\n
Without the extension, here is what I can see about the current activity from the OS point of view, with ‘top -c’:<\/p>\n
\ntop - 21:57:23 up 1 day, 11:22,  4 users,  load average: 4.35, 4.24, 4.16\nTasks: 201 total,   2 running, 199 sleeping,   0 stopped,   0 zombie\n%Cpu(s): 27.6 us, 19.0 sy,  0.0 ni, 31.0 id, 19.0 wa,  0.0 hi,  3.4 si,  0.0 st\nKiB Mem :  4044424 total,    54240 free,   282220 used,  3707964 buff\/cache\nKiB Swap:   421884 total,   386844 free,    35040 used.  3625000 avail Mem\n \n  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND\n 9766 postgres  20   0  440280 160036 150328 D  50.0  4.0  10:56.63 postgres: postgres pgio [local] SELECT\n 9762 postgres  20   0  439940 160140 150412 D  43.8  4.0  10:55.95 postgres: postgres pgio [local] SELECT\n 9761 postgres  20   0  440392 160088 150312 D  37.5  4.0  10:52.29 postgres: postgres pgio [local] SELECT\n 9763 postgres  20   0  440280 160080 150432 R  37.5  4.0  10:41.94 postgres: postgres pgio [local] SELECT\n 9538 postgres  20   0  424860 144464 142956 D   6.2  3.6   0:30.79 postgres: writer process\n<\/code><\/pre>\n
As I described in a previous post<\/a>, PostgreSQL changes the title of the process to display the current operation. This looks interesting, but not very detailed (only ‘SELECT’ here) and very misleading because here I’m running PGIO with 50% updates. The ‘SELECT’ here is the user call. Not the actual SQL statement running.<\/p>\n
We have more information from PG_STAT_ACTIVITY, but again only the top-level call is displayed, as I mentioned in a previous post<\/a>:<\/p>\n
\nselect * from pg_stat_activity where pid=9766;\n-[ RECORD 1 ]----+---------------------------------------------------------\ndatid            | 17487\ndatname          | pgio\npid              | 9766\nusesysid         | 10\nusename          | postgres\napplication_name | psql\nclient_addr      |\nclient_hostname  |\nclient_port      | -1\nbackend_start    | 2018-07-12 21:28:46.539052+02\nxact_start       | 2018-07-12 21:28:46.542203+02\nquery_start      | 2018-07-12 21:28:46.542203+02\nstate_change     | 2018-07-12 21:28:46.542209+02\nwait_event_type  | IO\nwait_event       | DataFileWrite\nstate            | active\nbackend_xid      | 37554\nbackend_xmin     | 37553\nquery            | SELECT * FROM mypgio('pgio4', 50, 3000, 131072, 255, 8);\nbackend_type     | client backend\n<\/code><\/pre>\n
Here, I know what the user is doing: a call to mypgio() started at 21:28:46. And I know which resources are involved on the system: DataFileWrite. But again the most important is missing, the link between the user call and the system resources. And you can only guess it here because you know that a SELECT do not write to datafiles. There’s something hidden in the middle, which is actually an UPDATE. Of course, we can see this UPDATE in PG_STAT_STATEMENTS. But there, it will not be linked with the current activity, the mypgio() call, nor the DataFileWrite wait event. And we also need some timing information to be able to see the database load over the time.<\/p>\n
Here is where the pgSentinel extension fills the gap, providing:<\/p>\n