PostgreSQL Performance Tuning / Postgresql.conf and on OS Level

[Shams Khan]

Hi all experts,

Please share your knowledge in the forum with your expert suggestions.

I want to optimize my current postgreSQL database 9.2 version

What should be the optimal size of each parameter: in postgresql.conf file

default_statistics_target = 100
maintenance_work_mem = Not initialised
checkpoint_completion_target = Not initialised
effective_cache_size = Not initialised
work_mem = Not initialised
wal_buffers = 8MB
checkpoint_segments = 16
shared_buffers = 32MB (have read should 20% of Physical memory)
max_connections = 100

*Need to increase the response time of running queries on server...*

1.What should be the optimal size of each parameter?
2.Is there any other mandatory parameter for memory tuning which I am
forgetting to add? Please suggest.
3.Please add more parameters if required.

*OS CentOS release 6.3 (Final)*
Kernal Version:
Linux db.win-dsl.com 2.6.32-279.11.1.el6.x86_64 #1 SMP Tue Oct 16 15:57:10
UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

CPU Model name : Dual-Core AMD Opteron(tm) Processor 8222 SE
with 8 CPU's and 16 cores

[***@db ~]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
CPU(s): 8
CPU MHz: 2992.143
Virtualization: AMD-V
L1d cache: 64K
L1i cache: 64K
L2 cache: 1024K
NUMA node0 CPU(s): 0,4
NUMA node1 CPU(s): 1,5
NUMA node2 CPU(s): 2,6
NUMA node3 CPU(s): 3,7

HDD 200GB
Database size = 40GB

*MEMORY SIZE*
[***@db ~]# free -m
total used free shared buffers cached
Mem: 64489 25859 38629 0 161 24312
-/+ buffers/cache: 1386 63103
Swap: 66671 0 66671


# Controls the default maxmimum size of a mesage queue
kernel.msgmnb = 65536

# Controls the maximum size of a message, in bytes
kernel.msgmax = 65536

# Controls the maximum shared segment size, in bytes
kernel.shmmax = 68719476736

# Controls the maximum number of shared memory segments, in pages
kernel.shmall = 4294967296


Thanks in advance!!!

[Kevin Grittner]

Post by Shams Khan
*Need to increase the response time of running queries on
server...*
8 CPU's and 16 cores
[64GB RAM]
HDD 200GB
Database size = 40GB

Without more info, there's a bit of guesswork, but...

Post by Shams Khan
maintenance_work_mem = Not initialised

I would say probably 1GB

Post by Shams Khan
effective_cache_size = Not initialised

48GB

Post by Shams Khan
work_mem = Not initialised

You could probably go 100MB on this.

Post by Shams Khan
wal_buffers = 8MB

16BM

Post by Shams Khan
checkpoint_segments = 16

Higher. Probably not more than 128.

Post by Shams Khan
shared_buffers = 32MB (have read should 20% of Physical memory)

16GB to start. If you have episodes of high latency, where even
queries which normally run very quickly all pause and then all
complete close together after a delay, you may need to reduce this
and/or increase the aggressiveness of the background writer. I've
had to go as low as 1GB to overcome such latency spikes.

Post by Shams Khan
max_connections = 100

Maybe leave alone, possibly reduce. You should be aiming to use a
pool to keep about 20 database connections busy. If you can't do
that in the app, look at pgbouncer.

Post by Shams Khan
 checkpoint_completion_target = Not initialised

It is often wise to increase this to 0.8 or 0.9

If I read this right, you have one 200GB drive for writes? That's
going to be your bottleneck if you write much data. You need a RAID
for both performance and reliability, with a good controller with
battery-backed cache configured for write-back. Until you have one
you can be less crippled on preformance by setting
synchronous_commit = off. The trade-off is that there will be a
slight delay between when PostgreSQL acknoleges a commit and when
the data is actually persisted.

-Kevin

[Shams Khan]

Hey Kevin,

Thanks for such great help :
I analyzed on query before changing parameters;

explain select count(distinct a.subsno ) from subsexpired a where
a.subsno not in (select b.subsno from subs b where b.subsno>75043 and
b.subsno<=112565) and a.subsno>75043 and a.subsno<=112565;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Aggregate (cost=99866998.67..99866998.68 rows=1 width=4)
-> Index Only Scan using ind_sub_new on subsexpired a
(cost=0.00..99866908.74 rows=35969 width=4)
Index Cond: ((subsno > 75043) AND (subsno <= 112565))
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.00..2681.38 rows=37977 width=4)
-> Index Only Scan using subs_pkey on subs b
(cost=0.00..2342.49 rows=37977 width=4)
Index Cond: ((subsno > 75043) AND (subsno <= 112565))


