Re: [PERFORM] Possible to find disk IOs for a Query?

[Mark Kirkwood]

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 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.




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=3 loops=3)
 Filter: (bid = 1)
 Rows Removed by Filter: 330
 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




--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Possible to find disk IOs for a Query?

[Lukas Fittl]

On Wed, Aug 31, 2016 at 3:01 PM, 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 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.

[1] https://www.postgresql.org/docs/9.5/static/pgstatstatements.html

Best,
Lukas

-- 
Lukas Fittl

Skype: lfittl
Phone: +1 415 321 0630

Re: [PERFORM] Possible to find disk IOs for a Query?

[Ben Chobot]

On Aug 31, 2016, at 3:01 PM, 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 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.

[PERFORM] Possible to find disk IOs for a Query?

[Bobby Mozumder]

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.

Thank you,

-bobby

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance