Yes, I'm doing multiple threads inserting to the same tables. I don't think the WAL is the issue as I even tried going ASYNC (non acid), disabled sync after writes, however still didn't got able to push full performance. I've checked the locks and I see lots of ExclusiveLock's with: select * from pg_locks order by mode locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath ---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--------------------------+---------+---------- relation | 16385 | 19295 | | | | | | | | 72/18 | 19879 | AccessShareLock | t | t relation | 16385 | 11069 | | | | | | | | 76/32 | 19881 | AccessShareLock | t | t virtualxid | | | | | 56/34 | | | | | 56/34 | 17952 | ExclusiveLock | t | t virtualxid | | | | | 27/33 | | | | | 27/33 | 17923 | ExclusiveLock | t | t virtualxid | | | | | 6/830 | | | | | 6/830 | 17902 | ExclusiveLock | t | t virtualxid | | | | | 62/34 | | | | | 62/34 | 17959 | ExclusiveLock | t | t virtualxid | | | | | 51/34 | | | | | 51/34 | 17947 | ExclusiveLock | t | t virtualxid | | | | | 36/34 | | | | | 36/34 | 17932 | ExclusiveLock | t | t virtualxid | | | | | 10/830 | | | | | 10/830 | 17906 | .................(about 56 of those) ExclusiveLock | t | t transactionid | | | | | | 30321 | | | | 55/33 | 17951 | ExclusiveLock | t | f transactionid | | | | | | 30344 | | | | 19/34 | 17912 | ExclusiveLock | t | f transactionid | | | | | | 30354 | | | | 3/834 | 17898 | ExclusiveLock | t | f transactionid | | | | | | 30359 | | | | 50/34 | 17946 | ExclusiveLock | t | f transactionid | | | | | | 30332 | | | | 9/830 | 17905 | ExclusiveLock | t | f transactionid | | | | | | 30294 | | | | 37/33 | 17933 | ExclusiveLock | t | f transactionid | | | | | | 30351 | | | | 38/34 | 17934 | ExclusiveLock | t | f transactionid | | | | | | 30326 | | | | 26/33 | 17922 | ExclusiveLock | t | f .................(about 52 of those) relation | 16385 | 19291 | | | | | | | | 72/18 | 19879 | ShareUpdateExclusiveLock | t | f (3 of those) relation | 16385 | 19313 | | | | | | | | 33/758 | 17929 | RowExclusiveLock | t | t (211 of those) However I don't see any of the EXTEND locks mentioned. I would give a try translating the trigger to C but I can't code it without a good sample to start from, if anyone has one and would like to share I would love to start from it and share with other people so everyone can benefit. ---------------------------------------- > Date: Thu, 20 Dec 2012 15:02:34 -0500 > From: sfrost@xxxxxxxxxxx > To: charlesrg@xxxxxxxxxxx > CC: pgsql-performance@xxxxxxxxxxxxxx > Subject: Re: Performance on Bulk Insert to Partitioned Table > > Charles, > > * Charles Gomes (charlesrg@xxxxxxxxxxx) wrote: > > I’m doing 1.2 Billion inserts into a table partitioned in > > 15. > > Do you end up having multiple threads writing to the same, underlying, > tables..? If so, I've seen that problem before. Look at pg_locks while > things are running and see if there are 'extend' locks that aren't being > immediately granted. > > Basically, there's a lock that PG has on a per-relation basis to extend > the relation (by a mere 8K..) which will block other writers. If > there's a lot of contention around that lock, you'll get poor > performance and it'll be faster to have independent threads writing > directly to the underlying tables. I doubt rewriting the trigger in C > will help if the problem is the extent lock. > > If you do get this working well, I'd love to hear what you did to > accomplish that. Note also that you can get bottle-necked on the WAL > data, unless you've taken steps to avoid that WAL. > > Thanks, > > Stephen -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
