Thread: Strange performance issue

Strange performance issue

From
Mike Harding
Date:
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";



Re: Strange performance issue

From
Tom Lane
Date:
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

Re: Strange performance issue

From
Mike Harding
Date:
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


Re: Strange performance issue

From
Brent Verner
Date:
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

Re: Strange performance issue

From
Francisco Reyes
Date:
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).


Pratical PostgreSQL now at production

From
"Command Prompt, Inc."
Date:
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/


Re: Strange performance issue

From
Sheer El-Showk
Date:
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)
>


Re: Strange performance issue

From
Mike Harding
Date:
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