Skip site navigation (1) Skip section navigation (2)

Copy performance issues

From: s anwar <sanwar(at)gmail(dot)com>
To: pgsql-performance <pgsql-performance(at)postgresql(dot)org>
Subject: Copy performance issues
Date: 2010-08-18 20:09:39
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
I am having severe COPY performance issues after adding indices. What used
to take a few minutes (without indices) now takes several hours (with
indices). I've tried to tweak the database configuration (based on Postgres
documentation and forums), but it hasn't helped as yet. Perhaps, I haven't
increased the limits sufficiently. Dropping and recreating indices may not
be an option due to a long time it takes to rebuild all indices.

I'll appreciate someone looking at my configuration and giving me a few
ideas on how to increase the copy performance.


Table structure:
table C:
           Table "public.C"
  Column  |       Type       | Modifiers
 sclk     | double precision | not null
 chan     | smallint         | not null
 det      | smallint         | not null
 x        | real             | not null
 y        | real             | not null
 z        | real             | not null
 r        | real             |
 t        | real             |
 lat      | real             |
 lon      | real             |
 a        | real             |
 b        | real             |
 c        | real             |
 time     | real             |
 qa       | smallint         | not null
 qb       | smallint         | not null
 qc       | smallint         | not null
    "C_pkey" PRIMARY KEY, btree (sclk, chan, det)

partitioned into *19* sub-tables covering lat bands. For example:

sub-table C0:
   Inherits: C
   Check constraints:
       "C0_lat_check" CHECK (lat >= (-10::real) AND lat < 0::real)
       "C0_pkey" PRIMARY KEY, btree (sclk, chan, det)
       "C0_lat" btree (lat)
       "C0_time" btree (time)
       "C0_lon" btree (lon)

sub-table C1:
   Inherits: C
   Check constraints:
       "C1_lat_check" CHECK (lat >= (-20::real) AND lat < -10::real)
       "C1_pkey" PRIMARY KEY, btree (sclk, chan, det)
       "C1_lat" btree (lat)
       "C1_time" btree (time)
       "C1_lon" btree (lon)

The partitions C?s are ~30G (328,000,000 rows) each except one, which is
~65G (909,000,000 rows). There are no rows in umbrella table C from which
C1, C2, ..., C19 inherit. The data is partitioned in C1, C2, ..., C19 in
order to promote better access. Most people will access the data in C by
specifying a lat range. Also, C?s can become quite large over time.

The COPY operation copies one file per partition, for each of the 19
partitions. Each file is between 300,000 - 600,000 records.

System configuration:
1. RHEL5 x86_64
2. 32G RAM
3. 8T RAID5 partition for database on a Dell PERC 5/E controller
   (I understand that I'll never get fast inserts/updates on it based on but cannot change
    to a RAID0+1 for now).
    Database's filesystem is ext4 on LVM on RAID5.
4. Postgres 8.4.2
    shared_buffers = 10GB
    temp_buffers = 16MB
    work_mem = 2GB
    maintenance_work_mem = 256MB
    max_files_per_process = 1000
    effective_io_concurrency = 3
    wal_buffers = 8MB
    checkpoint_segments = 40
    enable_seqscan = off
    effective_cache_size = 16GB
5. analyze verbose; ran on the database before copy operation

Bonnie++ output:
Version  1.03       ------Sequential Output------ --Sequential Input-
                    -Per Chr- --Block-- -Rewrite- -Per Chr- --Block--
Machine        Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP  /sec
dbtest    64240M 78829  99 266172  42 47904   6 58410  72 116247   9 767.9
                    ------Sequential Create------ --------Random
                    -Create-- --Read--- -Delete-- -Create-- --Read---
              files  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec %CP  /sec
                256 16229  98 371704  99 20258  36 16115  97 445680  99
17966  36

In response to


pgsql-performance by date

Next:From: Yeb HavingaDate: 2010-08-18 20:25:27
Subject: Re: write barrier question
Previous:From: Craig JamesDate: 2010-08-18 19:56:57
Subject: Re: write barrier question

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group