Results 1 to 13 of 13

Thread: tuning postgres

  1. #1
    Join Date
    May 2001
    Posts
    51

    Unanswered: tuning postgres

    I've got PG version 7.2 and i'm having a few performance problems.

    The database is really slow even though i have given PG ~250MB shared buffer space. My system has 1GB of RAM so i though 250MB was ok. It has 32MB sort memory aswell.
    max connections = 64.
    fsync = true. (people say its better to this this as it is + in later versions of PG it doesn't affect performance that much, i think)

    All the indicies are working properly but i dont know what else to try to increase the performance.

    My Database system does not receive nearly as much traffic as some PG sites out there yet it seems very slow compared to the way others describe their sites. Hence i know something can be done to speed it up but i dont know what.

    Hope someone can help

    Regards
    etones.

  2. #2
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    Stupid question

    Do you run a full vaccum from time to time?
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  3. #3
    Join Date
    May 2001
    Posts
    51
    Hi, thanks for the reply.

    Yes, we do a vacumb every day (via crond)

    http://www.e-tones.co.uk <- Main site
    http://www.e-tones.co.uk/forums <- Forums

    The forums, running on mysql, return instantly, the main site on postgre can take upto 20 seconds!

    Please help.

    Regards

  4. #4
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    querie

    The problem is that I must know more details
    I often analyse the performance on which queries take very long
    If you analyse them and change them you can increase the output about 20-40 %
    has this site a heavy load?
    and what programing language do you use?
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  5. #5
    Join Date
    May 2001
    Posts
    51
    we seem to have solved the problem (I hope!). The problem turned out to be infact a vaccum issue. I was performing a vacuum everyday but apparently not a FULL vacuum.

    I was using the command :

    vaccumdb -a -z

    thinking that this was a full vaccum. I then noticed the -f option and tried it. About 1/2 hour later the website was up to full speed.

    Do you know what makes the -f option so special?

    thanks for your help

    etones

  6. #6
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    full vacuum

    I don't know exactly.
    But a full vaccum makes some steps that a normal vaccum doesn't make

    I must read in the docs
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  7. #7
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    FULL VACUUM

    From the docs:

    FULL

    Selects "full" vacuum, which may reclaim more space, but takes much longer and exclusively locks the table.
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  8. #8
    Join Date
    May 2001
    Posts
    51
    Thanks alot.

    Do you have any generic tuning tips for postgre at all?

    Cheers
    Taz

  9. #9
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    Tuning

    no not at all
    the efficent way of tuning is to look closer at all sql-queries.


    Always mark the text or integer with '
    This is better for the optimizer to find the best execution plan

    If you have exacly the same queries a lot times
    work with the SPI-interface

    Programming with SPI. You work directly IN THE BACKEND.
    You can send an execution plan directly to the executor of the database.
    This saves the overhead of the optimizer and the rewrite system
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  10. #10
    Join Date
    Mar 2002
    Location
    Pilipinas
    Posts
    36

    Angry

    how do you install postgres in your system, is it from tar? i compiled my postgres in my RedHat 7.3 from tar, then i noticed that there are some failures during "make check" (abstime, tinterval, horology) but its perfectly ok on my RedHat7.2 machine.

    by the way, im using Postgresql7.2.2
    any ideas?
    --- Hey! its me!!! the MaleMan ---

  11. #11
    Join Date
    Sep 2001
    Location
    Vienna
    Posts
    400

    7.3

    I install it per tar cause i need much compiled within

    I have not yet tested the 7.3 cause it's in beta stadium.
    Wait for the next beta and try this one

    this is my suggestion
    http://www.postgresql.org
    --Postgresql is the only kind of thing--

  12. #12
    Join Date
    Mar 2002
    Location
    Pilipinas
    Posts
    36
    Thanks for the suggestion, anyway im just using it for testing purposes...
    --- Hey! its me!!! the MaleMan ---

  13. #13
    Join Date
    Mar 2002
    Location
    Pilipinas
    Posts
    36

    Wink backup

    guys, can you teach me how to do an incremental backup in PostgreSQL ? or a simple script will do....

    THANKS!
    --- Hey! its me!!! the MaleMan ---

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •