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

Memory leak when using prepared statements without arguments

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.1.1
    • Fix Version/s: 1.2.0
    • Component/s: readwritesplit
    • Labels:
      None

      Description

      There is a memory leak when executing prepared statements. I have tested this on a 3-node galera cluster on both the stable version and the 1.2 version.

      Config:

      [maxscale]
      threads=1
       
      [Galera Monitor]
      type=monitor
      module=galeramon
      servers=server1,server2,server3
      user=monitor
      passwd=password
      monitor_interval=1000
       
      [qla]
      type=filter
      module=qlafilter
      options=/tmp/QueryLog
       
      [RW Split Router]
      type=service
      router=readwritesplit
      servers=server1,server2,server3
      user=maxscale
      passwd=password
      localhost_match_wildcard_host=1
      max_slave_connections=100%
      router_options=slave_selection_criteria=LEAST_CURRENT_OPERATIONS,disable_sescmd_history=true
       
      [CLI]
      type=service
      router=cli
       
      [RW Split Listener]
      type=listener
      service=RW Split Router
      protocol=MySQLClient
      socket=/tmp/maxscale.sock
      port=3306
       
      [CLI Listener]
      type=listener
      service=CLI
      protocol=maxscaled
      port=6603
       
      [server1]
      type=server
      address=galera1
      port=3306
      protocol=MySQLBackend
       
      [server2]
      type=server
      address=galera2
      port=3306
      protocol=MySQLBackend
       
      [server3]
      type=server
      address=galera3
      port=3306
      protocol=MySQLBackend

      This is the PHP script that triggers the memory leak:

      <?php
      $isParent    = true;
      $children    = array();
      $start        = microtime( true);
       
      $stmtCount = (int)$_SERVER[ "argv" ][ 1 ];
      $procCount = (int)$_SERVER[ "argv" ][ 2 ];
      $type = $_SERVER[ "argv" ][ 3 ] ?: 'maxscale';
       
      $ceiling = $procCount;
       
      for ( $i = 0; (( $i < $ceiling) && ( $isParent)); $i++) {
          $pid = pcntl_fork();
          if ( $pid === 0) {
              $isParent = false;
       
          } elseif ( $pid != -1) {
              $children[] = $pid;
          }
      }
       
      function getStatus( $stmtType ) {
          $res = 0;
          for( $i = 1; $i <= 3; $i++ ) {
              $dsn = "mysql:host=galera{$i};dbname=database";
              $dbh = new PDO( $dsn, 'queryuser', 'password' );
              $stmt = $dbh->query( "show global status like 'Com_stmt_{$stmtType}'" );
              $stmt->execute();
              $stmtRes = $stmt->fetch( PDO::FETCH_ASSOC );
              $res += (int)$stmtRes[ 'Value' ];
          }
       
          return $res;
      }
       
      function getDiff() {
          $closed = getStatus( 'close' );
          $prepared = getStatus( 'prepare' );
          return $prepared - $closed;
      }
       
      if( $isParent ) {
          $d1 = getDiff();
          $status = null;
          while ( count( $children)) {
              pcntl_wait( $status);
              array_pop( $children);
          }
       
          echo "Completed in " . ( microtime( true) - $start) . " seconds.\n";
          $d2 = getDiff();
          $leaked = $d2 - $d1;
          echo "D1: {$d1}, D2: {$d2}, Leaked: {$leaked}\n";
          die();
      }
       
      $endpoint = $type === 'maxscale' ? 'unix_socket=/tmp/maxscale.sock' : 'host=galera1';
      $endpoint = 'host=127.0.0.1';
      $dsn = "mysql:{$endpoint};dbname=wiseflow";
       
      $options = array(
          PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
          PDO::ATTR_EMULATE_PREPARES => false,
          PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"
      );
       
      $dbh = new PDO( $dsn, 'queryuser', 'password', $options );
      for( $i = 0; $i < $stmtCount; $i++ ) {
        //  echo "Preparing stmt $i\n";
          $param = 'Com_stmt_prepare';
          $stmt = $dbh->prepare( "show global status where variable_name = ?" );
          $stmt->bindParam( 1, $param, PDO::PARAM_STR );
          $stmt->execute();
          $res = $stmt->fetch( PDO::FETCH_ASSOC );
       
      //    var_dump( $res );
      }

      The script was used to test some other stuff as well so you can disregard the getStatus() and getDiff() calls.
      I was able to reproduce it with a shell script as well:
      mysql -h127.0.0.1 -uqueryuser -ppassword < queries.txt
      queries.txt:
      SET NAMES "UTF8";
      PREPARE s1 FROM 'SHOW GLOBAL STATUS WHERE variable_name = ?';
      SET @a = "Com_stmt_prepare";
      EXECUTE s1 USING @a;
      PREPARE s1 FROM 'SHOW GLOBAL STATUS WHERE variable_name = ?';
      SET @a = "Com_stmt_close";
      EXECUTE s1 USING @a;

      It may take a while, you need to execute it a couple of thousand times

        Attachments

          Activity

            People

            Assignee:
            markus makela markus makela
            Reporter:
            michaeldg Michaël de groot
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.