List:Replication« Previous MessageNext Message »
From:Rick James Date:April 29 2010 8:08pm
Subject:Re: Slow query on replication master and slave
View as plain text  
There are LOTS of things that could have cause this.

First, it cannot use the index because of the way the WHERE is written.  
Suggest changing to
WHERE timeA <= 1266143632

MyISAM or InnoDB?
How much RAM?  (on each machine)
SHOW VARIABLES LIKE '%buffer';  -- on each machine!
Run the query twice -- it may run faster the second time.  (10x faster 
is my rule of thumb for cold system versus second run)

What do you mean by "static"?

That one query would be even faster this way (mathematically equivalent)
SELECT *, ABS(timeA-1266143632) FROM tableA WHERE timeA <= 1266143632 
ORDER BY *timeA DESC* LIMIT 1;
That should take more like 0.01 sec.-- on any machine!  (OK, a cold 
machine might take more like 0.05s.)



On 4/29/10 12:50 AM, Johan De Meersman wrote:
> I'm sure I answered to this exact post yesterday :-)
>
> On Wed, Apr 28, 2010 at 11:19 PM, Kandy Wong<kandyw@stripped>  wrote:
>
>    
>> Hi,
>>
>> Is it true that the performance of running a query on a live replication
>> master and slave has to be much slower than running a query on a static
>> server?
>>
>> I've tried to run the following query on a replication master and it
>> takes 1 min 13.76 sec to finish.
>> SELECT *, ABS(timeA-1266143632) as distance FROM tableA WHERE timeA -
>> 1266143632<= 0  ORDER BY distance LIMIT 1;
>>
>> And if I run it on the replication slave, it takes 24.15 sec.
>> But if I dump the whole database to another machine as static, it only
>> takes 3.70 sec or even less to finish.
>>
>> The table has 386 columns and timeA is an index.
>>
>> Is there a way to improve the query or any other factors that would
>> affect the performance?
>>
>> Thanks.
>>
>> Kandy
>>
>>
>> --
>> MySQL Replication Mailing List
>> For list archives: http://lists.mysql.com/replication
>> To unsubscribe:
>> http://lists.mysql.com/replication?unsub=1
>>
>>
>>      
>
>    

Thread
Slow query on replication master and slaveKandy Wong28 Apr
  • Re: Slow query on replication master and slaveJohan De Meersman29 Apr
    • Re: Slow query on replication master and slaveRick James29 Apr
  • Re: Slow query on replication master and slaveMats Kindahl30 Apr