CODEWITHSUNDEEP
proxymariadbload-balancingmaster-slavemaxscale
hekke
hekke

Reputation: 15

How to test load-balancer of MaxScale

I built master/slave constructure in MariaDB and set MaxScale. I tried to test MaxScale if it distribute SELECT queries to multiple replicaDBs but I'm not sure how to test it. Here is the information about servers I built.

[maxscale]
threads=auto


[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=masterDB, replicaDB1, replicaDB2, replicaDB3
user=monitor
password=alj123
monitor_interval=2s


[masterDB]
type=server
address=192.168.30.140
port=3306
protocol=MariaDBBackend
proxy_protocol=true
persistpoolmax=100
persistmaxtime=3600s
monitoruser=monitor
monitorpw=alj123
max_routing_connections=1000


[replicaDB1]
type=server
address=192.168.30.141
port=3306
protocol=MariaDBBackend
proxy_protocol=true
persistpoolmax=100
persistmaxtime=3600s
monitoruser=monitor
monitorpw=alj123
max_routing_connections=1000


[replicaDB2]
type=server
address=192.168.30.142
port=3306
protocol=MariaDBBackend
proxy_protocol=true
persistpoolmax=100
persistmaxtime=3600s
monitoruser=monitor
monitorpw=alj123
max_routing_connections=1000


[replicaDB3]
type=server
address=192.168.30.143
port=3306
protocol=MariaDBBackend
proxy_protocol=true
persistpoolmax=100
persistmaxtime=3600s
monitoruser=monitor
monitorpw=alj123
max_routing_connections=1000

# definition of Write Service

[Write-Service]
type=service
router=readconnroute
router_options=master, slave
servers=masterDB
user=maxscale
password=alj123
max_connections=100

# definition of Read-Service

[Read-Service]
type=service
router=readconnroute
router_options=slave
servers=replicaDB1,
        replicaDB2,
        replicaDB3
user=read
password=alj123
max_connections=100


# Write-Listener

[Write-Listener]
type=listener
service=Write-Service
protocol=MariaDBClient
port=3309
address=0.0.0.0
proxy_protocol_networks=192.168.30.145/24

# Read-Listener

[Read-Listener]
type=listener
service=Read-Service
protocol=MariaDBClient
port=3310
address=0.0.0.0
proxy_protocol_networks=192.168.30.145/24

And here is a php file that includes SELECT query and is run from client(192.168.30.160)

<?php
$start = (new DateTime())->getTimestamp();
echo "start= $start" . PHP_EOL;


for ($i=0; $i<40000; $i++) {
   $mysqli = new mysqli("p:192.168.30.145", "client", "alj123", "TOPGUN", "3310");

   $mysqli->query("SELECT pilot from TOPGUN.pilots");
   $sql = "SELECT @@hostname";
   $result = $mysqli->query($sql);

   if ($result->num_rows > 0) {
       $row = $result->fetch_assoc();
       echo "DB's host: " . $row['@@hostname'] . PHP_EOL;
   } else {
       echo "Failed to get the host of the DB";
   }

   $result->free();
   $mysqli->close();
   unset($tmp, $row, $result, $mysqli);
}
   $end = (new DateTime())->getTimestamp();
   echo 'end= ' . ($end - $start) . PHP_EOL;
?>

I expect that MaxScale distribute SELECT queries to three replicaDBs equally. However, the result was that MaxScale only passed the queries to one replicaDB. I'd like to know how to test MaxScale.

Could anyone help me, please?

Upvotes: 0

Views: 368

Answers (1)

Cao Shouguang
Cao Shouguang

Reputation: 148

  1. Log on to your read clusters using
mysql -ualj123 -p -P3310

User alj123 must have access from remote. i.e. alj123@'%'

  1. After logging run:
select @@hosthame;

The hostname where it's read will then display. The default is round-robin. Each time you run select @@hostname;, a different server name will display.

Upvotes: 0

Related Questions