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