*AFTER APPLYING YOUR SUGGESTED SETTINGS:*

explain select count(distinct a.subsno ) from subsexpired a where a.subsno
not in (select b.subsno from subs b where b.subsno>75043 and
b.subsno<=112565) and a.subsno>75043 and a.subsno<=112565;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Aggregate (cost=7990.70..7990.71 rows=1 width=4)
-> Index Only Scan using ind_sub_new on subsexpired a
(cost=2437.43..7900.78 rows=35969 width=4)
Index Cond: ((subsno > 75043) AND (subsno <= 112565))
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Index Only Scan using subs_pkey on subs b
(cost=0.00..2342.49 rows=37977 width=4)
Index Cond: ((subsno > 75043) AND (subsno <= 112565))

*PERFORMANCE WAS BOOSTED UP DRASTICALLY* ---when I edited the work_mem to
100 MB---just look at the difference;

One more thing Kevin, could you please help me out to understand how did
calculate those parameters?

Without more info, there's a bit of guesswork, but...
What exta info is required...please let me know...

Thanks again...

Post by Kevin Grittner

Post by Shams Khan
*Need to increase the response time of running queries on
server...*
8 CPU's and 16 cores
[64GB RAM]
HDD 200GB
Database size = 40GB

Without more info, there's a bit of guesswork, but...

Post by Shams Khan
maintenance_work_mem = Not initialised

I would say probably 1GB

Post by Shams Khan
effective_cache_size = Not initialised

48GB

Post by Shams Khan
work_mem = Not initialised

You could probably go 100MB on this.

Post by Shams Khan
wal_buffers = 8MB

16BM

Post by Shams Khan
checkpoint_segments = 16

Higher. Probably not more than 128.

Post by Shams Khan
shared_buffers = 32MB (have read should 20% of Physical memory)

16GB to start. If you have episodes of high latency, where even
queries which normally run very quickly all pause and then all
complete close together after a delay, you may need to reduce this
and/or increase the aggressiveness of the background writer. I've
had to go as low as 1GB to overcome such latency spikes.

Post by Shams Khan
max_connections = 100

Maybe leave alone, possibly reduce. You should be aiming to use a
pool to keep about 20 database connections busy. If you can't do
that in the app, look at pgbouncer.

Post by Shams Khan
checkpoint_completion_target = Not initialised

It is often wise to increase this to 0.8 or 0.9
If I read this right, you have one 200GB drive for writes? That's
going to be your bottleneck if you write much data. You need a RAID
for both performance and reliability, with a good controller with
battery-backed cache configured for write-back. Until you have one
you can be less crippled on preformance by setting
synchronous_commit = off. The trade-off is that there will be a
slight delay between when PostgreSQL acknoleges a commit and when
the data is actually persisted.
-Kevin

[Gabriel Muñoz]

Maybe

explain analyze select count(distinct a.subsno ) from subsexpired a where
a.subsno not in (select b.subsno from subs b where b.subsno>75043 and
b.subsno<=112565) and a.subsno>75043 and a.subsno<=112565;

Give you more information about real excecuting time.


About postgres.conf

checkpoint_segments = 64


Gabriel.

Post by Shams Khan
explain select count(distinct a.subsno ) from subsexpired a where
a.subsno not in (select b.subsno from subs b where b.subsno>75043 and
b.subsno<=112565) and a.subsno>75043 and a.subsno<=112565;

[Shams Khan]

Can somebody help me this???

Post by Shams Khan
Hey Kevin,
I analyzed on query before changing parameters;
explain select count(distinct a.subsno ) from subsexpired a where
a.subsno not in (select b.subsno from subs b where b.subsno>75043 and
b.subsno<=112565) and a.subsno>75043 and a.subsno<=112565;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Aggregate (cost=99866998.67..99866998.68 rows=1 width=4)
-> Index Only Scan using ind_sub_new on subsexpired a
(cost=0.00..99866908.74 rows=35969 width=4)
Index Cond: ((subsno > 75043) AND (subsno <= 112565))
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.00..2681.38 rows=37977 width=4)
-> Index Only Scan using subs_pkey on subs b
(cost=0.00..2342.49 rows=37977 width=4)
Index Cond: ((subsno > 75043) AND (subsno <= 112565))
*AFTER APPLYING YOUR SUGGESTED SETTINGS:*
explain select count(distinct a.subsno ) from subsexpired a where
a.subsno not in (select b.subsno from subs b where b.subsno>75043 and
b.subsno<=112565) and a.subsno>75043 and a.subsno<=112565;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Aggregate (cost=7990.70..7990.71 rows=1 width=4)
-> Index Only Scan using ind_sub_new on subsexpired a
(cost=2437.43..7900.78 rows=35969 width=4)
Index Cond: ((subsno > 75043) AND (subsno <= 112565))
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Index Only Scan using subs_pkey on subs b
(cost=0.00..2342.49 rows=37977 width=4)
Index Cond: ((subsno > 75043) AND (subsno <= 112565))
*PERFORMANCE WAS BOOSTED UP DRASTICALLY* ---when I edited the work_mem to
100 MB---just look at the difference;
One more thing Kevin, could you please help me out to understand how did
calculate those parameters?
Without more info, there's a bit of guesswork, but...
What exta info is required...please let me know...
Thanks again...

