Hi,
Concerning the problem of Pg using just 1 CPU I can't give any
ideas, but anyway I have a couple of ideas that may help you, depending on
whether you can drop the table or not:
- The first is that if your modifying the entire table then you could,
instead of doing lots of insert, updates & delete it may be faster to drop
and any indexes that it may have the table and the reconstruct it via
inserts or even better (I think this would be faster) making a file ( like
the ones generated by pg_dump) with the data and import the data using psql
($>cat yourimportfile | psql youdb) and try to use 'COPY "your_table" FROM
stdin' instead of inserts (you can see the format of this file by dumping
one of your DBs). Believe me, it's fast. After inserting/importing the data
into you table, then make the corresponding indexes.
- If you can't drop the table, then you should anyway drop any indexes it
has before modifying the table, modify it, and then replace them. Also,
when you use expressions like this 'where tqossumrwd =?' assuming
'tqossumrwd' is of an integer type, it makes a LOT of difference whether
it's an int4 or int8 (int4 is tens or hundreds times faster to compare than
int8).
Hope you may find some help in this,
At 06:51 AM 10/31/2001 -0600, you 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 |
Javier Dussaillant G.
[EMAIL PROTECTED]
Departamento de Desarrollo VRWEB ltda.
http://www.vrweb.cl
