Re: [Pgsphere-dev] GIST index concurrency concern
От | Oleg Bartunov |
---|---|
Тема | Re: [Pgsphere-dev] GIST index concurrency concern |
Дата | |
Msg-id | Pine.GSO.4.61.0411101355260.23066@ra.sai.msu.su обсуждение исходный текст |
Ответ на | Re: [Pgsphere-dev] GIST index concurrency concern (Oleg Bartunov <oleg@sai.msu.su>) |
Список | pgsql-hackers |
Patrick, you didn't say us about your setup. Have you proved you've seen locking issue for reading ? Are you sure you have no any locks in your code ? Any tests demonstrated your problem would be great. Oleg On Tue, 9 Nov 2004, Patrick Clery wrote: > Oleg, > Daniel and I have both been collaborating on this structure for a while now. > We are aware that GiST reads work very fast. But won't they be "paralyzed" > when there are writes? Both of us are working on dating sites, and the main > problem that concerns us is a very heavy traffic load. At this point I am > planning to queue all changes to a GiST index and commit them every 10-15 > minutes. Is that really necessary? It's realistic to assume here that if > there is a problem with locking the table for writes, it will be a problem in > this situation because this structure is going to be hit VERY hard (and > Daniel's situation is on an even larger scale). We hope that we can alleviate > that with a "transaction queue", but this is not a simple fix. Have you seen > any projects that were under a heavy load using a GiST index, and were they > able to avoid being "paralyzed" somehow? > > Thanks in advance, > Patrick > > On Tuesday 09 November 2004 22:08, Oleg Bartunov wrote: >> Oleg Bartunov <oleg@sai.msu.su> > >> Daniel, >> >> concurrency is a big issue of current implementation of GiST. >> But it should don't bite you for READ ops ! >> -hackers mailing list is a very relevant mailing list for GiST >> discussions. It's pity we several times claimed to work on GiST >> concurrency and recovery, but never got a chance :) >> I see Neil become interested in GiST concurrency, though. >> >> >> Oleg >> On Tue, 9 Nov 2004, Daniel Ceregatti wrote: >> >>> Hi, >>> >>> It's recently come to my attention that GIST indices suffer from >>> concurrency issues. I have already developed a dating sites using GIST >>> for use with attributes using the intarray contrib, and for Earth >>> distance/radius calculations using pg_sphere. >>> >>> I'm wondering if I haven't shot myself in the foot here. So far, I >>> understand that a GIST index will be locked by a backend for any DML. >>> Basically I'm concerned that my database will not scale in the manner >>> that I was hoping, because the sites that access the database are to be >>> used by many multiple concurrent users, doing some DML. >>> >>> I expect my site to sustain something around 1000-3000 new user >>> acquisitions per day, all of which will account for an insert into 3 >>> GIST indices. Additionally there will be people that will be updating >>> their attributes and locations as well, but this will probably only >>> account for a small fraction of the DML. We don't allow people to delete >>> stuff. >>> >>> My concern now is this concurrency issue. My question is: Is there >>> anyone out there using a GIST index on a database where there's a lot of >>> DML? Should I be concerned with this issue at all? >>> >>> If so, what can be done to minimize the impact of heavy DML on a GIST >>> index? I've pondered rolling all DML into queues via triggers and then >>> de-queuing them in one transaction every so often, like 15 minutes, via >>> cron. Any other suggestions? >>> >>> I'm posting to this list because I understand that both Oleg and Teodor >>> read it, and I found no other relevant list. If I've misposted, please >>> accept my apology and please direct me to the appropriate list. >>> >>> Thanks, >>> >>> Daniel >>> >> > _______________________________________________ > Pgsphere-dev mailing list > Pgsphere-dev@gborg.postgresql.org > http://gborg.postgresql.org/mailman/listinfo/pgsphere-dev > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
В списке pgsql-hackers по дате отправления: