Re: Very poor read performance, query independent

Mark,

First I must say that I changed my disks configuration from 4 disks in RAID
10 to 5 disks in RAID 0 because I almost ran out of disk space during the
last ingest of data.
Here is the result test you asked. It was done with a cold cache:

flows=# \timing
Timing is on.
flows=# explain select count(*) from flows;
QUERY PLAN

------------------------------------------------------------
-----------------------------------
Finalize Aggregate (cost=17214914.09..17214914.09 rows=1 width=8)
-> Gather (cost=17214914.07..17214914.09 rows=1 width=8)
Workers Planned: 1
-> Partial Aggregate (cost=17213914.07..17213914.07 rows=1
width=8)
-> Parallel Seq Scan on flows (cost=0.00..17019464.49
rows=388899162 width=0)
(5 rows)

Time: 171.835 ms
flows=# select pg_relation_size('flows');
pg_relation_size
------------------
129865867264
(1 row)

Time: 57.157 ms
flows=# select count(*) from flows;
LOG: duration: 625546.522 ms statement: select count(*) from flows;
count
-----------
589831190
(1 row)

Time: 625546.662 ms

The throughput reported by Postgresql is almost 198MB/s, and the throughput
as mesured by dstat during the query execution was between 25 and 299MB/s.
It is much better than what I had before! The i/o wait was about 12% all
through the query. One thing I noticed is the discrepency between the read
throughput reported by pg_activity and the one reported by dstat:
pg_activity always report a value lower than dstat.

Besides the change of disks configuration, here is what contributed the
most to the improvment of the performance so far:

Using Hugepage
Increasing effective_io_concurrency to 256
Reducing random_page_cost from 22 to 4
Reducing min_parallel_relation_size to 512kB to have more workers when
doing sequential parallel scan of my biggest table

Thanks for recomending this test, I now know what the real throughput
should be!

Charles

On Wed, Jul 12, 2017 at 4:11 AM, Mark Kirkwood <
mark(dot)kirkwood(at)catalyst(dot)net(dot)nz> wrote:

> Hmm - how are you measuring that sequential scan speed of 4MB/s? I'd
> recommend doing a very simple test e.g, here's one on my workstation - 13
> GB single table on 1 SATA drive - cold cache after reboot, sequential scan
> using Postgres 9.6.2:
>
> bench=# EXPLAIN SELECT count(*) FROM pgbench_accounts;
> QUERY PLAN
> ------------------------------------------------------------
> ------------------------
> Aggregate (cost=2889345.00..2889345.01 rows=1 width=8)
> -> Seq Scan on pgbench_accounts (cost=0.00..2639345.00 rows=100000000
> width=0)
> (2 rows)
>
>
> bench=# SELECT pg_relation_size('pgbench_accounts');
> pg_relation_size
> ------------------
> 13429514240
> (1 row)
>
> bench=# SELECT count(*) FROM pgbench_accounts;
> count
> -----------
> 100000000
> (1 row)
>
> Time: 118884.277 ms
>
>
> So doing the math seq read speed is about 110MB/s (i.e 13 GB in 120 sec).
> Sure enough, while I was running the query iostat showed:
>
> Device: rrqm/s wrqm/s r/s w/s rMB/s wMB/s avgrq-sz
> avgqu-sz await r_await w_await svctm %util
> sda 0.00 0.00 926.00 0.00 114.89 0.00 254.10
> 1.90 2.03 2.03 0.00 1.08 100.00
>
>
> So might be useful for us to see something like that from your system -
> note you need to check you really have flushed the cache, and that no other
> apps are using the db.
>
> regards
>
> Mark
>
>
> On 12/07/17 00:46, Charles Nadeau wrote:
>
>> After reducing random_page_cost to 4 and testing more, I can report that
>> the aggregate read throughput for parallel sequential scan is about 90MB/s.
>> However the throughput for sequential scan is still around 4MB/s.
>>
>>
>

--
Charles Nadeau Ph.D.
http://charlesnadeau.blogspot.com/