Обсуждение: Unexpected PostgreSQL performance degradation
I cant seem to find an answer to this in the archives... I'm hoping someone has come across this problem before. Beware thisis a long email... Ive got 2 servers one used for development and one used for production. Both servers are identical in every way except DEVhas 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 tableis then used to spit out a report for the user. The first part of the script (storing the data in the temp table) normallytakes 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 within20 seconds. PROD takes about 1 minute to finish. That is a massive 40 seconds difference! I know what your saying "PRODhas more users". I lock the PROD server so no one except my IP address can access it. Destroy the DB session / PHP sessionslinked 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 suggestedthat the database was cluttered and that it needed to be "re-orged" (like how windows defrags a HD). This was newsto me, however since i couldnt find a solution, i pg_dump-ed the DBs and restored them, ran the same test. Low and beholdPROD 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 5MBworth of free memory out of 1GB) and he kept on saying no its your script / PostgreSQL thats causing the problem. Outof 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. *************************************************************************************
"Cody Phanekham" <Cody.Phanekham@salmat.com.au> writes:
> Over a period of 3 or so months the performance of PostgreSQL on the
> PROD server seems to have degraded.
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.)
> 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)
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.
> Out of curiosity, I checked the memory usage after the DB restoration
> and the free memory was back up to 500MB!
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
> -----Original Message----- > From: Tom Lane [mailto:tgl@sss.pgh.pa.us] > > "Cody Phanekham" <Cody.Phanekham@salmat.com.au> writes: > > Over a period of 3 or so months the performance of PostgreSQL on the > > PROD server seems to have degraded. > > You say you were doing daily vacuuming, but had you checked that this > was adequate? I did a off-schedule vacuuming of the DB, ran the test straight after. No improvement in performance at all. How do you checkif the vacuum is 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.) I'll let the server admin know to increase the FSM. >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.) 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. *************************************************************************************