ScyllaDB Large Rows and Large Cells Tables

This document describes how to detect large rows and large cells in ScyllaDB. ScyllaDB is not optimized for very large rows or large cells. They require allocation of large, contiguous memory areas and therefore may increase latency. Rows may also grow over time. For example, many insert operations may add elements to the same collection, or a large blob can be inserted in a single operation.

Similar to the :doc:large partitions table <large-partition-table>, the large rows and large cells tables are updated when sstables are written or deleted, for example, on memtable flush or during compaction.

.. note:: The procedures described below require you to be using SSTables in :doc:MC Format (SSTable 3.0) </architecture/sstable/sstable3/sstable-format> or higher.

Find Large Rows ^^^^^^^^^^^^^^^^^^^^^^^^^

Search for large rows.

For example:

.. code-block:: cql

SELECT * FROM system.large_rows;

  keyspace_name | table_name | sstable_name     | row_size | partition_key | clustering_key | compaction_time
  --------------+------------+------------------+----------+---------------+----------------+---------------------------------
  mykeyspace    |         gr | md-1-big-Data.db |  1206130 |             1 |              1 | 2019-06-14 13:03:24.039000+0000

================================================ ================================================================================= Parameter Description ================================================ ================================================================================= keyspace_name The keyspace name that holds the large row

table_name The table name that holds the large row

sstable_name The SSTable name that holds the large row

row_size The size of the row in bytes

clustering_key The clustering key that holds the large row

compaction_time Time when compaction occurred ================================================ =================================================================================

Search within all the large rows for a specific keyspace and or table.

For example we are looking for the keyspace demodb and table tmcr:

.. code-block:: cql

SELECT * FROM system.large_rows WHERE keyspace_name = 'demodb' AND table_name = 'tmcr;

Find Large Cells ^^^^^^^^^^^^^^^^

Search for large cells.

For example:

.. code-block:: cql

> SELECT * FROM system.large_cells;


keyspace_name | table_name | sstable_name     | cell_size | partition_key | clustering_key | column_name | collection_elements | compaction_time                
--------------+------------+------------------+-----------+---------------+----------------+-------------+---------------------+---------------------------------
mykeyspace    |         gr | md-1-big-Data.db |   1206115 |             1 |              1 |        link |               17042 | 2019-06-14 13:03:24.034000+0000

table_name The table name that holds the large cell

sstable_name The SSTable name that holds the large cell

cell_size The size of the cell, in bytes

clustering_key The clustering key of the row that holds the large cell

column_name The column of the large cell

collection_elements The number of elements in the large collection cell

compaction_time Time when compaction occurred ================================================ =================================================================================

Search within all the large cells for a specific keyspace and or table.

For example we are looking for the keyspace demodb and table tmcr:

.. code-block:: cql

SELECT * FROM system.large_cells WHERE keyspace_name = 'demodb' AND table_name = 'tmcr;

Configure ^^^^^^^^^

Configure the detection threshold of large rows and large cells with the corresponding parameters in the scylla.yaml configuration file:

compaction_large_row_warning_threshold_mb parameter (default: 10MB).
compaction_large_cell_warning_threshold_mb parameter (default: 1MB).
compaction_collection_elements_count_warning_threshold parameter (default: 10000).

Once the threshold is reached, the relevant information is captured in the system.large_rows / system.large_cells tables. In addition, a warning message is logged in the ScyllaDB log (refer to :doc:logging </getting-started/logging>).

Storing ^^^^^^^ Large rows and large cells are stored in system tables with the following schemas:

.. code-block:: cql

CREATE TABLE system.large_rows ( keyspace_name text, table_name text, sstable_name text, row_size bigint, partition_key text, clustering_key text, compaction_time timestamp, PRIMARY KEY ((keyspace_name, table_name), sstable_name, row_size, partition_key, clustering_key) ) WITH CLUSTERING ORDER BY (sstable_name ASC, row_size DESC, partition_key ASC, clustering_key ASC) AND bloom_filter_fp_chance = 0.01 AND caching = {'keys': 'ALL', 'rows_per_partition': 'ALL'} AND comment = 'rows larger than specified threshold' AND compaction = {'class': 'SizeTieredCompactionStrategy'} AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'} AND crc_check_chance = 1.0 AND default_time_to_live = 0 AND gc_grace_seconds = 0 AND max_index_interval = 2048 AND memtable_flush_period_in_ms = 0 AND min_index_interval = 128 AND speculative_retry = '99.0PERCENTILE';

CREATE TABLE system.large_cells ( keyspace_name text, table_name text, sstable_name text, cell_size bigint, partition_key text, clustering_key text, column_name text, collection_elements bigint, compaction_time timestamp, PRIMARY KEY ((keyspace_name, table_name), sstable_name, cell_size, partition_key, clustering_key, column_name) ) WITH CLUSTERING ORDER BY (sstable_name ASC, cell_size DESC, partition_key ASC, clustering_key ASC, column_name ASC) AND bloom_filter_fp_chance = 0.01 AND caching = {'keys': 'ALL', 'rows_per_partition': 'ALL'} AND comment = 'cells larger than specified threshold' AND compaction = {'class': 'SizeTieredCompactionStrategy'} AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'} AND crc_check_chance = 1.0 AND default_time_to_live = 0 AND gc_grace_seconds = 0 AND max_index_interval = 2048 AND memtable_flush_period_in_ms = 0 AND min_index_interval = 128 AND speculative_retry = '99.0PERCENTILE';

Expiring Data ^^^^^^^^^^^^^ In order to prevent stale data from appearing, all rows in the system.large_rows and system.large_cells tables are inserted with Time To Live (TTL) equal to 30 days.

.. include:: /troubleshooting/_common/ts-return.rst