[OSM-dev] speeding the db

[SteveC]

Grant

Thanks for that.

I've changed the user table to innodb (after testing it on a empty test 
db) as you suggested and have sat watching mytop. queries resulting from 
people browsing osm.org/traces/ no longer lock the user table and the 
query throughput has gone up a billion percent or whatever.

So things should be significantly quicker now.

Grant Slater wrote:
> SteveC wrote:
>> grant says you can do
>>
>> ALTER TABLE `users`  ENGINE = innodb;
>>
>> on a per-table basis. This would immediately solve a ton of problems, 
>> does anyone have a setup/time to test all works with something like this?
>>
>>   
> 
> ALTER `Table` syntax defined here:
> http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
> 
> Per table engine/backend switching is supported.
> 
> My other notes:....
> 
> Current:
> - AUTO_INCREMENT| |columns, you must define an index for the table, and 
> that index must contain just the |AUTO_INCREMENT| column.
> (tables affected: `area_tags`,`gpx_file_tags`, `way_segments` & `ways`)
> - Innodb does not support fulltext.
> (tables affected: 'current_way_tags')
> - In InnoDB, having a long  PRIMARY KEY| |wastes a lot of disk space 
> because its value must be stored with every secondary index record. 
> (tables affected: `area_segments`,`area_tags`, `areas`, `gpx_file_tags`, 
> `way_segments`)
> 
> Future:
> - Innodb does not support indexes on spatial types.
> 
> Transaction support likely to create problems? Code tweaks?
> SET AUTOCOMMIT = 1???
> 
> Recommendations for InnoDB tuning? Defaults OK/Suitable?
> 
> Any comments?
> 
> Current DB Schema here: http://svn.openstreetmap.org/sql/mysql-schema.sql
> 
> 
> 
> 

-- 
have fun,

SteveC steve at asklater.com http://www.asklater.com/steve/