First thing that comes to mind:
a) do you have an index on tqosinsp_rwd?
b) did you vacuum the table in question?
6000 transactions per hour seems awfully slow.
It would also help if you provide structure of the tables. Version of
postgres, too. If there are awfully large rows, performance will go down,
no matter which DB you are using.
On Wed, 31 Oct 2001, Paul Falbe wrote:
>
> Hello,
>
> I recently wrote a script to perform replication of a couple of Tables.
> The problem is that 2 perform approx 6000 insert/update/deletes it
> takes approximately 1 hour. I am on a DELL box with dual processors
> and a hardware RAID. When my script runs the Postgres postmaster
> daemon takes 99.9% of one CPU. I have also tested my script my
> script on a uniprocessor with same results. I have searched google
> for tips on gaining speed but not found anything more than I am already
> doing. Any help would be appreciated.
>
> Thanks in Advance!
>
> ---snip
>
> #!/usr/bin/perl -w
>
> use DBI;
> use strict;
>
> sub cleanup;
>
> chdir("/opt/downloads/tqos");
>
> if ( -s "PROCESSING" ) {
> exit;
> }
> else {
> my $ddate=`date`;
> open(FLAG,">PROCESSING");
> print FLAG $ddate;
> close(FLAG);
> }
>
> my $dbh = DBI->connect("dbi:Pg:dbname=cassens","cassens","");
>
> $dbh->{AutoCommit}=0;
> $dbh->{RaiseError}=0;
>
> my $upd_sum = $dbh->prepare("update tqossum
> set trm = ?,
> inspdt = ?,
> svc = ?,
> mfg = ?,
> pltcd = ?,
> crtrk = ?,
> nbrinsp = ?,
> clninsps = ?,
> drtyinsps = ?
> where tqossumrwd =?")
> || die cleanup("prepare upd_sum");
>
> my $ins_sum = $dbh->prepare("insert into tqossum values (?,?,?,?,?,?,?,?,?,?)")
> || die cleanup("prepare ins_sum");
>
> my $del_sum = $dbh->prepare("delete from tqossum
> where tqossumrwd = ?")
> || die cleanup("prepare del_sum");
>
> my $upd_insp = $dbh->prepare("update tqosinsp
> set trm = ?,
> svc = ?,
> vin10 = ?,
> vin = ?,
> pltcd = ?,
> crtrk = ?,
> ldnbr = ?,
> inspdt = ?,
> trknbr = ?,
> trlnbr = ?,
> drvnbr = ?,
> shptomfg = ?,
> shptocust = ?,
> ldpos = ?,
> insparea = ?,
> insptyp = ?,
> inspsvrty = ?,
> chrgtype = ?,
> mfg = ?,
> rte1 = ?,
> rte2 = ?,
> hometrm = ?
> where tqosinsp_rwd =?")
> || die cleanup("prepare upd_insp");
>
> my $ins_insp = $dbh->prepare("insert into tqosinsp values
>(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
> || die cleanup("prepare ins_insp");
>
> my $del_insp = $dbh->prepare("delete from tqosinsp
> where tqosinsp_rwd = ?")
> || die cleanup("prepare del_insp");
>
> my @sumfiles=<sumtrk*tlr>;
> my @inspfiles=<insptrk*tlr>;
>
> eval {
> foreach my $file (@sumfiles) {
> ($file=$file)=~s/\.tlr/\.dta/g;
>
> if ( ! -s "$file" ) {
> next;
> }
>
> open(IN,"<$file");
> my @dtalines=<IN>;
> close(IN);
>
> foreach my $line (@dtalines) {
> chop($line);
> my @fld=split(/\|/,$line);
>
> if ( $fld[1] eq "UPDATE" ) {
> $upd_sum->execute($fld[5],$fld[6],$fld[7],$fld[8],$fld[9],
> $fld[10],$fld[11],$fld[12],$fld[13],$fld[4])
> || die cleanup("execute upd_sum");
> }
> elsif ( $fld[1] eq "INSERT" ) {
> $ins_sum->execute($fld[4],$fld[5],$fld[6],$fld[7],$fld[8],$fld[9],
> $fld[10],$fld[11],$fld[12],$fld[13])
> || die cleanup("execute ins_sum");
> }
> else {
> $del_sum->execute($fld[4])
> || die cleanup("execute del_sum");
> }
> }
> }
>
>
> foreach my $file (@inspfiles) {
> ($file=$file)=~s/\.tlr/\.dta/g;
>
> if ( ! -s "$file" ) {
> next;
> }
>
> open(IN,"<$file");
> my @dtalines=<IN>;
> close(IN);
>
> foreach my $line (@dtalines) {
> chop($line);
> my @fld=split(/\|/,$line);
>
> if ( $fld[1] eq "UPDATE" ) {
> $upd_insp->execute($fld[5],$fld[6],$fld[7],$fld[8],$fld[9],
> $fld[10],$fld[11],$fld[12],$fld[13],$fld[14],
> $fld[15],$fld[16],$fld[17],$fld[18],$fld[19],
> $fld[20],$fld[20],$fld[21],$fld[22],$fld[23],
> $fld[24],$fld[25],$fld[26],$fld[27],$fld[28],
> $fld[4])
> || die cleanup("execute upd_insp");
> }
> elsif ( $fld[1] eq "INSERT" ) {
> $ins_insp->execute($fld[4],$fld[5],$fld[6],$fld[7],$fld[8],$fld[9],
> $fld[10],$fld[11],$fld[12],$fld[13],$fld[14],
> $fld[15],$fld[16],$fld[17],$fld[18],$fld[19],
> $fld[20],$fld[21],$fld[22],$fld[23],$fld[24],
> $fld[25],$fld[26],$fld[27],$fld[28])
> || die cleanup("execute ins_insp");
> }
> else {
> $del_insp->execute($fld[4])
> || die cleanup("execute del_insp");
> }
> }
> }
>
> my $nfile;
> foreach my $file (@inspfiles) {
> $nfile="processed/".$file;
> rename $file, $nfile;
> ($file=$file)=~s/\.dta/\.tlr/g;
> $nfile="processed/".$file;
> rename $file, $nfile;
> }
> foreach my $file (@sumfiles) {
> $nfile="processed/".$file;
> rename $file, $nfile;
> ($file=$file)=~s/\.dta/\.tlr/g;
> $nfile="processed/".$file;
> rename $file, $nfile;
> }
> unlink("PROCESSING");
> $dbh->commit;
> };
>
> if ($@) {
> open(ERR,">>tqos.log");
> print ERR $@,"\n";
> close(ERR);
> $dbh->rollback;
> unlink("PROCESSING");
> }
>
> $dbh->disconnect;
> ---snip
>
> --
> Internet: | Paul J. Falbe |
> [EMAIL PROTECTED] | Cassens Transport | Std disclaimers apply.
> Voice: | 145 N. Kansas Str. | (But you knew that!)
> 618-656-3006 | Edwardsville, IL 62025 |
>
>
