PDA

View Full Version : tuning postgres

etones
09-07-02, 07:33
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.
eperich
09-08-02, 06:29
Do you run a full vaccum from time to time?
etones
09-08-02, 17:58
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
eperich
09-08-02, 18:08
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?
etones
09-09-02, 08:12
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
eperich
09-09-02, 08:18
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
eperich
09-11-02, 06:21
From the docs:

FULL

Selects "full" vacuum, which may reclaim more space, but takes much longer and exclusively locks the table.
etones
09-11-02, 06:25
Thanks alot.

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

Cheers
Taz
eperich
09-11-02, 06:31
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
MaleMan
09-11-02, 22:30
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? :)
eperich
09-12-02, 06:37
I install it per tar cause i need much compiled within :D

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
MaleMan
09-15-02, 21:45
Thanks for the suggestion, anyway im just using it for testing purposes... :D
MaleMan
02-07-03, 23:52
guys, can you teach me how to do an incremental backup in PostgreSQL ? or a simple script will do.... :D

THANKS!