Re: ideas on optimization

On 12 Jun 2001, Doug McNaught wrote:

> "Andy Jenks" <andy(at)compete(dot)com> writes:
>
> > currently we are testing postgres vs DB2 on speed and scalability. our test
> > database is approx 30GB and we are running freeBSD 4.3-STABLE with a 733MHZ
> > processor and 1GB of ram with postgres 7.1.2. our postgres queries are
> > taking over 4min while our DB2 queries are taking less than 30 sec. this is
> > really bad considering our DB will grow to over 6TB for 1 year worth of
> > data. Can postgres handle this, and is there a way to optimize the
> > postmaster to use all the system resources that it needs. currently we
> > start the postmaster this way:
> > pg_ctl -o "-o -F -B 3000 -N 2 -ps -pn" start
> > any help would be great.
>
> It would help if you post your schema (including indexes), and the
> EXPLAIN output from the queries you're testing. I'm not an SQL
> optimization guru (yet) but there are plenty of people on the list
> that are.
>
> -Doug

Sorry for interrupting, but I also have a question. About -B 3000 the manual mentions:

SHARED_BUFFERS (integer)
Sets the number of shared memory buffers the database server will use. The default is 64. Each buffer is typically 8192 bytes. This option can only be set at server start

Does this refer to the shared memory segment i.e. the one I can set through /proc/sys/kernel/shmall in my linux box?
Can somebody give the relation that should be retained between the total memory and shared buffers?

In postgresql.conf (Pg 7.1.1) it says "shared_buffer=2*max_connections # min 16". So if he's set -N 2 does it make any sense to have -B 3000?

TIA,
thalis

> --
> The rain man gave me two cures; he said jump right in,
> The first was Texas medicine--the second was just railroad gin,
> And like a fool I mixed them, and it strangled up my mind,
> Now people just get uglier, and I got no sense of time... --Dylan
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>