Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-admin by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group