Re: a heavy duty operation on an "unused" table kills my server

Eduardo Piombino wrote:
> But already knowing that the base system (i.e. components out of pg's
> control, like OS, hardware, etc) may be "buggy" or that it can fail in
> rationalizing the IO, maybe it would be nice to tell to whoever is
> responsible for making use of the IO subsystem (pg_bg_writer?), to use
> it in a moderately manner. That is ... This operation is not critical,
> please do not trash my system because it is not necessary. Use all the
> delays you would like, go slowly, please, I don't really care if you
> take a month. Or at least, be aware of current status of the IO
> system. If it is being busy, slow down, if it is free, speed up. Of
> course I won't care if it takes less time to complete.

There are three problems here:

1) The background writer does not have a central role in the I/O of the
system, and even if it did that would turn into a scalability issue.
Clients initiate a lot of work on their own, and it's not so easy to
actually figure out where to put a limiter at given that.

2) PostgreSQL aims to be cross-platform, and writing something that
adjusts operations based on what the OS is doing requires a lot of
OS-specific code. You end up needing to write a whole new library for
every platform you want to support.

3) Everyone who is spending money/time improving PostgreSQL has things
they think are more important to work on than resource limiters, so
there's just not anybody working on this.

Your request is completely reasonable and there are plenty of uses for
it. It's just harder than it might seem to build. One day we may find
someone with money to spend who can justify sponsoring development in
this area because it's a must-have for their PostgreSQL deployment. I
assure you that any number of people reading this list would be happy to
quote out that job.

But right now, there is no such sponsor I'm aware of. That means the
best we can do is try and help people work around the issues they do run
into in the most effective way possible, which in your case has wandered
into this investigation of your underlying disk subsystem. It's not
that we don't see that an alternate approach would make the problem go
away, the code needed just isn't available, and other project
development work (like the major replication advance that was just
committed today) are seen as more important.

--
Greg Smith 2ndQuadrant Baltimore, MD
PostgreSQL Training, Services and Support
greg(at)2ndQuadrant(dot)com www.2ndQuadrant.com