Re: increasingly slow insert/copy performance - Mailing list pgsql-general

Try turning the fsync off in your configuration file.

Joe,


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Todd Underwood
Sent: Sunday, April 17, 2005 9:18 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] increasingly slow insert/copy performance


howdy,

i'm by no means a postgres expert, so i'm probably making a dumb
mistake, but this is v. troubling.

we have a script that bulk-uploads about 45K rows.  we do this via perl
DBI using the COPY functionality.

the problem is that we do this about once per 5 minutes and the time it
takes to do the operation steadily increases (going from about 5s to
over 5m over the course of 24 hrs).  the table has one primary key (so
hence one index), that we drop and recreate during the bulk load.

here are the details of the test case:

CREATE TABLE test (one int primary key, two int, three int, four int,
five int);

script:

#!/usr/bin/perl -w

use DBI;                        # Load the DBI module

my $DBusername = "username";
my $DBpassword = "password";

my $dbin = "Pg:dbname=useradmin;host=databasehost";
my $table = "test";
my $i;
my $count;

$dbh = DBI->connect("dbi:$dbin",$DBusername,$DBpassword, {
      PrintError => 1,   ### Don't report errors via warn(  )
      RaiseError => 1,   ### Do report errors via die(  )
      AutoCommit => 0    ### Start a transaction block
  } );


$dbh->do("LOCK TABLE $table IN SHARE UPDATE EXCLUSIVE MODE")
    or die "Cannot lock $table...\n";

$dbh->do("ALTER TABLE $table DROP CONSTRAINT ${table}_pkey")
    or die "Cannot drop constraint ${table}_pkey...\n";

$dbh->do("DELETE FROM $table WHERE one>0")
    or die "Cannot empty $table...\n";

$dbh->do("COPY $table (one,two,three,four,five) FROM stdin")
    or die "Cannot COPY to DB ...\n";

for $i (1..45000){

    $dbh->func("$i\t$i\t$i\t$i\t$i\n", 'putline')
        or die
        "Couldn't update $table for $i\n";
}

$dbh->func("\\.\n", 'putline');
$dbh->func('endcopy');

if ($@) {
    $dbh->rollback;
    warn "Rolled back transaction: $@\n";
} else {
    $dbh->commit;
}

$dbh->do("ALTER TABLE $table add CONSTRAINT ${table}_pkey primary key
(one)")
    or die "Cannot add constraint ${table}_pkey...\n"; $dbh->commit;
$dbh->disconnect();

-------------------------------------------------------------

as i said, execution times start at about 2.5-5s and end up at several
minutes.  dropping and recreating the table fixes the problem.  vacuum
does not.

any thoughts?  i searched the list and think we're doing the right stuff
(by dropping the constraints and using the COPY function).  i'm not on
the list at this point, so i'll check the web archives, but replies to
me direclty are also appreciated.

thanks so much,

t.

--
_____________________________________________________________________
todd underwood
director of operations & security
renesys - interdomain intelligence
todd@renesys.com   www.renesys.com

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if
your
      joining column's datatypes do not match