MyRocks: doubts on column-family and indexing behaviour

293 views

Skip to first unread message

Upendra Yadav

unread,

Jun 8, 2021, 1:02:04 PM6/8/21

to MyRocks - RocksDB storage engine for MySQL

Hi,

I'm testing MyRocks for our specific use case. Below is the table structure:

CREATE TABLE `usertable1` (

`id` varchar(50) COLLATE utf8_bin NOT NULL,

`partition_info` text COLLATE utf8_bin NOT NULL,

`chunk_id` bigint(20) NOT NULL,

`topic_partition` varchar(50) COLLATE utf8_bin NOT NULL,

`data` mediumtext COLLATE utf8_bin NOT NULL,

PRIMARY KEY (`id`) COMMENT 'rev:cf_1',

KEY `index1` (`partition_info`(255),`chunk_id`,`topic_partition`) COMMENT 'rev:cf_1'

) ENGINE=ROCKSDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='ttl_duration=604800;'

I have some doubts and little understanding about MyRocks, that wants to verify here:

1. Below, I'm using 'select *', So it is doing row scan. Here resulted rows are in reverse order, because the column family is defined as 'rev:cf_1'.
Do this ordering based on a particular column value or it is just following insertion order of rows?

mysql> explain select * from usertable1 limit 10;

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+

1 row in set, 1 warning (0.00 sec)

mysql> select max(id), min(id) from usertable1;

+---------------+---------------+

| max(id) | min(id) |

+---------------+---------------+

| 1623142509296 | 1623136749420 |

+---------------+---------------+

1 row in set (0.00 sec)

mysql> select * from usertable1 limit 10;

+---------------+------------------+----------------+-----------------+-------------+

+---------------+------------------+----------------+-----------------+-------------+

+---------------+------------------+----------------+-----------------+-------------+

10 rows in set (0.00 sec)

2. Here, I'm projecting id column only. Why it is using index1?
And if I do not specify order by then how result follows ordering?

mysql> explain select id from usertable1 limit 10;

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> select id from usertable1 limit 10;

+---------------+

| id |

+---------------+

| 1623136749949 |

| 1623136749950 |

| 1623136749969 |

| 1623136750002 |

| 1623136750044 |

| 1623136750091 |

| 1623136750162 |

| 1623136750170 |

| 1623136750217 |

| 1623136750226 |

+---------------+

10 rows in set (0.00 sec)

mysql> explain select id from usertable1 order by id asc limit 10;

+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> select id from usertable1 order by id asc limit 10;

+---------------+

| id |

+---------------+

| 1623136749420 |

| 1623136749421 |

| 1623136749422 |

| 1623136749423 |

| 1623136749424 |

| 1623136749425 |

| 1623136749426 |

| 1623136749427 |

| 1623136749428 |

| 1623136749429 |

+---------------+

10 rows in set (0.00 sec)

3. Below, it use index1 to list rows.

mysql> explain select topic_partition from usertable1 limit 10;

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> select topic_partition from usertable1 limit 10;

+-----------------+

| topic_partition |

+-----------------+

| Topic-0 |

+-----------------+

10 rows in set (0.00 sec)

4. Below, column partition_info is TEXT type. So this query is not using index. It do row scan. Will Indexing work on TEXT type columns? In what case it will work?

mysql> explain select id, partition_info from usertable1 limit 10;

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+

1 row in set, 1 warning (0.00 sec)

mysql> select id, partition_info from usertable1 limit 10;

+---------------+------------------+

| id | partition_info |

+---------------+------------------+

| 1623142509296 | PartitionValue-1 |

| 1623142509295 | PartitionValue-3 |

| 1623142509294 | PartitionValue-3 |

| 1623142509293 | PartitionValue-4 |

| 1623142509292 | PartitionValue-0 |

| 1623142509291 | PartitionValue-0 |

| 1623142509290 | PartitionValue-0 |

