MySql Replication and network speed

We purchased 2 new HP DL385 servers that will be used for MySQL 5 in a
replication environment.

Server 1 (Master) is connected gigabit to our Cisco 3550 core switch

The Cisco 3550 core switch is connected gigabit over fiber to a Cisco 3548
switch in another building.

Server 2 (Slave) is connected to the Cisco 3548 on a 100MB switched port.

My question is whether or not the 100MB connection is going to be good
enough to do replication, or if I have to invest in getting gigabit all the
way to the slave server?

Thanks for any input or experience you can share.

- Chad J. Lemmer

Re: MySql Replication and network speed

"Chad J. Lemmer" wrote:

> My question is whether or not the 100MB connection is going to be good
> enough to do replication, or if I have to invest in getting gigabit all the
> way to the slave server?

It depends on how much you write on the Master machine. To be more
consise: how many and how big data modifying SQL statements you have.
As a very rough estimation MySQL replication sends all data modifying
*statements* from the master to each slave. No row data is sent (but
this will change with row based replication, coming in 5.1). So if
you delete 100GB rows on the master with TRUNCATE TABLE, only a few
bytes are sent to the slave(s). However UPDATE and INSERT are more
expensive in this respect.

Typically MySQL replication is cpu bound rather than I/O bound, because
all statements are serialized on the slave.


XL

Re: MySql Replication and network speed

>We purchased 2 new HP DL385 servers that will be used for MySQL 5 in a
>replication environment.
>
>Server 1 (Master) is connected gigabit to our Cisco 3550 core switch
>
>The Cisco 3550 core switch is connected gigabit over fiber to a Cisco 3548
>switch in another building.
>
>Server 2 (Slave) is connected to the Cisco 3548 on a 100MB switched port.
>
>My question is whether or not the 100MB connection is going to be good
>enough to do replication, or if I have to invest in getting gigabit all the
>way to the slave server?

So how much traffic in queries will the servers have? I can envision
a setup where replication by slow dialup modem is more than enough
(say, for the IP list of blocked mail servers). How up to date
must the slave be? There's a difference between my address book
(7 days for updates to take is not a big deal. I don't know if
anyone has implemented replication by postal service or carrier
pigeons, but it probably wouldn't be too slow.) and stock market
quotes (10 minutes delay may be a VERY big deal).

Replication tends to be more CPU-intensive than network-intensive.
If you update 10,000 rows, it doesn't send the 10,000 rows, it sends
the query that updated 10,000 rows (which is much shorter, but the
slave also has to go to the effort to figure out which 10,000 rows
need changing and how).

Gordon L. Burditt

Re: MySql Replication and network speed

Our current database (in Visual FoxPro) contains less than 10GB of data.
We're just moving into MySQL as we re-design and re-write our current
manufacturing (and accounting) system. We have about 150 users, so in
comparison to what MySQL is capable of, I get the impression we're just
touching the tip of the iceberg, at least for now until we get everything
re-written and implemented.

Thanks for all the info.

- Chad J. Lemmer

"Gordon Burditt" wrote in message
news:11tkp2h2vjrjve6@corp.supernews.com...
> >We purchased 2 new HP DL385 servers that will be used for MySQL 5 in a
>>replication environment.
>>
>>Server 1 (Master) is connected gigabit to our Cisco 3550 core switch
>>
>>The Cisco 3550 core switch is connected gigabit over fiber to a Cisco 3548
>>switch in another building.
>>
>>Server 2 (Slave) is connected to the Cisco 3548 on a 100MB switched port.
>>
>>My question is whether or not the 100MB connection is going to be good
>>enough to do replication, or if I have to invest in getting gigabit all
>>the
>>way to the slave server?
>
> So how much traffic in queries will the servers have? I can envision
> a setup where replication by slow dialup modem is more than enough
> (say, for the IP list of blocked mail servers). How up to date
> must the slave be? There's a difference between my address book
> (7 days for updates to take is not a big deal. I don't know if
> anyone has implemented replication by postal service or carrier
> pigeons, but it probably wouldn't be too slow.) and stock market
> quotes (10 minutes delay may be a VERY big deal).
>
> Replication tends to be more CPU-intensive than network-intensive.
> If you update 10,000 rows, it doesn't send the 10,000 rows, it sends
> the query that updated 10,000 rows (which is much shorter, but the
> slave also has to go to the effort to figure out which 10,000 rows
> need changing and how).
>
> Gordon L. Burditt