From: Schneider, Jeremy <schnjere@amazon.com>
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: Fred Habash <fmhabash@gmail.com>
Cc: andres@anarazel.de <andres@anarazel.de>
Cc: tgl@sss.pgh.pa.us <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
Date: Wed, 19 Sep 2018 01:25:46 +0000
Message-ID: <8E4AC460-ED4A-4AB4-B19E-F8CB5E94B04F@amazon.com> (raw)
In-Reply-To: <53497a4c61a606e0b144b9f9c1426536e4e1f358.camel@cybertec.at>
References: <5b9a9f81.1c69fb81.43388.0e8b@mx.google.com>
<13501.1536862322@sss.pgh.pa.us>
<20180913194347.sjnui3hajk7amcx6@alap3.anarazel.de>
<CADpeV5zJNzy10g_-NZ6Y_ppJy_CoJ0o2X=mapDjrpUmJojd0hg@mail.gmail.com>
<53497a4c61a606e0b144b9f9c1426536e4e1f358.camel@cybertec.at>
Aside from I/O going to a different kind of storage, I don't think anything Aurora-specific should be at play here.
Would the 118 million buffer accesses (hits+reads) only include the index scan, or would that number also reflect buffers accessed for the 500 million heap fetches?
While Aurora doesn't have a filesystem cache (since it's a different kind of storage), it does default the buffer_cache to 75% to offset this. It appears that as Laurenz has pointed out, this is simply a lot of I/O requests in a serial process.
BTW that's 900GB of data that was read (118 million buffers of 8k each) - on a box with only 61GB of memory available for caching.
-Jeremy
Sent from my TI-83
> On Sep 17, 2018, at 12:04 PM, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> Fred Habash wrote:
>> If I'm reading this correctly, it took 57M ms out of an elapsed time of 61M ms to read 45M pages from the filesystem?
>> If the average service time per sarr is < 5 ms, Is this a case of bloated index where re-indexing is warranted?
>>
>> explain (analyze,buffers,timing,verbose,costs)
>> select count(*) from jim.pitations ;
>> QUERY PLAN
>> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> Aggregate (cost=72893810.73..72893810.74 rows=1 width=8) (actual time=61141110.437..61141110.437 rows=1 loops=1)
>> Output: count(*)
>> Buffers: shared hit=72620045 read=45297330
>> I/O Timings: read=57489958.088
>> -> Index Only Scan using pit_indx_fk03 on jim.pitations (cost=0.58..67227187.37 rows=2266649344 width=0) (actual time=42.327..60950272.189 rows=2269623575 loops=1)
>> Output: vsr_number
>> Heap Fetches: 499950392
>> Buffers: shared hit=72620045 read=45297330
>> I/O Timings: read=57489958.088
>> Planning time: 14.014 ms
>> Execution time: 61,141,110.516 ms
>> (11 rows)
>
> 2269623575 / (45297330 + 72620045) ~ 20, so you have an average 20
> items per block. That is few, and the index seems indeed bloated.
>
> Looking at the read times, you average out at about 1 ms per block
> read from I/O, but with that many blocks that's of course still a long time.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
>
>
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: schnjere@amazon.com, laurenz.albe@cybertec.at, fmhabash@gmail.com, andres@anarazel.de, tgl@sss.pgh.pa.us, pgsql-performance@lists.postgresql.org
Subject: Re: Select count(*) on a 2B Rows Tables Takes ~20 Hours
In-Reply-To: <8E4AC460-ED4A-4AB4-B19E-F8CB5E94B04F@amazon.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