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@2ndQuadrant.com  www.2ndQuadrant.com