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.kirkw...@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/