List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:April 28 2010 6:55am
Subject:Re: slow query on replication master and slave
View as plain text  
On Wed, Apr 28, 2010 at 12:17 AM, Kandy Wong <kandyw@stripped> wrote:

> 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.

The slave most likely didn't have the table data in memory, so had to do a
disk read. Also, if you've performed that query before on your master, it
may still have been in the query cache.

Add the SQL_NO_CACHE keyword right after the word "select" to disable the
query cache when checking query performance.

> But if I dump the whole database to another machine as static, it only
> takes 3.70 sec or even less to finish.

When you load just that table, the data you just inserted will still have
been in memory and/or in the OS cache, avoiding a disk read.

> Is there a way to improve the query or any other factors that would affect
> the performance?

Rewrite your where clause to "where timeA <= 1266143632" - that will allow
you to put an index on timeA and benefiting from it. Also, ordering by timeA
should give the same ordering as by distance, and may also use the index to
sort instead of a filesort.

Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

slow query on replication master and slaveKandy Wong28 Apr
  • Re: slow query on replication master and slaveJohan De Meersman28 Apr