Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

1 Answer 1

up vote 2 down vote accepted

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
share|improve this answer
    
thanks @dezso for the explanation, same calculation applies for my sample as well –  sumit Feb 25 at 18:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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