Обсуждение: 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
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).
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/
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)
>
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