Date: Wed, 17 Aug 2016 04:45:03 -0700 (MST)
From: pinker <pinker@onet.eu>
To: pgsql-performance@postgresql.org
Message-ID: <1471434303741-5916781.post@n5.nabble.com>
Subject: Big data INSERT optimization - ExclusiveLock on extension of the
 table
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Hi,
the problem I'm dealing with is long holding locks during extensions of
table: 
process xxx still waiting for ExclusiveLock on extension of relation xxx of 
database xxx after 3000.158 ms
My application is write intensive, in one round I need to insert about 1M
rows. The general scheme of the process looks as follows:
1. rename table t01 to t02
2. insert into t02 1M rows in chunks for about 100k
3. from t01 (previously loaded table) insert data through stored procedure
to b01 - this happens parallel in over a dozen sessions
4. truncate t01

Some data:
PostgreSQL version 9.5

 commit_delay                        | 0                                       
| Sets the delay in microseconds between transaction commit and flushing WAL
to disk.
 checkpoint_completion_target        | 0.9                                     
| Time spent flushing dirty buffers during checkpoint, as fraction of
checkpoint interval
 maintenance_work_mem                | 2GB                                     
| Sets the maximum memory to be used for maintenance operations.
shared_buffers                      | 2GB

wal_block_size                      | 8192                                    
| Shows the block size in the write ahead log.
 wal_buffers                         | 16MB                                    
| Sets the number of disk-page buffers in shared memory for WAL.
 wal_compression                     | off                                     
| Compresses full-page writes written in WAL file.
 wal_keep_segments                   | 0                                       
| Sets the number of WAL files held for standby servers.
 wal_level                           | minimal                                 
| Set the level of information written to the WAL.
 wal_log_hints                       | off                                     
| Writes full pages to WAL when first modified after a checkpoint, even for
a non-critical modifications.
 wal_receiver_status_interval        | 10s                                     
| Sets the maximum interval between WAL receiver status reports to the
primary.
 wal_receiver_timeout                | 1min                                    
| Sets the maximum wait time to receive data from the primary.
 wal_retrieve_retry_interval         | 5s                                      
| Sets the time to wait before retrying to retrieve WAL after a failed
attempt.
 wal_segment_size                    | 16MB                                    
| Shows the number of pages per write ahead log segment.
 wal_sender_timeout                  | 1min                                    
| Sets the maximum time to wait for WAL replication.
 wal_sync_method                     | fdatasync                               
| Selects the method used for forcing WAL updates to disk.
 wal_writer_delay                    | 200ms                                   
| WAL writer sleep time between WAL flushes.
 work_mem                            | 32MB                                    
| Sets the maximum memory to be used for query workspaces.

Checkpoints occur every ~ 30sec.

Following the advices from this mailing list shared buffers size was changed
from 12 to 2GB but nothing has changed.

I'm not sure or my bottleneck is the I/O subsystem or there is anything else
I can do to make it faster? What I came up with is (but I'm not sure if any
of this makes sense):
* change settings for bgwriter/wal?
* make sure huge pages are in use by changing huge_pages parameter to on
* replace truncate with DROP/CREATE command?
* turning off fsync for loading?
* increase commit_delay value?
* move temporary tables to a different tablespace

Your advice or suggestions will be much appreciated.


--
View this message in context: http://postgresql.nabble.com/Big-data-INSERT-optimization-ExclusiveLock-on-extension-of-the-table-tp5916781.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance