[OSM-dev] Too many slow queries in db
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?
Silicon Circus Ltd.
More information about the dev