Post by Kevin Grittner

Post by Shams Khan
*Need to increase the response time of running queries on
server...*
8 CPU's and 16 cores
[64GB RAM]
HDD 200GB
Database size = 40GB

Without more info, there's a bit of guesswork, but...

Post by Shams Khan
maintenance_work_mem = Not initialised

I would say probably 1GB

Post by Shams Khan
effective_cache_size = Not initialised

48GB

Post by Shams Khan
work_mem = Not initialised

You could probably go 100MB on this.

Post by Shams Khan
wal_buffers = 8MB

16BM

Post by Shams Khan
checkpoint_segments = 16

Higher. Probably not more than 128.

Post by Shams Khan
shared_buffers = 32MB (have read should 20% of Physical memory)

16GB to start. If you have episodes of high latency, where even
queries which normally run very quickly all pause and then all
complete close together after a delay, you may need to reduce this
and/or increase the aggressiveness of the background writer. I've
had to go as low as 1GB to overcome such latency spikes.

Post by Shams Khan
max_connections = 100

Maybe leave alone, possibly reduce. You should be aiming to use a
pool to keep about 20 database connections busy. If you can't do
that in the app, look at pgbouncer.

Post by Shams Khan
checkpoint_completion_target = Not initialised

It is often wise to increase this to 0.8 or 0.9
If I read this right, you have one 200GB drive for writes? That's
going to be your bottleneck if you write much data. You need a RAID
for both performance and reliability, with a good controller with
battery-backed cache configured for write-back. Until you have one
you can be less crippled on preformance by setting
synchronous_commit = off. The trade-off is that there will be a
slight delay between when PostgreSQL acknoleges a commit and when
the data is actually persisted.
-Kevin

[Kevin Grittner]

Post by Shams Khan
*PERFORMANCE WAS BOOSTED UP DRASTICALLY* ---when I edited the
work_mem to 100 MB---just look at the difference;

You only showed EXPLAIN output, which only shows estimated costs.
As already suggested, try running both ways with EXPLAIN ANALYZE --
which will show both estimates and actual.

Post by Shams Khan
One more thing Kevin, could you please help me out to understand
how did calculate those parameters?

My own experience and reading about the experiences of others. If
you follow the pgsql-performance list, you will get a better "gut
feel" on these issues as well as picking up techniques for problem
solving. Speaking of which, that would have been a better list to
post this on. The one actual calculation I did was to make sure
work_mem was less than RAM * 0.25 / max_connections. I didn't go
all the way to that number because 100MB is enough for most
purposes and your database isn't very much smaller than your RAM.
You know, the melding of a routine calculation with gut feel.  :-)

Post by Shams Khan
Without more info, there's a bit of guesswork, but...
What exta info is required...please let me know...

The main things I felt I was missing was a description of your
overall workload and EXPLAIN ANALYZE output from a "typical" slow
query.

There's a page about useful information to post, though:

http://wiki.postgresql.org/wiki/SlowQueryQuestions

Now that you have somewhat reasonable tuning for the overall
server, you can look at the EXPLAIN ANALYZE output of queries which
don't run as fast as you thing they should be able to do, and  see
what adjustments to cost factors you might need to make. With the
numbers you previously gave, a wild guess would be that you'll get
generally faster run-times with these settings:

seq_page_cost = 0.1
random_page_cost = 0.1
cpu_tuple_cost = 0.5

Be sure to look at actual run times, not EXPLAIN cost estimates.

-Kevin

[Shams Khan]

Hi Kevin,

I got one more question, please help me out.

Question 1. How do we correlate our memory with kernel parameters, I mean
to say is there any connection between shared_buffer and kernel SHMMAX. For
example if I define my shared buffer more than my current SHMMAX value, it
would not allow me to use that ??or vice versa. Please throw some light.

Questions 2. I want to show the last result of last query before and after
changing the parameters, I found performance was degraded.

