[PERFORM] Update on high concurrency OLTP application and Postgres 8 tuning
Hi all, I was searching tips to speed up/reduce load on a Pg8 app. Thank you for all your suggestions on the matter. Thread is archived here: http://www.mail-archive.com/pgsql-performance@postgresql.org/msg18342.html After intensive application profiling and database workload analysis, I managed to reduce CPU load with application-level changes. For database overload in presence of many concurrent transactions, I found that just doing an ANALYZE on sensible relations makes the situation better. I scheduled a cron job every hour or so that runs an analyze on the 4/5 most intensive relations and sleeps 30 seconds between every analyze. This has optimized db response times when many clients run together. I wanted to report this, maybe it can be helpful for others out there... :-) -- Cosimo ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Update on high concurrency OLTP application and Postgres 8 tuning
On Wed, Sep 20, 2006 at 11:09:23AM +0200, Cosimo Streppone wrote: I scheduled a cron job every hour or so that runs an analyze on the 4/5 most intensive relations and sleeps 30 seconds between every analyze. This has optimized db response times when many clients run together. I wanted to report this, maybe it can be helpful for others out there... :-) This suggests to me that your statistics need a lot of updating. You _might_ find that setting the statistics to a higher number on some columns of some of your tables will allow you to analyse less frequently. That's a good thing just because ANALYSE will impose an I/O load. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match