Andrew Morgan on Databases

We recently hosted a live webinar covering advanced MySQL Replication topics as well as the latest developments. The webinar charts and replay are now available here. Below, you’ll find the questions raised by the audience together with the responses given.

More details on what was covered…

The biggest Web sites in the world rely on MySQL Replication to scale-out and provide High Availability for their data. Extend your knowledge of how MySQL Replication works and what you can achieve with it; join us for this technical webinar to explore some of the more advanced replication architectures as well as some of the latest product developments:

• Replication topologies, including master-slave, circular and multi-master
• Load balancing and query splitting
• Data aggregation with multi-source replication
• Global Transaction IDs and auto-failover with recovery
• Getting the best replication throughput
• Heterogeneous replication with the Binlog API

Questions & Answers

• Is the server_uuid constant in the lifetime of a MySQL server?: Yes, server_uuid it’s persisted in a file in the data_dir.
• How are the servers syncronized after a failure?: When slave connects to master it will inform master which GTIDs it already has, (whether just received or actually or applied), and the master will send what is missing.
• Are all the features being described available now in the latest community MySQL version?: Everything that is described is available to the community. Some features are currently in Development Milestone or labs releases but that is made clear in the charts.
• What is the maximum number of Slaves per Master and are there restrictions to the distance between Master and Slave?: There is no maximum number of slaves per master, that value depends on hardware and workload. There is an overhead on the master for each of the slaves (though this is reducing); you always have the option of replicating to another one or MySQL Servers and then use them as replication masters to fan out to many more slaves (those servers can even use the Black Hole storage engine so that they don’t even store the data. No distance limits, but with longer distances the network latency will increase. If using the default asynchronous replication, this latency has not effect other than possibly the slave(s) running slightly further behind but if using semi-synchronous replication then the transactions will take longer to commit.
• Is there a maximum number of worker threads that can be configured, or is it just dependient on your server hardware?: The maximum number of worker thread is 1024, but the real limitation is hardware.
• Is there a form of automatic client rerouting as a result of Switch/Failover?: Yes – MySQL Fabric which is also covered in this session.
• what’s the resolution of the timestamp?: Replication timestamp resolution is microseconds.
• Are all of these replication features available in both Synchronous and Asynchronous modes?: There is no synchronous mode, but they all work with both asynchronous and semi-synchronous replication.
• Why is semi-sync replication only available as plugin? It makes it harder to setup with the present restrictions.: By implementing features as plugins, we can evolve the software faster by implementing them in new modules rather than in the large, complex MySQL Server code base. It also means that we can iterate more frequently as it doesn’t need to be tied to a MySQL Server release.
• Does semi-synchronous replication wait for all slaves or just a single one?: You can specify how many slaves need to respond with the rpl_semi_sync_master_wait_slave_count option.
• In case of one master one slave, where slave can overtake master role in case of crash. Won’t it introduce split-brain scenario ? Does mysql have rollback settings in case of master crash?: Monitoring tools should ensure that the master has crashed crashed, or if it suspects that it became irresponsible then it should kill the master before performing the failover. Alternatively, take a look at MySQL Fabric.
• For now I have impression that if I want clients to automatically load balance read & writes between masters/slaves then I should do this using mysql proxy. Is this still the best practice?: What language are you using – this functionality is built into some connectors (for exampe for PHP and Java)? Alternatively you could look at MySQL Fabric or hardware or software load ballancers.
• How are autoincrement columns handled in MySQL Fabric when you have mulitiple HA Groups?: The first thing to point out is that an auto-increment column cannot be used as the sharding key. You can use auto_increment_increment and auto_increment_offset to make sure that you don’t repeat values on different shards (e.g. if you have 2 shards then odd values coule be on one and even on the other.
• Is MySQL Fabric queried for every transaction or query (and so becomes a single-point-of-falire)?: No, the connectors hold a cache of the routing data and so will use that rather than constantly querying the MySQL Fabric process.
• Does the Replication include DDL changes, not just DML?: Yes it does.
• How do I scale out write operation in MySQL 5.6: That’s where MySQL Fabric comes in, when we reach the write saturation point on master.
• Is NDB storage engine is good option for write operation scale out?: It can be a great solution but it will depend on how your data is structured and accessed. Take a look at theMySQL Cluster Evaluation Guide – Designing, Evaluating and Benchmarking MySQL Cluster as this will help you figure out if MySQL Cluster (NDB) is going to be the right option for your application
• But fabric still in beta version right?: Correct – it isn’t ready for production yet (at the time of writing this is true but be sure to check if you’re reading this laster!).
• Are the benchmarks of sync_binlog done with SSD or HDD machines?: SSD – see this blog post. Should we expect the same results with HDDs?: Yes, but with different orders of magnitude. Note that all benchmarks were made with SSDs, so we are comparing equal hardware on 5.5 and 5.6.