Re: Strange performance issue

My guess would be that introducing the second process brings in a large
set of other factors that make the application less CPU-bound.  It might
be I/O bound (non-indexed foreign key to anohter table or any full table
scan) or be spending most of its time in the kernel (also possible due to
full table scans).  My experience has been that when setup properly
postgres spends 20% of its time in the kernel (when the cpu is being fully
used), but this can spike up to 60% if you're doing full table scans.  In
performance tuning my database I downloaded atsar (maybe just sar for
freebsd) and watched various metrics as I ran my tests to see where the
system is spending its time -- watch the number of context swaps, the
number of pages being swapped in and out (freebsd if I recall doesn't
buffer writes so if you're writing a lot of pages the CPU might be
spending time swapping them out), the number of processes in the wait
queue.  You're system is definately not sitting idly by when you give it a
task -- if the CPU's at 3% some other part of the system must be
strainging.

Good luck,
Sheer

On Sun, 25 Nov 2001, Mike Harding wrote:

>
> I am running a capacity test just for fun, and notice that if I run
> one update the system uses 100% of the CPU, but if I use 2 updates at
> the same time the system is only about 3% loaded and the postgres
> backends rotate between
>      select
>      RUN
>      semwait
>
> ...this is on Freebsd.
>
> The test was just to store 100,000 new values in a single transaction...
>
> the table definition looks like
>
> --
> -- Selected TOC Entries:
> --
> \connect - mvh
> --
> -- TOC Entry ID 2 (OID 21452)
> --
> -- Name: boogers Type: TABLE Owner: mvh
> --
>
> CREATE TABLE "boogers" (
>     "num" integer,
>     "name" character varying(20),
>     "id" serial,
>     "modtime" timestamp with time zone DEFAULT now()
> );
>
> --
> -- Data for TOC Entry ID 5 (OID 21452)
> --
> -- Name: boogers Type: TABLE DATA Owner: mvh
> --
>
>
> COPY "boogers"  FROM stdin;
> \.
> --
> -- TOC Entry ID 3 (OID 21452)
> --
> -- Name: "boogers_name" Type: INDEX Owner: mvh
> --
>
> CREATE  INDEX "boogers_name" on "boogers" using btree ( "name" "varchar_ops" );
>
> --
> -- TOC Entry ID 4 (OID 21452)
> --
> -- Name: "boogers_num" Type: INDEX Owner: mvh
> --
>
> CREATE  INDEX "boogers_num" on "boogers" using btree ( "num" "int4_ops" );
>
> ... and the perl code looks like
>
> #!/usr/bin/perl
>
> use DBI;
>
> $dbh = DBI->connect("dbi:Pg:dbname=mvh","","",{AutoCommit => 0});
>
> $sth = $dbh->prepare("INSERT INTO boogers VALUES (?,?)");
>
> # insert a bunch of values
> for( $i=0; $i< 100000; $i++) {
>     $sth->execute( $i, $i);
> }
>
> $dbh->commit;
>
> $rc = $dbh->disconnect;
>
> print $rc, "\n";
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>