[OSM-dev] database statistics

Claudomiro Nascimento Jr. claudomiro at gmail.com
Mon Mar 26 08:07:19 BST 2007


Hi,

I did some research this weekend and definitely MyISAM could be (extrremaly)
fast for sites with little updating (10% of queries max) comparing with data
retrieval but not for OSM - The normal OSM utilization as show in the logs
is more than 50% of updates/inserts. The problem with MyISAM is that every
update/insert locks the hole table, blocking all subsequent queries on the
same table.

The consequences for the database utilization could be checked on two charts
that show http serving in a "quiet" moment
(http://wiki.openstreetmap.org/images/4/4f/Bysecond_200708111600.png )
compared with a busy one
(http://wiki.openstreetmap.org/images/9/92/Bysecond_200708112000b.png
). The "gaps" in this last graph are certanly periods when the backlog of
queries fron the web layer reached some limit (apache threads? connection
pool size?) and everything has to wait for about 1-3 minutes until MySQL is
available again.

Lock contention could be attenuated with some code changes
(http://dev.mysql.com/doc/refman/5.0/en/table-locking.html
), but before risking entering in a bigger problem, the right thing todo
IMHO would be level up the system monitoring and do a lot of tests on
simulated envs.

For better monitoring I suggest 2 things:

    * Collect MySQL stats periodicaly (every hour) for posterior
analysis/crosschecking. This could be something like:
        mysql -e "SHOW STATUS" >> /var/log/mysql-stats

    * Identify costly requests and/or heavy load periods. For this, the
Apache time-to-serve key (as suggested by Frederik) could be used. On
httpd.conf, add the following line:
           LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\"
\"%{User-Agent}i\" %D" combinedtime
      and change this one:
           CustomLog logs/access.log combinedtime

I Hope to load a full db this week to help on the tunnings effort.

[]s



2007/3/22, SteveC <steve at asklater.com>:
>
> Apache anonymised api data with description:
>
> http://wiki.openstreetmap.org/index.php/Database#Data_statistics
>
> Please play, make cool viz and stats.
>
> If there are _any_ problems with the anonymity side of things (eg, it
> not doing what it's supposed to) please contact me.
>
> 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
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/dev/attachments/20070326/3aaed414/attachment.html>


More information about the dev mailing list