pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
performance issues
6+ messages / 4 participants
[nested] [flat]

* performance issues
@ 2001-06-13 13:32 DHSC Webmaster <webmaster@dhs-club.com>
  2001-06-13 14:29 ` Re: performance issues Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: DHSC Webmaster @ 2001-06-13 13:32 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org

Hello all,
We are running 7.0.3 on a RH 7.0 box w/twin 733 pentium CPUs, 512 meg.
ram and a 3 drive raid 5 scsi array. The machine is dedicated. Our main
table is about 700,000 rows with an associated table of about 15,000
rows. 
-N 128 -B 256
logging to a file
Without spelling out our complete schema I'll give some basic info
main table has 37 columns with 3 indexes, several foreign key
constraints and one rule
the associated table has about 10 columns all or which are int 4 with
the exception of one text
Most of our queries are simple select queries, half of which would use
the indexes.
Now, does this scenario sound about right or do I have a lot of work to
do?
At any given moment having 12 - 16 queries running (say 1 update & one
insert, the rest selects),
I'm dragging bad. Vacuum -z was just run. top gives me numbers around
20. Even with just a 2 or 3 queries running top gives me 2 - 3. Does
this sound typical?
If my information is insufficient to give a ballpark quess, perhaps you
could give me some normal types of performance figures.

Thank you for your feedback.
-- 
Bill MacArthur
Webmaster
DHS Club



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: performance issues
  2001-06-13 13:32 performance issues DHSC Webmaster <webmaster@dhs-club.com>
@ 2001-06-13 14:29 ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-14 14:49   ` Re: performance issues DHSC Webmaster <webmaster@dhs-club.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2001-06-13 14:29 UTC (permalink / raw)
  To: DHSC Webmaster <webmaster@dhs-club.com>; +Cc: pgsql-admin@postgresql.org

DHSC Webmaster <webmaster@dhs-club.com> writes:
> We are running 7.0.3 on a RH 7.0 box w/twin 733 pentium CPUs, 512 meg.
> ram and a 3 drive raid 5 scsi array. The machine is dedicated. Our main
> table is about 700,000 rows with an associated table of about 15,000
> rows. 
> -N 128 -B 256

That's not nearly enough buffers for an installation that size.  Try -B
of a few thousand.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: performance issues
  2001-06-13 13:32 performance issues DHSC Webmaster <webmaster@dhs-club.com>
  2001-06-13 14:29 ` Re: performance issues Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-14 14:49   ` DHSC Webmaster <webmaster@dhs-club.com>
  2001-06-14 15:51     ` Re: performance issues Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: DHSC Webmaster @ 2001-06-14 14:49 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org

Thank you Tom. That has seemed to help substantially.
I received another sugestion to increase -B 30000 which would give 240
meg. to postgres. I plan to give that a try. I've already reset the
SHMMAX val in sysctl to accomodate that size. Do you see any reason not
to try this? My thought is if some is good, more must be better. :)
Also I noticed in this artice:
http://www.ca.postgresql.org/docs/faq-english.html#3.7
mention of another memory parameter (-S), however the elephant book
refers to -S as silent mode.
Could you clarify this?

Tom Lane wrote:
> 
> DHSC Webmaster <webmaster@dhs-club.com> writes:
> > We are running 7.0.3 on a RH 7.0 box w/twin 733 pentium CPUs, 512 meg.
> > ram and a 3 drive raid 5 scsi array. The machine is dedicated. Our main
> > table is about 700,000 rows with an associated table of about 15,000
> > rows.
> > -N 128 -B 256
> 
> That's not nearly enough buffers for an installation that size.  Try -B
> of a few thousand.
> 
>                         regards, tom lane

-- 
Bill MacArthur
Webmaster
DHS Club



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: performance issues
  2001-06-13 13:32 performance issues DHSC Webmaster <webmaster@dhs-club.com>
  2001-06-13 14:29 ` Re: performance issues Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-14 14:49   ` Re: performance issues DHSC Webmaster <webmaster@dhs-club.com>
@ 2001-06-14 15:51     ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-14 16:35       ` Re: performance issues Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2001-06-14 15:51 UTC (permalink / raw)
  To: DHSC Webmaster <webmaster@dhs-club.com>; +Cc: pgsql-admin@postgresql.org

DHSC Webmaster <webmaster@dhs-club.com> writes:
> Thank you Tom. That has seemed to help substantially.
> I received another sugestion to increase -B 30000 which would give 240
> meg. to postgres. I plan to give that a try. I've already reset the
> SHMMAX val in sysctl to accomodate that size. Do you see any reason not
> to try this? My thought is if some is good, more must be better. :)

