From: pinker <pinker@onet.eu>
To: pgsql-performance@postgresql.org
Subject: Big data INSERT optimization - ExclusiveLock on extension of the table
Date: Wed, 17 Aug 2016 04:45:03 -0700 (MST)
Message-ID: <1471434303741-5916781.post@n5.nabble.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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-tp...
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pinker@onet.eu
Subject: Re: Big data INSERT optimization - ExclusiveLock on extension of the table
In-Reply-To: <1471434303741-5916781.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox