Hi Moritz,
Thus, my questions:
- what's wrong with that query? I know they are big tables, but
according to EXPLAIN, this should be fast enough, because mysql's seeing
the indexes just fine.
As others have said, likely nothing wrong with the query per se. It
just sounds like you're hitting disk instead of cache.
- how can i tell mysql to switch off whatever cache or performance thing
it is that makes debugging such a PITA? is there a way to disable it
just for this query? (SELECT SQL_NO_CACHE doesn't seem to make a difference)
There are a few caches at play here:
1. query cache - caches entire query results; not what you're hitting here
2. storage engine caches - key_buffer_size for MyISAM and
innodb_buffer_pool_size for InnoDB; caches index data for MyISAM and
index and row data (technically pages) for InnoDB
3. OS disk cache; caches any data accessed from disk, not tunable for
MyISAM, tunable using innodb_flush_method=O_DIRECT for InnoDB
I would think what is happening is that you don't have a large enough
cache at level 2 above, which means your data doesn't fit in cache
inside MySQL, so it gets cached in the OS at level 3, which is rather
fickle and will page things out when you may not expect it.
Alternately, your system is busy enough that things still get paged out
at level 2 above in which case the solution isn't necessarily as simple.
Regards,
Jeremy
--
high performance mysql consulting
www.provenscaling.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
