1

is there any way to determine from server statistics* running MySQL, how much traffic(bits per second) WOULD replication consume between 2 servers, without setting-up the actual replication between those 2 servers ?

*Statistics = mysql commands over time, Disk IO per second, traffic, ram cpu...(e.g. CollectD rrd data)

Thank you. :)

1

On Apri 18, 2012, I addressed a similar question : MySQL : Does 'bytes_sent' and 'bytes_received' include mysqldump data?

I mentioned the following

You can find out what these are for all connections past and present by doing

SELECT * FROM information_schema.global_status
WHERE variable_name IN ('Bytes_received','Bytes_sent');

You can do so an individual session by doing

SELECT * FROM information_schema.session_status
WHERE variable_name IN ('Bytes_received','Bytes_sent');

I have good news and bad news. First ...

BAD NEWS

You cannot get a SQL thread to query information_schema.session_status. That's a violation of the first and fifth amendments (if applied against Replication's SQL thread).

GOOD NEWS (Sort of)

The only way you can track any kind of byte traffic is to do the following

  • Enable Binary Logging on the Slave
  • Dump all binlogs on the Slave using

    mysqlbinlog --server-id=(server_id of the Master) ... > Binlogs_From_Master.txt

  • Take the filesize of Binlogs_From_Master.txt and that's your best guess.

Subtract that filesize from whatever Bytes_received is, and that's the internal byte traffic on the Slave.

CAVEAT

You will have to set up replication to find that number out.

As an alternative, you could just use one the the following from the Master

as an educated guess.

Give it a Try !!!

  • Thank you for excellent answer. I reviewed how does MySQL replication work and (1.)it seems that for a rough guess of how much traffic will replication consume I can use: EST=size(BINLOG) x NUMBER_OF_SLAVES, am I partly correct or completely wrong ?:) (2.) SHOW GLOBAL bytes_sent on master can not be used if I have no slave hosts, can it ? – no6 Apr 14 '14 at 1:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.