Strange performance issue
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";
Mike Harding <mvh@ix.netcom.com> writes:
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
Would you try your test case with 7.2beta?
BTW, you really should mention which version you're using, and be
more specific about the platform than "Freebsd".
regards, tom lane
I am using FreeBSD-4.4-STABLE, cvsup'd last weekend, and the version
is 7.1.3 as reflected in the FreeBSD port system.
I can try with 7.2beta, but it might be awkward as I only have a
dial-up line... if somebody with 7.2 already installed could try this,
you will almost certainly beat me to results.
I am interested if this occurs on Linux or other OSes so I can get
feedback to the FreeBSD port maintainer or OS if it is OS specific.
It looks like there is some semaphore contention, and the backends
call 'select' with a timeout AFTER the semaphore is let go. Runs like
10x slower than 1 of these tests when I run 2.
- Mike H.
Cc: pgsql-general@postgresql.org
Comments: In-reply-to Mike Harding <mvh@ix.netcom.com>
message dated "Sun, 25 Nov 2001 20:54:28 -0800"
Date: Mon, 26 Nov 2001 10:18:58 -0500
From: Tom Lane <tgl@sss.pgh.pa.us>
X-SpamBouncer: 1.4 (8/24/01)
X-SBClass: OK
Mike Harding <mvh@ix.netcom.com> writes:
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
Would you try your test case with 7.2beta?
BTW, you really should mention which version you're using, and be
more specific about the platform than "Freebsd".
regards, tom lane
On 26 Nov 2001 at 08:04 (-0800), Mike Harding wrote:
|
| I am using FreeBSD-4.4-STABLE, cvsup'd last weekend, and the version
| is 7.1.3 as reflected in the FreeBSD port system.
|
| I can try with 7.2beta, but it might be awkward as I only have a
| dial-up line... if somebody with 7.2 already installed could try this,
| you will almost certainly beat me to results.
Testing your case with postgres 7.2b3 on FBSD 4.4-STABLE, I do not
see what you described. Below is a copy of top(1) running while
running two instances of your script.
9507 brent 41 0 5172K 3376K RUN 0:11 37.17% 28.47% postgres
9508 brent 41 0 5172K 3376K RUN 0:11 36.98% 28.32% postgres
9506 brent 2 0 4196K 3072K select 0:02 5.42% 4.15% perl
9505 brent 2 0 4196K 3072K select 0:02 4.85% 3.71% perl
running the same against 7.1.3 top(1) shows
9575 pgsql -4 0 6044K 3480K semwai 0:04 0.88% 0.88% postgres
9574 pgsql -4 0 6044K 3484K semwai 0:04 0.73% 0.73% postgres
9580 pgsql -4 0 6044K 3484K semwai 0:01 0.70% 0.68% postgres
9579 pgsql 2 0 6044K 3484K select 0:01 0.15% 0.15% postgres
cheers.
brent
--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing." -- Duane Allman
Thanks for confirming that
1 - there is a problem with 7.1.3 and I am not hallucinating, and
2 - it has been fixed!
It's possible that the 7.2 port won't work properly because of some
patch, I will test this after 7.2 enters the port tree as well.
Thanks for all the good work!
- Mike H.
X-Received-From: brent@mutt.rcfile.org
X-Delivered-To: mvh@ix.netcom.com
X-Spam-Filter: check_local@habanero.hesketh.net by digitalanswers.org
X-More-Information: http://spamfighter.hesketh.net
Date: Mon, 26 Nov 2001 13:30:31 -0500
From: Brent Verner <brent@rcfile.org>
Cc: tgl@sss.pgh.pa.us, pgsql-general@postgresql.org
Content-Disposition: inline
X-SpamBouncer: 1.4 (8/24/01)
X-SBClass: OK
On 26 Nov 2001 at 08:04 (-0800), Mike Harding wrote:
|
| I am using FreeBSD-4.4-STABLE, cvsup'd last weekend, and the version
| is 7.1.3 as reflected in the FreeBSD port system.
|
| I can try with 7.2beta, but it might be awkward as I only have a
| dial-up line... if somebody with 7.2 already installed could try this,
| you will almost certainly beat me to results.
Testing your case with postgres 7.2b3 on FBSD 4.4-STABLE, I do not
see what you described. Below is a copy of top(1) running while
running two instances of your script.
9507 brent 41 0 5172K 3376K RUN 0:11 37.17% 28.47% postgres
9508 brent 41 0 5172K 3376K RUN 0:11 36.98% 28.32% postgres
9506 brent 2 0 4196K 3072K select 0:02 5.42% 4.15% perl
9505 brent 2 0 4196K 3072K select 0:02 4.85% 3.71% perl
running the same against 7.1.3 top(1) shows
9575 pgsql -4 0 6044K 3480K semwai 0:04 0.88% 0.88% postgres
9574 pgsql -4 0 6044K 3484K semwai 0:04 0.73% 0.73% postgres
9580 pgsql -4 0 6044K 3484K semwai 0:01 0.70% 0.68% postgres
9579 pgsql 2 0 6044K 3484K select 0:01 0.15% 0.15% postgres
cheers.
brent
--
"Develop your talent, man, and leave the world something. Records are
really gifts from people. To think that an artist would love you enough
to share his music with anyone is a beautiful thing." -- Duane Allman
On Mon, 26 Nov 2001, Brent Verner wrote:
On 26 Nov 2001 at 08:04 (-0800), Mike Harding wrote:
|
| I am using FreeBSD-4.4-STABLE, cvsup'd last weekend, and the version
| is 7.1.3 as reflected in the FreeBSD port system.
|Testing your case with postgres 7.2b3 on FBSD 4.4-STABLE, I do not
see what you described. Below is a copy of top(1) running while
running two instances of your script.
9575 pgsql -4 0 6044K 3480K semwai 0:04 0.88% 0.88% postgres
When I first installed postgresql on FreeBSD 4.4-Stable I also noticed it
used considerable CPU even with somewhat trivial tasks. After doing some
reading I found out about increasing buffer, sort_mem and several FreeBSD
memory settings. This seems to have helped. For example the top line above
leads me to believe buffers and sort_mem were twicked on that machine.
My exact settings are:
postgresql.conf
shared_buffers = 5000
sort_mem = 8192
/etc/sysctl.conf
kern.ipc.shmall=65535
kern.ipc.shmmax=67108864
kern.ipc.shm_use_phys=1
The machine in question is stable as of early November and it has 192MB.
Each of the posgresql sessions take about 55MB on my machine and I usually
only have 1 or 2 sessions going (for now).
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:
Show quoted text
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)
Hello,
Just to let you guys know, Practical PostgreSQL has gone to the production
people. You can still view the book online at http://www.postgresql.info
but it might be a good time to buy it ;).
J
--
--
by way of pgsql-general@commandprompt.com
http://www.postgresql.info/
http://www.commandprompt.com/