Обсуждение: Limited Shared Buffer Problem

Hi All,

I have a server running CentOS5 with 6gb of memory that will run postgres 8.3 exclusively.
I would like to allocate 4gb of the memory to shared buffers for postgres.
I have modified some kernel settings as follows:

shmall  1048576 pages  4,294,967,296 bytes
shmmax 4,294,967,295 bytes

I can set the postgres config to shared_buffers = 2700MB but no higher.
If I try shared_buffers = 2750MB the server fails to start with a message it cannot allocate memory:

2010-01-29 11:24:39 EST FATAL:  shmat(id=1638400) failed: Cannot allocate memory

Is there some other setting that could be limiting the amount I can allocate?

Excerpt from postgresql.conf:

# - Memory -

shared_buffers = 2750MB                 # min 128kB or max_connections*16kB
                                        # (change requires restart)
temp_buffers = 32MB                     # min 800kB
max_prepared_transactions = 10          # can be 0 or more
                                        # (change requires restart)
# Note:  Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
work_mem = 2MB                          # min 64kB
maintenance_work_mem = 32MB             # min 1MB
#max_stack_depth = 2MB                  # min 100kB


Any help appreciated, Thanks

Rod

**Rod MacNeil wrote:
> Hi All,
>
> I have a server running CentOS5 with 6gb of memory that will run
> postgres 8.3 exclusively.
> I would like to allocate 4gb of the memory to shared buffers for postgres.

It might be worth pausing at this point:

The various postgresql tuning guides usually suggest that on a dedicated
system, you should give postgres about 1/4 of the RAM for shared
buffers, while telling it that the effective_cache_size = 1/2 RAM.

Postgres will make good use of the OS cache as a file-cache - the
"effective_cache_size" setting is advisory to postgres that it can
expect about this much data to be in RAM.

Also, If you are setting up a new system, it's probably worth going for
8.4.2. Postgres is relatively easy to build from source.

HTH,

Richard

On Fri, Jan 29, 2010 at 9:37 AM, **Rod MacNeil
<rmacneil@interactdirect.com> wrote:
> Hi All,
>
> I have a server running CentOS5 with 6gb of memory that will run postgres
> 8.3 exclusively.
> I would like to allocate 4gb of the memory to shared buffers for postgres.
> I have modified some kernel settings as follows:
>
> shmall  1048576 pages  4,294,967,296 bytes
> shmmax 4,294,967,295 bytes
>
> I can set the postgres config to shared_buffers = 2700MB but no higher.
> If I try shared_buffers = 2750MB the server fails to start with a message it
> cannot allocate memory:

Are you running 32 or 64 bit Centos?

Also, that's a rather high setting for shared_buffers on a 6G machine.
 Generally 2G or so should be plenty unless you have actual data sets
that are larger than that.

Richard Neill escribió:
>
>
> **Rod MacNeil wrote:
>> Hi All,
>>
>> I have a server running CentOS5 with 6gb of memory that will run
>> postgres 8.3 exclusively.
>> I would like to allocate 4gb of the memory to shared buffers for
>> postgres.
>
> It might be worth pausing at this point:
>
> The various postgresql tuning guides usually suggest that on a
> dedicated system, you should give postgres about 1/4 of the RAM for
> shared buffers, while telling it that the effective_cache_size = 1/2 RAM.
>
> Postgres will make good use of the OS cache as a file-cache - the
> "effective_cache_size" setting is advisory to postgres that it can
> expect about this much data to be in RAM.
>
> Also, If you are setting up a new system, it's probably worth going
> for 8.4.2. Postgres is relatively easy to build from source.
>
> HTH,
>
> Richard
>
All these values has to be combined with the others: shared_buffers,
work_mem,etc.
My recommendation is to go down a little the shmmax and the
shared_buffers values.
Is very necessary that you have these values so high?

Regards


--
-------------------------------------
"TIP 4: No hagas 'kill -9' a postmaster"
Ing. Marcos Luís Ortíz Valmaseda
PostgreSQL System DBA && DWH -- BI Apprentice

