I am guessing that you are using DELETE to remove the 75,000 unimportant. Change your batch job to CREATE a new table consisting only of the 5,000 important. You can use "CREATE TABLE table_name AS select_statement" command. Then drop the old table. After that you can use ALTER TABLE to change the name of the new table to that of the old one.
I am not an expert but if this is a viable solution for you then I think doing it this way will rid you of your bloating problem. Regards, Val --- On Wed, 30/7/08, Dave North <[EMAIL PROTECTED]> wrote: > From: Dave North <[EMAIL PROTECTED]> > Subject: [PERFORM] Database size Vs performance degradation > To: pgsql-performance@postgresql.org > Date: Wednesday, 30 July, 2008, 1:09 PM > Morning folks, > Long time listener, first time poster. Having an > interesting > problem related to performance which I'll try and > describe below and > hopefully get some enlightenment. First the environment: > > > Postgres 8.1.8 > shared_buffers = 2000 > max_fsm_pages = 400000 > Redhat Enterprise 4 > Running on HP DL380 w/ 4GB RAM, dual 10K HDDs in RAID 0+1 > Also running on the server is a tomcat web server and other > ancillaries > > Now, the problem. We have an application that continually > writes a > bunch of data to a few tables which is then deleted by a > batch job each > night. We're adding around 80,000 rows to one table > per day and > removing around 75,000 that are deemed to be > "unimportant". Now, the > problem we see is that after a period of time, the database > access > becomes very 'slow' and the load avg on the machine > gets up around 5. > When this happens, the application using the DB basically > grinds to a > halt. Checking the stats, the DB size is around 7.5GB; no > tables or > indexes look to be 'bloated' (we have been using > psql since 7.3 with the > classic index bloat problem) and the auto-vac has been > running solidly. > > We had this problem around a month ago and again yesterday. > Because the > application needs reasonably high availability, we > couldn't full vacuum > so what we did was a dump and load to another system. What > I found here > was that after the load, the DB size was around 2.7GB - a > decrease of > 5GB. Re-loading this back onto the main system, and the > world is good. > > One observation I've made on the DB system is the disk > I/O seems > dreadfully slow...we're at around 75% I/O wait > sometimes and the read > rates seem quite slow (hdparm says around 2.2MB/sec - > 20MB/sec for > un-cached reads). I've also observed that the OS cache > seems to be > using all of the remaining memory for it's cache > (around 3GB) which > seems probably the best it can do with the available > memory. > > Now, clearly we need to examine the need for the > application to write > and remove so much data but my main question is: > > Why does the size of the database with so much > "un-used" space seem to > impact performance so much? If (in this case) the extra > 5GB of space is > essentially "unallocated", does it factor into > any of the caching or > performance metrics that the DBMS uses? And if so, would I > be better > having a higher shared_buffers rather than relying so much > on OS cache? > > Yes, I know we need to upgrade to 8.3 but that's going > to take some time > :) > > Many thanks in advance. > > Dave > > ___ > Dave North > [EMAIL PROTECTED] > Signiant - Making Media Move > Visit Signiant at: www.signiant.com > <http://www.signiant.com/> > > > -- > Sent via pgsql-performance mailing list > (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance __________________________________________________________ Not happy with your email address?. Get the one you really want - millions of new email addresses available now at Yahoo! http://uk.docs.yahoo.com/ymail/new.html -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
