Thread: Possible to find disk IOs for a Query?
Is it possible to find the number of disk IOs performed for a query? EXPLAIN ANALYZE looks like it shows number of sequentialrows scanned, but not number of IOs. My database is on an NVMe SSD, and am trying to cut microseconds of disk IO per query by possibly denormalizing. Thank you, -bobby
On Aug 31, 2016, at 3:01 PM, Bobby Mozumder <bmozumder@gmail.com> wrote:
Is it possible to find the number of disk IOs performed for a query? EXPLAIN ANALYZE looks like it shows number of sequential rows scanned, but not number of IOs.
Postgres knows the number of rows it will need to pull to do your query, but it has no way of knowing if a block not in its own cache can be satisfied via filesystem cache, or if it will fall through to disk read. If you are on linux, you might be able to tell the effectiveness of your filesystem cache via something like http://www.brendangregg.com/blog/2014-12-31/linux-page-cache-hit-ratio.html
…but that's hardly going to show you something as granular as a per-query cost.
On Wed, Aug 31, 2016 at 3:01 PM, Bobby Mozumder <bmozumder@gmail.com> wrote:
Is it possible to find the number of disk IOs performed for a query? EXPLAIN ANALYZE looks like it shows number of sequential rows scanned, but not number of IOs.
My database is on an NVMe SSD, and am trying to cut microseconds of disk IO per query by possibly denormalizing.
Maybe helpful, altough slightly different since it works on an aggregate basis:
If you set "track_io_timing=on" in your postgresql.conf, you can use pg_stat_statements [1] to get I/O timings (i.e. how long a certain type of query took for I/O access).
Typically I'd use this in combination with system-level metrics, so you can understand which queries were running at the time of a given I/O spike.
Best,
Lukas
--
Lukas Fittl
Skype: lfittl
Phone: +1 415 321 0630
Skype: lfittl
Phone: +1 415 321 0630
On 01/09/16 10:01, Bobby Mozumder wrote:
> Is it possible to find the number of disk IOs performed for a query? EXPLAIN ANALYZE looks like it shows number of
sequentialrows scanned, but not number of IOs.
>
> My database is on an NVMe SSD, and am trying to cut microseconds of disk IO per query by possibly denormalizing.
>
Try EXPLAIN (ANALYZE, BUFFERS) e.g:
bench=# EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM pgbench_accounts
WHERE bid=1;
QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------------------------------
Finalize Aggregate (cost=217118.90..217118.91 rows=1 width=8) (actual
time=259
.723..259.723 rows=1 loops=1)
Buffers: shared hit=2370 read=161727
-> Gather (cost=217118.68..217118.89 rows=2 width=8) (actual
time=259.686..
259.720 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=2370 read=161727
-> Partial Aggregate (cost=216118.68..216118.69 rows=1
width=8) (actu
al time=258.473..258.473 rows=1 loops=3)
Buffers: shared hit=2208 read=161727
-> Parallel Seq Scan on pgbench_accounts
(cost=0.00..216018.33
rows=40139 width=0) (actual time=0.014..256.820 rows=33333 loops=3)
Filter: (bid = 1)
Rows Removed by Filter: 3300000
Buffers: shared hit=2208 read=161727
Planning time: 0.044 ms
Execution time: 260.357 ms
(14 rows)
...shows the number of (8k unless you've changed it) pages read from
disk or cache. Now this might not be exactly what you are after - the
other way to attack this is to trace your backend postgres process (err
perfmon...no idea how to do this on windows...) and count read and write
calls.
regards
Mark
On 01/09/16 17:56, Mark Kirkwood wrote: > the other way to attack this is to trace your backend postgres > process (err perfmon...no idea how to do this on windows...) No idea why I thought you were on windows (maybe was reading another message just before yours) - sorry!