Re: Implementing cost limit/delays for insert/delete/update/select

Hello,

[ I have not yet had the time to look at code again in response to
some of the points raised raised by several people; but I wanted to
follow-up somewhat still on other bits. ]

> > You would have to test for whether it's time to sleep much more often.
> > Possibly before every ExecProcNode call would be enough.
>
> That would have overhead comparable to EXPLAIN ANALYZE, which is a lot.
>
> I'm fairly dubious about this whole proposal: it's not clear to me that
> the vacuum delay stuff works very well at all, and to the extent that it
> does work it's because vacuum has such stylized, predictable behavior.

Well, it definitely works well enough to make a large difference in my
use cases. In particular with respect to the amount of write activity
generated which easily causes latency problems. That said, it remains
to be seen how much of an issue heavy write activity will be once
upgraded to 8.3 and after tweaking the Linux buffer cache.

Right now, I do not expect the database to be even useful in one of my
use cases, if it were not for delay points during vacuuming.

So although I make no argument as to whether it works better due to
the limited and understood nature of vacuuming, it is definitely an
appreciate feature for my use cases.

> The same can't be said of general SQL queries. For one thing, it's
> not apparent that rate-limiting I/O would be sufficient, because
> although vacuum is nearly always I/O bound, general queries often are
> CPU bound; or their system impact might be due to other factors like
> contention for shared-memory data structures.

In my case I mostly care about I/O. I believe that this is going to be
a fairly common fact with anyone whose primary concern is latency.

The good part about CPU contention is that it is handled quite well by
modern operating systems/hardware. Even on a single-core machine, a
single CPU bound query should still only have a percentage-wise
throughput impact on other traffic (normally; of course you might have
some particularly bad contention on some resource, etc). If your
database is very sensitive to latency, you are likely running it at
far below full throughput, meaning that there should be quite a bit of
margin in terms of CPU.

This would be especially true on multi-core machines where the impact
of a single backend is even less.

The problem I have with I/O is that saturating I/O, in particular with
writes, has all sorts of indirect effects that are difficult to
predict, and are not at all guaranteed to translate into a simple
percentage-wise slow-down. For example, I've seen stalls lasting
several *minutes* due to a bulk DELETE of a million rows or so. With
mixed random-access writes, streaming writes, and the PG buffer cache,
the operating system buffer cache, and the RAID controller's cache, it
is not at all unlikely that you will have significant latency problems
when saturating the system with writes.

So recognizing that I am not likely to ever have very good behavior
while saturating the storage system with writes, I instead want to
limit the write activity generated to a sensible amount (preferably
such that individual bursts are small enough to e.g. fit in a RAID
controller cache). This reduces the problem of ensuring good behavior
with respect to short burst of writes and their interaction with
checkpoints, which is a much easier problem than somehow ensuring
"fairness" under write-saturated load.

So that is where my motivation comes from; in more or less all my use
cases, limiting disk I/O is massively more important than limiting CPU
usage.

On this topic, I have started thinking again about direct I/O. I asked
about this on -performance a while back in a different context and it
seemed there was definitely no clear concensus that one "should" have
direct I/O. That seemed to be mostly due to a perceived lack of
increase in throughput. However my gut feel is that by bypassing the
OS buffer cache, you could significantly improve real-time/latency
sensitive aspects in PostgreSQL in cases where throughput is not your
primary concern.

Perhaps something like that would be a more effective approach.

> Priority inversion is
> a pretty serious concern as well (ie, a sleeping "low priority" query
> might be blocking other queries).

I presume this is in reference to bulk modifications (rather than
selects) blocking other transactions with conflicting updates?

If so, yes I see that. The feature would be difficult to use reliably
for writes except in very controlled situations (in my particular
use-case that I am tunnel vision:ing on, it is guaranteed that there
is no conflict due to the nature of the application).

But is my understanding correct that there is no reason to believe
there are such issues for read-only queries, or queries that do not
actually conflict (at the SQL level) with concurrent transactions?

(Ignoring the impact it might have on old transactions hanging around
for a longer time.)

--
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <peter(dot)schuller(at)infidyne(dot)com>'
Key retrieval: Send an E-Mail to getpgpkey(at)scode(dot)org
E-Mail: peter(dot)schuller(at)infidyne(dot)com Web: http://www.scode.org