Problem statement

Stale selects occur with maxscale casual read configuration in place (global parameter in use). Basically, we will be seeing stale records if the following condition over the maxscale will be met - number of client connections is greater than 1.
As our tests showed, records are delayed for about ±0.2 seconds.

In this report we are describing techniques used for testing and current maxscale configuration.

Testing Technique

1. We will be running a simple PHP script that is going to do INSERT and SELECT using one connection / thread.

The content of the script is shown below.

<?php

$conn = mysqli_connect('server', 'username', 'psw', "dbname", 4006);

if (!$conn)

Unknown macro: { die("Connection failed}

echo "Connected successfully\n";

$conn->query("TRUNCATE testtable");

$i = 0;
while (1){
$i++;

$conn->begin_transaction();

try

Unknown macro: { $conn->query("INSERT INTO testtable (ID, LastName, FirstName, Age) VALUES ($i, 'Jonas', 'Testas', 44)"); $conn->commit(); }

catch (mysqli_sql_exception $exception)

Unknown macro: { $conn->rollback(); throw "Err}

$result = $conn->query("SELECT id FROM testtable WHERE id={$i}");
$row = $result->fetch_row();
if (!isset($row))

Unknown macro: { echo "-------> ID not found => $in"; }

if ($i % 100 == 0) echo "$i rec inserted.\n";
usleep(1000);
}
?>

2. Running the script, provides following results with no or very low number of stale reads.

1800 rec inserted.
1900 rec inserted.
2000 rec inserted.
-------> ID not found => 2033
2100 rec inserted.
2200 rec inserted.
2300 rec inserted.

3. Once we generate additional traffic going over the maxscale, problem increases drastically with a lot of missing IDs.

sysbench --mysql-db=testas123 --mysql-host=DB --mysql-port=4006 --mysql-user=user --mysql-password=PSW --mysql-dry-run=off --table-size=10000 --tables=16 --db-driver=mysql --time=100 /usr/share/sysbench/oltp_read_write.lua prepare
sysbench --mysql-db=testas123 --mysql-host=DB --mysql-port=4006 --mysql-user=user --mysql-password=PSW --mysql-dry-run=off --table-size=10000 --tables=16 --db-driver=mysql --threads=10 --time=100 /usr/share/sysbench/oltp_read_write.lua run

--threads=1 - problem occurs sometimes
--threads=10 - problem occurs very often

Maxscale versions and config we use

MaxScale 2.5.13 - d1e1cdec7e3ac58566883dd9d2aebc6c69436455
CMake flags: -DBUILD_TESTS=N -DBUILD_MMMON=Y -DBUILD_CDC=Y -DBUILD_GUI=Y -DPACKAGE=Y -DDISTRIB_SUFFIX=ubuntu.focal MaxScale 2.5.13 - d1e1cdec7e3ac58566883dd9d2aebc6c69436455
CMake flags: -DBUILD_TESTS=N -DBUILD_MMMON=Y -DBUILD_CDC=Y -DBUILD_GUI=Y -DPACKAGE=Y -DDISTRIB_SUFFIX=ubuntu.focal

Config:

[maxscale]
threads=auto

[mariadb-1]
type=server
address=IP1
port=3306
protocol=MariaDBBackend

[mariadb-2]
type=server
address=IP2
port=3306
protocol=MariaDBBackend

[mariadb-3]
type=server
address=IP3
port=3306
protocol=MariaDBBackend

[MariaDB-Monitor]
type=monitor
module=mariadbmon
servers=mariadb-1, mariadb-2, mariadb-3
user=maxuser
password=PSW
monitor_interval=50
enforce_read_only_slaves=true

[Read-Write-Service]
type=service
router=readwritesplit
servers=mariadb-1, mariadb-2, mariadb-3
user=maxuser
password=PSW
master_reconnection=true
master_failure_mode=error_on_write
slave_selection_criteria=ADAPTIVE_ROUTING
master_accept_reads=true
causal_reads=global
transaction_replay=true

[Read-Write-Listener]
type=listener
service=Read-Write-Service
protocol=MariaDBClient
port=4006

MariaDB instances have this setting on:

session_track_system_variables="autocommit,character_set_client,character_set_connection,character_set_results,last_gtid,time_zone

Demonstration: https://www.youtube.com/watch?v=RMNQMgQBisw