From: Rick James Date: January 27 2010 5:57pm Subject: Re: Replication Speed List-Archive: http://lists.mysql.com/replication/1805 Message-Id: MIME-Version: 1.0 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: quoted-printable Another approach to monitoring replication delay... 1. Inject a "heartbeat" into the Master. It need not be more than UPDATEin= g 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=3D0 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 th= e original Master). --=20 Rick James MySQL Geek > From: Suresh Kuna > Date: Wed, 27 Jan 2010 22:03:40 +0530 > To: Miguel Ara=FAjo > Cc: Johan De Meersman , > Subject: Re: Replication Speed >=20 > It is the size of the binlog that it uses for reading BTW. >=20 >=20 > 2010/1/27 Miguel Ara=FAjo >=20 >> 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. >>=20 >> On Jan 27, 2010, at 4:04 PM, Suresh Kuna wrote: >>=20 >> Hope the replication has been setup and running. We no need to bother ab= out >> the which relay log the slave is reading. It depends on how much lag beh= ind >> the current position of the read_master and exec_master. >>=20 >> Thanks >> Suresh Kuna >> MySQL DBA >>=20 >> 2010/1/27 Miguel Ara=FAjo >>=20 >>> 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. >>>=20 >>> Regards, >>> Miguel Ara=FAjo >>>=20 >>> On Jan 27, 2010, at 3:03 PM, Suresh Kuna wrote: >>>=20 >>> Miguel - It varies from machine to machine, network to network, depends= on >>> several factors like load, the sql you tested and more over all hardwar= e >>> components present in the server. >>>=20 >>> Thanks >>> Suresh Kuna >>> MySQL DBA >>>=20 >>> 2010/1/27 Miguel Ara=FAjo >>>=20 >>>> I want to do do a tpcc benchark on a replicated database with a >>>> relatively large number of replicas. >>>>=20 >>>> The major timing issue is right what you've said "when an event will g= et >>>> *executed* on the slave", but I don't want to measure the occasional >>>> multisecond delay. As you said, replication is generally fast (in orde= r of >>>> milliseconds), and my intention is to measure this delay in the order = of >>>> milliseconds. >>>>=20 >>>> So, my approach is to read (each 10ms) the binary logs on master and e= ach >>>> slave as the benchmark runs, and compare the position to finally calcu= late >>>> the delay time. And, like I've started questioning in this thread, my = doubt >>>> is on what relay log to read. >>>>=20 >>>> Thanks. >>>>=20 >>>> Regards, >>>> Miguel Ara=FAjo. >>>>=20 >>>> On Jan 27, 2010, at 2:35 PM, Johan De Meersman wrote: >>>>=20 >>>>> The event *arrives* pretty soon after - unless you have a flaky >>>> network, that's not really you should worry about. >>>>>=20 >>>>> The major timing issue will be when an event will get *executed* on t= he >>>> 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 finis= hes, >>>> and thus you could see the occasional multisecond delay. >>>>>=20 >>>>> It all depends on your workload, though. >>>>>=20 >>>>> 2010/1/27 Miguel Ara=FAjo >>>>> Hello. >>>>>=20 >>>>> I want to measure replication speed for a great number of replicas. B= y >>>> that I mean how soon an event arrives at the slave after being logger = to >>>> the >>>> master's binary log. >>>>>=20 >>>>> So, I've started developing an application to read the replication lo= gs >>>> in order to compare them (compare by time, the log position on the mas= ter >>>> node with the log position on the slave's). My first approach was to u= se >>>> 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 positi= on >>>> on the Master's log executed? And compare with the master's log? >>>>>=20 >>>>> Thank you. >>>>>=20 >>>>> Regards, >>>>> Miguel Ara=FAjo >>>>>=20 >>>>>=20 >>>>>=20 >>>>> -- >>>>> MySQL Replication Mailing List >>>>> For list archives: http://lists.mysql.com/replication >>>>> To unsubscribe: >>>> http://lists.mysql.com/replication?unsub=3Dvegivamp@stripped >>>>>=20 >>>>>=20 >>>>>=20 >>>>>=20 >>>>> -- >>>>> Bier met grenadyn >>>>> Is als mosterd by den wyn >>>>> Sy die't drinkt, is eene kwezel >>>>> Hy die't drinkt, is ras een ezel >>>>=20 >>>>=20 >>>=20 >>>=20 >>> -- >>> Thanks >>> Suresh Kuna >>> MySQL DBA >>>=20 >>>=20 >>>=20 >>=20 >>=20 >> -- >> Thanks >> Suresh Kuna >> MySQL DBA >>=20 >>=20 >>=20 >=20 >=20 > --=20 > Thanks > Suresh Kuna > MySQL DBA