| 1623142509289 | PartitionValue-3 |

| 1623142509288 | PartitionValue-0 |

| 1623142509287 | PartitionValue-3 |

+---------------+------------------+

10 rows in set (0.00 sec)

5. How many column families should I use if I'm going to create 10k tables?
Each active user may have 5 to 20 tables. Append type of data(insert only) with 7 days TTL. machine config: 1 TB SSD, 64 GB RAM.

Matsunobu Yoshinori

unread,

Jun 9, 2021, 1:33:37 AM6/9/21

to MyRocks - RocksDB storage engine for MySQL

Hi, let me answer as follows.

> 1. Below, I'm using 'select *', So it is doing row scan. Here resulted rows are in reverse order, because the column family is defined as 'rev:cf_1'.
Do this ordering based on a particular column value or it is just following insertion order of rows?

Like InnoDB, MyRocks adopts clustered index structure.

Your query " select * from usertable1 limit 10" uses primary key, and the column family is inverse order,

so rows are stored in descending order by the primary key. You can see that results are ordered by id in descending order.

> 2. Here, I'm projecting id column only. Why it is using index1?
And if I do not specify order by then how result follows ordering?

Because secondary indexes of the clustered index structure have primary key columns inside,

and in your case secondary index is smaller than primary key. So optimizer decided to use

secondary key instead of primary key.

You will see a different query plan when you use ORDER BY.

> 3. Below, it use index1 to list rows.

I am not exactly sure what your question was, but #2 would explain why index1 was used.

> 4. Below, column partition_info is TEXT type. So this query is not using index. It do row scan. Will Indexing work on TEXT type columns? In what case it will work?

No, indexing the entire TEXT/BLOB fields is not supported. You can create an index with prefix though.

> 5. How many column families should I use if I'm going to create 10k tables?
Each active user may have 5 to 20 tables. Append type of data(insert only) with 7 days TTL. machine config: 1 TB SSD, 64 GB RAM.

We normally recommend less than 10 column families, but it depends on many factors such as write volume.

Your table definition does not use an explicit TTL timestamp column, but we recommend defining it.

Implicit TTL column means once you insert rows, you can't change expiration time. Dumping and loading the table

will update expiration time, which may be confusing. With explicit TTL column, you can easily know when the rows will expire.

- Yoshinori

Upendra Yadav

unread,

Jun 9, 2021, 3:16:05 AM6/9/21

to MyRocks - RocksDB storage engine for MySQL

Thank a lot for replying..

Can we have - 10 column families per table or overall only 10 column families in a myRocks system that can be shared by multiple tables?

In myrocks documents, I read somewhere - each column family have its own resources like - memtable(64 MB in memory) and configs.

In previous mail, I missed 3rd question:

3. Below, it use index1 to list rows. Why it is listing chunk_id and topic_partition in ascending order? I have defined index1 with reverse column family: 'rev:cf_1'.

mysql> explain select topic_partition from usertable1 limit 10;

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> select topic_partition from usertable1 limit 10;

+-----------------+

| topic_partition |

+-----------------+

| Topic-0 |

+-----------------+

10 rows in set (0.00 sec)

mysql> explain select id, chunk_id, topic_partition from usertable1 limit 20;

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

+----+-------------+------------+------------+-------+---------------+--------+---------+------+-------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

mysql> select id, chunk_id, topic_partition from usertable1 limit 20;

+---------------+----------------+-----------------+

| id | chunk_id | topic_partition |

+---------------+----------------+-----------------+

| 1623136749949 | 20210608000000 | Topic-0 |

| 1623136749950 | 20210608000000 | Topic-0 |

| 1623136749969 | 20210608000000 | Topic-0 |

| 1623136750002 | 20210608000000 | Topic-0 |

| 1623136750044 | 20210608000000 | Topic-0 |

| 1623136750091 | 20210608000000 | Topic-0 |

