PlanetMysql.ru — информация о СУБД MySQL

I've written a few times about database consistency before, mainly in conjunction with NoSQL and the concept of Eventual consistency. Now, I'm about to do an update on the subject, as I have come to realize a few things.

From an oldtimer like myself, having been an SQL guy for 25 years, I remember Punk-rock and even The Beatles and I having hair growing out of my ears, what can be contributed? Well, let me beging with stating what I mean when I say Database consistency. What I mean is Consistency as the C in ACID (no, we aren't talking drugs here, we are talking databases). Let's see what the online authorative reference work on just about anything on this planet, from the size of J-Lo's feet to the number of Atoms in the universe (those two numbers are quite far apart by the way), Wikipedia: "The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including but not limited to constraints, cascades, triggers, and any combination thereof." In other words, consistency means that the databas is always in a consistent state, the different data items in it (rows, if you wish) are "in sync" with eachother. I think most of you agree with this notion.

Now, when it comes to NoSQL databases, like MongoDB, this terminology is different. These guys introduced Eventual consistency, which means that the database will eventually reach a consistent state with regards to a specific transaction that changes that "state" of the database. But there are multiple transactions at the same time, and they aren't necessarily, in an Eventual consistentcy model, consistent with eachother as they aren't on the same node. But the theory goes that some time, eventually, they will. If the system never stops, and transactions keep coming, then eventual consistens is determined to happen within 100 ms or less from the point in time when pigs fly. But if you stop all state changing transactions, then the state of the database will reach consistency. Eventually.

Now in NoSQL circles there is a thing called a Consistent read. If my database was consistent, then any read is consistent, right? And in the case of use SQL RDBMS folks, consistency is about the state of the database when I write to it? Well, if you have an eventual consistency model, where you have data distributed all over the place, things are different. To begin with, the basic thing that you have to make sure, and the NoSQL databases do this, is to ensure that the writes to the databases are all in order (we know this from MySQL also, and it is part of the issue with the MySQL slaves, and the NoSQL guys aren't fixing this particular bottleneck). And here we mean they are in order in each and every node. Across nodes, we don't care, which is where I get my abilility to scale out writes from!

A consistent read is a read where the data I am reading is in a consistent state, or sometime that my data is the most recent data. These two aren't always the same, but the second (reading most recent data) typically implies the former, although I assume this is not always the case. This is VERY different from the meaning of Database Consistency as we RDBMS folks look at it. All the same, the concept sure is useful, and as the NoSQL distributed systems doesn't need to keep the data consistent on a global level, a lot of shortcuts can be taken. But having Read Consistency has litte to do with Database Consistency. Your NoSQL fans will complain here and try to tell you that these achieve the same thing, but they don't. Achieving global Database Consistency costs an arm and a leg or two in performance, but the database is ALWAYS consistent.

So two different things, both with advantages and disadvantages, but they are STILL different! And the NoSQL folks will confuse things by allowing you not to have even Read Consistency, somewhat implying that turning it on means you get Database Consistency and that the Read Consistency model (which is very very simple by the way) means you get the effect of Database Consistency using Eventual Consistency. Nope. You don't. Which doesn't make it bad, but IT IS NOT THE SAME THING!

/Karlsson
PlanetMySQL Voting: Vote UP / Vote DOWN

I've written a few times about database consistency before, mainly in conjunction with NoSQL and the concept of Eventual consistency. Now, I'm about to do an update on the subject, as I have come to realize a few things.

From an oldtimer like myself, having been an SQL guy for 25 years, I remember Punk-rock and even The Beatles and I having hair growing out of my ears, what can be contributed? Well, let me beging with stating what I mean when I say Database consistency. What I mean is Consistency as the C in ACID (no, we aren't talking drugs here, we are talking databases). Let's see what the online authorative reference work on just about anything on this planet, from the size of J-Lo's feet to the number of Atoms in the universe (those two numbers are quite far apart by the way), Wikipedia: "The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including but not limited to constraints, cascades, triggers, and any combination thereof." In other words, consistency means that the databas is always in a consistent state, the different data items in it (rows, if you wish) are "in sync" with eachother. I think most of you agree with this notion.

Now, when it comes to NoSQL databases, like MongoDB, this terminology is different. These guys introduced Eventual consistency, which means that the database will eventually reach a consistent state with regards to a specific transaction that changes that "state" of the database. But there are multiple transactions at the same time, and they aren't necessarily, in an Eventual consistentcy model, consistent with eachother as they aren't on the same node. But the theory goes that some time, eventually, they will. If the system never stops, and transactions keep coming, then eventual consistens is determined to happen within 100 ms or less from the point in time when pigs fly. But if you stop all state changing transactions, then the state of the database will reach consistency. Eventually.

Now in NoSQL circles there is a thing called a Consistent read. If my database was consistent, then any read is consistent, right? And in the case of use SQL RDBMS folks, consistency is about the state of the database when I write to it? Well, if you have an eventual consistency model, where you have data distributed all over the place, things are different. To begin with, the basic thing that you have to make sure, and the NoSQL databases do this, is to ensure that the writes to the databases are all in order (we know this from MySQL also, and it is part of the issue with the MySQL slaves, and the NoSQL guys aren't fixing this particular bottleneck). And here we mean they are in order in each and every node. Across nodes, we don't care, which is where I get my abilility to scale out writes from!

A consistent read is a read where the data I am reading is in a consistent state, or sometime that my data is the most recent data. These two aren't always the same, but the second (reading most recent data) typically implies the former, although I assume this is not always the case. This is VERY different from the meaning of Database Consistency as we RDBMS folks look at it. All the same, the concept sure is useful, and as the NoSQL distributed systems doesn't need to keep the data consistent on a global level, a lot of shortcuts can be taken. But having Read Consistency has litte to do with Database Consistency. Your NoSQL fans will complain here and try to tell you that these achieve the same thing, but they don't. Achieving global Database Consistency costs an arm and a leg or two in performance, but the database is ALWAYS consistent.

So two different things, both with advantages and disadvantages, but they are STILL different! And the NoSQL folks will confuse things by allowing you not to have even Read Consistency, somewhat implying that turning it on means you get Database Consistency and that the Read Consistency model (which is very very simple by the way) means you get the effect of Database Consistency using Eventual Consistency. Nope. You don't. Which doesn't make it bad, but IT IS NOT THE SAME THING!

/Karlsson
PlanetMySQL Voting: Vote UP / Vote DOWN

Having just written an interview response about NoSQL concepts for a RDBMS audience it was poetic that an inconspicuous title “(4 of 3)” highlights that both a MySQL read scalable implementation via replication and a NoSQL solution can share a common lack of timely consistency of data. For the sake of Group Commit I hope my data is always consistent at some location at some point in time as soon as possible.

In attempting to comment to Kristian Nielsen’s Fixing MySQL group commit (part 4 of 3) I was forced to watch an ad before I could even add a comment. Go jump Live Journal, it’s quicker to write my own blog post.

And if anybody is still reading, I had just written the following.

“There is clearly a place for NoSQL solutions. The two primary types of products are a key/value store and a schema-less solution. You need to learn the strengths, benefits and weaknesses of both. For a RDBMS resource the lack of transactions, the lack of joins and the concept of eventually consistent can take some time to accept.”

PlanetMySQL Voting: Vote UP / Vote DOWN

Having just written an interview response about NoSQL concepts for a RDBMS audience it was poetic that an inconspicuous title “(4 of 3)” highlights that both a MySQL read scalable implementation via replication and a NoSQL solution can share a common lack of timely consistency of data. For the sake of Group Commit I hope my data is always consistent at some location at some point in time as soon as possible.

In attempting to comment to Kristian Nielsen’s Fixing MySQL group commit (part 4 of 3) I was forced to watch an ad before I could even add a comment. Go jump Live Journal, it’s quicker to write my own blog post.

And if anybody is still reading, I had just written the following.

“There is clearly a place for NoSQL solutions. The two primary types of products are a key/value store and a schema-less solution. You need to learn the strengths, benefits and weaknesses of both. For a RDBMS resource the lack of transactions, the lack of joins and the concept of eventually consistent can take some time to accept.”

PlanetMySQL Voting: Vote UP / Vote DOWN

We all knew that we are risking with MMM. Risking, and placing availability as a more important like consistency.  But non of us can risk loosing data forever but we show using it, regarding to our conversations think:  "I can fix my data later on, but I can’t turn back time and prevent the downtime. (Pascal Hoffman@xaprb.com)".

As I wrote before about staying online, now let me write about how to stay consistent.

We all know, mmm is not like a key of salvation, but its getting close to it . While MySQL doesn't support multi-master-slave environments from it's source code, we will sleep badly wondering on the safety of our precious databases.

But its not just about MMM, a few days ago we ran in to a well known InnoDB "feature". Its about the auto increment counter determination on restart. InnoDB try to count the next auto increment value on MySQL restart what can screw up things in the replication as in your data integrity too (http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html) what could be risky when you are about to use mmm, because you can restart your masters whenever you just want to.

In this post I'm using maatkit to verify and restore the rows without locking or downtime.

Maatkit:

This is not a success of MMM, but success of maatkit.
I don't want to write about maatkit. Everybody who is able to use MMM were met with maatkit. Great stuff and if you didn't met with it yet, this is the right time. http://www.maatkit.org

Verification between your masters:

This is the hardest part, you CAN'T and/or you SHOULD NOT to avoid manual overview.

I'm using mk-table-sync for this scenerio. As the about say: mk-table-sync finds and fixes data differences between MySQL tables.
This is what we are going to do, but lets consider the possibilities. If we goes trough the help menu, we can see there is a lots of options. Worth going trough of them.

Here is the command I used to execute.

mk-table-sync --chunksize 100 --databases mytestdb --lock 1 --transaction --skipbinlog --print  db02 db01

And here is the explain:

--chunksize 100 : Chunksize means the size of the chunks maatkit compare to each other. Mean rows. In this command maatkit compare the tables by chunks which are contain 100 rows. Its an affordable size, Its wont be faster and as I benchmarked neider become slower, but as you will see later, its start a transaction and lock the table. More about locking and transactions later but to understand why I recommend a small chunksize, you have to know that this means that the locking of the table is it in will be a short lock.

--database mytestdb: Allows you to give a comma-separated list of databases should be taken care of. In this example I use only one database but as I mentioned you can pass a list of databases you want to scan or what you don't want to scan.

--lock 1: As I mentioned before, maatkit provide you two kind of "locking". The first one is a real table lock and the second one is the way of using transactions. With innodb tables using transaction is a good choice, more about later, but with MyIsam tables, I think the best way is lock 1 regarding to what I want to do here, syncing online masters to each other. mk-table-sync provide 3 different ways of locking here: "0=none, 1=per sync cycle, 2=per table, or 3=globally" as far as I know, 0 means no locks, thats not a good way if you are about to sync online masters under traffic. Locking each table could be a wrong decision too, in case of a larger table, and globally is the same under usage. So this is why I just picked lock 1 what will lock while it's take care of my chunk (this is why I lowered the chunk size to 100).

--transaction: This is what i was talking about above. Instead of locking, mk-table-sync able to use transactions . Starting a transaction with a larger chunk size could cause lock-wait-timeout messages as locking can. This is why there is a small chunk size again. You have to listen to your isolation level too. As you know, its could be unsafe under read-committed.

--skipbinlog: This is the most important part of it. In a multi-master environment syncing without this option is like a suicide. With this, mk-table-sync will execute the fixing queries without logging in to binlog. I should not to mention, that logging to binlog on the slave could really mess up your databases as the other master will replicate and execute it.

--print: mk-table-sync provide three different (and some of them able to combine) ways to run. First I would recommend is --print. Print will just print to the stdout (what you can forward to file with 1>/your/file/path) but when you redirect the stdout, don't forget when using with --ask-pass that this message won't show up for your . You can use --execute instead. That will execute the queries immediately. I'm always scared by that a little bit so I rather get the output, go over it and execute what I think I should.

db02/db01: In my example these two hosts are the writable master (at first) and the passive master as a second one.You can use whatever hosts you want. Commands will be executed on the second host.

What to do with your output: When its done and its find differences, you can find where ever your stdout goes. In thet file or on your screen you can see the queries what mk-table-sync would execute to make your passive master consistent. Now its up to you what to do with this information. You can execute each of the or re-run the sync with --execute instead of --print.

Syncing slaves

If you done with your masters, you can sync the slaves on the same way, but you can use the already consistent passive master of your to sync from. Both maatkit and mmm provide some very useful scripts to rebuild slaves and you always got a passive master to do from. With this you are free to rebuild your cluster whenever you want.

PlanetMySQL Voting: Vote UP / Vote DOWN