Re: Increasing the shared memory

An extract from --> http://www.powerpostgresql.com/PerfList/ might help
you....

shared_buffers:

As a reminder: This figure is NOT the total memory PostgreSQL has to work
with. It is the block of dedicated memory PostgreSQL uses for active
operations, and should be a minority of your total RAM on the machine, since
PostgreSQL uses the OS disk cache as well. Unfortunately, the exact amount
of shared buffers required is a complex calculation of total RAM, database
size, number of connections, and query complexity. Thus it's better to go
with some rules of thumb in allocating, and monitor the server (particuarly
pg_statio views) to determine adjustments.
On dedicated servers, useful values seem to be between between 8MB and 400MB
(between 1000 and 50,000 for 8K page size). Factors which raise the desired
shared buffers are larger active portions of the database, large complex
queries, large numbers of simultaneous queries, long-running procedures or
transactions, more available RAM, and faster/more CPUs. And, of course,
other applications on the machine. Contrary to some expectations, allocating
much too much shared_buffers can actually lower peformance, due time
required for scanning. Here's some examples based on anecdotes and TPC tests
on Linux machines:

* Laptop, Celeron processor, 384MB RAM, 25MB database: 12MB/1500
* Athlon server, 1GB RAM, 10GB decision-support database: 120MB/15000
* Quad PIII server, 4GB RAM, 40GB, 150-connection heavy transaction
processing database: 240MB/30000
* Quad Xeon server, 8GB RAM, 200GB, 300-connection heavy transaction
processing database: 400MB/50000

Please note that increasing shared_buffers, and a few other memory
parameters, will require you to modify your operating system's System V
memory parameters. See the main PostgreSQL documentation for instructions on
this.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 4/2/07, Sorin N. Ciolofan <ciolofan(at)ics(dot)forth(dot)gr> wrote:
>
> Thanks,
>
>
>
>
>
> I've a value of 1000 set for shared_buffers, does this means
> that I use 8kbX1000=8Mb of Shared Mem?
>
>
>
> The definition from the manual is quite confusing:
>
>
>
> shared_buffers (integer)
>
> Sets the amount of memory the database server uses for shared memory
> buffers. The default is typically 32 megabytes (32MB), but may be less if
> your kernel settings will not support it (as determined during initdb).
> This setting must be at least 128 kilobytes and at least 16 kilobytes times
> max_connections<http://www.postgresql.org/docs/current/static/runtime-config-connection.html#GUC-MAX-CONNECTIONS>.
>
>
>
>
> What does the integer number represent? Number of shared buffers? If yes,
> what size does each shared buffer have?
>
> "The default is typically 32 megabytes" suggests that this integer could
> also represent the number of megabytes?!?
>
> In the postgresql.conf file is an ambiguous comment that could induce the
> idea that each shared buffer has 8 kb.
>
> So, which is the meaning of this integer?
>
>
>
> Thanks.
>
> S.
>
>
> ------------------------------
>
> *From:* Shoaib Mir [mailto:shoaibmir(at)gmail(dot)com]
> *Sent:* Monday, April 02, 2007 1:01 PM
> *To:* Sorin N. Ciolofan
> *Cc:* pgsql-general(at)postgresql(dot)org; pgsql-admin(at)postgresql(dot)org
> *Subject:* Re: [ADMIN] Increasing the shared memory
>
>
>
> I guess shared_buffers (in postgresql.conf file) will help you here if you
> have properly setup your kernel.SHMMAX value.
>
> --
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com )
>
> On 4/2/07, *Sorin N. Ciolofan* <ciolofan(at)ics(dot)forth(dot)gr> wrote:
>
>
>
> Hello!
>
>
>
> I'd like to ask you if there is any Postgre configuration parameter (like
> the ones defined in postgresql.conf file) that could be used for
> increasing the shared memory for Postgre?
>
>
>
> Thank you very much
>
> With best regards,
>
> Sorin
>
>
>