I recently set up a MariaDB galera cluster for our production. I used sysbench to benchmark the cluster against the old database which is on a single server.
On my PRD Galera Cluster I got the following results:
SQL statistics: queries performed: read: 3914980 write: 0 other: 782996 total: 4697976 transactions: 391498 (1304.77 per sec.) queries: 4697976 (15657.22 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.0492s total number of events: 391498 Latency (ms): min: 5.37 avg: 12.26 max: 66.20 95th percentile: 15.83 sum: 4798745.23 Threads fairness: events (avg/stddev): 24468.6250/414.77 execution time (avg/stddev): 299.9216/0.01
Meanwhile our old single database production got this results:
SQL statistics: queries performed: read: 5306060 write: 0 other: 1061212 total: 6367272 transactions: 530606 (1768.51 per sec.) queries: 6367272 (21222.18 per sec.) ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.) General statistics: total time: 300.0266s total number of events: 530606 Latency (ms): min: 3.87 avg: 9.04 max: 59.99 95th percentile: 12.08 sum: 4798278.00 Threads fairness: events (avg/stddev): 33162.8750/440.14 execution time (avg/stddev): 299.8924/0.01
Now I'm wondering why does the cluster operate a bit slower than the single database? They have the same specs: Quadcore CPU, 32GB RAM and vm.swappiness=1. Here's my cluster configuration (same across 3 servers) and is using HAProxy to load balance between 3 servers:
max_connections = 3000 wsrep_slave_threads=4 innodb_lock_wait_timeout=8000 innodb_io_capacity=2000 innodb_buffer_pool_size=25G innodb_buffer_pool_instances=25 innodb_log_buffer_size=256M innodb_log_file_size=1G innodb_flush_log_at_trx_commit=2 innodb_flush_method = O_DIRECT_NO_FSYNC innodb_read_io_threads=8 innodb_write_io_threads=4 thread_handling = pool-of-threads thread_stack = 192K thread_cache_size = 4 thread_pool_size = 8 thread_pool_oversubscribe = 3 wsrep_provider_options="gcache.size=10G; gcache.page_size=10G"
I used sysbench on a spare server, does the latency between servers also affect the outputs? I would appreciate any inputs, thank you.