Poor performance with MySQL Galera using OpenStack Rally user authentication workload

[Hui Kang]

Hi,

I have observed poor performance when using Galera patched MySQL. The workload

is user authentication operations generated by Rally. Here is the results

Currency: 512 users

95th of latency for Single node Vanilla MySQL:               21 second
95th of latency for Single node Galera patched MySQL: 160 second

As you can see, the vanilla MySQL is about 8x faster than Galera. Since I use only one

node in the galera MySQL cluster, there should be not replication traffic.

For both version of MySQL, I used the default /etc/my.cnf configuration. The only 

difference is in the galera related setup. 

Has anyone have similar observation? Or any performance tips? Thanks.

- Hui

[Hui Kang]

Hi,

I have observed poor performance when using Galera patched MySQL. The workload

is user authentication operations (mostly read operations) generated by OpenStack Rally. Here is the results

Currency: 512 users

95th of latency for Single node Vanilla MySQL:               21 second
95th of latency for Single node Galera patched MySQL: 160 second

As you can see, the vanilla MySQL is about 8x faster than Galera. Since I use only one

node in the galera MySQL cluster, there should be no replication traffic.

For both version of MySQL, I used the default /etc/my.cnf configuration. The only 

difference is in the galera related setup. 

[Philip Stoev]

Hello.

It seems that this is a known issue:
https://bugs.launchpad.net/percona-xtradb-cluster/+bug/1443755

You may wish to try adding a second Galera node (in the same datacenter) and
see if performance improves.

Thank you.

Philip Stoev

--
You received this message because you are subscribed to the Google Groups
"codership" group.
To unsubscribe from this group and stop receiving emails from it, send an
email to codership-tea...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Hui Kang]

Hi, Philip,

Thanks for you advice. I added a second node to the cluster and there is not improvement to the galera performance. I also found that by default  wsrep_causal_reads  is 0.

Here is the output of the wsrep_% of my cluster.

+------------------------------+---------------------------------------+
| Variable_name                | Value                                 |
+------------------------------+---------------------------------------+
| wsrep_local_state_uuid       | 2ac06f0b-04ab-11e5-9b60-a3023f0a5818  |
| wsrep_protocol_version       | 7                                     |
| wsrep_last_committed         | 9353                                  |
| wsrep_replicated             | 9351                                  |
| wsrep_replicated_bytes       | 15974939                              |
| wsrep_repl_keys              | 41048                                 |
| wsrep_repl_keys_bytes        | 543457                                |
| wsrep_repl_data_bytes        | 14833018                              |
| wsrep_repl_other_bytes       | 0                                     |
| wsrep_received               | 38                                    |
| wsrep_received_bytes         | 668                                   |
| wsrep_local_commits          | 9197                                  |
| wsrep_local_cert_failures    | 0                                     |
| wsrep_local_replays          | 0                                     |
| wsrep_local_send_queue       | 0                                     |
| wsrep_local_send_queue_max   | 35                                    |
| wsrep_local_send_queue_min   | 0                                     |
| wsrep_local_send_queue_avg   | 13.975008                             |
| wsrep_local_recv_queue       | 0                                     |
| wsrep_local_recv_queue_max   | 2                                     |
| wsrep_local_recv_queue_min   | 0                                     |
| wsrep_local_recv_queue_avg   | 0.026316                              |
| wsrep_local_cached_downto    | 3                                     |
| wsrep_flow_control_paused_ns | 1762872223008                         |
| wsrep_flow_control_paused    | 0.243444                              |
| wsrep_flow_control_sent      | 0                                     |
| wsrep_flow_control_recv      | 532                                   |
| wsrep_cert_deps_distance     | 418.409261                            |
| wsrep_apply_oooe             | 0.942145                              |
| wsrep_apply_oool             | 0.000000                              |
| wsrep_apply_window           | 14.055823                             |
| wsrep_commit_oooe            | 0.000000                              |
| wsrep_commit_oool            | 0.000000                              |
| wsrep_commit_window          | 13.113571                             |
| wsrep_local_state            | 4                                     |
| wsrep_local_state_comment    | Synced                                |
| wsrep_cert_index_size        | 1377                                  |
| wsrep_causal_reads           | 0                                     |
| wsrep_cert_interval          | 27.058603                             |
| wsrep_incoming_addresses     | 158.85.113.73:3306,158.85.113.72:3306 |
| wsrep_evs_delayed            |                                       |
| wsrep_evs_evict_list         |                                       |
| wsrep_evs_repl_latency       | 0/0/0/0/0                             |
| wsrep_evs_state              | OPERATIONAL                           |
| wsrep_gcomm_uuid             | 3522d994-04ab-11e5-9141-ba8937ba94de  |
| wsrep_cluster_conf_id        | 2                                     |
| wsrep_cluster_size           | 2                                     |
| wsrep_cluster_state_uuid     | 2ac06f0b-04ab-11e5-9b60-a3023f0a5818  |
| wsrep_cluster_status         | Primary                               |
| wsrep_connected              | ON                                    |
| wsrep_local_bf_aborts        | 0                                     |
| wsrep_local_index            | 0                                     |
| wsrep_provider_name          | Galera                                |
| wsrep_provider_vendor        | Codership Oy <in...@codership.com>     |
| wsrep_provider_version       | 3.9(r93aca2d)                         |
| wsrep_ready                  | ON                                    |
+------------------------------+---------------------------------------+

