From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: pinker <pinker@onet.eu>
To: pgsql-performance@postgresql.org
Subject: Re: Re: Big data INSERT optimization - ExclusiveLock on extension of the table
Date: Fri, 19 Aug 2016 09:13:20 -0500
Message-ID: <d27b50ca-5e5d-0b5a-d7dd-c270fe019c65@BlueTreble.com> (raw)
In-Reply-To: <1471559195448-5917136.post@n5.nabble.com>
References: <1471434303741-5916781.post@n5.nabble.com>
<0805fb3c-ed48-bece-690c-5e60c7b40e65@BlueTreble.com>
<1471559195448-5917136.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 8/18/16 5:26 PM, pinker wrote:
>
>
>> 1. rename table t01 to t02
> OK...
>> 2. insert into t02 1M rows in chunks for about 100k
> Why not just insert into t01??
>
> Because of cpu utilization, it speeds up when load is divided
That still doesn't explain why you renamed t01 to t02.
>> 3. from t01 (previously loaded table) insert data through stored procedure
> But you renamed t01 so it no longer exists???
>> to b01 - this happens parallel in over a dozen sessions
> b01?
>
> that's another table - permanent one
>
>> 4. truncate t01
> Huh??
>
> The data were inserted to permanent storage so the temporary table can be
> truncated and reused.
Except t01 doesn't exist anymore...
> Ok, maybe the process is not so important; let's say the table is loaded,
> then data are fetched and reloaded to other table through stored procedure
> (with it's logic), then the table is truncated and process goes again. The
> most important part is holding ExclusiveLocks ~ 1-5s.
The process is important though, because AFAIK the only thing that
blocks the extension lock is another process extending the relation,
vacuum, or something trying to record information about free space and
an FSM page not existing. Is there something else doing inserts into the
table at the same time? Is something doing a bunch of updates or deletes
on pages that are newly inserted?
BTW, there we improvements made to relation extension in 9.6, so if you
have some way to test this on 9.6 it would be useful to know if it's
still a problem or not.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: Jim.Nasby@BlueTreble.com, pinker@onet.eu
Subject: Re: Re: Big data INSERT optimization - ExclusiveLock on extension of the table
In-Reply-To: <d27b50ca-5e5d-0b5a-d7dd-c270fe019c65@BlueTreble.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