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