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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
