From: Justin Pryzby <pryzby@telsasoft.com>
To: Vladimir Ryabtsev <greatvovan@gmail.com>
Cc: f.pardi@portavita.eu
Cc: pgsql-performance@lists.postgresql.org
Subject: Re: Why could different data in a table be processed with different performance?
Date: Mon, 24 Sep 2018 18:34:23 -0500
Message-ID: <20180924233423.GV2471@telsasoft.com> (raw)
In-Reply-To: <CAMqTPqnUB54YXQutPKPWdoUKEZOnV58WndfTvZagrNLiTr6RHw@mail.gmail.com>
References: <CAMqTPq=aFwfiqv=P9EDs+UxeOgfe03kyQ0uvtUHvSxvfSvYR+w@mail.gmail.com>
<4dbb8bea-bea3-74d8-a537-36bb9864cb9f@portavita.eu>
<CAMqTPqkMDEkvx7jUquUCCkwgwYdcS-byzimjap2osxYWqp1KjA@mail.gmail.com>
<e8d93055-9643-0ad2-d8bd-3f4fc344bf51@portavita.eu>
<CAMqTPqnUB54YXQutPKPWdoUKEZOnV58WndfTvZagrNLiTr6RHw@mail.gmail.com>
On Mon, Sep 24, 2018 at 03:28:15PM -0700, Vladimir Ryabtsev wrote:
> > it is not unusual to have 1GB cache or more... and do not forget to drop
> the cache between tests + do a sync
> I also reviewed import scripts and found the import was done in DESCENDING
> order of IDs.
This seems significant..it means the heap was probably written in backwards
order relative to the IDs, and the OS readahead is ineffective when index
scanning across a range of IDs. From memory, linux since (at least) 2.6.32 can
optimize this. You mentioned you're using 4.4. Does your LVM have readahead
ramped up ? Try lvchange -r 65536 data/postgres (or similar).
Also..these might be an impractical solution for several reasons, but did you
try either 1) forcing a bitmap scan (of only one index), to force the heap
reads to be ordered, if not sequential? SET enable_indexscan=off (and maybe
SET enable_seqscan=off and others as needed).
Or, 2) Using a brin index (scanning of which always results in bitmap heap
scan).
> > - how big is the table?
> pg_table_size('articles') = 427 GB
> pg_table_size('pg_toast.pg_toast_221558') = 359 GB
Ouch .. if it were me, I would definitely want to make that a partitioned table..
Or perhaps two unioned together with a view? One each for the sparse and dense
range?
> > You can make use of pg_buffercache in order to see what is actually
> cached.
> It seems that there is no such a view in my DB, could it be that the module
> is not installed?
Right, it's in the postgresql -contrib package.
And you have to "CREATE EXTENSION pg_buffercache".
> > - As Laurenz suggested (VACUUM FULL), you might want to move data around.
> You can try also a dump + restore to narrow the problem to data or disk
> I launched VACUUM FULL, but it ran very slowly, according to my calculation
> it might take 17 hours. I will try to do copy data into another table with
> the same structure or spin up another server, and let you know.
I *suspect* VACUUM FULL won't help, since (AIUI) it copies all "visible" tuples
from the source table into a new table (and updates indices as necessary). It
can resolve bloat due to historic DELETEs, but since I think your table was
written in reverse order of pkey, I think it'll also copy it in reverse order.
CLUSTER will fix that. You can use pg_repack to do so online...but it's going
to be painful for a table+toast 1TiB in size: it'll take all day, and also
require an additional 1TB while running (same as VAC FULL).
Justin
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: pgsql-performance@postgresql.org
Cc: pryzby@telsasoft.com, greatvovan@gmail.com, f.pardi@portavita.eu, pgsql-performance@lists.postgresql.org
Subject: Re: Why could different data in a table be processed with different performance?
In-Reply-To: <20180924233423.GV2471@telsasoft.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox