Re: Large database help

> xbdelacour@yahoo.com writes:
> > Hi everyone, I'm more or less new to PostgreSQL and am trying to setup a
> > rather large database for a data analysis application. Data is collected
> > and dropped into a single table, which will become ~20GB. Analysis happens
> > on a Windows client (over a network) that queries the data in chunks across
> > parallel connections. I'm running the DB on a dual gig p3 w/ 512 memory
> > under Redhat 6 (.0 I think).
>
> > I am setting 'echo 402653184 >/proc/sys/kernel/shmmax', which is being
> > reflected in top. I also specify '-B 48000' when starting postmaster.
>
> Hm.  384M shared memory request on a 512M machine.  I'll bet that the
> kernel is deciding you don't need all that stuff in RAM, and is swapping
> out chunks of the shared memory region to make room for processes and
> its own disk buffering activity.  Try a more reasonable -B setting, like
> maybe a quarter of your physical RAM, max.  There's no percentage in -B
> large enough to risk getting swapped.  Moreover, any physical RAM that
> does happen to be free will be exploited by the kernel for disk
> buffering at its level, so you aren't really saving any I/O by
> increasing Postgres' internal buffering.

FYI, I will complete a PostgreSQL performance tuning article this week
that will appear on the PostgreSQL web site.  It will talk about the
problems if making -B too high, causing pages to be swapped out.

--
  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