patternsqlMinor
Postgres settings reported differently in pgsql
Viewed 0 times
differentlysettingspostgresreportedpgsql
Problem
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;
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?
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?
Solution
I see the same difference on my test database:
Then watching the numbers for a short while, I set up the following query:
So, to me it looks like that
Note that there is a third way to check settings, namely the
show shared_buffers;
shared_buffers
────────────────
768MB
select name, setting from pg_settings where name = 'shared_buffers';
name │ setting
────────────────┼─────────
shared_buffers │ 98304Then watching the numbers for a short while, I set up the following query:
SELECT 98304 * 8192 / (1024 * 1024);
?column?
──────────
768So, 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
-----------------
768MBCode Snippets
show shared_buffers;
shared_buffers
────────────────
768MB
select name, setting from pg_settings where name = 'shared_buffers';
name │ setting
────────────────┼─────────
shared_buffers │ 98304SELECT 98304 * 8192 / (1024 * 1024);
?column?
──────────
768SELECT current_setting('shared_buffer');
current_setting
-----------------
768MBContext
StackExchange Database Administrators Q#59635, answer score: 5
Revisions (0)
No revisions yet.