I have Quadcore server with 8GB RAM
vendor_id : GenuineIntel
cpu family : 6
model : 44
model name : Intel(R) Xeon(R) CPU E5607 @ 2.27GHz
stepping : 2
cpu MHz : 1197.000
cache size : 8192 KB
MemTotal: 8148636 kB
MemFree: 4989116 kB
Buffers: 8464 kB
Cached: 2565456 kB
SwapCached: 81196 kB
Active: 2003796 kB
Inactive: 843896 kB
Active(anon): 1826176 kB
Inactive(anon): 405964 kB
Active(file): 177620 kB
Inactive(file): 437932 kB
Unevictable: 0 kB
Mlocked: 0 kB
SwapTotal: 16779260 kB
SwapFree: 16303356 kB
Dirty: 1400 kB
Writeback: 0 kB
AnonPages: 208260 kB
Mapped: 1092008 kB
Shmem: 1958368 kB
Slab: 224964 kB
SReclaimable: 60136 kB
SUnreclaim: 164828 kB
KernelStack: 2864 kB
PageTables: 35684 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 20853576 kB
Committed_AS: 3672176 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 303292 kB
VmallocChunk: 34359429308 kB
HardwareCorrupted: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
DirectMap4k: 6144 kB
DirectMap2M: 2082816 kB
DirectMap1G: 6291456 kB
My database size is
pg_size_pretty
----------------
21 GB
i have one table which has data more than 160500460 rows almost.......and i have partioned with yearwise in different schemas
stk_source
Table "_100410.stk_source"
Column | Type | Modifiers | Storage | Description
-----------------------+-----------+-----------------------------------------------------+----------+-------------
source_id | integer | not null default nextval('source_id_seq'::regclass) | plain |
stock_id | integer | | plain |
source_detail | integer[] | | extended |
transaction_reference | integer | | plain |
is_user_set | boolean | default false | plain |
Triggers:
insert_stk_source_trigger BEFORE INSERT ON stk_source FOR EACH ROW EXECUTE PROCEDURE stk_source_insert_trigger()
Child tables: _100410_200809.stk_source,
_100410_200910.stk_source,
_100410_201011.stk_source,
_100410_201112.stk_source
Has OIDs: yes
Also have indexes
ss_source_id_pk" PRIMARY KEY, btree (source_id)
"stk_source_stock_id_idx" btree (stock_id)
First two years data is very less so no issues
and next two years table size is 2GB & 10 GB respectively.
EXPLAIN select * from stk_source ;
QUERY PLAN
-------------------------------------------------------------------------------------
Result (cost=0.00..6575755.39 rows=163132513 width=42)
-> Append (cost=0.00..6575755.39 rows=163132513 width=42)
-> Seq Scan on stk_source (cost=0.00..42.40 rows=1080 width=45)
-> Seq Scan on stk_source (cost=0.00..20928.37 rows=519179 width=42)
-> Seq Scan on stk_source (cost=0.00..85125.82 rows=2111794 width=42)
-> Seq Scan on stk_source (cost=0.00..6469658.80 rows=160500460 width=42)
because of this table my total database performance got affected i want
to optimize the settings by reading the below blogs i have changed some
configurations but no use still sytem is slow
http://comments.gmane.org/gmane.comp.db.postgresql.performance/29561
Actually we are using one PHP application in that we have used Postgresql 9.0.3 database.The server is accessing 40 -50 users
daily....so want to have more performance....my config details are
below....
Could any one help how to tune the settings for better performance???
Thanks in advance..........
# - Memory -
shared_buffers = 2GB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
max_prepared_transactions = 0 # zero disables the feature
# (change requires restart)
# Note: Increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
work_mem = 48MB # min 64kB
maintenance_work_mem = 256MB # min 1MB
max_stack_depth = 6MB # min 100kB
# - Planner Cost Constants -
seq_page_cost = 1.0 # measured on an arbitrary scale
random_page_cost = 3.0 # same scale as above
cpu_tuple_cost = 0.03 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
effective_cache_size = 4GB
------------------------------------------------------------------------
free -t -m
total used free shared buffers cached
Mem: 7957 3111 4845 0 10 2670
-/+ buffers/cache: 430 7527
Swap: 16385 458 15927
Total: 24343 3570 20773
ipcs -l
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 18014398509481983
max total shared memory (kbytes) = 4611686018427386880
min seg size (bytes) = 1
------ Semaphore Limits --------
max number of arrays = 1024
max semaphores per array = 250
max semaphores system wide = 256000
max ops per semop call = 32
semaphore max value = 32767
------ Messages Limits --------
max queues system wide = 3977
max size of message (bytes) = 65536
default max size of queue (bytes) = 65536
--
RegardsMohamed Hashim.N
Mobile:09894587678
