I cant seem to find an answer to this in the archives... I'm hoping someone has come across this problem before. Beware this is a long email...

Ive got 2 servers one used for development and one used for production. Both servers are identical in every way except DEV has only 512MB of memory. Both run NetBSD 1.6, PostgreSQL 7.3.2 and PHP 4.3.2

DEV: The DB gets restored (dropped then restored from dump file) from PROD's DB daily. Only the developers use this server.

PROD: 300+ records get imported daily. Vacuum is run daily. 20+ users access this server daily.

Ive got a PHP script which retrieves data from 5 databases and compiles the data into a temporary table. This temporary table is then used to spit out a report for the user. The first part of the script (storing the data in the temp table) normally takes about 10-20 seconds. The second part (spitting out the report) takes another 15-30 seconds. Incase your wondering, I track the time within the PHP script.

Over a period of 3 or so months the performance of PostgreSQL on the PROD server seems to have degraded.

I run the same PHP script on DEV and PROD with the same data to get a rough indication. DEV finishes the first part within 20 seconds. PROD takes about 1 minute to finish. That is a massive 40 seconds difference! I know what your saying "PROD has more users". I lock the PROD server so no one except my IP address can access it. Destroy the DB session / PHP sessions linked to anyone logged on, run the test again. Same results!

For days i was unable to come up with a logical explanation for the degradation in performance. Then my manager suggested that the database was cluttered and that it needed to be "re-orged" (like how windows defrags a HD). This was news to me, however since i couldnt find a solution, i pg_dump-ed the DBs and restored them, ran the same test. Low and behold PROD finishes the first part of the script within 20 seconds!!

Funny thing was, i was hounding the server admin saying there is something wrong with server (because there was only 5MB worth of free memory out of 1GB) and he kept on saying no its your script / PostgreSQL thats causing the problem. Out of curiosity, I checked the memory usage after the DB restoration and the free memory was back up to 500MB!

Now my questions are:
What could have caused PostgreSQL's performance degradation?
If this is a known problem, is restoring the DB the only way to rectify it?

Thanks in advance
Cody Phanekham


************************************************** ***********************************
This e-mail, including any attachments to it, may contain confidential and/or personal information.
If you have received this e-mail in error, you must not copy, distribute, or disclose it, use or take any action
based on the information contained within it.

Please notify the sender immediately by return e-mail of the error and then delete the original e-mail.

The information contained within this e-mail may be solely the opinion of the sender and may not necessarily
reflect the position, beliefs or opinions of Salmat on any issue.

This email has been swept for the presence of computer viruses known to Salmat's anti-virus systems.

For more information, visit our website at www.salmat.com.au.
************************************************** ***********************************


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

"Cody Phanekham" <Cody.Phanekham (AT) salmat (DOT) com.au> writes:
You say you were doing daily vacuuming, but had you checked that this
was adequate? The behavior you describe sounds a lot like table or
index bloat. Tables bloat if you don't vacuum often enough to keep the
amount of free space within what the free space map (FSM) can hold.
(You can cure this by increasing the FSM size, or by vacuuming more
often, or a combination.) Indexes bloat if the range of indexed keys
moves significantly --- in PG 7.3 there is not a lot you can do about
this except to REINDEX periodically. (The index bloat problem is
thought to be largely solved in 7.4, however.)

On any Unix machine, near-zero free memory is the expected and desirable
condition. That's because the kernel automatically uses any memory
that's not currently needed for processes to hold cached disk pages.
What you need to worry about is not free memory per se, but the fraction
of real RAM that's being used for disk buffers --- when that gets too
small, then you worry.

You didn't improve matters, you just temporarily invalidated all the
kernel's cached copies of database file pages. This will mean extra
physical I/O until the kernel repopulates its cache, after which you'll
be back to near-zero "free" memory. That's not a bad thing.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

I did a off-schedule vacuuming of the DB, ran the test straight after. No improvement in performance at all. How do you check if the vacuum is adequate?

The behavior you describe sounds a lot like table or
I'll let the server admin know to increase the FSM.

Unfortunately we cant upgrade to 7.4 at this time


************************************************** ***********************************
This e-mail, including any attachments to it, may contain confidential and/or personal information.
If you have received this e-mail in error, you must not copy, distribute, or disclose it, use or take any action
based on the information contained within it.

Please notify the sender immediately by return e-mail of the error and then delete the original e-mail.

The information contained within this e-mail may be solely the opinion of the sender and may not necessarily
reflect the position, beliefs or opinions of Salmat on any issue.

This email has been swept for the presence of computer viruses known to Salmat's anti-virus systems.

For more information, visit our website at www.salmat.com.au.
************************************************** ***********************************


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster