Re: Database size Vs performance degradation - Mailing list pgsql-performance

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_nameAS select_statement" command. Then drop the old table. After that you can use ALTER TABLE to change the name
ofthe 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
bloatingproblem. 

Regards,
Val


--- On Wed, 30/7/08, Dave North <DNorth@signiant.com> wrote:

> From: Dave North <DNorth@signiant.com>
> 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
> dnorth@signiant.com
> 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