====== Benchmark MariaDB ======
L'idée est d'appuyer la page "[[kb:linux:generalites:Monter cluster MariaDB Galera avec HAproxy]]".
Ceci a été réalisé sur plusieurs VM avec les specs suivantes :
^ CPU | 1 cœur |
^ RAM | 2GB |
^ Disque | NVMe limité a 200MB lecture et écriture |
^ OS | Debian Buster |
^ Version | 10.3.22-0+deb10u1 |
Pour servir de référence, voici les résultat d'un serveur seul serveur :
=== READ ONLY : ===
# 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
=== WRITE ONLY : ===
# 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
=== READ/WRITE ONLY : ===
# 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 :
=== READ ONLY ===
# 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
=== WRITE ONLY : ===
# 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
=== READ/WRITE ONLY : ===
# 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
==== Conclusion : ====
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.