473,842 Members | 1,633 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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
Jan 27 '06 #1
3 2028
"Chad J. Lemmer" <cj******@chart er.net> 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
Jan 27 '06 #2
>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
Jan 27 '06 #3
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" <go***********@ burditt.org> wrote in message
news:11******** *****@corp.supe rnews.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

Jan 29 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
3271
by: I.P. | last post by:
Hi, it's my story. I have two 4.0.14 mysql server on one machine with win XP Professional polish version. First acts as master: on port 3300 Second acts as slave: on port 3301 below my configuration:
0
2155
by: I.P. | last post by:
No one has replied to my post. ----- Original Message ----- From: "I.P." <jancio_wodnik@wp.pl> To: <mysql@lists.mysql.com> Sent: Monday, August 18, 2003 1:01 PM Subject: mysql 4.0.14 + replication + windows XP PROF Hi, it's my story.
10
1942
by: Fred the man | last post by:
Hi, Some of our customers have remote offices. I was wondering if it'd be safe to have a DBMS running at their central office, and have our client application running on hosts in the branches connect to it through a VPN via the Net? What happens if the connection goes south while a branch office was making changes? Does the DBMS just rollbacks changes automatically after a time-out?
2
11217
by: AMD | last post by:
Hi, I would like to have MySQL use a mapped network drive. I'd like to do this in case there is a failure of the mysql machine, I can just replace it with a new machine pointing to the same network drive. Is this possible at all? Thanks, Andre M. Descombes
39
8440
by: Mairhtin O'Feannag | last post by:
Hello, I have a client (customer) who asked the question : "Why would I buy and use UDB, when MySql is free?" I had to say I was stunned. I have no experience with MySql, so I was left sort of stammering and sputtering, and managed to pull out something I heard a couple of years back - that there was no real transaction safety in MySql. In flight transactions could be lost.
2
4935
by: Beda Christoph Hammerschmidt | last post by:
MySQL, Access, ODBC, Replication Hello, i have a central mysql database that is accesed by multiple clients running MS Access with ODBC. This works fine if Access has a permanent connection to mysql. But now, i want MS Access to operate offline (e.g. on notebook in the
67
12567
by: Bob Powell | last post by:
To whom it may concern: I find the recent articles in various trade publications a little disturbing due to the lack of PostgrSQL mention. I continue to see articles about how IBM may be considering MYSQL for development an open_source web database. Why isn't PostgreSQL being considered or talked about by major industry giants? As a DBA I know that Postgres is far superior to MYSQL but if the industry directs it's energies towards...
13
2318
by: Jim Hubbard | last post by:
I have a client that desires a new application for use at several stores in different cities. He wants "real time" access to all store data and the ability to continue to run locally even if the internet connection goes down. It occurs to me that a local MySQL instance should definitely be in each store to continue work if the net is down for any reason. But, what is the best way to synchronize/share data among all of the stores? ...
0
12904
Coldfire
by: Coldfire | last post by:
Since i cannot show the differences in a two-column like table. I am first putting MS SQL Server 2005 and then MySQL 5.x. MS SQL Server 2005 Brief Overview - SQL Server is a full-fledged database system developed specifically for large enterprise databases. All advanced features of a relational database are fully implemented. - Once you purchase the product, you are only limited to the Sybase-derived engine.
0
9715
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10612
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10672
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10314
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7858
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7038
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5696
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4089
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3145
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.