USED EXPLAIN ANALYZE

radius=# explain analyze select * from subsexpired where subsno between
5911 and 50911 and subsno not in (select subsno from subs where subsno
between 5911 and 50911);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ind_sub_new on subsexpired (cost=1943.39..6943.84
rows=30743 width=69) (actual time=124.628..142.203 rows=430 loops=1)
Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 62079
SubPlan 1
-> Index Only Scan using subs_pkey on subs (cost=0.00..1876.77
rows=26647 width=4) (actual time=0.030..44.743 rows=27397 loops=1)
Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
Heap Fetches: 27397
Total runtime: 142.812 ms
----------------------------------------------------------------------------------------------------------------------

After: using the parameters as suggested.

radius=# explain analyze select * from subsexpired where subsno between
5911 and 50911 and subsno not in (select subsno from subs where subsno
between 5911 and 50911);
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ind_sub_new on subsexpired (cost=1943.39..6943.84
rows=30743 width=69) (actual time=128.351..144.532 rows=430 loops=1)
Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
Filter: (NOT (hashed SubPlan 1))
Rows Removed by Filter: 62079
SubPlan 1
-> Index Only Scan using subs_pkey on subs (cost=0.00..1876.77
rows=26647 width=4) (actual time=0.030..47.848 rows=27397 loops=1)
Index Cond: ((subsno >= 5911) AND (subsno <= 50911))
Heap Fetches: 27397
Total runtime: 145.127 ms
(9 rows)


Thanks

Post by Kevin Grittner

Post by Shams Khan
*PERFORMANCE WAS BOOSTED UP DRASTICALLY* ---when I edited the
work_mem to 100 MB---just look at the difference;

You only showed EXPLAIN output, which only shows estimated costs.
As already suggested, try running both ways with EXPLAIN ANALYZE --
which will show both estimates and actual.

Post by Shams Khan
One more thing Kevin, could you please help me out to understand
how did calculate those parameters?

My own experience and reading about the experiences of others. If
you follow the pgsql-performance list, you will get a better "gut
feel" on these issues as well as picking up techniques for problem
solving. Speaking of which, that would have been a better list to
post this on. The one actual calculation I did was to make sure
work_mem was less than RAM * 0.25 / max_connections. I didn't go
all the way to that number because 100MB is enough for most
purposes and your database isn't very much smaller than your RAM.
You know, the melding of a routine calculation with gut feel. :-)

Post by Shams Khan
Without more info, there's a bit of guesswork, but...
What exta info is required...please let me know...

The main things I felt I was missing was a description of your
overall workload and EXPLAIN ANALYZE output from a "typical" slow
query.
http://wiki.postgresql.org/wiki/SlowQueryQuestions
Now that you have somewhat reasonable tuning for the overall
server, you can look at the EXPLAIN ANALYZE output of queries which
don't run as fast as you thing they should be able to do, and see
what adjustments to cost factors you might need to make. With the
numbers you previously gave, a wild guess would be that you'll get
seq_page_cost = 0.1
random_page_cost = 0.1
cpu_tuple_cost = 0.5
Be sure to look at actual run times, not EXPLAIN cost estimates.
-Kevin

[s***@gmail.com]

Kevin you Rocks!!!
It was really very helpful...Happy weekend!!!

------Original Message------
From: Kevin Grittner
To: Shams Khan
To: pgsql-***@postgresql.org
Subject: Re: [ADMIN] PostgreSQL Performance Tuning / Postgresql.conf and on OS Level
Sent: Dec 15, 2012 01:50

Post by Shams Khan
*PERFORMANCE WAS BOOSTED UP DRASTICALLY* ---when I edited the
work_mem to 100 MB---just look at the difference;

You only showed EXPLAIN output, which only shows estimated costs.
As already suggested, try running both ways with EXPLAIN ANALYZE --
which will show both estimates and actual.

Post by Shams Khan
One more thing Kevin, could you please help me out to understand
how did calculate those parameters?

My own experience and reading about the experiences of others. If
you follow the pgsql-performance list, you will get a better "gut
feel" on these issues as well as picking up techniques for problem
solving. Speaking of which, that would have been a better list to
post this on. The one actual calculation I did was to make sure
work_mem was less than RAM * 0.25 / max_connections. I didn't go
all the way to that number because 100MB is enough for most
purposes and your database isn't very much smaller than your RAM.
You know, the melding of a routine calculation with gut feel.  :-)

Post by Shams Khan
Without more info, there's a bit of guesswork, but...
What exta info is required...please let me know...

The main things I felt I was missing was a description of your
overall workload and EXPLAIN ANALYZE output from a "typical" slow
query.

