Uploaded image for project: 'Percona Server'
  1. Percona Server
  2. PS-5421

MyRocks - Documentation for rocksdb_db_write_buffer_size appears to be incorrect

    Details

    • Type: Improvement
    • Status: On Hold
    • Priority: Medium
    • Resolution: Unresolved
    • Affects Version/s: Not 5.6
    • Fix Version/s: None
    • Component/s: Documentation, MyRocks
    • Labels:
      None
    • Environment:

      Description

      The page I am referring to can be found here

      https://www.percona.com/doc/percona-server/5.7/myrocks/variables.html#rocksdb_db_write_buffer_size

      The text currently states....

      Specifies the size of the memtable used to store writes in MyRocks. This is the size per column family. When this size is reached, the memtable is flushed to persistent media.

      Based on my testing this appears to be maximum allowed size for all write buffers in the MyRocks instance.

      For my test I configured rocksdb_db_write_buffer_size to be 32 Mb

       

      mysql> show global variables like 'Rocksdb_db_write_buffer_size';
      +------------------------------+----------+
      | Variable_name                | Value    |
      +------------------------------+----------+
      | rocksdb_db_write_buffer_size | 33554432 |
      +------------------------------+----------+
      1 row in set (0.01 sec)
      

      I also created two tables, each with their own column family. When I checked the information_schema.ROCKSDB_CF_OPTIONS table, they both showed as having a 64 Mb write buffer per column family.

       

       

      mysql> create database localtest
          -> ;
      Query OK, 1 row affected (0.00 sec)mysql> use localtest;mysql> CREATE TABLE `t1` (
          ->   `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
          ->   `c2` char(255) NOT NULL,
          ->   `c3` char(255) NOT NULL,
          ->   `c4` char(255) NOT NULL,
          ->   `c5` char(255) NOT NULL,
          ->   `c6` char(255) NOT NULL,
          ->   `c7` char(255) NOT NULL,
          ->   `c8` char(255) NOT NULL,
          ->   `c9` char(255) NOT NULL,
          ->   `c10` char(255) NOT NULL,
          ->   `ci` int(11) NOT NULL,
          ->   PRIMARY KEY (`c1`) COMMENT 'cf_t1'
          -> ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1;
      Query OK, 0 rows affected (0.11 sec)mysql> CREATE TABLE `t2` (
          ->   `c1` int(10) unsigned NOT NULL AUTO_INCREMENT,
          ->   `c2` char(255) NOT NULL,
          ->   `c3` char(255) NOT NULL,
          ->   `c4` char(255) NOT NULL,
          ->   `c5` char(255) NOT NULL,
          ->   `c6` char(255) NOT NULL,
          ->   `c7` char(255) NOT NULL,
          ->   `c8` char(255) NOT NULL,
          ->   `c9` char(255) NOT NULL,
          ->   `c10` char(255) NOT NULL,
          ->   `ci` int(11) NOT NULL,
          ->   PRIMARY KEY (`c1`) COMMENT 'cf_t2'
          -> ) ENGINE=ROCKSDB DEFAULT CHARSET=latin1;
      Query OK, 0 rows affected (0.32 sec)mysql> select * from information_schema.ROCKSDB_CF_OPTIONS where option_type = 'WRITE_BUFFER_SIZE';
      +------------+-------------------+----------+
      | CF_NAME    | OPTION_TYPE       | VALUE    |
      +------------+-------------------+----------+
      | __system__ | WRITE_BUFFER_SIZE | 67108864 |
      | cf_t1      | WRITE_BUFFER_SIZE | 67108864 |
      | cf_t2      | WRITE_BUFFER_SIZE | 67108864 |
      | default    | WRITE_BUFFER_SIZE | 67108864 |
      +------------+-------------------+----------+
      4 rows in set (0.01 sec)
      

       

      I then used a tool that I have to write data into the target schema at 1000 records per tables and repeated this process and checked the information_schema.ROCKSDB_CFSTATS page to watch the size of the memtables in memory to see at what point they would rotate to immutable and initiate a flush. It got to about 8 Mb total before flushing.

      mysql> select sum(value) from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2');
      +------------+
      | sum(value) |
      +------------+
      |    9353024 |
      +------------+
      1 row in set (0.00 sec)mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2');
      +--------------+
      | memtablemegs |
      +--------------+
      |   8.91973877 |
      +--------------+
      1 row in set (0.00 sec)....mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2');
      +--------------+
      | memtablemegs |
      +--------------+
      |   4.01597595 |
      +--------------+
      1 row in set (0.00 sec)mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2');
      +--------------+
      | memtablemegs |
      +--------------+
      |   0.78307343 |
      +--------------+
      1 row in set (0.00 sec)
      

      Needless to say, neither of the column family memtables got anywhere close to the 64 Mb limit.

       

      I then tested again, changing only the rocksdb_db_write_buffer_size variable to 128 Mb. When I performed the same write test the combined memtable usage made it all the way to 94 Mb before starting a flush. 

      mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2');
      +--------------+
      | memtablemegs |
      +--------------+
      |   0.00325012 |
      +--------------+
      1 row in set (0.00 sec)mysql> #started data load
      mysql> #started data load
      mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2');
      +--------------+
      | memtablemegs |
      +--------------+
      |   3.46221161 |
      +--------------+
      1 row in set (0.00 sec)....mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE' and cf_name in ('cf_t1', 'cf_t2');
      +--------------+
      | memtablemegs |
      +--------------+
      |  94.33660889 |
      +--------------+
      1 row in set (0.00 sec)
      

      I then performed one additional test leaving the variable at its default setting of 0, which would imply that no limit should be placed on the size of the memtable. In this case it made it all the way to 118 Mb before the flush occured

      mysql> show global variables like 'Rocksdb_db_write_buffer_size';
      +------------------------------+-------+
      | Variable_name                | Value |
      +------------------------------+-------+
      | rocksdb_db_write_buffer_size | 0     |
      +------------------------------+-------+
      1 row in set (0.00 sec)mysql> #data write starts
      mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE';
      +--------------+
      | memtablemegs |
      +--------------+
      |   2.68215942 |
      +--------------+
      1 row in set (0.01 sec)......mysql> select sum(value)/1024/1024 as memtablemegs from information_schema.ROCKSDB_CFSTATS where stat_type = 'CUR_SIZE_ACTIVE_MEM_TABLE';
      +--------------+
      | memtablemegs |
      +--------------+
      | 118.19731903 |
      +--------------+
      1 row in set (0.00 sec)
      

      In conclusion, I believe that the documentation for this variable is wrong and this does NOT set the write buffer size for each column family. In order to set the write buffer size for each column family I had to use the variable rocksdb_default_cf_options and configure the column family option write_buffer_size like so

      rocksdb_default_cf_options = write_buffer_size={value}

      I believe that the rocksdb_db_write_buffer_size sets a maximum cap for the amount of memory being used by active memtables across all column families. If the amount of memory being used across all column families approaches this value it will initiate a rotation to immutable and then flush.

       

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                patrick.birch Patrick Birch
                Reporter:
                Sylvester Peter Sylvester
              • Votes:
                2 Vote for this issue
                Watchers:
                5 Start watching this issue

                Dates

                • Created:
                  Updated: