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