L'idée est d'appuyer la page “Monter cluster MariaDB Galera avec HAproxy”.

Ceci a été réalisé sur plusieurs VM avec les specs suivantes :

Pour servir de référence, voici les résultat d'un serveur seul serveur :

# sysbench /usr/share/sysbench/oltp_read_only.lua  --threads=150 --events=0 --time=60 --mysql-host=192.168.1.219 --mysql-user=sysbench --mysql-password=XXXXXXXXXXXXX --mysql-db=sysbench --mysql-port=3306 --tables=10 --table-size=1000000 --range_selects=off --db-ps-mode=disable run
 
SQL statistics:
    queries performed:
        read:                            786340
        write:                           0
        other:                           157268
        total:                           943608
    transactions:                        78634  (1309.19 per sec.)
    queries:                             943608 (15710.30 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
General statistics:
    total time:                          60.0619s
    total number of events:              78634
 
Latency (ms):
         min:                                    1.91
         avg:                                  114.53
         max:                                 1486.99
         95th percentile:                      150.29
         sum:                              9006051.07
 
Threads fairness:
    events (avg/stddev):           524.2267/4.21
    execution time (avg/stddev):   60.0403/0.02

# sysbench /usr/share/sysbench/oltp_write_only.lua  --threads=150 --events=0 --time=60 --mysql-host=192.168.1.219 --mysql-user=sysbench --mysql-password=XXXXXXXXXXXXX --mysql-db=sysbench --mysql-port=3306 --tables=10 --table-size=1000000 --range_selects=off --db-ps-mode=disable run
 
SQL statistics:
    queries performed:
        read:                            0
        write:                           56908
        other:                           28454
        total:                           85362
    transactions:                        14227  (236.59 per sec.)
    queries:                             85362  (1419.55 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
General statistics:
    total time:                          60.1321s
    total number of events:              14227
 
Latency (ms):
         min:                                    9.60
         avg:                                  633.86
         max:                                 6140.78
         95th percentile:                     1903.57
         sum:                              9017982.08
 
Threads fairness:
    events (avg/stddev):           94.8467/7.38
    execution time (avg/stddev):   60.1199/0.02
 

# sysbench /usr/share/sysbench/oltp_read_write.lua  --threads=150 --events=0 --time=60 --mysql-host=192.168.1.219 --mysql-user=sysbench --mysql-password=XXXXXXXXXXXXX --mysql-db=sysbench --mysql-port=3306 --tables=10 --table-size=1000000 --range_selects=off --db-ps-mode=disable run
 
SQL statistics:
    queries performed:
        read:                            338790
        write:                           135516
        other:                           67758
        total:                           542064
    transactions:                        33879  (560.14 per sec.)
    queries:                             542064 (8962.31 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
General statistics:
    total time:                          60.4814s
    total number of events:              33879
 
Latency (ms):
         min:                                   11.90
         avg:                                  267.50
         max:                                 1551.45
         95th percentile:                      669.89
         sum:                              9062801.75
 
Threads fairness:
    events (avg/stddev):           225.8600/7.52
    execution time (avg/stddev):   60.4187/0.14

Et pour le cluster :

# sysbench /usr/share/sysbench/oltp_read_only.lua  --threads=450 --events=0 --time=60 --mysql-host=192.168.1.210 --mysql-user=sysbench --mysql-password=XXXXXXXXXXXX --mysql-db=sysbench --mysql-port=3306 --tables=10 --table-size=1000000 --range_selects=off --db-ps-mode=disable --report-interval=1 run
 
READ CLUSTER :
SQL statistics:
    queries performed:
        read:                            1696160
        write:                           0
        other:                           339232
        total:                           2035392
    transactions:                        169616 (2821.89 per sec.)
    queries:                             2035392 (33862.68 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
General statistics:
    total time:                          60.1061s
    total number of events:              169616
 
Latency (ms):
         min:                                   68.24
         avg:                                  160.41
         max:                                  636.26
         95th percentile:                      219.36
         sum:                             27208895.71
 
Threads fairness:
    events (avg/stddev):           374.4283/3.93
    execution time (avg/stddev):   60.0638/0.03

# sysbench /usr/share/sysbench/oltp_write_only.lua  --threads=450 --events=0 --time=60 --mysql-host=192.168.1.210 --mysql-user=sysbench --mysql-password=XXXXXXXXXXXX --mysql-db=sysbench --mysql-port=3306 --tables=10 --table-size=1000000 --range_selects=off --db-ps-mode=disable --report-interval=1 run
 
SQL statistics:
    queries performed:
        read:                            0
        write:                           22116
        other:                           10956
        total:                           33072
    transactions:                        5427   (73.59 per sec.)
    queries:                             33072  (448.48 per sec.)
    ignored errors:                      102    (1.38 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
General statistics:
    total time:                          73.7405s
    total number of events:              5427
 
Latency (ms):
         min:                                  152.79
         avg:                                 5524.15
         max:                                27907.00
         95th percentile:                    14562.82
         sum:                             29979552.28
 
Threads fairness:
    events (avg/stddev):           12.0600/0.50
    execution time (avg/stddev):   66.6212/3.99

# sysbench /usr/share/sysbench/oltp_read_write.lua  --threads=450 --events=0 --time=60 --mysql-host=192.168.1.210 --mysql-user=sysbench --mysql-password=XXXXXXXXXXXX --mysql-db=sysbench --mysql-port=3306 --tables=10 --table-size=1000000 --range_selects=off --db-ps-mode=disable --report-interval=1 run
 
SQL statistics:
    queries performed:
        read:                            55220
        write:                           22083
        other:                           10926
        total:                           88229
    transactions:                        5404   (71.71 per sec.)
    queries:                             88229  (1170.72 per sec.)
    ignored errors:                      118    (1.57 per sec.)
    reconnects:                          0      (0.00 per sec.)
 
General statistics:
    total time:                          75.3617s
    total number of events:              5404
 
Latency (ms):
         min:                                  235.84
         avg:                                 5578.60
         max:                                27048.90
         95th percentile:                    14827.42
         sum:                             30146729.02
 
Threads fairness:
    events (avg/stddev):           12.0089/0.56
    execution time (avg/stddev):   66.9927/4.46

D'un point de vue requêtes de lecture, il y a un gain significatif de performance, là ou en écriture, il y a une réduction, du fait de la synchronisation de la données sur l'ensemble du cluster.