Re: Re: Big data INSERT optimization - ExclusiveLock on extension of the table
От | Jim Nasby |
---|---|
Тема | Re: Re: Big data INSERT optimization - ExclusiveLock on extension of the table |
Дата | |
Msg-id | d27b50ca-5e5d-0b5a-d7dd-c270fe019c65@BlueTreble.com обсуждение исходный текст |
Ответ на | Re: Big data INSERT optimization - ExclusiveLock on extension of the table (pinker <pinker@onet.eu>) |
Список | pgsql-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
В списке pgsql-performance по дате отправления: