Обсуждение: tunning strategy needed
Hello, I have an old application that was written on Postgres 8.1. There are a few hundreds tables, 30-40 columns per table, hundreds of views, and all the sql is inside java code. We are moving it to 8.4, it seems to be VERY slow. There are 20-30 tables transactions - the objects are spread acrross multiple tables and some tables have data from different objects. I need a short term tuning strategy minimizing rewrite & redesign. Should I start with replacing the sql with procedures? Should I start with replacing the views with the procedures to save time on recreating an execution plan and parsing? Should I start with tuning server parameters ? all your suggestions are greatly appreciated! thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/tunning-strategy-needed-tp4710245p4710245.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
On 18/08/2011 6:40 AM, hyelluas wrote: > Hello, > > I have an old application that was written on Postgres 8.1. > There are a few hundreds tables, 30-40 columns per table, hundreds of > views, and all the sql is inside java code. > > We are moving it to 8.4, it seems to be VERY slow. > There are 20-30 tables transactions - the objects are spread acrross > multiple tables and some tables have data from different objects. > > I need a short term tuning strategy minimizing rewrite& redesign. > > - Turn on auto explain and slow query logging - Examine the slow queries and plans. Run them manually with EXPLAIN ANALYZE. Check that the statistics make sense and if they're inaccurate, increase the statistics targets on those columns/tables then re-ANALYZE. - If the stats are accurate but the query is still slow, try playing with the cost parameters and see if you get a better result, then test those settings server-wide to see if they improve overall performance. -- Craig Ringer
On 18 Srpen 2011, 0:40, hyelluas wrote: > Should I start with replacing the sql with procedures? > > Should I start with replacing the views with the procedures to save time > on > recreating an execution plan and parsing? > > Should I start with tuning server parameters ? Yes, you should start by tuning the server as a whole. Did you just install the DB and restored your database? Have you tuned the config? Tell us what are the basic performance-related parameters, i.e. shared_buffers effective_cache_size checkpoint_segments checkpoint_completion_target work_mem maintainance_work_mem seq_page_cost random_page_cost and more information about the hardware and setup too (RAM, database size). There's a quite nice guide regarding general tuning: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server Tomas
thank you, it is a great article. the current on 8.1 checkpoint_segments = 3. it looks too low for me , but I'm not tuning the 8.1 schema. I'm looking for a generic approach of improving that beast while moving it to 8.4 I'm trying to understand the internals for Views vs. Functions there was none on 8.1 and I'm using pgpsql for 8.4. Does it make sence to put view's sql into a function? would it save on re-creating the execution plan and parsing? thank you. Helen -- View this message in context: http://postgresql.1045698.n5.nabble.com/tunning-strategy-needed-tp4710245p4717048.html Sent from the PostgreSQL - performance mailing list archive at Nabble.com.