Thread: PostgreSQL Performance Tuning / Postgresql.conf and on OS Level

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

[root@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
[root@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 you Rocks!!!
It was really very helpful...Happy weekend!!!

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

Shams Khan wrote:

> *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.

> 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.  :-)

> 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