PostgreSQL Performance Tuning / Postgresql.conf and on OS Level

From: Shams Khan <shams(dot)khan22(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: PostgreSQL Performance Tuning / Postgresql.conf and on OS Level
Date: 2012-12-14 17:51:56
Message-ID: CAM42boqA4wW1Acw50bA54RPDNRLva8SxiMf-SAo0Sk5oftUBCg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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(at)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(at)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!!!

Browse pgsql-admin by date

  From Date Subject
Next Message Kevin Grittner 2012-12-14 18:50:25 Re: PostgreSQL Performance Tuning / Postgresql.conf and on OS Level
Previous Message Kris Jurka 2012-12-14 16:51:14 Re: Fwd: [ADMIN] Confuse about the behaveior of PreparedStatement.executeBatch (jdbc)