Re: Performance on Bulk Insert to Partitioned Table - Mailing list pgsql-performance

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