Active Session History In PostgreSQL: Why?, How? and use cases
Bertrand is an IT professional since about 20 years mainly focusing on databases and data. He currently works for Amazon Web Services (as a Database Engineer). He is an OakTable member, has been active in the Oracle community since 2012 and is a regular speaker in Europe conferences. He is currently having a lot of interest around open source and mainly PostgreSQL.
No video of the event yet, sorry!
PostgreSQL provides session activity. However, in order to gather activity behavior, users have to sample the pg_stat_activity view multiple times. Furthermore, it could be even more useful to link the session activity to the sql statistics (coming from pg_stat_statements): that would provide the ability to easily drill-down to the sqls activity from a general overview of the database activity. Over an historical period of time, it could useful to answer questions like: Which backend type was taking most time? What wait events type were taking most time? What wait events were taking most time? Which application name was taking most time? What was a session doing? Which session was blocking which one? How long has a session been blocked? What does a SQL statement wait for? How many sessions were running in CPU? Which database was taking most time? On which wait event was the session waiting for? And so on…. In this session, a brand new extension will be presented (see https://github.com/pgsentinel/pgsentinel for more details) This extension collects the history of the sessions activity and in combination with pg_stat_statements can also link the session activity with sql statistics. You could see it as samplings of pg_stat_activity (one second interval as default) providing more information: ash_time: the sampling time top_level_query: the top level statement (in case PL/pgSQL is used) query: the statement being executed (not normalised, as it is in pg_stat_statements, means you see the values) cmdtype: the statement type (SELECT,UPDATE,INSERT,DELETE,UTILITY,UNKNOWN,NOTHING) queryid: the queryid of the statement (the one coming from pg_stat_statements) blockers: the number of blockers blockerpid: the pid of the blocker (if blockers = 1), the pid of one blocker (if blockers > 1) blocker_state: state of the blocker (state of the blockerpid) Thanks to the queryid field you are able to link the session activity with the sql activity. Thanks to the blocking information you are able to drill down in case of session(s) being blocked. It's implemented as in-memory ring buffer where samples are written with given (configurable) period. Therefore, user can see some number of recent samples depending on history size (configurable). In this session, we'll see why the active session history approach could be useful, how it has been implemented through an extension and some use cases with live demo. Then, we'll see how to get granular queries statistics (by sampling the pg_stat_statements at the same time as the sessions are actives) thanks to the extension and how to make use of them.
- 20 min
- Postgres Conference 2020
- Ops and Administration