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

8.4 COPY performance regression on Solaris

From: Alan Li <ali(at)truviso(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: 8.4 COPY performance regression on Solaris
Date: 2009-06-17 05:46:12
Message-ID: 782056770906162246q264f80cbha5524b1b8cae718f@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

It seems that a COPY of 8M rows to a table to 8.4rc1 takes >30% longer than
it does to 8.3.7 on Solaris.

Here are the steps I've taken to reproduce this problem on two different
solaris boxes (Solaris 10 11/06 s10x_u3wos_10 X86 and Solaris 10 8/07
s10x_u4wos_12b X86).  I've tried this on a Linux box, and I do not see the
problem there.

1. Run the following in psql client to generate a 8M row data file.

copy (select generate_series(1,8000000), ('1 second'::interval *
generate_series(1,8000000) + '2007-01-01'::timestamp)) to
'/export/home/alan/work/pgsql/dump.out' with csv;

2. Build 8.3.7 and 8.4rc1 with the following config.

./configure --prefix=`pwd`/../pgsql CC=/opt/SUNWspro/bin/cc CFLAGS="-xO3
-xarch=native \
-xspace -W0,-Lt -W2,-Rcond_elim -Xa  -xildoff -xc99=none -xCC"
--without-readline --with-includes=/opt/csw/include
--with-libraries=/opt/csw/lib

3. Run the following on each.

pg_ctl stop -D data -m fast
rm -rf data
initdb -D data
cat postgresql.conf >> data/postgresql.conf
pg_ctl start -l cq.log -D data -w
psql -f ddl.sql postgres
time psql -c "copy t from '/export/home/alan/work/pgsql/dump.out' with csv"
postgres

Here are the numbers from several runs I've done.

8.3.7 - Solaris 10 11/06 s10x_u3wos_10 X86
real    0m43.971s
user    0m0.002s
sys     0m0.003s
real    0m44.042s
user    0m0.002s
sys     0m0.003s
real    0m44.828s
user    0m0.002s
sys     0m0.004s
real    0m43.921s
user    0m0.002s
sys     0m0.003s

8.4rc1 - Solaris 10 11/06 s10x_u3wos_10 X86
real    1m0.041s
user    0m0.002s
sys     0m0.003s
real    1m0.258s
user    0m0.002s
sys     0m0.004s
real    1m0.173s
user    0m0.002s
sys     0m0.003s
real    1m0.402s
user    0m0.002s
sys     0m0.003s
real    1m0.767s
user    0m0.002s
sys     0m0.003s

8.3.7 - Solaris 10 8/07 s10x_u4wos_12b X86
real    0m36.242s
user    0m0.002s
sys     0m0.004s
real    0m37.206s
user    0m0.002s
sys     0m0.004s
real    0m38.431s
user    0m0.002s
sys     0m0.004s
real    0m38.885s
user    0m0.002s
sys     0m0.004s
real    0m38.177s
user    0m0.002s
sys     0m0.004s
real    0m38.332s
user    0m0.002s
sys     0m0.004s
real    0m38.401s
user    0m0.002s
sys     0m0.004s
real    0m36.817s
user    0m0.002s
sys     0m0.004s
real    0m39.505s
user    0m0.002s
sys     0m0.004s
real    0m38.871s
user    0m0.002s
sys     0m0.004s
real    0m38.939s
user    0m0.002s
sys     0m0.004s
real    0m38.823s
user    0m0.002s
sys     0m0.004s
real    0m37.955s
user    0m0.002s
sys     0m0.004s
real    0m39.196s
user    0m0.002s
sys     0m0.004s

8.4rc1 - Solaris 10 8/07 s10x_u4wos_12b X86
real    0m50.603s
user    0m0.002s
sys     0m0.004s
real    0m49.945s
user    0m0.002s
sys     0m0.004s
real    0m50.547s
user    0m0.002s
sys     0m0.004s
real    0m50.061s
user    0m0.002s
sys     0m0.004s
real    0m48.151s
user    0m0.002s
sys     0m0.004s
real    0m50.133s
user    0m0.002s
sys     0m0.004s
real    0m50.583s
user    0m0.002s
sys     0m0.004s

Has anyone else seen this problem?

Thanks, Alan

Attachment: postgresql.conf
Description: application/octet-stream (183 bytes)
Attachment: ddl.sql
Description: text/x-sql (51 bytes)

Responses

pgsql-performance by date

Next:From: Alberto DalmasoDate: 2009-06-17 07:07:54
Subject: Re: performance with query
Previous:From: Scott MarloweDate: 2009-06-17 05:12:48
Subject: Re: Index Scan taking long time

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