Centro de Tecnologías de Almacenamiento y Análisis de Datos (CENTALAD)
Universidad de las Ciencias Informáticas

Linux User # 418229

-- PostgreSQL --

http://www.postgresql-es.org
http://www.postgresql.org
http://www.planetpostgresql.org

-- DWH + BI --

http://www.tdwi.org

-------------------------------------------

You are probably running 32bit OS. So the problem is that the OS
cannot allocate more than 3G of memory continuous . Then the only
solution is to migrate to a 64bit OS.

2010/1/29 **Rod MacNeil <rmacneil@interactdirect.com>:
> Hi All,
>
> I have a server running CentOS5 with 6gb of memory that will run postgres
> 8.3 exclusively.
> I would like to allocate 4gb of the memory to shared buffers for postgres.
> I have modified some kernel settings as follows:
>
> shmall  1048576 pages  4,294,967,296 bytes
> shmmax 4,294,967,295 bytes
>
> I can set the postgres config to shared_buffers = 2700MB but no higher.
> If I try shared_buffers = 2750MB the server fails to start with a message it
> cannot allocate memory:
>
> 2010-01-29 11:24:39 EST FATAL:  shmat(id=1638400) failed: Cannot allocate
> memory
>
> Is there some other setting that could be limiting the amount I can
> allocate?
>
> Excerpt from postgresql.conf:
>
> # - Memory -
>
> shared_buffers = 2750MB                 # min 128kB or max_connections*16kB
>                                         # (change requires restart)
> temp_buffers = 32MB                     # min 800kB
> max_prepared_transactions = 10          # can be 0 or more
>                                         # (change requires restart)
> # Note:  Increasing max_prepared_transactions costs ~600 bytes of shared
> memory
> # per transaction slot, plus lock space (see max_locks_per_transaction).
> work_mem = 2MB                          # min 64kB
> maintenance_work_mem = 32MB             # min 1MB
> #max_stack_depth = 2MB                  # min 100kB
>
>
> Any help appreciated, Thanks
>
> Rod
>
>

2010/1/29 Richard Neill <rn214@cam.ac.uk>:
>
>
> **Rod MacNeil wrote:
>>
>> Hi All,
>>
>> I have a server running CentOS5 with 6gb of memory that will run postgres
>> 8.3 exclusively.
>> I would like to allocate 4gb of the memory to shared buffers for postgres.
>
> It might be worth pausing at this point:
>
> The various postgresql tuning guides usually suggest that on a dedicated
> system, you should give postgres about 1/4 of the RAM for shared buffers,
> while telling it that the effective_cache_size = 1/2 RAM.
>
> Postgres will make good use of the OS cache as a file-cache - the
> "effective_cache_size" setting is advisory to postgres that it can expect
> about this much data to be in RAM.

AFAIK effective_cache_size is estimation of OS Page Cache + Estimated
Cache in shared_buffers.

>
> Also, If you are setting up a new system, it's probably worth going for
> 8.4.2. Postgres is relatively easy to build from source.
>
> HTH,
>
> Richard
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>



--
Cédric Villemain

Thanx, I will try out that recommendation.





--
Rod MacNeil
Senior Software Engineer
Interact Direct Marketing, Inc.
www.interactdirect.com
rmacneil@interactdirect.com
Primary Phone Mississauga Ontario: 905-278-4086
Alternate Phone London Ontario: 519-438-6245, Ext 183

Cédric Villemain wrote:
> AFAIK effective_cache_size is estimation of OS Page Cache + Estimated
> Cache in shared_buffers.
>

Yes, the total value you set is used as is, and should include both
pieces of memory.  The planner doesn't add the shared_buffers value to
the total first for you, as some people might guess it would.

The only thing effective_cache_size is used for is estimating how
expensive an index is likely to be to use, to make decisions like when
to do an index-based scan instead of just scanning the table itself.

--
Greg Smith    2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com  www.2ndQuadrant.com