Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing
От | Hannu Krosing |
---|---|
Тема | Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing |
Дата | |
Msg-id | 1133941388.3543.23.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing relation locking overhead) (Greg Stark <gsstark@mit.edu>) |
Ответы |
Re: Concurrent CREATE INDEX, try 2 (was Re: Reducing
|
Список | pgsql-hackers |
Ühel kenal päeval, T, 2005-12-06 kell 19:32, kirjutas Greg Stark: > Hannu Krosing <hannu@skype.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
В списке pgsql-hackers по дате отправления: