[OSM-dev] Too many slow queries in db

Jon Bright jon at siliconcircus.com
Tue Sep 4 10:46:40 BST 2007


Tom Hughes wrote:
> Seriously, if you've got a magic way to make a database query
> a 100 million row table in the blink of an eyelid I'll be only
> to happy to deploy it.

We have a 3.4 billion row table and various other tables ranging from 
500 million rows to 2 billion or so, all running under MySQL using 
MyISAM.  Queries on the 3.4 billion row table run in more or less the 
blink of an eyelid (test queries range from 0.03 to 1.5s).  In another 
more insert-heavy table (3 insert/second more or less continuously, 
peaking somewhere around 20 inserts/second) albeit with only 20-odd 
million rows, the same is true.  This is all commodity hardware (if 
mildly expensive commodity hardware).

None of this helps you, of course.  But in order to be able to help, it 
would be necessary to see more details of your query load.  In 
particular, is the slow query log available to look at somewhere?  Are 
the mysql config and show status outputs at 
http://wiki.openstreetmap.org/index.php/Database still current?

Jon Bright
Silicon Circus Ltd.

More information about the dev mailing list