Re: Re: Big data INSERT optimization - ExclusiveLock on extension of the table

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