3

We are running PostgreSQL 9.2 on CentOS 6 and I am seeing the settings are reported differently when I run the show effective_cache_size; command vs select * from pg_settings where name = 'effective_cache_size'; query. As far as I can understand these commands should be identical. For example

show effective_cache_size;

effective_cache_size
----------------------
 2816MB
(1 row)

vs

select name,setting from pg_settings where name = 'effective_cache_size';

name    | effective_cache_size

setting | 360448

I am trying to figure out the value used by PostgreSQL. I get the same results if I run this as a superuser. Which one do I trust?

4
  • 1
    In your question you talk about shared buffers, yet your example shows effective_cache_size.
    – user1822
    Feb 25, 2014 at 17:40
  • Isn't "effective_cache_size" a parameter that can be set on cluster and on session level? Feb 25, 2014 at 17:50
  • @a_horse_with_no_name edited the question to match the example output, thanks.
    – sumit
    Feb 25, 2014 at 18:10
  • @colin-t-hart no setting has been done to this param besides the config file
    – sumit
    Feb 25, 2014 at 18:11

1 Answer 1

5

I see the same difference on my test database:

show shared_buffers;
 shared_buffers 
────────────────
 768MB


select name, setting from pg_settings where name = 'shared_buffers';
      name      │ setting 
────────────────┼─────────
 shared_buffers │ 98304

Then watching the numbers for a short while, I set up the following query:

SELECT 98304 * 8192 / (1024 * 1024);
 ?column? 
──────────
      768

So, to me it looks like that pg_settings reports these sizes in 8 kb pages (8192 refers to this fact).

Note that there is a third way to check settings, namely the current_setting() function:

SELECT current_setting('shared_buffer');
 current_setting
-----------------
 768MB
1
  • thanks @dezso for the explanation, same calculation applies for my sample as well
    – sumit
    Feb 25, 2014 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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