List:Replication« Previous MessageNext Message »
From:Rick James Date:January 27 2010 5:57pm
Subject:Re: Replication Speed
View as plain text  
Another approach to monitoring replication delay...
1. Inject a "heartbeat" into the Master.  It need not be more than UPDATEing
a single-cell table with the current time.
2. Poll that table on the slave to see how far behind things are.  Note:
NOW(), etc, even when replicated, reflect the value that was stored on the
master, even in SBR.

Comments:
* 1-second resolution.
* With NNTP, you can time slew is not a problem; otherwise syncing the
clocks _could_ be an issue.
* This handles "relay" (and multi-master with slaves) 'correctly'.  In
contrast, Seconds_behind_master is relative to the immediate "master" (the
relay machine), not the original inserter.  If replication to the relay
stops, the slaves hanging off it will say Seconds_behind_master=0 even
though nothing is coming through.

There is another flaw in all the techniques (including this one)...  A
1-hour query (eg a big ALTER) will lead to delay estimates that are _up to_
1-hour off.  None of the techniques compensates for whether this query is
just starting or nearly finished.  You really want to know when will the
query _after_ the ALTER be finished (relative to when it was finished on the
original Master).

-- 
Rick James
MySQL Geek



> From: Suresh Kuna <sureshkumarilu@stripped>
> Date: Wed, 27 Jan 2010 22:03:40 +0530
> To: Miguel Araújo <noradone@stripped>
> Cc: Johan De Meersman <vegivamp@stripped>,
> <replication@stripped>
> Subject: Re: Replication Speed
> 
> It is the size of the binlog that it uses for reading BTW.
> 
> 
> 2010/1/27 Miguel Araújo <noradone@stripped>
> 
>> Yes, the replication has been setup and it's running. Thanks for the
>> clarification Suresh. But, how can I read the values of read_master and
>> exec_master, outside the mysql client? By that I mean in a external
>> application.
>> Thanks.
>> 
>> On Jan 27, 2010, at 4:04 PM, Suresh Kuna wrote:
>> 
>> Hope the replication has been setup and running. We no need to bother about
>> the which relay log the slave is reading. It depends on how much lag behind
>> the current position of the read_master and exec_master.
>> 
>> Thanks
>> Suresh Kuna
>> MySQL DBA
>> 
>> 2010/1/27 Miguel Araújo <noradone@stripped>
>> 
>>> Suresh - Yes, I know that fact. That's why I will use virtual machines to
>>> do the tests and analyze the results.
>>> I hope you can help me with the binary logs doubt.
>>> Thanks.
>>> 
>>> Regards,
>>> Miguel Araújo
>>> 
>>> On Jan 27, 2010, at 3:03 PM, Suresh Kuna wrote:
>>> 
>>> Miguel - It varies from machine to machine, network to network, depends on
>>> several factors like load, the sql you tested and more over all hardware
>>> components present in the server.
>>> 
>>> Thanks
>>> Suresh Kuna
>>> MySQL DBA
>>> 
>>> 2010/1/27 Miguel Araújo <noradone@stripped>
>>> 
>>>> I want to do do a tpcc benchark on a replicated database with a
>>>> relatively large number of replicas.
>>>> 
>>>> The major timing issue is right what you've said "when an event will get
>>>> *executed* on the slave", but I don't want to measure the occasional
>>>> multisecond delay. As you said, replication is generally fast (in order
> of
>>>> milliseconds), and my intention is to measure this delay in the order of
>>>> milliseconds.
>>>> 
>>>> So, my approach is to read (each 10ms) the binary logs on master and
> each
>>>> slave as the benchmark runs, and compare the position to finally
> calculate
>>>> the delay time. And, like I've started questioning in this thread, my
> doubt
>>>> is on what relay log to read.
>>>> 
>>>> Thanks.
>>>> 
>>>> Regards,
>>>> Miguel Araújo.
>>>> 
>>>> On Jan 27, 2010, at 2:35 PM, Johan De Meersman wrote:
>>>> 
>>>>> The event *arrives* pretty soon after - unless you have a flaky
>>>> network, that's not really you should worry about.
>>>>> 
>>>>> The major timing issue will be when an event will get *executed* on
> the
>>>> slave, and the answer is: it varies. Events get executed sequentially on
>>>> the
>>>> slave, so although it will generally be fast (order of milliseconds), a
>>>> huge
>>>> update or other long-running query will block the queue until it
> finishes,
>>>> and thus you could see the occasional multisecond delay.
>>>>> 
>>>>> It all depends on your workload, though.
>>>>> 
>>>>> 2010/1/27 Miguel Araújo <noradone@stripped>
>>>>> Hello.
>>>>> 
>>>>> I want to measure replication speed for a great number of replicas.
> By
>>>> that I mean how soon an event arrives at the slave after being logger to
>>>> the
>>>> master's binary log.
>>>>> 
>>>>> So, I've started developing an application to read the replication
> logs
>>>> in order to compare them (compare by time, the log position on the
> master
>>>> node with the log position on the slave's). My first approach was to use
>>>> the
>>>> mysqlbinlog to get the log position, in the 'Master_Log_File'. But in
> the
>>>> slave's the last position that the SQL thread has read and executed is
> the
>>>> 'Relay_Log_Pos', right?
>>>>> I have to read the slave's relay log, in order to get the last
> position
>>>> on the Master's log executed? And compare with the master's log?
>>>>> 
>>>>> Thank you.
>>>>> 
>>>>> Regards,
>>>>> Miguel Araújo
>>>>> 
>>>>> 
>>>>> 
>>>>> --
>>>>> MySQL Replication Mailing List
>>>>> For list archives: http://lists.mysql.com/replication
>>>>> To unsubscribe:
>>>> http://lists.mysql.com/replication?unsub=1
>>>>> 
>>>>> 
>>>>> 
>>>>> 
>>>>> --
>>>>> Bier met grenadyn
>>>>> Is als mosterd by den wyn
>>>>> Sy die't drinkt, is eene kwezel
>>>>> Hy die't drinkt, is ras een ezel
>>>> 
>>>> 
>>> 
>>> 
>>> --
>>> Thanks
>>> Suresh Kuna
>>> MySQL DBA
>>> 
>>> 
>>> 
>> 
>> 
>> --
>> Thanks
>> Suresh Kuna
>> MySQL DBA
>> 
>> 
>> 
> 
> 
> -- 
> Thanks
> Suresh Kuna
> MySQL DBA

Thread
Replication SpeedMiguel Araújo27 Jan
  • Re: Replication SpeedErwan Ben Souiden27 Jan
    • Re: Replication Speedsureshkumarilu27 Jan
  • Re: Replication SpeedMarcus Bointon27 Jan
  • Re: Replication SpeedJohan De Meersman27 Jan
    • Re: Replication SpeedMiguel Araújo27 Jan
      • Re: Replication SpeedSuresh Kuna27 Jan
        • Re: Replication SpeedMiguel Araújo27 Jan
          • Re: Replication SpeedSuresh Kuna27 Jan
            • Re: Replication SpeedMiguel Araújo27 Jan
              • Re: Replication SpeedSuresh Kuna27 Jan
                • Re: Replication SpeedRick James27 Jan
  • Re: Replication SpeedAndrej Pintar27 Jan
Re: Replication SpeedMiguel Araújo27 Jan