[OSM-dev] speeding the db
Grant Slater
openstreetmap at firefishy.com
Mon Mar 26 10:49:04 BST 2007
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
More information about the dev
mailing list