Galera Cluster关注 [英] Galera Cluster concerns

问题描述

我想在我们的生产环境中使用Galera群集，但是我有些担心；

I want to use Galera cluster in our production environment, but i have some concerns;

1. 每个表必须至少定义一个显式主键.

1. Each table must have at least one explicit primary key defined.

每个表必须在InnoDB或XtraDB存储引擎下运行.

Each table must run under InnoDB or XtraDB storage engine.

分批处理您的大笔交易.例如，不是让一个事务插入100,000行，而是将其分解为较小的块，例如，每个事务插入1000行.

Chunk up your big transaction in batches. For example, rather than having one transaction insert 100,000 rows, break it up into smaller chunks of e.g., insert 1000 rows per transaction.

您的应用程序可以容忍非连续的自动增量值.

Your application can tolerate non-sequential auto-increment values.

架构更改的处理方式不同.

Schema changes are handled differently.

通过将写入发送到单个节点来处理热点/Galera死锁.

Handle hotspots/Galera deadlocks by sending writes to a single node.

我想对上述所有问题进行澄清.此外，我们有600多个数据库正在生产中，galera可以在此环境中工作吗?

I will like some clarification for all aforementioned points.Also we have over 600 databases in production, can galera work in this Environment??

推荐答案

这是要处理一次的很多.有两个问题，表的创建(invloves Schema ，请参见第5点)和使用这些表的应用程序.我会尝试:

That is a LOT to handle in one shot. There are two issues, table creation (invloves Schema, see point 5) and applications that use those tables. I'll try:

1)每个表必须至少定义一个显式主键.

1)Each table must have at least one explicit primary key defined.

创建表时，不能有任何没有具有主键的表.使用字段和索引创建表.这些索引之一必须声明为PRIMARY KEY.

When you are creating a table, you can't have any table that DOES NOT have a primary key. Tables are created with fields and INDEXES. One of those indexes must be declared as PRIMARY KEY.

2)每个表必须在InnoDB或XtraDB存储引擎下运行.

2)Each table must run under InnoDB or XtraDB storage engine.

创建表时，必须具有ENGINE=InnoDB或ENGINE=XtraDB. Galera不处理默认的MyISAM类型表

When tables are created, the must have ENGINE=InnoDB or ENGINE=XtraDB. Galera does not handle the default MyISAM type tables

3)分批处理您的大笔交易.例如，而不是 一笔交易插入100,000行，将其分解成较小的行 块，例如，每笔交易插入1000行.

3)Chunk up your big transaction in batches. For example, rather than having one transaction insert 100,000 rows, break it up into smaller chunks of e.g., insert 1000 rows per transaction.

这与您的架构无关，但与您的应用程序有关.尽量不要在一个transaction中包含一个INSERT大量数据的应用程序. 请注意，该方法可以正常工作，但存在风险.这不是强制要求，而是建议.

This is not related to your schema, but your application. Try not to have an application that INSERTs a lot of data in one transaction. Note that this will work, but is risky. This is NOT a requirement, but a suggestion.

4)您的应用程序可以容忍非连续的自动增量值.

4)Your application can tolerate non-sequential auto-increment values.

在群集中，您可以更新多个服务器.如果字段是自动递增的，则每个群集成员都可能尝试递增同一字段.您的应用程序应该永不，假设下一个ID与上一个ID相关.对于自动增量字段，请不要 IMPOSE 值，而应由数据库处理.

With a cluster, you can have multiple servers being updated. If a field is auto-incremented, each cluster member could be trying to increment the same field. Your application should NEVER EVER assume that the next ID is related to the previous ID. For auto-increment fields, do not IMPOSE a value, let the DB handle it.

5)模式更改的处理方式不同.

5)Schema changes are handled differently.

模式是表和索引的描述，而不是添加，删除或检索信息的事务.您有多台服务器，因此必须谨慎处理Schema更改，以便所有服务器都能赶上.

The Schema is the description of the tables and indexes and not the transactions that add, delete or retrieve information. You have multiple servers, so a Schema change has to be handled with care, so that all servers do catch up.

6)通过将写入发送到单个节点来处理热点/Galera死锁.

6)Handle hotspots/Galera deadlocks by sending writes to a single node.

这与应用程序和数据库有关.死锁是指应用程序的两个不同部分尝试获取值(ValueA)的情况，因为DB锁定了它以便可以对其进行更改，然后尝试获取另一个值(ValueB)以进行相同的使用.如果另一部分尝试先锁定ValueB，则首先锁定ValueB，这将导致死锁，因为每个应用程序都锁定了另一个应用程序的 next 值.为避免这种情况，最好tp只写入群集中的一台服务器，然后使用其他服务器进行读取.请注意，您的应用程序中仍然可能存在死锁.但是您可以避免Galera造成这种情况.

This is both application and DB related. A deadlock is a condition where 2 different parts of an app try to get a value (ValueA), as the DB to lock it so it can be changed, and then try to get another value (ValueB) for the same use. If another part tries to First Lock ValueB , then ValueA, we have a deadlock, Because each app has locked the next value of the other app. To avoid this, it's best tp write to only one server in the cluster and use the other servers for reading. Do note that you can still have deadlocks in your applications. But you can avoid Galera creating the situation.

这篇关于Galera Cluster关注的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！