6 ms·

MySQL Cluster 7.2 GA Released, Delivers 1 Billion Queries per Minute

122 points by jsavimbi 15y ago

deleted 15y ago
[deleted]
jonstjohn 15y ago
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.
- matkeep 15y ago
  There are a list of users here, they are running MySQL Cluster in production: http://mysql.com/customers/cluster/ http://mysql.com/customers/cluster/
nlh 15y ago
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?
- HarrisonFisk 15y ago
  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.
  - cpleppert 15y ago
    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.
- plasma 15y ago
  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-down-join-spj-preview/ 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".
pc 15y ago
Has anyone here used MySQL Cluster in production?
- bermanoid 15y ago
  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?
  - HarrisonFisk 15y ago
    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.
  - seanp2k2 15y ago
    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.
  - macros 15y ago
swah 15y ago
If only I had some data to fill the wonderful databases available these days.
- ypcx 15y ago
  Start with a public dataset - Wikipedia, Stack Overflow, etc. Or request Amazon's product DB (you have to convince them that they will benefit). Do something interesting with it and people will notice.
thibaut_barrere 15y ago
Anyone with some real-life feedback on this (or previous versions even?)
- iso8859-1 15y ago
  http://news.ycombinator.com/item?id=3594892 http://news.ycombinator.com/item?id=3594892
jsr 15y ago
Definitely an impressive benchmark by any standard. However, there are some things to be aware of: 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.
- HarrisonFisk 15y ago
  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.
- jonasoreland 15y ago
  #4 is fixed in version 7.0 and upwards
- geoffhill 15y ago
  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.
videoappeal 15y ago
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?
- wmf 15y ago
  For whatever reason, database people have been using transactions per minute for a long time.
  - robryan 15y ago
    I was thinking it might have been to prevent people using max queries per second even if it is unsustainable. Although an average should cover that.
mathnode 15y ago
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-mysql-cluster-72.html 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.
dedene 15y ago
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?
- seanp2k2 15y ago
  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.
- andrewmorgan 15y ago
  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-cluster-over-multiple-hosts/ 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;"
jacques_chester 15y ago
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.

122 points by jsavimbi 15y ago

deleted 15y ago

[deleted]

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.

matkeep 15y ago
There are a list of users here, they are running MySQL Cluster in production: http://mysql.com/customers/cluster/ http://mysql.com/customers/cluster/

nlh 15y ago

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?

HarrisonFisk 15y ago
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.
- cpleppert 15y ago
  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.
plasma 15y ago
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-down-join-spj-preview/ 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".

pc 15y ago

Has anyone here used MySQL Cluster in production?

bermanoid 15y ago
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?
- HarrisonFisk 15y ago
  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.
- seanp2k2 15y ago
  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.
- macros 15y ago

swah 15y ago

If only I had some data to fill the wonderful databases available these days.

ypcx 15y ago
Start with a public dataset - Wikipedia, Stack Overflow, etc. Or request Amazon's product DB (you have to convince them that they will benefit). Do something interesting with it and people will notice.

thibaut_barrere 15y ago

Anyone with some real-life feedback on this (or previous versions even?)

iso8859-1 15y ago
http://news.ycombinator.com/item?id=3594892 http://news.ycombinator.com/item?id=3594892

jsr 15y ago

Definitely an impressive benchmark by any standard. However, there are some things to be aware of: 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.

HarrisonFisk 15y ago
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.
jonasoreland 15y ago
#4 is fixed in version 7.0 and upwards
geoffhill 15y ago
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.

videoappeal 15y ago

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?

wmf 15y ago
For whatever reason, database people have been using transactions per minute for a long time.
- robryan 15y ago
  I was thinking it might have been to prevent people using max queries per second even if it is unsustainable. Although an average should cover that.

mathnode 15y ago

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-mysql-cluster-72.html 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.

dedene 15y ago

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?

seanp2k2 15y ago
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.
andrewmorgan 15y ago
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-cluster-over-multiple-hosts/ 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;"

jacques_chester 15y ago

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.