Grokbase › Groups › PostgreSQL › pgsql-performance › August 2011
FAQ
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.

Search Discussions

  • Craig Ringer at Aug 18, 2011 at 5:46 am ⇧

    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
  • Tomas Vondra at Aug 18, 2011 at 8:52 am ⇧

    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
  • Hyelluas at Aug 19, 2011 at 9:15 pm ⇧
    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.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-performance @
categoriespostgresql
postedAug 17, '11 at 10:40p
activeAug 19, '11 at 9:15p
posts4
users3
websitepostgresql.org
irc#postgresql

3 users in discussion

Hyelluas: 2 posts Tomas Vondra: 1 post Craig Ringer: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase