Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 1966F64FCD2 for ; Wed, 10 Sep 2008 14:49:32 -0300 (ADT) Received: from postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 45786-04 for ; Wed, 10 Sep 2008 14:49:26 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-gx0-f17.google.com (mail-gx0-f17.google.com [209.85.217.17]) by postgresql.org (Postfix) with ESMTP id D97A964FCC4 for ; Wed, 10 Sep 2008 14:49:24 -0300 (ADT) Received: by gxk10 with SMTP id 10so14269922gxk.19 for ; Wed, 10 Sep 2008 10:49:23 -0700 (PDT) Received: by 10.143.5.7 with SMTP id h7mr525574wfi.319.1221068962833; Wed, 10 Sep 2008 10:49:22 -0700 (PDT) Received: by 10.143.39.19 with HTTP; Wed, 10 Sep 2008 10:49:22 -0700 (PDT) Message-ID: Date: Wed, 10 Sep 2008 10:49:22 -0700 From: "Scott Carey" To: "Alan Hodgson" Subject: Re: Improve COPY performance for large data sets Cc: pgsql-performance@postgresql.org In-Reply-To: <200809101014.34249@hal.medialogik.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_39659_9562095.1221068962829" References: <48C7FA69.1080507@brightbuilders.com> <200809101014.34249@hal.medialogik.com> X-Google-Sender-Auth: 7c22d397ef12d6ce X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200809/117 X-Sequence-Number: 31430 ------=_Part_39659_9562095.1221068962829 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline A single SATA drive may not be the best performer, but: 1. It won't make a load take 12 hours unless we're talking a load that is in total, similar to the size of the disk. A slow, newer SATA drive will read and write at at ~50MB/sec at minimum, so the whole 320GB can be scanned at 3GB per minute. Thats ~ 5 hours. It is not likely that 20M records is over 20GB, and at that size there is no way the disk is the bottleneck. 2. To figure out if the disk or CPU is a bottleneck, don't assume. Check iostat or top and look at the disk utilization % and io wait times. Check the backend process CPU utilization. In my experience, there are many things that can cause COPY to be completely CPU bound even with slow disks -- I have seen it bound to a 5MB/sec write rate on a 3Ghz CPU, which a drive from 1998 could handle. It seems like this case is resolved, but there are some other good tuning recommendations. Don't blame the disk until the disk is actually showing high utilization though. COPY is bound typically by the disk or a single CPU. It is usually CPU bound if there are indexes or constraints on the table, and sometimes even when there are none. The pg_bulkload tool in almost all cases, will be significantly faster but it has limitations that make it inappropriate for some to use. On Wed, Sep 10, 2008 at 10:14 AM, Alan Hodgson wrote: > On Wednesday 10 September 2008, Ryan Hansen < > ryan.hansen@brightbuilders.com> > wrote: > >Currently it's taking about 12 hours to complete on a 64 bit > > server with 3 GB memory allocated (shared_buffer), single SATA 320 GB > > drive. I don't seem to get any improvement running the same operation > > on a dual opteron dual-core, 16 GB server. > > > > I'm not asking for someone to solve my problem, just some direction in > > the best ways to tune for faster bulk loading, since this will be a > > fairly regular operation for our application (assuming it can work this > > way). I've toyed with the maintenance_work_mem and some of the other > > params, but it's still way slower than it seems like it should be. > > So any contributions are much appreciated. > > Your drive subsystem, such as it is, is inappropriate for a database. Your > bottleneck is your drive. > > Turning fsync off might help. You should also drop all indexes on the table > before the COPY and add them back after (which would eliminate a lot of > random I/O during the COPY). > > -- > Alan > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > ------=_Part_39659_9562095.1221068962829 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
A single SATA drive may not be the best performer, but:

1. It won't make a load take 12 hours unless we're talking a load that is in total, similar to the size of the disk.  A slow, newer SATA drive will read and write at at ~50MB/sec at minimum, so the whole 320GB can be scanned at 3GB per minute.  Thats ~ 5 hours.  It is not likely that 20M records is over 20GB, and at that size there is no way the disk is the bottleneck.

2. To figure out if the disk or CPU is a bottleneck, don't assume.  Check iostat or top and look at the disk utilization % and io wait times.  Check the backend process CPU utilization.  In my experience, there are many things that can cause COPY to be completely CPU bound even with slow disks -- I have seen it bound to a 5MB/sec write rate on a 3Ghz CPU, which a drive from 1998 could handle.

It seems like this case is resolved, but there are some other good tuning recommendations.  Don't blame the disk until the disk is actually showing high utilization though. 

COPY is bound typically by the disk or a single CPU.  It is usually CPU bound if there are indexes or constraints on the table, and sometimes even when there are none.

The pg_bulkload tool in almost all cases, will be significantly faster but it has limitations that make it inappropriate for some to use.



On Wed, Sep 10, 2008 at 10:14 AM, Alan Hodgson <ahodgson@simkin.ca> wrote:
On Wednesday 10 September 2008, Ryan Hansen <ryan.hansen@brightbuilders.com>
wrote:
>Currently it's taking about 12 hours to complete on a 64 bit
> server with 3 GB memory allocated (shared_buffer), single SATA 320 GB
> drive.  I don't seem to get any improvement running the same operation
> on a dual opteron dual-core, 16 GB server.
>
> I'm not asking for someone to solve my problem, just some direction in
> the best ways to tune for faster bulk loading, since this will be a
> fairly regular operation for our application (assuming it can work this
> way).  I've toyed with the maintenance_work_mem and some of the other
> params, but it's still way slower than it seems like it should be.
> So any contributions are much appreciated.

Your drive subsystem, such as it is, is inappropriate for a database. Your
bottleneck is your drive.

Turning fsync off might help. You should also drop all indexes on the table
before the COPY and add them back after (which would eliminate a lot of
random I/O during the COPY).

--
Alan

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

------=_Part_39659_9562095.1221068962829--