Uploaded image for project: 'MariaDB MaxScale'
  1. MariaDB MaxScale
  2. MXS-3695

Causal Consistency with MaxScale's Read/Write Split Router issue

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 2.5.13
    • 2.5.15
    • readwritesplit
    • None
    • Ubuntu 20

    Description

      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) {
        die("Connection failed: " . mysqli_connect_error());
      }
       
      echo "Connected successfully\n";
       
      $conn->query("TRUNCATE testtable");
       
      $i = 0;
      while (1){
        $i++;
       
        $conn->begin_transaction();
       
        try {
        $conn->query("INSERT INTO testtable (ID, LastName, FirstName, Age) VALUES ($i, 'Jonas', 'Testas', 44)");
        $conn->commit();
        } catch (mysqli_sql_exception $exception) {
            $conn->rollback();
            throw "Err: ".$exception;
        }
       
        $result = $conn->query("SELECT id FROM testtable WHERE id={$i}");
        $row = $result->fetch_row();
        if (!isset($row)){
           echo "-------> ID not found => $i\n";
        }
        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

      Attachments

        Issue Links

          Activity

            People

              markus makela markus makela
              domas Domas
              Votes:
              0 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.