Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing

Ühel kenal päeval, T, 2005-12-06 kell 19:32, kirjutas Greg Stark:
> Hannu Krosing <hannu(at)skype(dot)net> writes:
>
> > The scenario where concurrent create index command is be needed is 24/7
> > OLTP databases, which can't be taken down for maintenance. Usully they
> > can be arranged to tolerate postponing a few transactions for one
> > second.
>
> Well, the dominant defining characteristic of "OLTP" is precisely that you do
> *not* have under your control the timing requirements and can't make such
> arrangements. That is, you have to process requests as fast as they come in
> whatever that might be.

While "as fast as possible" is a good goal when designing and optimising
a DB engine proper, you never need to design a real system to a spec "as
fast as possible" but rather to some given expected performance.

For me a 24/7 OLTP is more like a "Real Time" system, where all queries
have to be processed in "not more than" a certain time v.s. "as fast as
possible". There "as fast as possible" is a secondary goal, a lot less
important than meeting the deadlines.

For example one real db processes requests usually in 50-200ms, but the
maximum the client is prepared to wait is set to 20 sec. Anything longer
than that and the bells start ringing.

> But that said, realistically *any* solution has to obtain a lock at some time
> to make the schema change. I would say pretty much any O(1) (constant time)
> outage is at least somewhat acceptable as contrasted with the normal index
> build which locks out other writers for at least O(n lg n) time. Anything on
> the order of 100ms is probably as good as it gets here.

For me any delay less than the client timeout is acceptable and anything
more than that is not. N sec is ok, N+1 is not. It's as simple as that.

And if the CREATE INDEX takes 2 weeks in order to let other OLTP
processing go on uninterrupted then it is completely OK. I can afford to
set the deadline for it accordingly.

Thinking of it, maybe concurrent CREATE INDEX should also honour
vacuum_cost_* GUC's and throttle its progress accordingly in order to
not starve others on IO/CPU .

--------------------
Hannu