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.

Search Discussions

  • Bob Lunney at Mar 31, 2011 at 12:48 am
    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 wrote:
    From: Strange, John W <john.w.strange@jpmchase.com>
    Subject: [PERFORM] COPY with high # of clients, partitioned table locking issues?
    To: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.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@postgresql.org)
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-performance
  • Samuel Gendler at Mar 31, 2011 at 1:31 am

    On Wed, Mar 30, 2011 at 5:48 PM, Bob Lunney wrote:

    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?
    10. are you copying directly into the child tables or into the parent and
    then redirecting to child tables via a trigger?
  • Emanuel Calvo Franco at Mar 31, 2011 at 10:43 am
    Your message was dropped into my Spam lable :S


    2011/3/30 Strange, John W <john.w.strange@jpmchase.com>:
    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
    But you are using stdin for COPY! The best way is use files. Maybe you must
    review postgresql.conf configuration, especially the WAL configuration.
    How many times you do this procedure? which is the amount of data involved?




    --
    --
    Emanuel Calvo
    Helpame.com
  • Maciek Sakrejda at Mar 31, 2011 at 4:53 pm
    But you are using stdin for COPY! The best way is use files.
    I've never heard this before, and I don't see how reading from files
    could possibly help. Can you clarify?
    ---
    Maciek Sakrejda | System Architect | Truviso

    1065 E. Hillsdale Blvd., Suite 215
    Foster City, CA 94404
    (650) 242-3500 Main
    www.truviso.com
  • Greg Smith at Apr 1, 2011 at 6:50 am

    On 03/30/2011 04:56 PM, Strange, John W wrote:
    [ 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: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.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
    When you insert something new into the database, sometimes it has to
    grow the size of the underlying file on disk to add it. That's called
    "relation extension"; basically the table gets some number of 8K blocks
    added to the end of it. If your workload tries to push new blocks into
    a table with no free space, every operation will become serialized
    waiting on individual processes grabbing the lock for relation extension.

    The main reasonable way around this from a high level is to write
    something that makes the extensions create significantly more data when
    they get into this situation than they do right now. Don't just extend
    by one block; extend by a large numer instead, if you believe you're in
    this sort of situation. That's probably going to take a low-level code
    change to actually fix the issue inside PostgreSQL though.


    --
    Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
    PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us
    "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedMar 30, '11 at 8:56p
activeApr 1, '11 at 6:50a
posts6
users6
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase