[OSM-dev] speeding the db
SteveC
steve at asklater.com
Mon Mar 26 22:54:39 BST 2007
oh and I did the same for the mapnik layer so it too should be much quicker.
SteveC wrote:
> 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/
More information about the dev
mailing list