Re: COPY with high # of clients, partitioned table locking issues?

John,

Sorry to hear you're struggling with such underpowered hardware. ;-) A little more information would be helpful, though:

1. What version of PG are you running?
2. What are the constraints on the child tables?
3. How many rows does each copy insert?
4. Are these wrapped in transactions?
5. are the child tables created at the same time the copies are taking place? In the same transaction?
6. Are the indexes in place on the child table(s) when the copies are running? Do they have to be to validate the data?
7. What are the configuration settings for the database? (Just the ones changed from the default, please.)
8. Which file system are you running for the database files? Mount options?
9. Are the WAL files on the same file system?

Bob Lunney

--- On Wed, 3/30/11, Strange, John W <john(dot)w(dot)strange(at)jpmchase(dot)com> wrote:

> From: Strange, John W <john(dot)w(dot)strange(at)jpmchase(dot)com>
> Subject: [PERFORM] COPY with high # of clients, partitioned table locking issues?
> To: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
> Date: Wednesday, March 30, 2011, 4:56 PM
> Just some information on our setup:
>
> - HP DL585 G6
> - 4 x AMD Opteron 8435 (24 cores)
> - 256GB RAM
> - 2 FusionIO 640GB PCI-SSD (RAID0)
> - dual 10GB ethernet.
>
> - we have several tables that we store calculated values
> in.
> - these are inserted by a compute farm that calculates the
> results and stores them into a partitioned schema (schema
> listed below)
> - whenever we do a lot of inserts we seem to get exclusive
> locks.
>
> Is there something we can do to improve the performance
> around locking when doing a lot of parallel inserts with
> COPY into?  We are not IO bound, what happens is that
> the copies start to slow down and continue to come in and
> cause the client to swap, we had hit over 800+ COPYS were in
> a waiting state, which forced us to start paging heavily
> creating an issue.  If we can figure out the locking
> issue the copys should clear faster requiring less memory in
> use.
>
> [ 2011-03-30 15:54:55.886 EDT ] 14405 [local]
> asgprod:4d938288.3845 LOG:  process 14405 still waiting
> for ExclusiveLock on extension of relation 470273 of
> database 16384 after 5001.894 ms
> [ 2011-03-30 15:54:55.886 EDT ] 14405 [local]
> asgprod:4d938288.3845 CONTEXT:  COPY
> reportvalues_part_1931, line 1: "660250     
> 41977959       
> 11917   584573.43642105709"
> [ 2011-03-30 15:54:55.886 EDT ] 14405 [local]
> asgprod:4d938288.3845 STATEMENT:  COPY
> reportvalues_part_1931 FROM stdin USING DELIMITERS ' 
>      '
> [ 2011-03-30 15:54:56.015 EDT ] 7294 [local]
> asgprod:4d938939.1c7e LOG:  process 7294 still waiting
> for ExclusiveLock on extension of relation 470606 of
> database 16384 after 5062.968 ms
> [ 2011-03-30 15:54:56.015 EDT ] 7294 [local]
> asgprod:4d938939.1c7e CONTEXT:  COPY
> reportvalues_part_1932, line 158: "660729 
>    41998839       
> 887     45000.0"
> [ 2011-03-30 15:54:56.015 EDT ] 7294 [local]
> asgprod:4d938939.1c7e STATEMENT:  COPY
> reportvalues_part_1932 FROM stdin USING DELIMITERS ' 
>       '
> [ 2011-03-30 15:54:56.077 EDT ] 25781 [local]
> asgprod:4d938556.64b5 LOG:  process 25781 still waiting
> for ExclusiveLock on extension of relation 470606 of
> database 16384 after 5124.463 ms
>
> relation      |    16384
> |   470606 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 93/677526     
>     | 14354 | RowExclusiveLock     
>    | t
> relation      |    16384
> |   470606 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 1047/4     
>        | 27451 |
> RowExclusiveLock         | t
> relation      |    16384
> |   470606 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 724/58891     
>     | 20721 | RowExclusiveLock     
>    | t
> transactionid |          | 
>         |      | 
>      |         
>   |      94673393 |     
>    |       | 
>         | 110/502566     
>    |  1506 | ExclusiveLock   
>         | t
> virtualxid    |       
>   |          |   
>   |       | 975/92 
>    |           
>    |     
>    |       | 
>         | 975/92     
>        | 25751 |
> ExclusiveLock            | t
> extend        |    16384
> |   470606 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 672/102043     
>    | 20669 | ExclusiveLock   
>         | f
> extend        |    16384
> |   470606 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 1178/10     
>       |  6074 | ExclusiveLock 
>           | f
> virtualxid    |       
>   |          |   
>   |       | 37/889225 
> |           
>    |     
>    |       | 
>         | 37/889225     
>     |  4623 | ExclusiveLock   
>         | t
> relation      |    16384
> |   405725 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 39/822056     
>     | 32502 | AccessShareLock     
>     | t
> transactionid |          | 
>         |      | 
>      |         
>   |      94673831 |     
>    |       | 
>         | 917/278     
>       | 23134 | ExclusiveLock   
>         | t
> relation      |    16384
> |   470609 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 537/157021     
>    | 11863 | RowExclusiveLock   
>      | t
> relation      |    16384
> |   470609 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 532/91114     
>     |  7282 | RowExclusiveLock   
>      | t
> virtualxid    |       
>   |          |   
>   |       | 920/8   
>   |           
>    |     
>    |       | 
>         | 920/8     
>         | 23137 | ExclusiveLock 
>           | t
> relation      |    16384
> |   425555 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 39/822056     
>     | 32502 | AccessShareLock     
>     | t
> relation      |    16384
> |   470606 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 915/10     
>        | 22619 |
> RowExclusiveLock         | t
> relation      |    16384
> |   470606 |      | 
>      |         
>   |           
>    |     
>    |       | 
>         | 344/387563     
>    | 30343 | RowExclusiveLock   
>      | tNumber of child tables: 406 (Use
> \d+ to list them.)
>
>
> riskresults=# \d reportvalues_part_1932;
>     Table "public.reportvalues_part_1932"
>     Column    |   
>    Type       |
> Modifiers
> --------------+------------------+-----------
> reportid     | integer   
>       | not null
> scenarioid   | integer     
>     | not null
> instrumentid | integer          |
> not null
> value        | double precision |
> Indexes:
>     "reportvalues_part_1932_pkey" PRIMARY KEY,
> btree (reportid, scenarioid, instrumentid)
> Inherits: reportvalues_part
>
> riskresults=# \d reportvalues_part;
>       Table "public.reportvalues_part"
>     Column    |   
>    Type       |
> Modifiers
> --------------+------------------+-----------
> reportid     | integer   
>       | not null
> scenarioid   | integer     
>     | not null
> instrumentid | integer          |
> not null
> value        | double precision |
> Indexes:
>     "reportvalues_part_pkey" PRIMARY KEY, btree
> (reportid, scenarioid, instrumentid)
> Number of child tables: 406 (Use \d+ to list them.)
>
> This communication is for informational purposes only. It
> is not
> intended as an offer or solicitation for the purchase or
> sale of
> any financial instrument or as an official confirmation of
> any
> transaction. All market prices, data and other information
> are not
> warranted as to completeness or accuracy and are subject to
> change
> without notice. Any comments or statements made herein do
> not
> necessarily reflect those of JPMorgan Chase & Co., its
> subsidiaries
> and affiliates.
>
> This transmission may contain information that is
> privileged,
> confidential, legally privileged, and/or exempt from
> disclosure
> under applicable law. If you are not the intended
> recipient, you
> are hereby notified that any disclosure, copying,
> distribution, or
> use of the information contained herein (including any
> reliance
> thereon) is STRICTLY PROHIBITED. Although this transmission
> and any
> attachments are believed to be free of any virus or other
> defect
> that might affect any computer system into which it is
> received and
> opened, it is the responsibility of the recipient to ensure
> that it
> is virus free and no responsibility is accepted by JPMorgan
> Chase &
> Co., its subsidiaries and affiliates, as applicable, for
> any loss
> or damage arising in any way from its use. If you received
> this
> transmission in error, please immediately contact the
> sender and
> destroy the material in its entirety, whether in electronic
> or hard
> copy format. Thank you.
>
> Please refer to http://www.jpmorgan.com/pages/disclosures
> for
> disclosures relating to European legal entities.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>