Skip site navigation (1) Skip section navigation (2)

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

From: Mohamed Hashim <nmdhashim(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org, pgsql-general(at)postgresql(dot)org, php-db(at)lists(dot)php(dot)net
Cc: nmdhashim <nmdhashim(at)gmail(dot)com>, "Karthi(dot)(dot)" <karthiisforu(at)gmail(dot)com>
Subject: Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
Date: 2011-10-28 07:02:03
Message-ID: CACBfhZPO1GJv4nVunPXGA2EZq923On-CBGtxyhtQYAaSMCuBEQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-performance
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

Responses

pgsql-performance by date

Next:From: Gregg JaskiewiczDate: 2011-10-28 07:58:26
Subject: Re: [PERFORM] Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
Previous:From: Amitabh KantDate: 2011-10-28 06:40:10
Subject: Re: Choosing between Intel 320, Intel 510 or OCZ Vertex 3 SSD for db server

pgsql-general by date

Next:From: Thomas KellererDate: 2011-10-28 07:29:49
Subject: "Named" column default expression
Previous:From: kucojDate: 2011-10-28 06:49:50
Subject: Re: Unable to write inside TEMP environment variable path

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group