Re: VACUUM, 24/7 availability and 7.2 - Mailing list pgsql-general

On Wed, Oct 10, 2001 at 04:22:07PM -0400, wsheldah@lexmark.com wrote:
>
>
> Just to keep things in perspective, how large are your current databases, and
> what do you or the company consider to be a signficant length of time?  Right
> now I have a development database with just a few thousand records of test data,
> and vacuum takes just a very few seconds a day.  I think I recall hearing on
> this list of it taking a minute or three for databases several gigabytes in
> size.  For some sites this would be tolerable, for others it wouldn't.

We are having some trouble with some tables in which we have lots of
update's (and insert/delete's). "A lot" being several thousands per day
(I haven't measured the exact numbers recently). VACUUM is running twice
a day and locks these tables a long time where 10-15 minutes is not
exceptional. This table has only approx 100k records, but these are
updated very often and that seems to cause to much rubbish for vacuum
>
> I'm also interested to hear what the future holds for vacuum.  If nothing else,
> it couldn't hurt postgresql's public relations.  :-)

indeed. we are currently considering moving the guilty tables over til
MySQL as we're not using any advanced features in it, while keeping all
other data in PostgreSQL. Anything that keeps us from doing that would
be good PR :)

/erwin

>
> --Wes Sheldahl
>
>
>
>
>
> Ian Barwick <SUNGLASSESbarwick%gmx.net@interlock.lexmark.com> on 10/10/2001
> 07:27:56 AM
>
> To:   pgsql-general%postgresql.org@interlock.lexmark.com
> cc:    (bcc: Wesley Sheldahl/Lex/Lexmark)
> Subject:  [GENERAL] VACUUM, 24/7 availability and 7.2
>
>
> I'm doing some work for a smallish company which conducts
> its business largely online. Currently they have a legacy
> mishmash of Oracle and MySQL databases which they wish
> to unify one one platform (RDBMS with client access via
> browser and custom serverside applications for employees
> and customers).
>
> PostgreSQL would be my primary candidate. However the company's
> operating requirments mean that the data needed for interaction
> with customers / website users must be available on a 24/7 basis.
> This is primarily a) data related to product ordering and
> tables for storing order data; and b) website user authentication
> and personalisation data (logins, user preferences etc).
>
> It is therefore not an option to have these databases offline
> at regular intervals for any significant length of time for
> VACUUMing. Replicating data to say MySQL databases is
> technically feasible, at least in the case of b) above, but
> not desirable. Are there any existing "native" PostgreSQL solutions
> to this problem?
>
> More importantly, what is the situation on VACUUM for release 7.2?
> It seems from the pgsql-hackers list that there are plans for
> a none-exclusively locking VACUUM, e.g.:
>
>
http://groups.google.com/groups?q=vacuum&hl=en&group=comp.databases.postgresql.hackers&rnum=1&selm=12833.990140724%40sss.pgh.pa.us
>
>
> (sorry about the long URL); how far advanced are they, and is
> there any kind of release schedule for 7.2?
>
> Any answers (or pointers thereto, haven't found any myself :-()
> much appreciated
>
>
> Ian Barwick
>
> --
>
> Remove SUNGLASSES to reply ;-)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
--
Erwin Lansing         --         http://droso.org

I love deadlines.                       -- Douglas Adams
I love the whooshing sound they make as the fly by.