MySQL Cluster 7.2 GA Released, Delivers 1 Billion Queries per Minute(mysql.com)
mysql.com
MySQL Cluster 7.2 GA Released, Delivers 1 Billion Queries per Minute
http://www.mysql.com/why-mysql/white-papers/mysql-cluster-7.2-ga.html
34 comments
For point #3, NDB has supported on disk non-indexed attributes for a while now (2-3 years?). So you just need to be able to fit indexes in memory which is a much smaller dataset, but still limiting.
I'm sure for the benchmark it was all in memory though.
I'm sure for the benchmark it was all in memory though.
Also to add to #3: The NDB C++ API doesn't use raw SQL queries either (it uses a lower lever of abstraction for accessing the database), so it avoids the overhead of having to parse SQL queries. Most production systems and third-party libraries use SQL queries.
#4 is fixed in version 7.0 and upwards
I'm a novice in this area, so looking past the somewhat sensationalist headline (or, perhaps, I'm missing the wit? "Now with 70x more performance!")...
Can someone who knows this stuff give a bit of an overview on the significance of this release?
I read the NoSQL stuff as Oracle/MySQL trying to compete (at least in terms of marketing-speak) with the wave of competition that's arrived in the DB market. Is there any meat to it?
Can someone who knows this stuff give a bit of an overview on the significance of this release?
I read the NoSQL stuff as Oracle/MySQL trying to compete (at least in terms of marketing-speak) with the wave of competition that's arrived in the DB market. Is there any meat to it?
The 70x speed boost isn't a trivial change - it's for JOIN performance.
Cluster has "sql nodes" (that you query against) and "data nodes" that house the actual data, which "sql nodes" talk to to actually process the query.
Cluster can now tell the data nodes to only return necessary subsets of data back to the "sql nodes" when doing a JOIN, instead of pulling down more information (and filtering on the sql node side) when answering complex JOIN queries.
You can read more about this at "push down" feature at http://www.clusterdb.com/mysql-cluster/trying-out-mysql-push...
Note that the linked article also outlines more about this feature under the heading "70x Higher JOIN Performance with Adaptive Query Localization".
Cluster has "sql nodes" (that you query against) and "data nodes" that house the actual data, which "sql nodes" talk to to actually process the query.
Cluster can now tell the data nodes to only return necessary subsets of data back to the "sql nodes" when doing a JOIN, instead of pulling down more information (and filtering on the sql node side) when answering complex JOIN queries.
You can read more about this at "push down" feature at http://www.clusterdb.com/mysql-cluster/trying-out-mysql-push...
Note that the linked article also outlines more about this feature under the heading "70x Higher JOIN Performance with Adaptive Query Localization".
NDB originally only had a NoSQL interface called NDB API. NDB API is a bit complex and when MySQL acquired them, they added the SQL layer on top of it to make it easier for developers to use it.
This release adds an additional memcache api layer which sits on top of NDB API, so that you can more easily program against it.
This release adds an additional memcache api layer which sits on top of NDB API, so that you can more easily program against it.
NDB can be used as a replacement for the entire SQL stack so you don't need to run MySQL programs at all if you program against the interface.
MySQL Cluster seems like an awesome solution to applications with a high write to read ratio (which applies to several that I currently work on). I'd be curious to hear cases of people successfully using MySQL Cluster and some of its pros/cons in real-life applications.
There are a list of users here, they are running MySQL Cluster in production:
http://mysql.com/customers/cluster/
Has anyone here used MySQL Cluster in production?
I did try around the time it split from the normal releases (6.0?). The overall feeling I got was that lots of simple things don't work, trivial bugs stay open for... well mine are still open and there was next to no documentation for any kind of troubleshooting. NDB returned error numbers which were not properly translated by mysql_error, so you were left looking them up the hard way. There was also next to no community - just one IRC channel where guru ndb hackers seemed to know everything about it... but any kind of debugging through them was pretty hard if you ended up with a "I've got a XXGB db which just doesn't come up, what do I do?" situation.
If this has improved lately, I'd be very interested to hear about the changes!
If this has improved lately, I'd be very interested to hear about the changes!
Yeah, that was pretty much exactly my experience last time I tried it. (Around version 7.1 / mid-2010, I think.)
Ditto (early 2010).
Since our complexity requirements were low we settled for a home-grown solution (basically memcached with write-through) and so far didn't regret.
I, too, remain curious if anyone is running this at scale and has bumped into the various corner cases (exceeding capacity, hardware dying, etc.).
Since our complexity requirements were low we settled for a home-grown solution (basically memcached with write-through) and so far didn't regret.
I, too, remain curious if anyone is running this at scale and has bumped into the various corner cases (exceeding capacity, hardware dying, etc.).
I'm also very curious to hear real world feedback on this, I've been aware of the option for a while, but never heard much about actual use.
Automatic sharding and memcached integration are pretty awesome features, and could definitely ease code at the application level (sharding code is a particularly special pain in the ass, not so much getting it working, but allowing for re-sharding migrations if you decide you need more shards, especially trying to do so without downtime which involves all sorts of nasty tradeoffs).
But bang-for-the-buck and reliability wise, I'm still unsure, I've heard very little about this in the wild. Is this more suited for high write v. read ratio situations, or is it aiming more at the Vertica/Greenplum big-data uses, or something else?
Automatic sharding and memcached integration are pretty awesome features, and could definitely ease code at the application level (sharding code is a particularly special pain in the ass, not so much getting it working, but allowing for re-sharding migrations if you decide you need more shards, especially trying to do so without downtime which involves all sorts of nasty tradeoffs).
But bang-for-the-buck and reliability wise, I'm still unsure, I've heard very little about this in the wild. Is this more suited for high write v. read ratio situations, or is it aiming more at the Vertica/Greenplum big-data uses, or something else?
It is more suited for realtime transaction processing type activity, not for big data type analytics.
NDB was originally created by and for telecoms. So very high write/read rates with very fast response times and very high availability required. Generally not extremely large datasets.
If you know what you are doing, NDB can work extremely well. It supports all of the highend cluster goodies including online software upgrade, online node addition, automatic handling of node failures, geographic async replication, etc...
However, it certainly has a pretty steep learning curve from the admin point of view and it is a bit easy to mess things up. It is a bit brittle due to this, but once it is setup properly and running, it can deliver on the promises.
NDB was originally created by and for telecoms. So very high write/read rates with very fast response times and very high availability required. Generally not extremely large datasets.
If you know what you are doing, NDB can work extremely well. It supports all of the highend cluster goodies including online software upgrade, online node addition, automatic handling of node failures, geographic async replication, etc...
However, it certainly has a pretty steep learning curve from the admin point of view and it is a bit easy to mess things up. It is a bit brittle due to this, but once it is setup properly and running, it can deliver on the promises.
We've been happily using it for the better part of a year now. Our data set fits easily in memory and the limitations of the product aren't a problem at all. We started with riak but the cognitive overhead on the developer side was a bit much for the operations side win, mysql cluster has been trouble free so far.
MySQL is an expensive toy if you spring for Enterprise. Crashes all over the place, bugs stay open forever, no one really knows how to fix it, people on IRC are mean and think they know more than they do, etc.
Check out Postgres or Cassandra.
Check out Postgres or Cassandra.
Yes; ran quite well with around 16GB of data in three nodegroups of two replicas each. Rolling restarts for making configuration changes (including the expansion of the cluster) were a total blessing. We peaked around 2,000 queries per second, and maintained (with a good indexing strategy) < 0.01s average query time using the SQL layer.
Yes. And its a pain in the ass. From reading whats new in this release I could care less about AQL but hearing them say you can bring up a whole cluster with one command it shares user permissions between them... that gets me excited!
I played around it with in some of our apps, and I can second that running it was a pain in the ass. It seemed to fail randomly (the entire cluster would go down) even with minimal use.
I know I must have been doing something horribly wrong, but I never could really figure out what it was.
I know I must have been doing something horribly wrong, but I never could really figure out what it was.
When did we go from standard measure of per second for these types of things, you know, requests per second, transactions per second, to per minute? Statistics eh?
As a MySQL, Oracle and Clustrix DBA this is very sexy news. Here is the extra info on the benchmark http://mikaelronstrom.blogspot.com/2012/02/105bn-qpm-using-m...
But no info on what disk setup they used. But for every instance node there was a storage node.
But no info on what disk setup they used. But for every instance node there was a storage node.
There's a great deal of hand-waving here. My understand is that it's a distributed in-memory KV store. Not something you could realistically run TPC-C against and still get "1 billion" TPM.
Looks awesome! Does anyone have a suggestion for a good site explaining what's the best path for migrating from a master / master setup to a MySQL Cluster?
You still have MySQL Servers in MySQL Cluster and you can mix and match storage engines (e.g. use InnoDB for some, Cluster (ndb) for others). Cluster 7.2 uses a tweaked version of MySQL 5.5 for the MySQL servers and so if you're already on 5.5 then the migration is fairly simple...
0) Always a good idea to backup! 1) Create your cluster... http://www.clusterdb.com/mysql-cluster/deploying-mysql-clust... - stop before starting your mysqlds 2)Configure your mysqlds to use the same data directory as your existing ones 3) Stop your existing mysqlds 4) Start up your new (cluster) mysqlds. At this point you still have your existing tables stored in the original engine (e.g. MyISAM or InnoDB). 5) Migrate appropriate tables to cluster... "ALTER TABLE <tab-name> ENGINE=ndbcluster;"
0) Always a good idea to backup! 1) Create your cluster... http://www.clusterdb.com/mysql-cluster/deploying-mysql-clust... - stop before starting your mysqlds 2)Configure your mysqlds to use the same data directory as your existing ones 3) Stop your existing mysqlds 4) Start up your new (cluster) mysqlds. At this point you still have your existing tables stored in the original engine (e.g. MyISAM or InnoDB). 5) Migrate appropriate tables to cluster... "ALTER TABLE <tab-name> ENGINE=ndbcluster;"
Schedule downtime, dump DBs, backup EVERYTHING three times, import DBs into new RDBMS, switch over config and bring everything back up.
You know who has scheduled maintenance? Apple, Verizon, Sprint, etc.
You know who has scheduled maintenance? Apple, Verizon, Sprint, etc.
Anyone with some real-life feedback on this (or previous versions even?)
[deleted]
1) They used Infiniband interconnects. Running on ethernet is likely to yield less impressive results.
2) Their benchmark does simple primary key lookups. If you start doing joins or transactions that need to hit multiple data nodes, things will slow down. Depending on your workload, this may or may not be an issue.
3) NDB is an in-memory storage engine, so you're limited to the aggregate RAM in your cluster for max storage size.
4) AFAIK, MySQL Cluster doesn't re-balance, you need to pre-determine how data is partitioned and changing it at runtime is hard. I don't know if this has changed in the later releases.