slow query on replication master and slave

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 General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: slow query on replication master and slave

--001636c92f215f863604854680d3
Content-Type: text/plain; charset=ISO-8859-1

On Wed, Apr 28, 2010 at 12:17 AM, Kandy Wong 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

--001636c92f215f863604854680d3--