| 1623136750162 | 20210608000000 | Topic-0 |

| 1623136750170 | 20210608000000 | Topic-0 |

| 1623136750217 | 20210608000000 | Topic-0 |

| 1623136750226 | 20210608000000 | Topic-0 |

| 1623136750281 | 20210608000000 | Topic-0 |

| 1623136750291 | 20210608000000 | Topic-0 |

| 1623136750341 | 20210608000000 | Topic-0 |

| 1623136750347 | 20210608000000 | Topic-0 |

| 1623136750355 | 20210608000000 | Topic-0 |

| 1623136750375 | 20210608000000 | Topic-0 |

| 1623136750392 | 20210608000000 | Topic-0 |

| 1623136750406 | 20210608000000 | Topic-0 |

| 1623136912344 | 20210608000000 | Topic-0 |

| 1623136912365 | 20210608000000 | Topic-0 |

+---------------+----------------+-----------------+

20 rows in set (0.00 sec)

mysql> select max(chunk_id), min(chunk_id) from usertable1;

+----------------+----------------+

| max(chunk_id) | min(chunk_id) |

+----------------+----------------+

| 20210609000000 | 20210608000000 |

+----------------+----------------+

1 row in set (0.11 sec)

Another question:
For TEXT/BLOB column, If I create an index with prefix, then also it do row scan only. Why?

in my table structure, i have define index: KEY `index1` (`partition_info`(255),`chunk_id`,`topic_partition`) COMMENT 'rev:cf_1'

Here, partition_info is a TEXT column. But still below query do row scan. It do not use Index.

mysql> explain select id, partition_info from usertable1 limit 20;

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+

+----+-------------+------------+------------+------+---------------+------+---------+------+-------+----------+-------+

1 row in set, 1 warning (0.00 sec)

mysql> select id, partition_info from usertable1 limit 20;

+---------------+------------------+

| id | partition_info |

+---------------+------------------+

| 1623142509296 | PartitionValue-1 |

| 1623142509295 | PartitionValue-3 |

| 1623142509294 | PartitionValue-3 |

| 1623142509293 | PartitionValue-4 |

| 1623142509292 | PartitionValue-0 |

| 1623142509291 | PartitionValue-0 |

| 1623142509290 | PartitionValue-0 |

| 1623142509289 | PartitionValue-3 |

| 1623142509288 | PartitionValue-0 |

| 1623142509287 | PartitionValue-3 |

| 1623142509286 | PartitionValue-4 |

| 1623142509285 | PartitionValue-0 |

| 1623142509284 | PartitionValue-4 |

| 1623142509283 | PartitionValue-4 |

| 1623142509282 | PartitionValue-4 |

| 1623142509281 | PartitionValue-2 |

| 1623142509280 | PartitionValue-2 |

| 1623142509279 | PartitionValue-1 |

| 1623142509278 | PartitionValue-1 |

| 1623142509277 | PartitionValue-0 |

+---------------+------------------+

20 rows in set (0.00 sec)

Matsunobu Yoshinori

unread,

Jun 23, 2021, 2:39:22 PM6/23/21

to MyRocks - RocksDB storage engine for MySQL

> Can we have - 10 column families per table or overall only 10 column families in a myRocks system that can be shared by multiple tables?

There is no column family limit in MyRocks. I'd suggest testing with your data to find reasonable column family allocations.

Keep in mind that you can define column family options by rocksdb_default_cf_options my.cnf variable.

> 3. Below, it use index1 to list rows. Why it is listing chunk_id and topic_partition in ascending order? I have defined index1 with reverse column family: 'rev:cf_1'.

Use ORDER BY if you want specific ordering. Reverse column family means descending scan is faster but does not mean

it returns rows with descending order without ORDER BY DESC.

> For TEXT/BLOB column, If I create an index with prefix, then also it do row scan only. Why?

Please test with InnoDB and report if you see behavior differences between InnoDB and MyRocks.