First of all, it seems Oracle still does not hesitate to introduce new features and behavior in the process. Just check these major changes:
- Starting with 5.6.17, MySQL now supports rebuilding regular and partitioned
InnoDB tables using
InnoDB tables using
A colleague and I have been looking at GTID on MySQL recently and you may be interested in the blog post that results from that. You can see it here. http://blog.booking.com/mysql-5.6-gtids-evaluation-and-online-migration.html.
With datacenters being stretched by resource-intensive applications, more and more businesses are outgrowing their existing in-house capacity and having to reconfigure their IT operations. But how do you migrate a busy application to a totally new data center without downtime? How will the application scale in a virtualized cloud environment? And how do you guard against cloud server failures and keep a high level of uptime?
In this example, we will show you how to migrate a Web application (Wordpress) from a local data center to a AWS VPC. Without downtime even!
Eventual consistency is a consistency model used in many large distributed databases which requires that all changes to a replicated piece of data eventually reach all affected replicas; conflict resolution is not handled and responsibility is pushed up to the application author in the event of conflicting updates .
Eventual consistency is a specific form of weak consistency; the storage system guarantees that if no new updates are made to the object, eventually all accesses will return the last updated value . If no failures occur, the maximum size of the inconsistency window can be determined based on factors such as communication delays, the load on the system, and the number of replicas involved in the replication scheme .
A few examples of eventually consistent systems:
At an extremely high level, replication in MongoDB and MySQL are similar. Both databases have exactly one machine, the primary (or master), that accepts writes from clients. With a single transaction (or atomic operation, in MongoDB’s case), the tables and oplog (or binary log in MySQL) are modified to reflect the change. The log captures what the change is so other secondaries (or slaves) can read the changes and process them, making the slaves identical to the master. (Note that I am NOT talking about multi-master replication.)
Underneath the covers, their implementations are quite different. And in peeking underneath the covers while developing TokuMX, I learned[Read more...]
This Thursday (20th March 2014) we’ll be hosted a free webinar covering advanced MySQL Replication topics as well as the latest developments. As always, the webinar is free but you need to register here (http://www.mysql.com/news-and-events/web-seminars/advanced-mysql-replication-architectures-and-latest-developments/" target="_blank) – even if you can’t join live, you’ll then be sent a link to the replay.
More details on what to expect…
The biggest Web sites in the[Read more...]
To follow-up and describe some of the methods and techniques behind replicating into Hadoop from MySQL in real-time, and how this can be combined into your data workflow, Continuent are running a webinar with me presenting that will go over the details and provide a demo of the data replication process.
Real-Time Data Loading from MySQL to Hadoop with New Tungsten Replicator 3.0
Hadoop is an increasingly popular means of analyzing transaction data from MySQL. Up until now mechanisms for moving data between MySQL and Hadoop have been rather limited. The new Continuent Tungsten Replicator 3.0 provides enterprise-quality replication from MySQL to Hadoop. Tungsten Replicator 3.0 is 100% open source, released under a GPL V2 license, and available for download at
replication is in MariaDB 10.0. I did some benchmarks on the code in
10.0.9. The results are quite good! Here is a graph that shows a 10-times
improvement when enabling parallel replication:
The graph shows the transaction per second as a function of number of slave
worker threads, when the slave is executing events from the master at full
speed. The master binlog was generated with sysbench oltp.lua. When the binlog
is enabled on the slave and made crash-safe (
--innodb-flush-log-at-trx-commit=1), the slave is about ten times faster
at 12 worker threads and above compared to the old single-threaded
These results are for[Read more...]
True, GTID is upon us whether via MySQL 5.6 or Tungsten Replicator (and wasn't it in Google Patches since 2009?).
But some of us are still using standard replication with MySQL 5.5, and the "what's with all these binary log files and positions" question is ever erupting. The output of SHOW SLAVE STATUS confuses people new to it. It confuses me time and again.
So here's the semi visual guide to interpreting the SHOW SLAVE STATUS.
A master writes binary logs. These are typically and conventionally called mysql-bin.##### or mysqld-bin.##### (replace ##### with digits).
A slave connects to its master, and reads entries from the master's binary logs. The slave writes those entries into its own relay logs. These are[Read more...]
We had a great webinar on Thursday about replicating from MySQL to Hadoop (watch the whole thing). It was great, but one of the questions at the end was ‘is there an easy way to test’.
Sadly we can’t go giving out convenient ready-to-run downloads of these things because of licensing and and other complexities, so I want to try and make it as simple and straightforward as possible by giving you the directions to complete. I’m going to be point to the Continuent Documentation every now and then so this is not too crowded, but we should get through it pretty easily.
For this to work:
Madrid MySQL Users Group will have its next meeting on 20th March. Details can be found on the group’s Meetup page.
I will be giving a presentation on MySQL replication hopefully aimed at all levels, but covering some details relevant to larger setups. The meeting will be in Spanish.
Look forward to seeing you there.
La próxima reunión de Madrid MySQL Users Group tendrá lugar el jueves 20 de marzo. Se puede encontrar más detalles en la página del grupo. Ofreceré una presentación sobre replicación de MySQL dirigido a gente de todos los niveles, pero incluirá información relevante a entornos más grandes. La presentación será en español.
Espero veros allí.
Earlier this month I blogged about our new Hadoop applier, I published the docs for that this week (http://docs.continuent.com/tungsten-replicator-3.0/deployment-hadoop.html) as part of the Tungsten Replicator 3.0 documentation (http://docs.continuent.com/tungsten-replicator-3.0/index.html). It contains some additional interesting nuggets that will appear in future blog posts.
How do you get data from a MySQL Cluster into Hadoop? Easy, replicate from the cluster to a stand alone MySQL instance and from there use the MySQL Hadoop Applier to HDFS.
This question came from a long time MySQL user who has jumped into the Big Data world.
MaxScale is a Proxy for the MySQL protocol built with a modular architecture. The underlying concept of modules allows to extend the MaxScale proxy services. The current version implements Read Write splitting and Connection Load Balancing. Internally MySQL queries go through a SQL parsing phase. This gives MaxScale great capabilities regarding queries routing.
So if [...]
It has been a few busy months until now. I have moved from Italy to Thailand, and the move has been my first priority, keeping me from attending FOSDEM and interacting with social media. Now I start catching my breath, and looking around for new events to attend. But before I get into this, let’s make a few things clear:
I have just implemented MASTER_GTID_WAIT() in MariaDB 10.0. This can be used to give a very elegant solution to the problem of stale reads in replication read-scaleout, without incuring the overheads normally associated with synchronous replication techniques. This idea came up recently in a discussion with Stephane Varoqui, and is similar to the concept of Lamport logical clock described in this Wikipedia article.
I wanted to describe this, hoping to induce people to test and maybe start using this, as it is a simple but very neat idea, actually.
A very typical use of MariaDB/MySQL replication is[Read more...]
If you happen to work with personal data, chances are you are subject to SOX (Sarbanes-Oxley) whether you like it or not.
One of the worst aspects of this is that if you want to be able to analyse your data and you replicate out to another host, you have to find a way of anonymizing the information. There are of course lots of ways of doing this, but if you are replicating the data, why not anonymize it during the replication?
Of the many cool features in Tungsten Replicator, one of my favorites is filtering. This allows you to process the stream of changes that are coming from the data extracted from the master and perform operations on it. We use it a lot in the replicator for ignoring tables, schemas and columns, and for ensuring that we have the correct information within the THL.
Given this, let’s use it to anonymize the data as it is being[Read more...]
MySQL 5.6 has been GA for just over a year now. See MySQL 5.6.10 Release Notes. Congratulations on your birthday! That is quite a long time. I was using it earlier in production because it worked and could do things that 5.5 could not do, but earlier versions were to use at your own risk, and indeed if prodded incorrectly would fall on the floor. That is fair enough because they were work in progress, yet if you poked them the right way they did a very good job. Those dev versions have been long since upgraded which is good so they do not need quite as much care and attention.
So from where I see 5.6 it works very well. One big change that has made a large difference but which I think a lot of people may not really understand or use is the[Read more...]
At FOSDEM 2014 ProxySQL and MaxScale were both presented. Both are proxy that can help build sophisticated MariaDB/MySQL architectures. But currently what is the most used proxy with MySQL? It is HAproxy. HAproxy is a level 4 proxy that has no knowledge of the MySQL protocol. Being low level makes it very fast but it [...]
Last Wednesday, I spoke at the San Francisco MySQL Meetup on the topic of changes coming in MySQL 5.7 (and later). We actually went through two different slide decks; the first on features being considered for deprecation in MySQL 5.7 (or later), and the second set providing a brief overview of the new features and benefits already introduced in MySQL 5.7 via the development milestone releases (DMRs) published to date. A big thanks to the entire SF Meetup group, and in particular the organizers (Erin, Mike and Darren), for having me. The event was streamed and recorded, and you can view the full presentation on YouTube. The slide deck can be found here.
The discussion around proposed[Read more...]
I am unable to bring myself to trust the Seconds_behind_master value on SHOW SLAVE STATUS. Even with MySQL 5.5's CHANGE MASTER TO ... MASTER_HEARTBEAT_PERIOD (good thing, applied when no traffic goes from master to slave) it's easy and common to find fluctuations in Seconds_behind_master value.
And, when sampled by your favourite monitoring tool, this often leads to many false negatives.
At Outbrain we use HAProxy as proxy to our slaves, on multiple clusters. More about that in a future post. What's important here is that our decision whether a slave enters or leaves a certain pool (i.e. gets UP or DOWN status in HAProxy) is based on replication lag. Taking slaves out when they are actually replicating well is bad, since this reduces the amount of serving[Read more...]
The other day I was working on an issue where one of the slaves was showing unexpected lag. Interestingly with only the IO thread running the slave was doing significantly more IO as compared to the rate at which the IO thread was fetching the binary log events from the master.
I found this out by polling the SLAVE STATUS and monitoring the value of Read_Master_Log_Pos as it changed over time. Then compared it to the actual IO being done by the server using the pt-diskstats tool from the excellent Percona Toolkit. Note that, when doing this analysis, I had already stopped the slave SQL thread[Read more...]
Over the last few days, I integrated the MySQL 5.6 GTID version of the Percona Replication Manager (PRM) work of Frédéric Descamps, a colleague at Percona. The agent supports the GTID replication mode of MySQL 5.6 and if the master suffers a hard crash, it picks the slave having applied the highest transaction ID from the dead master. Given the nature of GTID-based replication, that causes all the other slaves to resync appropriately to their new master which is pretty cool and must yet be matched by the regular PRM agent.
For now, it is part of a separate agent, mysql_prm56, which may be integrated with the regular agent in the future. To use it, download the agent with the link above, the pacemaker configuration is similar to the one of the[Read more...]
After a few months of submissions and reviews, the program for PerconaLive MySQL Conference and Expo 2014 is published. The conference will be held in Santa Clara, from April 1 to 4, 2014.
Registration with early bird discount is available until February 2nd. If you plan to attend, this is probably the best time to act.
I will be presenting twice at the conference:[Read more...]
As noted in a previous post, MySQL Connector/Java supports multi-master replication topographies as of version 5.1.27, allowing you to scale read load to slaves while directing write traffic to multi-master (or replication ring) servers. The new release of version 5.1.28 builds upon this, allowing live management of replication host (single or multi-master) topographies. This parallels functionality that has long existed for load-balanced connections, and enables users to add or remove hosts – or now[Read more...]