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
e*ffective_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
--
Regards
Mohamed Hashim.N
Mobile:09894587678
