Some Reasonable Defaults for MySQL Settings

"slave_net_timeout

"There is no heartbeat mechanism that helps slaves to know that their connection to a master hasn’t vanished. What can happen is a network connection gets broken between the two in a way that neither detects. This is typically a firewall/router issue or something that neither host initiates or sees, so neither one is able to send (or receive) a TCP packet that would normally begin to shut down the connection. This is especially true of replication topologies that involve crossing significant distances where multiple networks and providers may be involved.

"MySQL uses a simple timeout mechanism to detect this hopefully rare occurrence. If the slave I/O thread has not seen anything from the master in slave_net_timeout seconds, it will connect and then attempt to reconnect and continue replicating. That mechanism works very well and allows slaves to deal with the occasional network glitch.

"Unfortunately the default value for this variable is 3600. That’s a full hour of time that passes before the slave decides to give up and try starting with a new connection. So not only do you run the risk of a slave being nearly an hour behind on replication, you may find that this is trickier to detect than you might think!"

Complete Story

Related Stories:

• Hacking with CouchDB(Nov 09, 2009)
• An Introduction to CouchDB(Nov 02, 2009)
• Data By The Numbers(Oct 28, 2009)
• NoSQL: Distributed and Scalable Non-Relational Database Systems(Oct 19, 2009)
• Everything is Unix(Oct 13, 2009)
• Expecting to Fail(Sep 26, 2009)
• Database Storage Performance Testing in a Hurry(Sep 22, 2009)
• The Curious Case of the Failing Connections, Part 2(Sep 15, 2009)
• Redis: Lightweight key/value Store That Goes the Extra Mile(Sep 02, 2009)
• Quick and Dirty MySQL Performance Troubleshooting(Aug 20, 2009)
• MySQL Sandbox: Treat MySQL Instances like Virtual Machines(Jul 28, 2009)
• MySQL Performance from the Start(Jul 14, 2009)