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!!!