Master Slave query time discrepancy

[drew]

Hi all,

The problem I am trying to solve is this:

I run exactly the same query on both a master db server and also on one of its slaves. On the master, it takes on average half a second to run. However, on the slave it takes upwards of 2 minutes and usually about 3 and a half minutes. I have several slaves running off the same master and all the slaves act the same way with this query.

The variables set in my.cnf are all the same values and running the SHOW VARIABLES statement confirms this. On the net I have read that the sort_buffer may be important in this matter. I have the sort_buffer set to 4M on both the master and the slave.

The average queries per second on the master is about 67, whereas on the slave the average queries per second is about 126.

An explain of the query tells me that it is using Using where a temporary table and filesort. And that it only has to do about 150,000 rows. It also uses the correct key. This information is probably not necessary as it works perfectly on the master just not on the slave.

I have run a myisamchk -raS over all the tables on the slave, so the indexes are not busted.

Has anyone seen this before or do they have any advice that could help me solve the problem? I am at a loss to work out why exactly the same query works well on the master but not on the slave where the only difference is the read load.

Thanks in advance.

Drew