[PHP-DB] Performance Problem with postgresql 9.03, 8GB RAM,Quadcore Processor Server--Need help!!!!!!!
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 dailyso want to have more performancemy 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 sc
Re: [PHP-DB] Simple MySQL sample code runs out of memory
On Thu, Oct 27, 2011 at 10:04 PM, wrote: > Something more fundamental is going on. > > Here is simpler code which still reproduces the problem. > I get: > > PHP Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to > allocate 20 bytes) in xx3.php on line 26 > > What's up with that? > Hmm, okay, I haven't worked with such huge datasets in MySQL and PHP before, so now I'm speculating here. If the dataset is so huge that it's eating up all your memory, perhaps you need to cycle through the data base using limits, and process it in chunks, and release each dataset as you're done processing it. I know I've had to do that with Oracle databases before that were storing 100's of millions of records. It's rather like pagination for displaying data, only in this case you can have much much higher chunks than a displayed page. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php