public inbox for [email protected]
help / color / mirror / Atom feedFrom: Mark Kirkwood <[email protected]>
To: Charles Nadeau <[email protected]>
To: Igor Neyman <[email protected]>
Cc: Andreas Kretschmer <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: Very poor read performance, query independent
Date: Wed, 12 Jul 2017 14:11:53 +1200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CADFyZw6JrhsLR_eYOeCjjiQMzz5bepk6AfMRBu0hnaQg+vN-=A@mail.gmail.com>
References: <CADFyZw7aGoD0AaStxdyHByR5Qta=M5wx0v=iptKLhPUp+EOKvA@mail.gmail.com>
<[email protected]>
<CADFyZw4UanW5TbFajWKWhN9XcW+8gtCXw+kssHo47Wpr1A=zJw@mail.gmail.com>
<DM5PR07MB28103FD558CB6628CECE07BEDAA90@DM5PR07MB2810.namprd07.prod.outlook.com>
<CADFyZw6JrhsLR_eYOeCjjiQMzz5bepk6AfMRBu0hnaQg+vN-=A@mail.gmail.com>
List-Unsubscribe: <mailto:[email protected]?body=unsub%20pgsql-performance>
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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Very poor read performance, query independent
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox