Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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


--
Regards
Mohamed Hashim.N
Mobile:09894587678

[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux