Re: [PERFORM] Very poor read performance, query independent

Ah yes - that seems more sensible (but still slower than I would expect
for 5 disks RAID 0). You should be able to get something like 5 *
(single disk speed) i.e about 500MB/s.

Might be worth increasing device read ahead (more than you have
already). Some of these so-called 'smart' RAID cards need to be hit over
the head before they will perform. E.g: I believe you have it set to 128
- I'd try 4096 or even 16384 (In the past I've used those settings on
some extremely stupid cards that refused to max out their disks known
speeds).

Also worth investigating is RAID stripe size - for DW work it makes
sense for it to be reasonably big (256K to 1M), which again will help
speed is sequential scans.

Cheers

Mark


On 15/07/17 02:09, Charles Nadeau wrote:
> 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.kirkwood@catalyst.net.nz <mailto:mark.kirkwood@catalyst.net.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/