[OSM-dev] Anyone with a speedy gazetteer

Tom Hughes tom at compton.nu
Mon Jan 12 15:54:39 GMT 2009


David Earl wrote:

> OK, I've got three possible ways forward:
> 
> (a) convert to InnoDB.
> 
> (b) do updates on different tables to queries and switch them over on 
> completion.
> 
> (c) both
> 
> The downside of (a) is that there is an overhead to row locking; of (b) 
> is that you do every update twice, which is probably an even bigger 
> overhead, and of (c) both.

The main problem with b is that you need to make a complete copy of the 
tables before you start the update - the logic presumably goes like this:

    - Copy live_database to temp_database

    - Update temp_database

    - Delete live_database

    - Rename temp_database to live_database

or have I misunderstood?

> One possibly complicating factor is that I am now writing the search 
> terms to a table as a log, so there may still be contention for a write 
> lock here. But this should be brief and predictably stable - it just 
> writes a new row in a table only for this purpose. This is the only 
> write operation involved in doing a search.

Appending to a MyISAM table is a special case that does not require 
taking a write lock on the table, so inserts are OK so long as you never 
delete from the table (if you delete then inserts will take locks until 
all the slots freed by the deletes are used up).

Tom

-- 
Tom Hughes (tom at compton.nu)
http://www.compton.nu/




More information about the dev mailing list