On Fri, Jul 10, 2009 at 2:47 PM, Willy-Bas Loos<willybas@xxxxxxxxx> wrote: > Hi, > > Thanks for your answers! > I'm using 8.1 and 8.2 on windows2003 servers, and it's true that i could > probably configure them much better. Note that support for 8.1 on windows is gone, as it is no longer considered supportable due to design / engineering issues. Upgrading them all to 8.3 or 8.4 is probably a good idea since they are better at autovacuum and such than 8.2 and before. > We've recently moved to brand new dedicated database servers with pg8.3 on > debian in 2 projects and it has been much easier to configure these > correctly. There I don't encounter the probems that i described. Smart move. The amount of effort needed to learn debian or any other linux distro is usually less than the amount of ongoing effort to keep a production pg server happy on windows. Also, 64 bit pgsql on 64 bit unix/linux is capable of better scaling and handling more memory. > The thing is that the whole concept of autovacuum is not feeling right. > Per design, the vacuum is likely to kick off when i am doing something big. That assumes that autovacuum always runs in some mode that must interfere with db operation. If you set the autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, autovacuum_vacuum_scale_factor, autovacuum_vacuum_threshold parameters properly, as well as the free space map settings large enough to hold all your dead tuples, then autovacuum should not cause a lot of issues, unless your machine is already IO bound. And if it's already IO bound and dragging butt, then the problem isn't autovacuum, but a machine without enough IO bandwidth to do its job well. > And when i am doing something big, a vacuum is the last thing i'd wish for. I don't even notice when it kicks in on my servers. > I'd wish for a vacuum when the database is doing nothing at all, but the > autovacuum will NEVER kick off in such a moment. Again, if the delay and such are set right, then autovac will use so little IO as to be unnoticeable. > That's why i feel better scheduling the vacuum at times at which i know > things will be generally quiet. For some very large tables on slow IO machines, it makes sense to remove them from the purview of autovac, I agree. Take a look at the pg_autovacuum table. it's pretty easy to see how it works. > To be honest, i am a bit surprised that all 3 reactions recommend using > autovacuum, even if it means i have to buy a new server for this purpouse. Well, autovacuum is more primitive in the versions you're running, and more likely to get in the way. 8.3, and to a greater extent 8.4, remove a lot of these issues. > I was thinking that autovacuum was just a mechanism to ensure that postgres > works well out of the box, but that it would be recommended to schedule your > own vacuum tailored to your specific needs. > I agree though, that it is a tough tailoring job and that the autovacuum > must be doing a better job than i am. It just fires at the wrong time. And that's kind of the point, that it's better to have a db that runs a little slow than one that explodes in sheets of flame. > Just a thought (to think positively..): wouldn't it be possible to let the > autovacuum wait until the load goes down, or until the end of the > transaction that triggered the autovacuum? You could set up a cron job that updated the pg_autovacuum table at certain times to accomplish this. I'm not sure how easy it would be to program autovac to do the same thing. You could certainly set the cost delay higher than normal (like 20 or 40 ms) for some tables so that autovac didn't get in the way, but then you run the risk of it never keeping up, and on 8.1 with only one thread to autovac, that could be bad. Definitely consider upgrading pg versions on your windows machines. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
