Hi all, Excuse me if it's a silly question (I'm a newbie here), but what about using a search engine component instead of SQL queries ? I think about Apache Lucene (Java search engine) for example ( http://lucene.apache.org/) which seems to me a better solution about indexing and searching than SQL requests ?
Nico On Mon, Jan 12, 2009 at 2:05 PM, Tom Hughes <t...@compton.nu> wrote: > David Earl wrote: > > > While what Tom says may help performance, I don't think it is why things > > are running disparately slowly at the moment. > > Oh yes it is... > > > It is mainly extremely slow at present because it has got into a state > > where daily updates are interfering with each other and I haven't had a > > chance to look at why this is happening. It is getting worse, because > > each day slows down more so the next day starts up and interferes with > > that. I know it needs attention. While the updates are running, searches > > are much slower because they don't get much opportunity to run. I > > suspect his vicious circle started because there was one day when there > > was an exceptionally large update and this ran fro > 24 hours, running > > into the next. > > How many times do I have to explain the problem... The problem is lock > contention because you're using MyISAM tables. > > Here is what happens: > > - You start update and it runs quite happily initially > > - User issues query which, for whatever reason, takes a long > times (many minutes or even hours) to run, which leads to a > read lock on the main tables > > - Update blocks waiting for a write lock > > - All other queries now block behind the writer as they can't > get a read lock until it is done > > - Several hours later the update is still blocked, all the Apache > processes are wedged with pending queries and the whole thing > is buggered > > - Eventually the slow query finishes and the update happens, then > hundreds of pending queries all kick off and content with each > other so run really slowly > > - The next update is now blocked behind all those readers > > - The above repeats ad infinitum and nobody gets anything much done > > If it's really bad then, as you've noticed, one day's update won't > finish before the next one is due to start. > > It is really very simple - MyISAM tables are not suitable for any case > where you will have contention between readers and writers, especially > if some of the read queries may be long running. > > Tom > > -- > Tom Hughes (t...@compton.nu) > http://www.compton.nu/ > > _______________________________________________ > dev mailing list > dev@openstreetmap.org > http://lists.openstreetmap.org/listinfo/dev >
_______________________________________________ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
