7

We're on CentOS release 6.6, PostgreSQL version 8.4.20. (Yes, this is not bleeding edge.)

In postgresql.conf, we have:

shared_buffers = 4096MB

kernel shm values are set nice and high:

[root@green data]# sysctl -a | grep shm
kernel.shmmax = 15922077696
kernel.shmall = 3887226
kernel.shmmni = 4096
kernel.shm_rmid_forced = 0

We have plenty of memory:

[root@green data]# free
             total       used       free     shared    buffers     cached
Mem:      31097812   30474972     622840    2873672    1961088   20565360
-/+ buffers/cache:    7948524   23149288
Swap:      1959920      93852    1866068

Yet the value for shared_buffers reported by pg_settings is only 512MB, not the 4GB set in postgresql.conf:

postgres=# select name, setting, min_val, max_val, context from  
pg_settings where name='shared_buffers';
      name      | setting | min_val |  max_val   |  context   
----------------+---------+---------+------------+------------
 shared_buffers | 524288  | 16      | 1073741823 | postmaster

Yes, we've done a full restart, and SHOW config_file confirms that I've edited the right postgresql.conf.

My very great thanks to anyone who can provide insight into this mystery.

10

The canonical unit for shared_buffers is pages of 8kB, so the actual memory allocated in bytes is:

524288 * 8192 = 4294967296 or 4096*1024*1024 as requested.

You can also check the size of the segment of memory with ipcs -m

  • qalc is a nice tool to deal with this conversions Command: qalc 524288*8 kibibytes to mebibytes, Return: 524288 * (8 * kibibyte) = 4096 mebibytes – Eduardo Baitello Jul 30 '18 at 15:05
5

What @Daniel explained so accurately becomes rather obvious when you add the respective column to your query:

SELECT name, setting, unit, min_val, max_val, context
FROM   pg_settings WHERE name = 'shared_buffers';

Or just:

SELECT * FROM pg_settings WHERE name = 'shared_buffers';

Consider the project guidelines for your "non-bleeding-edge" (a.k.a. outdated and unsupported) Postgres version.

0

got the same issue and when I ran the following query:

select * from pg_settings 

I got sourcefile pointing to postgresql.auto.conf, so removed the file as only shared_buffers value was set in the file and restarted

select *  from pg_settings where name='shared_buffers';

-[ RECORD 1 ]---+-------------------------------------------------------------
name            | shared_buffers
sourcefile      | /u02/pgsql/data/postgresql.conf

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.