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.
Thanks.
Saadat.
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
Indexes:
"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)
Indexes:
"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)
Indexes:
"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
http://wiki.postgresql.org/wiki/SlowQueryQuestions 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- --Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
dbtest 64240M 78829 99 266172 42 47904 6 58410 72 116247 9 767.9 1
------Sequential Create------ --------Random Create--------
-Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
256 16229 98 371704 99 20258 36 16115 97 445680 99 17966 36
dbtest,64240M,78829,99,266172,42,47904,6,58410,72,116247,9,767.9,1,256,16229,98,371704,99,20258,36,16115,97,445680,99,17966,36
I'll appreciate someone looking at my configuration and giving me a few ideas on how to increase the copy performance.
Thanks.
Saadat.
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
Indexes:
"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)
Indexes:
"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)
Indexes:
"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
http://wiki.postgresql.org/wiki/SlowQueryQuestions 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- --Random-
-Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks--
Machine Size K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP
dbtest 64240M 78829 99 266172 42 47904 6 58410 72 116247 9 767.9 1
------Sequential Create------ --------Random Create--------
-Create-- --Read--- -Delete-- -Create-- --Read--- -Delete--
files /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP
256 16229 98 371704 99 20258 36 16115 97 445680 99 17966 36
dbtest,64240M,78829,99,266172,42,47904,6,58410,72,116247,9,767.9,1,256,16229,98,371704,99,20258,36,16115,97,445680,99,17966,36
