Re: Slowdown problem when writing 1.7million records

At 02:25 PM 27-02-2001 -0500, Tom Lane wrote:
>
>> Is this sort of degradation normal using a PostgreSQL database?
>
>No, it's not. Do you have any triggers or rules on this table that

I find slowdowns with inserts with rollbacks and updates with commits ( no
triggers or rules) :(.

But no noticeable slowdown for inserts with commits (even with a unique);

Here are some tests I did:

select version();
PostgreSQL 7.1beta4 on i686-pc-linux-gnu, compiled by GCC egcs-2.91.66

drop table test;
create table test (a text unique);

--- dbtest7
cat dbtest7
#!/usr/bin/perl -wT
use DBI;
my $c=0;

my $dbh = DBI->connect('DBI:Pg(AutoCommit =>
0):dbname=lylyeoh','lylyeoh','hoho') or die("Error connecting to
database!",$DBI::errstr);

my $SQL=<<"EOT";
insert into test (a) values (?)
EOT
my $sth = $dbh->prepare($SQL);
while ($c++<2000) {
$sth->execute($c) or die("Error executing query!",
$DBI::errstr);
}
$dbh->rollback;
$dbh->disconnect;

--- results:
time ./dbtest7
0.24user 0.08system 0:01.60elapsed 19%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (383major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.30user 0.06system 0:01.56elapsed 22%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.23user 0.08system 0:01.57elapsed 19%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.26user 0.09system 0:01.57elapsed 22%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.25user 0.10system 0:01.59elapsed 21%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.27user 0.05system 0:01.60elapsed 19%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.31user 0.07system 0:01.62elapsed 23%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.25user 0.09system 0:01.63elapsed 20%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.24user 0.16system 0:01.62elapsed 24%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.23user 0.05system 0:01.63elapsed 17%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.26user 0.09system 0:01.64elapsed 21%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.27user 0.06system 0:01.67elapsed 19%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.31user 0.06system 0:01.68elapsed 21%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.29user 0.07system 0:01.69elapsed 21%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.27user 0.06system 0:01.69elapsed 19%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.23user 0.17system 0:01.70elapsed 23%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.25user 0.08system 0:01.72elapsed 19%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.31user 0.09system 0:01.74elapsed 22%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.32user 0.08system 0:01.76elapsed 22%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.28user 0.07system 0:01.75elapsed 20%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.21user 0.07system 0:01.75elapsed 15%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.25user 0.10system 0:01.78elapsed 19%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.31user 0.04system 0:01.76elapsed 19%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.20user 0.06system 0:01.79elapsed 14%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.29user 0.10system 0:01.82elapsed 21%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest7
0.30user 0.14system 0:01.80elapsed 24%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps

Eventually it went to 2 seconds, and probably will continue increasing.

It is even more dramatic for updates:

drop table test;
create table test ( t text,id int);
insert into test (t,id) values ('',1);

--- dbtest3
#!/usr/bin/perl -wT
use DBI;
my $c=0;

my $dbh = DBI->connect('DBI:Pg(AutoCommit =>
0):dbname=lylyeoh','lylyeoh','haha') or die("Error connecting to
database!",$DBI::errstr);

my $SQL=<<"EOT";
update test set t=? where id=1
EOT
my $sth = $dbh->prepare($SQL);
my $rv='';
while ($c++<1000) {
$rv = $sth->execute("a$c") or die("Error executing query!",
$DBI::errstr);
}
$dbh->commit;
$dbh->disconnect;

time ./dbtest3
0.20user 0.09system 0:00.99elapsed 29%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest3
0.13user 0.08system 0:01.30elapsed 16%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest3
0.20user 0.05system 0:01.62elapsed 15%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest3
0.25user 0.02system 0:01.98elapsed 13%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest3
0.22user 0.06system 0:02.47elapsed 11%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps
[lylyeoh(at)nimbus perl]$ time ./dbtest3
0.23user 0.02system 0:02.88elapsed 8%CPU (0avgtext+0avgdata 0maxresident)k
0inputs+0outputs (382major+231minor)pagefaults 0swaps

Any way to stop this degradation short of a "vacuum test"?

The "update with commit" slowdown affects my hits per second for my webapp.

Cheerio,
Link.