From: John Rouillard <rouilj@renesys.com>
To: pgsql-performance@postgresql.org
Subject: Re: Very poor performance loading 100M of sql data using copy
Date: Mon, 28 Apr 2008 18:00:53 +0000
Message-ID: <20080428180053.GL6622@renesys.com> (raw)
In-Reply-To: <48160F05.2090207@enterprisedb.com>
References: <20080428172431.GI6622@renesys.com>
<48160F05.2090207@enterprisedb.com>
On Mon, Apr 28, 2008 at 06:53:09PM +0100, Heikki Linnakangas wrote:
> John Rouillard wrote:
> >We are running postgresql-8.1.3 under Centos 4
> You should upgrade, at least to the latest minor release of the 8.1
> series (8.1.11), as there has been a bunch of important bug and security
> fixes. Or even better, upgrade to 8.3, which has reduced the storage
> size of especially variable length datatypes like text/char/varchar in
> particular. As your COPY is I/O bound, reducing storage size will
> translate directly to improved performance.
Yup. Just saw that suggestion in an unrelated email.
> >dm-6 is where the data files reside and dm-4 is where the WAL archives
> >are kept. Note all the DM's are on the same RAID 0 device /dev/sda2.
>
> Another reason to upgrade to 8.3: if you CREATE or TRUNCATE the table in
> the same transaction as you COPY into it, you can avoid WAL logging of
> the loaded data, which will in the best case double your performance as
> your WAL is on the same physical drives as the data files.
We can't do this as we are backfilling a couple of months of data into
tables with existing data.
> >The only indexes we have to drop are the ones on the primary keys
> >(there is one non-primary key index in the database as well).
> >
> >Can you drop an index on the primary key for a table and add it back
> >later? Am I correct in saying: the primary key index is what enforces
> >the unique constraint in the table? If the index is dropped and
> >non-unique primary key data has been added, what happens when you
> >re-add the index?
>
> Yes, the index is what enforces the uniqueness. You can drop the primary
> key constraint, and add it back after the load with ALTER TABLE. If the
> load introduces any non-unique primary keys, adding the primary key
> constraint will give you an error and fail.
That's the part I am worried about. I guess using psql to delete the
problem row then re-adding the index will work.
> Dropping and recreating the indexes is certainly worth trying.
Thanks for the info.
--
-- rouilj
John Rouillard
System Administrator
Renesys Corporation
603-244-9084 (cell)
603-643-9300 x 111
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rouilj@renesys.com
Subject: Re: Very poor performance loading 100M of sql data using copy
In-Reply-To: <20080428180053.GL6622@renesys.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox