[OSM-talk] [OSM-dev] speeding the db

[Ray Booysen]

Fantastic!  Thats great.

On 3/26/07, SteveC <steve at asklater.com> wrote:
>
> 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/
>
> _______________________________________________
> dev mailing list
> dev at openstreetmap.org
> http://lists.openstreetmap.org/cgi-bin/mailman/listinfo/dev
>



-- 
Ray Booysen
raybooysen at rjb.za.net
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/talk/attachments/20070327/b07ecc2c/attachment.html>