List:General Discussion« Previous MessageNext Message »
From:Jeremy Cole Date:November 11 2007 6:11am
Subject:Re: mysql eluding query debugging?
View as plain text  
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
Thread
mysql eluding query debugging?Moritz von Schweinitz9 Nov
  • Re: mysql eluding query debugging?Dan Nelson9 Nov
  • Re: mysql eluding query debugging?Jeremy Cole11 Nov
Re: mysql eluding query debugging?Moritz von Schweinitz9 Nov
  • Re: mysql eluding query debugging?Brent Baisley10 Nov