Re: Strange performance issue
От | Sheer El-Showk |
---|---|
Тема | Re: Strange performance issue |
Дата | |
Msg-id | Pine.LNX.4.33.0111261600150.25866-100000@laudanum.saraf.com обсуждение исходный текст |
Ответ на | Strange performance issue (Mike Harding <mvh@ix.netcom.com>) |
Список | pgsql-general |
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) >
В списке pgsql-general по дате отправления: