Understanding MariaDB MyRocks Performance with INFORMATION_SCHEMA.ROCKSDB_CFSTATS Table

• ROCKSDB_CFSTATS Table
  This specific table exposes statistics about MyRocks Column Families (CF). Column families are a way to group related data within a MyRocks table.
• Information Schema
  This is a special set of tables within MariaDB that provide information about the database itself. You can't modify them, but you can query them to understand how MariaDB is functioning.
• MyRocks Storage Engine
  This is an alternative storage engine for MariaDB that leverages RocksDB, a high-performance key-value store.

What the table offers

• It contains columns like CF_NAME (name of the column family), STAT_TYPE (type of statistic, like number of keys, size), and VALUE (the actual statistical value).
• The ROCKSDB_CFSTATS table provides insights into the performance of your MyRocks tables.

How to access it

• To view the table structure and contents, you can use a SELECT statement like this:
• You need the PROCESS privilege on the MariaDB server to even query it.
• You can't directly manipulate this table using standard SQL statements like INSERT or UPDATE.

SELECT * FROM INFORMATION_SCHEMA.ROCKSDB_CFSTATS;

---

SELECT 
  CF_NAME as column_family_name,
  STAT_TYPE as statistic_type,
  VALUE as statistic_value
FROM INFORMATION_SCHEMA.ROCKSDB_CFSTATS
WHERE CF_NAME = 'default'  /* Replace 'default' with your actual column family name */
ORDER BY statistic_type;

This query retrieves statistics for the column family named default (replace with your actual column family name). It selects the CF_NAME, STAT_TYPE, and VALUE columns and orders them by STAT_TYPE for better readability.

---

1. MyRocks SHOW ENGINE ROCKSDB STATUS
   This command provides a more detailed breakdown of MyRocks engine statistics. It includes information like table size, key and value counts, and various compaction metrics. You can access it using the following command within your MariaDB client:

SHOW ENGINE ROCKSDB STATUS;

1. EXPLAIN PLAN
   While not specific to MyRocks, the EXPLAIN PLAN statement can be helpful for understanding how the MariaDB optimizer utilizes indexes and chooses execution plans for your queries. This can indirectly provide insights into how MyRocks is being used for specific tables.

2. Performance Monitoring Tools
   Consider using external monitoring tools that integrate with MariaDB. These tools can track various performance metrics, including storage engine details. Popular options include:

   • MySQL Enterprise Monitor (commercial)
   • Percona Monitoring and Management (commercial)
   • Prometheus with the mysql_exporter (open-source)

The best approach depends on what specific information you're looking for.

• For comprehensive performance monitoring, consider external tools.
• For broader engine-level statistics and understanding query execution plans, explore SHOW ENGINE ROCKSDB STATUS and EXPLAIN PLAN.
• If you need detailed statistics on MyRocks column families, the ROCKSDB_CFSTATS table remains the most relevant option.