My galera version is
mysqld  Ver 5.6.22-72.0-56 for debian-linux-gnu on x86_64 (Percona XtraDB Cluster (GPL), Release rel72.0, Revision 978, WSREP version 25.8, wsrep_25.8.r4150)

Do you have any suggestions? Thanks.

- Hui

[Philip Stoev]

Hello,

Can you provide a bit more information about your setup and how you run
Rally. When you added a second node, was it in the same data center?

Will it be possible to run some other benchmark against your cluster, such
as sysbench?

email to mailto:codership-team%2Bunsu...@googlegroups.com.

[Hui Kang]

Hi, Philip,

Now I have been switching to sysbench to test my galera cluster. The first thing I want to ensure the correctness of my galera cluster is that the real-only performance can be scaled out. However, I observe minimal improvement on the read-only performance with my sysbench read-only workload from 2 galera nodes to 4 galera.

Here is my testbed setup

sysbench 0.5

MySQL: (Percona XtraDB Cluster (GPL), Release rel72.0, Revision 978, WSREP version 25.8, wsrep_25.8.r4150)

All the MySQL servers are behind an HaProxy.

           |

4         |  29ms      |    29ms  

300     |  1650ms  |   1591ms

The first column is number of threads, the second column is 95th latency of 2 galera nodes, and the third column is 95 th latency of 4 nodes.

As you can see there is not too much difference between 2 nodes and 4 nodes. From the webportal of haproxy, I can tell that the number of threads directed to each nodes are evenly distributed.

The following is my /etc/mysql/my.cnf on each node. Could you provide any suggestions? Thanks.

- Hui

[MYSQLD]

max_connections=10000
max_connect_errors=10000

log_bin
log_slave_updates

innodb_flush_log_at_trx_commit = 2
innodb_read_io_threads=16

user = mysql
default_storage_engine = InnoDB
basedir = /usr
datadir = /var/lib/mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
innodb_autoinc_lock_mode = 2
log_queries_not_using_indexes = 1
max_allowed_packet = 512M
binlog_format = ROW
wsrep_provider = /usr/lib/libgalera_smm.so
wsrep_node_address =192.168.3.31
wsrep_cluster_name="mydockerpxc"
wsrep_cluster_address = gcomm://192.168.3.41,192.168.3.51,192.168.3.61
wsrep_node_name =
wsrep_slave_threads = 4
wsrep_sst_method = xtrabackup-v2
wsrep_sst_auth = pxcuser:pxcpass
[sst]
streamfmt = xbstream
[xtrabackup]
compress
compact
parallel = 2
compress_threads = 2
rebuild_threads = 2

[Philip Stoev]

Are you running a workload that is 100% reads? If so, then each Galera node
would be identical to a stand-alone MySQL server, so reads should scale
linearly.

If they do not, one possible reason is that the machine where you run
sysbench is saturated and can not drive all four nodes. You may wish to
check CPU and network utilization on that machine.

email to mailto:mailto:codership-team%252Buns...@googlegroups.com.

[Hui Kang]

On Mon, Jun 8, 2015 at 12:30 PM, Philip Stoev <philip...@galeracluster.com> wrote:

Are you running a workload that is 100% reads? If so, then each Galera node would be identical to a stand-alone MySQL server, so reads should scale linearly.

Yes, the workload is 100% reads as I used --oltp-read-only=on. Also at the end of the sys bench run, I can see write is 0.

 

If they do not, one possible reason is that the machine where you run sysbench is saturated and can not drive all four nodes. You may wish to check CPU and network utilization on that machine.

Actually each Galera node is in a VM, and the 4 VMs are on the same hosts. But I cap the CPU time of each VM so that the neither the physical host nor any VM is saturated. For example, in each VM, the  usr10%, sys 5%, si % 6.

The cpu utilization of the host is around 30%.

- Hui

[alexey.y...@galeracluster.com]

On 2015-06-08 19:36, Hui Kang wrote:
> Actually each Galera node is in a VM, and the 4 VMs are on the same
> hosts.
> But I cap the CPU time of each VM so that the neither the physical host
> nor
> any VM is saturated. For example, in each VM, the usr10%, sys 5%, si %
> 6.
>
> The cpu utilization of the host is around 30%.

What about IO? Network?

To make sure that it is at all viable to benchmark anything in such
setup, how about starting 4 plain MySQLs and run the same readonly tests
against them?

>> | wsrep_local_send_queue_max | 35
>> |
>> | wsrep_local_send_queue_min | 0
>> |
>> | wsrep_local_send_queue_avg | 13.975008
>> |
>> | wsrep_local_recv_queue | 0
>> |
>> | wsrep_local_recv_queue_max | 2
>> |
>> | wsrep_local_recv_queue_min | 0
>> |
>> | wsrep_local_recv_queue_avg | 0.026316
>> |

>> | wsrep_flow_control_paused_ns | 1762872223008
>> |
>> | wsrep_flow_control_paused | 0.243444
>> |
>> | wsrep_flow_control_sent | 0
>> |
>> | wsrep_flow_control_recv | 532
>> |

This for example shows that this node can't replicate at full speed
(these all should be zeroes, or close), the other node is only 75% as
fast. And it can be anything from poor configuration to node simply
being not properly warmed up.