MySQL Doing 1 Billion Queries Per Minute

digitalpoint

Well-known member
The new version of MySQL Cluster (7.2) has been deemed GA status (stable). For those that aren't familiar with MySQL Cluster, it allows multiple servers to act as a single DB server and is transparent to the application (it's just a different storage engine in MySQL). Any node is fully safe for both reads *and* writes. You define how many nodes you want your data replicated across (so all data is available if nodes go down), and if a node goes down, it instantly starts sharding data to other nodes so it gets back to having at least xx live copies of data. You can also add additional servers/nodes for relatively linear scalability if you need more capacity (you can add nodes without restarting anything).

Long story short... it's very nice. :)

Some interesting benchmarks on the new version... http://mikaelronstrom.blogspot.com/2012/02/105bn-qpm-using-mysql-cluster-72.html

An 8 data node setup was benchmarked at over 1 billion queries per minute with commodity hardware. Let's break that down... 16.7M queries per second for the DB cluster. Break that down further to individual nodes, and it's still pretty impressive... 2.1M queries per second on average for each server/node within the cluster.

Not just limited to reads (reads are of course faster), but write capacity is impressive as well... doing 1.9M SQL writes (update/insert) per second.

Little summary video: http://www.oracle.com/pls/ebn/swf_viewer.load?p_shows_id=11464419
 
If I have only 2 servers. Are them still good to use Mysql Cluster? (I'm using Relication)
 
If you aren't running into problems/bottlenecks with your current setup, it's probably a little overkill.

MySQL Cluster stores all data and indexes in memory by default, so you would need enough memory on each search to store all your databases (you could set it up so all data exists only on one node, but that means if one of the two servers goes offline, not all data is available). A general rule for the memory each data node would need would need for MySQL Cluster is: database size * replicas * 1.25 / number of data nodes. For example, if you had 10GB worth of databases you wanted to spread across 4 servers with the system keeping 2 copies of all data: 10GB * 2 * 1.25 / 4... you would need 6.25GB of memory for each data node.
 
An 8 data node setup was benchmarked at over 1 billion queries per minute with commodity hardware. Let's break that down... 16.7M queries per second for the DB cluster. Break that down further to individual nodes, and it's still pretty impressive... 2.1M queries per second on average for each server/node within the cluster.

Thats impressive for a free application, given that some of our "tweaked" oracle clusters at work run at around 12m queries/sec.
 
Thats impressive for a free application, given that some of our "tweaked" oracle clusters at work run at around 12m queries/sec.

Tests like the one described above are very tweaked. There's a lot of the major DB vendors doing them every so often, just to compare their peens, basically. It's done on hardware that is specifically put together for the test, with a DB engine that is tweaked and moulded to take the best of the hardware, and most of the queries will be extremely simplified to ensure maximum performance. It hardly ever mimics what would be run in a production environment.

That said, the sheer numbers are still impressive :)
 
Tests like the one described above are very tweaked. There's a lot of the major DB vendors doing them every so often, just to compare their peens, basically. It's done on hardware that is specifically put together for the test, with a DB engine that is tweaked and moulded to take the best of the hardware, and most of the queries will be extremely simplified to ensure maximum performance. It hardly ever mimics what would be run in a production environment.

That said, the sheer numbers are still impressive :)
Yep... no doubt. They obviously are going to run stuff that shows off the capability of the product. The part that's most impressive is the difference between the previous version of their own product (4-8 times speed increase for basic queries... but even more impressive [and something those benchmarks didn't dive into] is the 40-80x speed increase for complex queries).

And they definitely didn't tune everything as much as they could. For example they used Infiniband interconnects between servers, but went the simple route and just ran IP over Infiniband, rather than the faster (but more complicated to set up) option of SDP via LD_PRELOAD.
 
They did some more benchmarking with more data nodes (this time focused on SQL write scalability)...

http://www.mysql.com/why-mysql/white-papers/mysql-cluster-benchmarks.php

Running the tests, MySQL Cluster delivered:
  • 4.3 Billion fully consistent reads (SELECTs) per minute
  • 1.2 Billion fully transactional writes (UPDATEs) per minute

The most interesting thing (to me) is that it scales linearly... you need more capacity, you simply add more servers (which you can do without taking down the DB cluster or even restarting it)...
mysql_cluster_72_benchmarks_updates.png


Either way, getting 72,000,000 million SQL reads (SELECTs) per second *AND* 20,000,000 million SQL writes (UPDATEs) per second at the same time if fairly impressive if you ask me. And makes MariaDB's Galera Cluster implementation just seems kind of slow. :)
 
They did some more benchmarking with more data nodes (this time focused on SQL write scalability)...

http://www.mysql.com/why-mysql/white-papers/mysql-cluster-benchmarks.php



The most interesting thing (to me) is that it scales linearly... you need more capacity, you simply add more servers (which you can do without taking down the DB cluster or even restarting it)...
mysql_cluster_72_benchmarks_updates.png


Either way, getting 72,000,000 million SQL reads (SELECTs) per second *AND* 20,000,000 million SQL writes (UPDATEs) per second at the same time if fairly impressive if you ask me. And makes MariaDB's Galera Cluster implementation just seems kind of slow. :)

Comparing Galera to Oracle's cluster solution for MySQL is not really fair, two very different storage engines, innodb and ndbcluster.
 
Errrr... So you would pick a storage engine before you compared cluster solutions, and then evaluate your clustering options based on what's available for that storage engine you locked yourself in to?

I'd think comparing storage engines is exactly what you would *want* to do.

Side note - don't give Oracle too much credit for MySQL Cluster... They only have it because they acquired it with MySQL. :)
 
Errrr... So you would pick a storage engine before you compared cluster solutions, and then evaluate your clustering options based on what's available for that storage engine you locked yourself in to?

I'd think comparing storage engines is exactly what you would *want* to do.

Side note - don't give Oracle too much credit for MySQL Cluster... They only have it because they acquired it with MySQL. :)
Of course not, I am just saying that the two cluster solutions are based on different technologies and cannot really be compared like for like.
 
Top Bottom