适用于Rails 3应用程序的MySQL Cluster(NDB)与MySQL Replication(InnoDB):优点/缺点? [英] MySQL Cluster (NDB) vs MySQL Replication (InnoDB) for Rails 3 apps: pros/cons?

问题描述

我们正在对当前系统进行概述，试图找出是否可以提高性能和性能.可靠性.

We are doing an overview of our current systems, trying to figure out if we can improve performance & reliability.

当前，我们运行大量内部Rails应用程序和基于Rails的网站.有些已经是Rails 3，有些已经转换为Rails3.它们都连接到以下MySQL设置.

Currently we run a bunch of internal Rails apps and our Rails based website. Some are Rails 3 already, some are being converted to Rails 3. They all connect to the following MySQL Setup.

mysql01 ( master server) => mysql02 (slave) =>(每天将数据库备份到驱动器，该驱动器每天，每周，每月和每半年一次备份).

mysql01 ( master server) => mysql02 (slave) => ( daily DB backups to a drive, that is backed up on a daily, weekly, monthly & semi-annual basis).

所有写操作都发生在mysql01上，大多数短读操作也会发生，一些更耗资源的读操作"(例如，每月/每周的报告需要3到10分钟才能运行并将数据转储到csv或备份中)到mysql02服务器.我们每天大约有3-5K的访问我们的网站，并且有大约20-30个内部用户，他们每天使用各种应用进行库存，订单处理等.因此，除了这些报告之外，这些服务器的负载特别大还是要运行奴隶.

All writes happen on mysql01 and most short reads go to it as well, some "more resource consuming reads" ( like monthly/weekly reports that take 3-10 minutes to run and dump data into csv or backups) go to mysql02 server. We get about 3-5K visits per day to our site, and have about 20-30 internal users, that use various apps daily for inventory , order processing, etc. So these servers are not particularly under heavy loads other then those reports, that run of the slave anyways.

所有服务器都在Debian Lenny VM的virtualized XEN池中运行.

All servers run in a virtualized XEN pool on Debian Lenny VMs.

因此，我们正在审查系统，并且有人提出了切换到MySQL Cluster (NDB)设置的建议.我从理论上知道它，但从未真正运行过它.那么，有经验的人是否知道与我们当前设置相比有何优缺点，以及涉及Ruby/Rails应用程序时的任何特别注意事项?

So we are doing a review of the systems, and somebody threw a suggestion of switching to MySQL Cluster (NDB) setup. I know of it in theory, but have never actually run it. So does anyone who had experience with it know of any pro / cons vs our current setup, and of any particular caveats when it involves Ruby / Rails applications?

推荐答案

最近发布到文档中的InnoDB和MySQL Cluster(ndb)有很好的比较...值得一看:

There is a good comparison of InnoDB and MySQL Cluster (ndb) recently posted to the docs...worth taking a look: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-compared.html

集群体系结构由应用程序访问的MySQL服务器池组成；这些MySQL服务器实际上并不存储集群数据，而是在下面的数据节点池中对数据进行分区.每个MySQL Server都可以访问所有数据节点中的数据.如果一个MySQL服务器更改了一条数据，则所有其他MySQL服务器立即可见.

The Cluster architecture consists of a pool of MySQL Servers that are accessed by the application(s); these MySQL Servers don't actually store the Cluster data, the data is partitioned over the pool of data nodes below. Every MySQL Server has access to the data in all of the data nodes. If one MySQL server changes a piece of data then it is instantly visible to all of the other MySQL Servers.

显然，此体系结构使横向扩展数据库极其容易.与分片不同，应用程序不需要知道数据保存在哪里-它可以在所有可用的MySQL服务器之间实现负载平衡.与使用MySQL复制进行横向扩展不同，群集使您可以纵向扩展读写.可以将新的数据节点或MySQL服务器添加到现有的群集中，而不会丢失应用程序的服务.

Obviously, this architecture makes it extremely easy to scale out the database. Unlike sharding, the application doesn't need to know where the data is held - it can just load balance across all available MySQL Servers. Unlike scaling out with MySQL replication Cluster allows you to scale writes just as well as reads. New data nodes or MySQL servers can be added to an existing Cluster with no loss of service to the application.

MySQL Cluster的无共享架构意味着它可以提供极高的可用性(99.999％+).每次更改数据时，它都会被同步复制到另一个数据节点.如果一个数据节点发生故障，则应用程序读取&备份数据节点会自动处理写请求.

MySQL Cluster's shared-nothing architecture means that it can deliver extremely high availability (99.999%+). Every time you change data, it is synchronously replicated to a second data node; if one data node fails then the applications read & write requests are automatically handled by the backup data node.

由于MySQL Cluster的分布式特性，某些操作可能会较慢(例如，具有数千个临时结果的JOIN-尽管有一个可解决此问题的原型解决方案)，但其他操作则可能非常快并且可以很好地扩展(例如，主键读写).您可以选择将表(甚至列)存储在内存或磁盘中，并且通过选择内存选项(在backgoround中将检查点更改为磁盘上的更改)可以非常快速地完成事务.

Due to the distributed nature of MySQL Cluster, some operations can be slower (for example JOINs that have thousands of interim results - though there is a prototype solution available which addresses this) but others can be very fast and can scale extremely well (e.g. primary key reads and writes). You have the option of storing tables (or even columns) in memory or on disk and by choosing the memory option (with changes checkpointed to disk in the backgoround) transactions can be very quick.

MySQL群集的设置可能比单个MySQL服务器更复杂，但是它可以防止您必须在应用程序中实现分片或读写拆分.秋千和回旋处.

MySQL Cluster can be more complex to set up than a single MySQL server but it can prevent you having to implement sharding or read/write splitting in your application. Swings and roundabouts.

要从MySQL群集中获得最佳性能和可伸缩性，可能需要调整应用程序(请参阅群集性能调整白皮书:

To get the best performance and scalability out of MySQL Cluster you need may need to tweak your application (see Cluster performance tuning white paper: http://www.mysql.com/why-mysql/white-papers/mysql_wp_cluster_perfomance.php). If you own the application this isn't normally a big deal but if you're using someone else's application that you can't modify then it could be a problem.

最后一点是，它不需要全部或全部-您可以选择将某些表存储在Cluster中，而另一些使用其他存储引擎存储，这是每个表的选项.您还可以在Cluster和其他存储引擎之间进行复制(例如，将Cluster用于运行时数据库，然后复制到InnoDB以生成复杂的报告).

A final note is that it doesn't need to be all or nothing - you can choose to store some of your tables in Cluster and some using other storage engines, this is a per-table option. Also you can replicate between Cluster and other storage engines (for example, use Cluster for your run-time database and then replicate to InnoDB to generate complex reports).

这篇关于适用于Rails 3应用程序的MySQL Cluster(NDB)与MySQL Replication(InnoDB):优点/缺点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！