Re: Re: Slowdown problem when writing 1.7million records

> Server (v6.2), I am accessing the files using JDBC from a Windows 2000 PC.

I don't use Microsoft software, nor Java, but a few general suggestions
for you:

- you may want to create the INDEX at the end (using CREATE INDEX)
in one operation

- you may want to run your requests in transactions: e.g. a transaction
which is committed every N insertion.

- in some rare case you may want to disable the fsync() of the
PostgreSQL

- you may want to use a more efficient interface, such as the
COPY command.

Example (using transactions)

#! /usr/bin/perl -wI /home/schaefer/perl-libs
# $Id$

use DBI;
use test_db;
use strict;

my $debug = 0;

my $result = 0; # success
my $reason = "of an unknown error";

$| = 1;

my $dbh = &open_database();
if (defined($dbh)) {
my $amount_entries = 4000000;
my $commit_every = 10000;

# Sometimes, large data entries are done better with a COPY.

$dbh->{AutoCommit} = 0; # Use transactions.

foreach (('sol_f', 'sol_i', 'sol_d')) {
# Using transactions should make insertion faster, since fsync()
# are probably not required. However, when changes are very big,
# it might actually make it slower or using much space, this
# is why we have this $commit_every above and below.
# was expecting a BEGIN WORK; but that
# seem to be implicit.

eval {
my $i;
my $failed_reason = "unknown db error";

print "Populating " . $_ . " ...";
for ($i = 0; ($i < $amount_entries) && ($result == 0); $i++) {
my @titles = ('id', 'ref', 'sentpos', 'wordpos');
if (!&do_query($dbh,
"INSERT INTO " . $_ . "("
. join(", ", @titles)
. ") VALUES ("
. join(", ", ('?') x @titles)
. ")",
\(at)titles,
undef,
[ int(rand(32768)),
'truc',
int(rand(32768)),
'temp'
],
undef,
\$failed_reason)) {
$result = 1;
$reason = "can't insert " . $i . ": " . $failed_reason;
}
else {
if (($i % $commit_every) == 0) {
if ($dbh->commit) {
print "C ";
}
else {
$result = 1;
$reason = "can't commit: " . $dbh->errstr;
}
}
}
}

if ($result == 0) {
print " POPULATED.\n";
}
else {
print " FAILED.\n";
}
}; # DB is not set to die mode, so we will catch only our bugs.
if ($@) {
print;
$result = 1;
$reason = "transaction failed: " . $@; # Not always right.
$dbh->rollback; # res. ign. (in failure mode anyway)
}
elsif ($result) {
$dbh->rollback; # res. ign. (in failure mode anyway)
}
else {
if ($dbh->commit) {
$result = 1;
$reason = "can't commit: " . $dbh->errstr;
}
print "COMMITTED.\n";
}
}

$dbh->{AutoCommit} = 1; # No transactions

if (!$dbh->disconnect) {
$result = 1;
$reason = "disconnect error: " . $dbh->errstr;
}
undef $dbh;
}
else {
# Obviously, can't use $dbh->errstr here.
$reason = "can't database connect: " . $DBI::errstr;
$result = 1;
}

if ($result) {
print $0 . ": failed " . $result . " because " . $reason . "\n";
}
else {
print "SUCCESSFUL.\n";
}
exit $result;

sub create_table {
my($dbh, $name, $val) = @_;
my $result = 0;

my $sth = $dbh->prepare("CREATE TABLE $name ($val)");
if (defined($sth)) {
my $rv = $sth->execute;
if (defined($rv)) {
if ($debug) {
print "$name: succeeded.\n";
}

$result = 1;
}

$sth->finish;
undef $sth;
}

return $result;
}

And using COPY:

#! /usr/bin/perl -wI /home/schaefer/perl-libs
# USAGE
# ./copy.pl | psql test_db
# $Id$

my $amount_entries = 4000000;
my $tell_every = 100000;

print "COPY sol_f FROM stdin;\n";
my $i;
for ($i = 1; $i <= $amount_entries; $i++) {
print int(rand(32768)) . "\t" . 'truc' . "\t" . int(rand(32768)) . "\t"
. 'temp' . "\n";
if (($i % $tell_every) == 0) {
print STDERR $i . "\n";
}
}
print ".\n";