There's a page about useful information to post, though:

http://wiki.postgresql.org/wiki/SlowQueryQuestions

Now that you have somewhat reasonable tuning for the overall
server, you can look at the EXPLAIN ANALYZE output of queries which
don't run as fast as you thing they should be able to do, and  see
what adjustments to cost factors you might need to make. With the
numbers you previously gave, a wild guess would be that you'll get
generally faster run-times with these settings:

seq_page_cost = 0.1
random_page_cost = 0.1
cpu_tuple_cost = 0.5

Be sure to look at actual run times, not EXPLAIN cost estimates.

-Kevin

Sent on my BlackBerry® from Vodafone

[Kevin Grittner]

Post by Shams Khan
Question 1. How do we correlate our memory with kernel parameters, I mean
to say is there any connection between shared_buffer and kernel SHMMAX. For
example if I define my shared buffer more than my current SHMMAX value, it
would not allow me to use that ??or vice versa. Please throw some light.

If SHMMAX is not large enough to allow the PostgreSQL service to
acquire the amount of shared memory it needs based on your
configuration settings, the PostgreSQL server will log an error and
fail to start. Please see the docs for more information:

http://www.postgresql.org/docs/current/static/kernel-resources.html

Post by Shams Khan
Questions 2. I want to show the last result of last query before and after
changing the parameters, I found performance was degraded.
 Total runtime: 142.812 ms
 Total runtime: 145.127 ms

The plan didn't change and the times were different by less than
2%. There can easily be that much variation from one run to the
next. If you try the same query many times (say, 10 or more) with
each configuration and it is consistently faster with one than the
other, then you will have pretty good evidence which configuration
is better for that particular query. If the same configuration wins
in general, use it.

Since performance differences which are that small are often caused
by very obscure issues, it can be very difficult to pin down the
reason. It's generally not anything to fret over.

-Kevin

[Shams Khan]

Hi Kevin,

When I check Idle session running question, shows the many queries running
but end of the query it shows Rollback and commit which take lot of time. I
am little scared bcoz I made changes in memory parameter first time in
postgres and getting this result, earlier I have not seen this. Is that
fine? Which parameter impact on this? please help...

select now()-query_start as runtime,client_addr,pid,query from
pg_stat_activity where not query like '%IDLE%' order by 1;

00:00:51.314855 | 95.129.0.28 | 26052 | COMMIT
00:01:23.655743 | 95.129.0.28 | 26118 | COMMIT
00:00:16.707913 | 95.129.0.28 | 26567 | COMMIT
00:00:17.084691 | 95.129.0.28 | 26565 | COMMIT
00:00:20.118008 | 95.129.0.28 | 26378 | COMMIT
00:00:31.952375 | 95.129.0.28 | 26514 | COMMIT

Post by Kevin Grittner

Post by Shams Khan
Question 1. How do we correlate our memory with kernel parameters, I mean
to say is there any connection between shared_buffer and kernel SHMMAX.

For

Post by Shams Khan
example if I define my shared buffer more than my current SHMMAX value,

it

Post by Shams Khan
would not allow me to use that ??or vice versa. Please throw some light.

If SHMMAX is not large enough to allow the PostgreSQL service to
acquire the amount of shared memory it needs based on your
configuration settings, the PostgreSQL server will log an error and
http://www.postgresql.org/docs/current/static/kernel-resources.html

Post by Shams Khan
Questions 2. I want to show the last result of last query before and

after

Post by Shams Khan
changing the parameters, I found performance was degraded.
Total runtime: 142.812 ms
Total runtime: 145.127 ms

The plan didn't change and the times were different by less than
2%. There can easily be that much variation from one run to the
next. If you try the same query many times (say, 10 or more) with
each configuration and it is consistently faster with one than the
other, then you will have pretty good evidence which configuration
is better for that particular query. If the same configuration wins
in general, use it.
Since performance differences which are that small are often caused
by very obscure issues, it can be very difficult to pin down the
reason. It's generally not anything to fret over.
-Kevin

[Kevin Grittner]

Post by Shams Khan
select now()-query_start as runtime,client_addr,pid,query from
pg_stat_activity where not query like '%IDLE%' order by 1;
When I check Idle session running question, shows the many queries running
but end of the query it shows Rollback and commit which take lot of time.

No, you need to adjust that query. Add the state column and maybe
the xact_start column to your output, and it should then be obvious
how to modify your where clause. People felt it would be useful to
see what the last statement was which had been run on a connection
which was idle or (especially) idle in transaction. The query
column no longer shows anything other than a query.

-Kevin