# OOM due to missing support of BIT data type pushdown to TiFlash ## Background The `BIT` data type (and several other types) cannot be pushed down to TiFlash. Queries that filter on a column of type `BIT` or another unsupported data type will require rows to be returned to TiDB for filtering. This can cause unexpected memory usage in multiple components of the cluster. You can find the list of data types supported for TiFlash pushdown at the end of the [Supported push-down calculations section in Use TiFlash](https://docs.pingcap.com/tidb/stable/use-tiflash#supported-push-down-calculations): "expressions that contain the Time/Bit/Set/Enum/Geometry type cannot be pushed down to TiFlash". If you are executing a join for which the optimizer chooses an execution plan that includes both TiKV and TiFlash, more operations than expected can be pushed to TiKV (`tikv-server`), which can cause OOM (Out of Memory). It's also possible that an unexpected number of rows will need to be transferred to TiDB to perform filtering, which can cause TiDB (`tidb-server`) to use more memory than expected and encounter OOM (Out of Memory). This simple demonstration shows the different pushdown behavior in TiFlash when using the `BIT` vs. the `INT` data type: ``` mysql> show create table t1\G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `b` bit(1) DEFAULT NULL, `i` int(11) DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=2060001 1 row in set (0.00 sec) mysql> explain select count(*) from t1 where b=0x01; +-------------------------------+----------+--------------+---------------+------------------------------------------------+ | id | estRows | task | access object | operator info | +-------------------------------+----------+--------------+---------------+------------------------------------------------+ | HashAgg_8 | 1.00 | root | | funcs:count(1)->Column#3 | | └─Selection_18 | 52428.80 | root | | eq(test.t1.b, 1) | | └─TableReader_21 | 65536.00 | root | | data:ExchangeSender_20 | | └─ExchangeSender_20 | 65536.00 | cop[tiflash] | | ExchangeType: PassThrough | | └─TableRangeScan_19 | 65536.00 | cop[tiflash] | table:t1 | range:[0,+inf], keep order:false, stats:pseudo | +-------------------------------+----------+--------------+---------------+------------------------------------------------+ 5 rows in set (0.00 sec) mysql> explain select count(*) from t1 where i=1; +---------------------------------+----------+-------------------+---------------+----------------------------------+ | id | estRows | task | access object | operator info | +---------------------------------+----------+-------------------+---------------+----------------------------------+ | HashAgg_26 | 1.00 | root | | funcs:count(Column#6)->Column#4 | | └─TableReader_28 | 1.00 | root | | data:ExchangeSender_27 | | └─ExchangeSender_27 | 1.00 | batchCop[tiflash] | | ExchangeType: PassThrough | | └─HashAgg_9 | 1.00 | batchCop[tiflash] | | funcs:count(1)->Column#6 | | └─Selection_25 | 0.00 | batchCop[tiflash] | | eq(test.t1.i, 1) | | └─TableRangeScan_24 | 65536.00 | batchCop[tiflash] | table:t1 | range:[0,+inf], keep order:false | +---------------------------------+----------+-------------------+---------------+----------------------------------+ 6 rows in set (0.00 sec) ``` If you compare the number of estRows for the "root" tasks in each result, you can see that many more rows are processed by the "root" task when filtering on the `BIT` column than on the `INT` column. ## Diagnosis You can use this query to identify tables that use the `BIT` data type: ``` select table_schema, table_name, column_name, data_type, column_type from information_schema.columns where data_type='bit'; ``` ``` mysql> select table_schema, table_name, column_name, data_type, column_type from information_schema.columns where data_type='bit'; +--------------+------------+-------------+-----------+-----------------+ | table_schema | table_name | column_name | data_type | column_type | +--------------+------------+-------------+-----------+-----------------+ | test | t1 | b | bit | bit(1) unsigned | +--------------+------------+-------------+-----------+-----------------+ 1 row in set (0.01 sec) ``` And you can use this query to find all tables with `BIT` columns that have TiFlash replicas: ``` select table_schema, table_name, column_name, data_type, column_type from information_schema.columns join information_schema.tiflash_replica using (table_schema, table_name) where data_type='bit'; ``` ``` 5.7.25-TiDB-v5.2.2 (root) [test]> select table_schema, table_name, column_name, data_type, column_type, replica_count from information_schema.columns join information_schema.tiflash_replica using (table_schema, table_name) where data_type='bit'; +--------------+------------+-------------+-----------+-----------------+---------------+ | table_schema | table_name | column_name | data_type | column_type | replica_count | +--------------+------------+-------------+-----------+-----------------+---------------+ | test | t1 | b | bit | bit(1) unsigned | 1 | +--------------+------------+-------------+-----------+-----------------+---------------+ 1 row in set (0.01 sec) ``` ## Workarounds 1. If you need to push work down to TiFlash, avoid `BIT` and other unsupported data types, and use `INT`, `BINARY`, or another supported data type instead. 2. If you must use `BIT` or another unsupported data type, increase the amount of memory available to the node type(s) that encounter OOM.