- CPU usage (user and system)
- Disk access (read and write)
PostgreSQL already has its own wrapper around getrusage (see pg_rusage.c), but it’s only used in a few places like VACUUM/ANALYZE execution statistics, only to display CPU usage and execution time, that wasn’t enough for our need.
The first version of the extension gave access to these metrics, but only with the granularity of the query operation (SELECT, UPDATE, INSERT…). It was interesting but still not enough. However, that’s all that could be done with the existing infrastructure.
But then, this patch is committed : Expose qurey ID in pg_stat_statements view.. That means that, starting with PostgreSQL 9.4, we now have a way to aggregate statistic per query, database and user, as long as pg_stat_statements is installed, which is far more useful. That’s what the new version 2.0 of pg_stat_statements is all about.
As I said just before, this version of pg_stat_kcache relies on pg_stat_statements:
What does the extension provide ?
There are two functions:
- pg_stat_kcache(): returns the metric values, grouped by query, database and user.
- pg_stat_kcache_reset(): reset the metrics.
And two views on top of the first function:
- pg_stat_kcache: provide the metrics, aggregated by database only
- pg_stat_kcache_detail: provide the same information as the pg_stat_kcache() function, but with the actual query text, database and user names.
Here are the units:
- reads: in bytes
- reads_blks: raw output of getursage, unit is 512bits on linux
- writes: in bytes
- writes_blks: raw output of getursage, unit is 512bits on linux
- user_time: in seconds
- system_time: in seconds
So now, let’s see in detail all this stuff.
Let’s first generate some activity to see all that counters going up:
Which gives us:
The INSERT query had a runtime of about 1 minute. We see that it used 7.6s of CPU, and wrote 890 MB on disk. Without any surprise, this query is I/O bound.
The SELECT query had a runtime of 5.1s, and it consumed 5s of CPU time. As expected, using md5() is CPU expensive, to the bottleneck here is the CPU. Also, we see that this query wrote 14000128 bytes. Why would a simple SELECT query without any aggregate would write 13MB on disk ? Yes, the answer is generate_series(), which use a temporary file if the data don’t fit in work_mem:
And we find the exact amount of writes :)
As we now have the number of bytes physically read from disk, and pg_stat_statements provides the bytes read on shared_buffers, read outside the shared_buffers and written, we can compute many things, like:
- an exact hit-ratio, meaning having :
- what was read from the shared_buffers
- what was read in the filesystem cache
- what was read from disk
And, thanks to pg_stat_statements, we can compute this exact hit-ratio per query and/or user and/or database!
For instance, getting these metrics on all databases on a server:
Or getting the 5 most I/O writes consuming queries per database:
As you can see, this new extension is really helpful to have a lot of informations about physical resources consumption on a PostgreSQL server, which wasn’t possible to retrieve before.
But you’ll get much more if you use it with PoWA, as it will gather all the required informations periodically, and will do all the maths to show you nice graphs and charts to ease the interpretation of all these metrics.
It mean that you’ll have all these informations, sampled on a few minutes interval. So, knowing which queries use the most CPU between 2 and 3 AM will just be a few clicks away from you.
If you want to take a look a this interface, you can check out the offical demo, at http://demo-powa.dalibo.com, powa // demo.