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

MyRocks: Corrected documentation for rocksdb_db_write_buffer_size.

    XMLWordPrintable

Details

    • Bug
    • Status: Done
    • Medium
    • Resolution: Fixed
    • Not 5.6.x
    • 5.7.29-32, 8.0.19-10
    • Documentation, MyRocks
    • None

    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.

       

      Attachments

        Activity

          People

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

            Dates

              Created:
              Updated:
              Resolved:

              Time Tracking

                Estimated:
                Original Estimate - Not Specified
                Not Specified
                Remaining:
                Remaining Estimate - Not Specified
                Not Specified
                Logged:
                Time Spent - 2 hours
                2h

                Smart Checklist