Re: Performance on Bulk Insert to Partitioned Table

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(at)snowman(dot)net
> To: charlesrg(at)outlook(dot)com
> CC: pgsql-performance(at)postgresql(dot)org
> Subject: Re: [PERFORM] Performance on Bulk Insert to Partitioned Table
>
> Charles,
>
> * Charles Gomes (charlesrg(at)outlook(dot)com) 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