Subject: Re: Re: Big data INSERT optimization - ExclusiveLock on
 extension of the table
To: pinker <pinker@onet.eu>, <pgsql-performance@postgresql.org>
References: <1471434303741-5916781.post@n5.nabble.com>
 <0805fb3c-ed48-bece-690c-5e60c7b40e65@BlueTreble.com>
 <1471559195448-5917136.post@n5.nabble.com>
From: Jim Nasby <Jim.Nasby@BlueTreble.com>
Message-ID: <d27b50ca-5e5d-0b5a-d7dd-c270fe019c65@BlueTreble.com>
Date: Fri, 19 Aug 2016 09:13:20 -0500
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0)
 Gecko/20100101 Thunderbird/45.2.0
MIME-Version: 1.0
In-Reply-To: <1471559195448-5917136.post@n5.nabble.com>
Content-Type: text/plain; charset="windows-1252"; format=flowed
Content-Transfer-Encoding: 7bit
Received-SPF: None (protection.outlook.com: bluetreble.com does not designate
 permitted sender hosts)
SpamDiagnosticOutput: 1:99
SpamDiagnosticMetadata: NSPM
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 19 Aug 2016 14:13:23.0763 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-Transport-CrossTenantHeadersStamped: CY4PR11MB1638
X-Pg-Spam-Score: -1.9 (-)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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