Subject: Re: Very poor read performance, query independent
To: Charles Nadeau <charles.nadeau@gmail.com>,
 Igor Neyman <ineyman@perceptron.com>
Cc: Andreas Kretschmer <andreas@a-kretschmer.de>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
References: 
 <CADFyZw7aGoD0AaStxdyHByR5Qta=M5wx0v=iptKLhPUp+EOKvA@mail.gmail.com>
 <dc5d2b63-0a1b-ff76-c88a-87c2c41bd5b8@a-kretschmer.de>
 <CADFyZw4UanW5TbFajWKWhN9XcW+8gtCXw+kssHo47Wpr1A=zJw@mail.gmail.com>
 <DM5PR07MB28103FD558CB6628CECE07BEDAA90@DM5PR07MB2810.namprd07.prod.outlook.com>
 <CADFyZw6JrhsLR_eYOeCjjiQMzz5bepk6AfMRBu0hnaQg+vN-=A@mail.gmail.com>
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
Message-ID: <a8fa0c8a-1269-338e-80a6-cb121574be7c@catalyst.net.nz>
Date: Wed, 12 Jul 2017 14:11:53 +1200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.2.1
MIME-Version: 1.0
In-Reply-To: 
 <CADFyZw6JrhsLR_eYOeCjjiQMzz5bepk6AfMRBu0hnaQg+vN-=A@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 7bit
Content-Language: en-US
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


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