1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySQL Doing 1 Billion Queries Per Minute

Discussion in 'General PHP and MySQL Discussions' started by digitalpoint, Feb 26, 2012.

  1. digitalpoint

    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
    Rob Fritz, lazy llama, R_A and 4 others like this.
  2. Dinh Thanh

    Dinh Thanh Well-Known Member

    I have two questions?

    1. Is it easy to setup Cluster?
    2. Is it free to use?
  3. digitalpoint

    digitalpoint Well-Known Member

    Dinh Thanh likes this.
  4. Dinh Thanh

    Dinh Thanh Well-Known Member

    If I have only 2 servers. Are them still good to use Mysql Cluster? (I'm using Relication)
  5. digitalpoint

    digitalpoint Well-Known Member

    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.
    Brandon Sheley and Dinh Thanh like this.
  6. Dinh Thanh

    Dinh Thanh Well-Known Member

    OK, very interesting.
    Sure to try soon.
  7. Phil Conway

    Phil Conway Active Member

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

    SchmitzIT Well-Known Member

    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 :)
  9. digitalpoint

    digitalpoint Well-Known Member

    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.
  10. digitalpoint

    digitalpoint Well-Known Member

    They did some more benchmarking with more data nodes (this time focused on SQL write scalability)...


    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)...

    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. :)
    p4guru and CyclingTribe like this.
  11. Deebs

    Deebs Well-Known Member

    Comparing Galera to Oracle's cluster solution for MySQL is not really fair, two very different storage engines, innodb and ndbcluster.
  12. digitalpoint

    digitalpoint Well-Known Member

    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. :)
  13. Deebs

    Deebs Well-Known Member

    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.
  14. digitalpoint

    digitalpoint Well-Known Member

    Which could be said about all clustering solutions... You can't compare then since the technology is different.
  15. Digital Doctor

    Digital Doctor Well-Known Member

Share This Page