Not always; there are several reasons why more can be worse:

1. It's a good idea to leave plenty of free RAM for kernel-level disk
buffering.  Postgres has been tuned (to the extent that it is tuned)
to operate with a fair amount of kernel buffering underneath it.

2. If you are on a platform where the kernel thinks it can swap out
parts of shared memory (which is most platforms, these days), it's
counterproductive to make your shared mem large enough that it's not
all being used heavily.  If the kernel decides to swap out some buffer
space, you have a net loss in performance, because you just wasted
disk I/O (especially if the page in the buffer is dirty --- it'll
eventually have to be read in again, then written out again, to no
purpose).

3. Also, there are some operations (such as dropping a table) which
cause linear scans of the whole buffer array, so making it too big
can slow things down, depending on your query mix.

While I have not done a lot of measurement to back this up, my rule of
thumb is that you don't want to reserve more than about a quarter of
your physical RAM for Postgres' disk buffers, even on a dedicated
machine.  But feel free to experiment and see what results you get.
Please report back if you do any sort of organized tuning experiments;
AFAIK we don't have many hard numbers about these effects.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: performance issues
  2001-06-13 13:32 performance issues DHSC Webmaster <webmaster@dhs-club.com>
  2001-06-13 14:29 ` Re: performance issues Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-14 14:49   ` Re: performance issues DHSC Webmaster <webmaster@dhs-club.com>
  2001-06-14 15:51     ` Re: performance issues Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-06-14 16:35       ` Bruce Momjian <pgman@candle.pha.pa.us>
  2001-06-16 20:51         ` Re: performance issues Brian McCane <bmccane@mccons.net>
  0 siblings, 1 reply; 6+ messages in thread

From: Bruce Momjian @ 2001-06-14 16:35 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: DHSC Webmaster <webmaster@dhs-club.com>; pgsql-admin@postgresql.org

> 2. If you are on a platform where the kernel thinks it can swap out
> parts of shared memory (which is most platforms, these days), it's
> counterproductive to make your shared mem large enough that it's not
> all being used heavily.  If the kernel decides to swap out some buffer
> space, you have a net loss in performance, because you just wasted
> disk I/O (especially if the page in the buffer is dirty --- it'll
> eventually have to be read in again, then written out again, to no
> purpose).

FYI, FreeBSD had the ability to disable shared memory swapping with a
sysctl setting, while BSD/OS does not swap out shared memory.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: performance issues
  2001-06-13 13:32 performance issues DHSC Webmaster <webmaster@dhs-club.com>
  2001-06-13 14:29 ` Re: performance issues Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-14 14:49   ` Re: performance issues DHSC Webmaster <webmaster@dhs-club.com>
  2001-06-14 15:51     ` Re: performance issues Tom Lane <tgl@sss.pgh.pa.us>
  2001-06-14 16:35       ` Re: performance issues Bruce Momjian <pgman@candle.pha.pa.us>
@ 2001-06-16 20:51         ` Brian McCane <bmccane@mccons.net>
  0 siblings, 0 replies; 6+ messages in thread

From: Brian McCane @ 2001-06-16 20:51 UTC (permalink / raw)
  To: pgsql-admin@postgresql.org

On Thu, 14 Jun 2001, Bruce Momjian wrote:

> > 2. If you are on a platform where the kernel thinks it can swap out
> > parts of shared memory (which is most platforms, these days), it's
> > counterproductive to make your shared mem large enough that it's not
> > all being used heavily.  If the kernel decides to swap out some buffer
> > space, you have a net loss in performance, because you just wasted
> > disk I/O (especially if the page in the buffer is dirty --- it'll
> > eventually have to be read in again, then written out again, to no
> > purpose).
> 
> FYI, FreeBSD had the ability to disable shared memory swapping with a
> sysctl setting, while BSD/OS does not swap out shared memory.
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 853-3000
>   +  If your life is a hard drive,     |  830 Blythe Avenue
>   +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

I was going to mention this but had to get to my database machine to find
it first.  The command is:

sysctl -w kern.ipc.shm_use_phys=1

This is supposed to make shared memory use physical memory, but I don't
think it is a an absolute rule.  If you use too much memory, it will still
swap I believe.

- brian




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-06-16 20:51 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-13 13:32 performance issues DHSC Webmaster <webmaster@dhs-club.com>
2001-06-13 14:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-14 14:49   ` DHSC Webmaster <webmaster@dhs-club.com>
2001-06-14 15:51     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-14 16:35       ` Bruce Momjian <pgman@candle.pha.pa.us>
2001-06-16 20:51         ` Brian McCane <bmccane@mccons.net>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox