与独立的mariaDB服务器相比，galera的性能非常差 [英] Getting a very bad performance with galera as compared to a standalone mariaDB server

问题描述

我创建的galera设置出现了令人无法接受的低性能.在我的设置中，有2个双活节点，我使用HA-proxy负载均衡器以循环方式在两个节点上进行读/写.

I am getting an unacceptable low performance with the galera setup i created. In my setup there are 2 nodes in active-active and i am doing read/writes on both the nodes in a round robin fashion using HA-proxy load balancer.

使用具有以下配置的单个mariadb服务器，我很容易能够在我的应用程序上获得10000 TPS:36 vpcu，60 GB RAM，SSD，10Gig专用管道

I was easily able to get over 10000 TPS on my application with the single mariadb server with the below configuration: 36 vpcu, 60 GB RAM, SSD, 10Gig dedicated pipe

使用galera时，虽然我正在使用通过ha-proxy平衡的2个节点(36vcpu，60 GB RAM)的DB负载，但我几乎没有获得3500 TPS.有关信息，ha-proxy作为独立节点托管在其他服务器上.到目前为止，我已经删除了ha-proxy，但是性能没有任何改善.

With galera i am hardly getting 3500 TPS although i am using 2 nodes(36vcpu, 60 GB RAM) of DB load balanced by ha-proxy. For information, ha-proxy is hosted as a standalone node on a different server. I have removed ha-proxy as of now but there is no improvement in performance.

有人可以在my.cnf中提出一些调整参数的建议，我应该考虑调整此性能严重不佳的设置.

Can someone please suggest some tuning parameters in my.cnf i should consider to tune this severely under-performing setup.

我正在使用以下my.cnf文件:

I am using the below my.cnf file:

推荐答案

通过具有以下配置的单个mariadb服务器:36 vpcu，60 GBRAM，SSD，10Gig专用管道

I was easily able to get over 10000 TPS on my application with the single mariadb server with the below configuration: 36 vpcu, 60 GB RAM, SSD, 10Gig dedicated pipe

尽管我使用的是galera，但我几乎没有获得3500 TPS的收益通过ha-proxy平衡DB的节点(36vcpu，60 GB RAM).

With galera i am hardly getting 3500 TPS although i am using 2 nodes(36vcpu, 60 GB RAM) of DB load balanced by ha-proxy.

基于Galera的集群并非旨在按我的意图扩展写入量；实际上，正如Rick前面提到的那样:向同一表的多个节点发送写操作将最终导致认证冲突，这将反映为应用程序的死锁，从而增加了巨大的开销.

Clusters based on Galera are not designed to scale writes as I see you intend to do; In fact, as Rick mentioned above: sending writes to multiple nodes for the same tables will end up causing certification conflicts that will reflect as deadlocks for your application, adding huge overhead.

我的galera设置我得到了令人无法接受的低性能创建.在我的设置中，有两个节点处于主动-主动状态，我正在做使用HA代理以循环方式在两个节点上读取/写入负载均衡器.

I am getting an unacceptable low performance with the galera setup i created. In my setup there are 2 nodes in active-active and i am doing read/writes on both the nodes in a round robin fashion using HA-proxy load balancer.

请将所有写入发送到单个节点，看看是否可以提高性能；由于Galera使用的虚拟同步复制的性质，总会有一些开销，这实际上会增加您执行的每次写操作的网络开销(尽管基于真正的基于时钟的并行复制会相当大地抵消这种影响，但是您仍然必须遵守看到吞吐量略低.)

Please send all writes to a single node and see if that improves performane; There will always be some overhead due to the nature of virtually-synchronous replication that Galera uses, which literally adds network overhead to each write you perform (albeit true clock-based parallel replication will offset this impact quite a bit, still you are bound to see slightly lower throughput volumes).

另外，请确保在完成原子工作单元后尽快使事务简短，并保持COMMIT状态，因为复制证书过程是单线程的，并且会阻止其他节点上的写入(如果您看到写入者的话)节点显示事务wsrep预先提交阶段，这意味着其他节点正在对大型事务进行认证，或者该节点正遭受某种性能问题-交换，全盘，滥用大量读取等.

Also make sure to keep your transactions short and COMMIT as soon as you are done with an atomic unit of work, since replication-certification process is single-threaded and will stall writes on the other nodes (if you see that your writer node shows transactions wsrep pre-commit stage that means the other nodes are doing certification for a large transaction or that the node is suffering performance problems of some sort -swap, full disk, abusively large reads, etc.

希望有帮助，让我们知道当您移动到单节点时如何进行.

Hope that helps, and let us know how it goes when you move to single node.

这篇关于与独立的mariaDB服务器相比，galera的性能非常差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！