Re: [GENERAL] database speed
Quoting Doug McNaught [EMAIL PROTECTED]: Chris Stokes [EMAIL PROTECTED] writes: The REINDEX is needed because VACUUM doesn't free up index space in some circumstances. 7.4 (currently in late beta) will fix this. Sorry Doug, Yes I am doing a vacuum regularly - in fact, when the speed of the DB becomes slower, the vacuum takes longer and longer too. I have never used the REINDEX I wondered if it might be a row chaining issue of some sort ?!? The index bloat problem can occur when you have a an indexed SERIAL column whose value always increases, and you delete older rows on a regular basis. VACUUM recycles the row storage, but the index never shrinks. You can check: next time you get the speed/data growth problem, find which files in the database directory are growing, and use 'oid2name' (in the source tree under 'contrib') to find out what they are. Odds are they are indexes and REINDEXing their table will fix the problem. If you are running a recent version of PG (7.3 and maybe 7.2 as well) and have a very active database, you may also need to increase your free space map (FSM) size in postgresql.conf, and possibly run VACUUM FULL once a week or so. -Doug ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org Speaking of recent versions... In 7.4, doesn't the cluster command address this problem? -- Keith C. Perry Director of Networks Applications VCSN, Inc. http://vcsn.com This email account is being host by: VCSN, Inc : http://vcsn.com ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] database speed
DM == Doug McNaught [EMAIL PROTECTED] writes: DM The index bloat problem can occur when you have a an indexed SERIAL DM column whose value always increases, and you delete older rows on a DM regular basis. VACUUM recycles the row storage, but the index never DM shrinks. I get this on tables which are updated a lot, even if I don't do any mass deletes of older rows. The indexes on other columns also bloat rapidly on some tables. It is a significant problem, especially since reindex locks the tables for significant amounts of time. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D.Khera Communications, Inc. Internet: [EMAIL PROTECTED] Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] database speed
Vivek Khera wrote: DM == Doug McNaught [EMAIL PROTECTED] writes: DM The index bloat problem can occur when you have a an indexed SERIAL DM column whose value always increases, and you delete older rows on a DM regular basis. VACUUM recycles the row storage, but the index never DM shrinks. I get this on tables which are updated a lot, even if I don't do any mass deletes of older rows. The indexes on other columns also bloat rapidly on some tables. It is a significant problem, especially since reindex locks the tables for significant amounts of time. Is ist possible for a trigger to fork off a 'reindex' command and the trigger terminates leaving the reindex running? Using PL/PGSQL? If a counter were kept in some table for each row, and after a certain amount of updates/deletes, reindex were run on an automatic, more often time frequency? ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] database speed
Dennis Gearon [EMAIL PROTECTED] writes: Is ist possible for a trigger to fork off a 'reindex' command and the trigger terminates leaving the reindex running? Even if possible, ick. I'd say the best thing to do would to have such a trigger set a flag (value in a one-row table) and have a cron job that checks for that flag and runs REINDEX. -Doug ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] database speed
Hi all, WE currently use PG for the local database on our POS workstations in shops. Data is then extracted and sent back to head office. Also product updates etc are regularly sent to the shops. I have noticed over time that the shop PG databases get slower and slower. If I do a pg_dump, recreate the database and then restore the data - all speed problems are gone and the database folder shrinks in size substantially. From this information could anyone tell me what are the likely causes and what we may or may not be doing in our extractions and updates that are not good PG insert, update, delete procedures Thanks Chris Stokes Senior Systems Consultant Bass Software Phone: +61 3 8415 9305 (Direct) Fax: +61 3 9427 1752 Mobile: +61 409 851 447 [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] www.basssoftware.com http://www.basssoftware.com/ Level 5 534 Church Street Richmond, Victoria, 3121, Australia ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] database speed
Chris Stokes [EMAIL PROTECTED] writes: WE currently use PG for the local database on our POS workstations in shops. Data is then extracted and sent back to head office. Also product updates etc are regularly sent to the shops. I have noticed over time that the shop PG databases get slower and slower. If I do a pg_dump, recreate the database and then restore the data - all speed problems are gone and the database folder shrinks in size substantially. From this information could anyone tell me what are the likely causes and what we may or may not be doing in our extractions and updates that are not good PG insert, update, delete procedures You should periodically be doing: a) VACUUM (at least once a day, probably) and b) REINDEX Are you doing both of these? See the docs if you're not familiar with them. The REINDEX is needed because VACUUM doesn't free up index space in some circumstances. 7.4 (currently in late beta) will fix this. -Doug ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [GENERAL] database speed
Chris Stokes [EMAIL PROTECTED] writes: The REINDEX is needed because VACUUM doesn't free up index space in some circumstances. 7.4 (currently in late beta) will fix this. Sorry Doug, Yes I am doing a vacuum regularly - in fact, when the speed of the DB becomes slower, the vacuum takes longer and longer too. I have never used the REINDEX I wondered if it might be a row chaining issue of some sort ?!? The index bloat problem can occur when you have a an indexed SERIAL column whose value always increases, and you delete older rows on a regular basis. VACUUM recycles the row storage, but the index never shrinks. You can check: next time you get the speed/data growth problem, find which files in the database directory are growing, and use 'oid2name' (in the source tree under 'contrib') to find out what they are. Odds are they are indexes and REINDEXing their table will fix the problem. If you are running a recent version of PG (7.3 and maybe 7.2 as well) and have a very active database, you may also need to increase your free space map (FSM) size in postgresql.conf, and possibly run VACUUM FULL once a week or so. -Doug ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
