Re: Large database help

From: Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: xbdelacour(at)yahoo(dot)com, pgsql-admin(at)postgresql(dot)org
Subject: Re: Large database help
Date: 2001-04-23 01:25:53
Message-ID: 200104230125.f3N1Pr500762@candle.pha.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

> xbdelacour(at)yahoo(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message Bruce Momjian 2001-04-23 01:26:53 Re: Large database help
Previous Message Randall Perry 2001-04-22 23:54:25 Connecting via perl gives "root" does not exist