Re: WAL Rate Limiting

On 02/20/2014 04:16 PM, Robert Haas wrote:
> On Thu, Feb 20, 2014 at 9:43 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> The design choice of making the limit only apply to bulk ops is
>> because that is where the main problem lies. Rate limiting will cause
>> a loss of performance in the main line for non-bulk operations, so
>> adding tests there will not be valuable.
> That's pure sophistry. Of course rate limiting will cause "a loss of
> performance in the main line for non-bulk operations".
I think he meant that *adding code for* rate limiting there will cause
loss of performance even if not used.
> Rate limiting
> will also cause a loss of performance for bulk operations. The whole
> point of rate limiting is to cause a loss of performance.
Not when it is not enabled it should not.
> That's not
> a bug; that's what the feature is explicitly designed to do.
>
> So-called "non-bulk operations", which seems to be defined as more or
> less "anything where actually making this feature work seemed hard",
NO, it is anything that a decent DBA could not script to run in
chunks, which he would do anyway for other reasons, like not
to lock out OLTP.
> can include huge inserts, updates, or deletes that greatly depress
> performance for other parts of the system, either individually or in
> aggregate. And it's just as legitimate to want to tamp down that
> activity as it is to want to slow down CLUSTER. Remember, this is a
> GUC, so it need not be set identically for every user session. It is
> not hard at all to imagine a situation where the user wishes to limit
> the rate at which some sessions can write WAL so as to avoid
> interfering with other, higher-priority tasks happening in other
> sessions.
It is hard to imagine, but it is still a much more infrequent than needing
to do concurrent ops like VACUUM and CREATE INDEX CONCURRENTLY .
> That is hardly a niche use case; I think I've seen it
> reported, if anything, even more frequently than problems with what
> you're calling bulk operations.
Could be, but they are two separate cases. One helps DBA get stuff
done without stepping on users toes, the other is about protecting
one user from the other.

It is arguable that the first is a sub-case of the other, but I don't think
these should be controlled by the same GUC though you may want to
have some checking for sane values between the two

Maybe call this one `maintenance_wal_rate_limit_delay` same way as we
have `maintenance_work_mem`

Cheers

--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