【 使用环境 】测试环境
【 OB or 其他组件 】
【 使用版本 】
【问题描述】t1表使用zstd_1.3.8压缩模式，t2表使用lz4_1.0压缩模式，分别插入100w数据，t1表大小为5M，t2表大小为2M；手动修改t1表的压缩模式为lz4_1.0后，t1表的大小还是5M（合并后）。再创建一个t3表压缩模式为lz4_1.0，把t1表的数据插入到t3表后，t3表大小为2M（合并后），符合预期。这里的疑问是一个有数据的表，手动修改表的压缩模式后，表的实际大小不会改变了嘛？
【复现路径】具体实验步骤如下
obclient(root@obmysql)[db1]> show create table t1;
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE t1 (
id int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘zstd_1.3.8’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
±------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
obclient(root@obmysql)[db1]> show create table t2;
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t2 | CREATE TABLE t2 (
id int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘lz4_1.0’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.004 sec)
obclient(root@obmysql)[db1]> select count() from t1;
±---------+
| count() |
±---------+
| 1000000 |
±---------+
1 row in set (0.003 sec)
obclient(root@obmysql)[db1]> select count() from t2;
±---------+
| count() |
±---------+
| 1000000 |
±---------+
1 row in set (0.002 sec)
obclient(root@obmysql)[db1]> SELECT /*+ READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(50000000) */ a.svr_ip, a.svr_port, a.database_name, c.object_type, c.object_name, round(SUM(data_size) / 1024 / 1024) AS data_size_mb, round(SUM(required_size) / 1024 / 1024) AS required_size_mb FROM oceanbase.dba_ob_table_locations a JOIN ( SELECT tablet_id, svr_ip, svr_port, data_size, required_size FROM oceanbase.dba_ob_tablet_replicas ) b ON a.tablet_id = b.tablet_id AND a.database_name = ‘db1’ AND a.svr_ip = b.svr_ip AND a.svr_port = b.svr_port JOIN oceanbase.dba_objects c ON a.table_id = c.object_id AND c.object_type = ‘TABLE’ AND c.object_name In (‘t1’,‘t2’) GROUP BY a.svr_ip, a.svr_port, a.database_name, c.object_type, c.object_name ORDER BY required_size_mb DESC;
±----------------±---------±--------------±------------±------------±-------------±-----------------+
| svr_ip | svr_port | database_name | object_type | object_name | data_size_mb | required_size_mb |
±----------------±---------±--------------±------------±------------±-------------±-----------------+
| 192.168.116.168 | 2882 | db1 | TABLE | t1 | 5 | 8 |
| 192.168.116.168 | 2882 | db1 | TABLE | t2 | 2 | 2 |
±----------------±---------±--------------±------------±------------±-------------±-----------------+
2 rows in set (0.008 sec)
obclient(root@obmysql)[db1]> alter table t1 COMPRESSION ‘lz4_1.0’;
Query OK, 0 rows affected (0.075 sec)
obclient(root@obmysql)[db1]> show create table t1;
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE t1 (
id int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘lz4_1.0’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
obclient(root@obmysql)[db1]> create table t3(id int) compression ‘lz4_1.0’;
Query OK, 0 rows affected (0.048 sec)
obclient(root@obmysql)[db1]> insert into t3 select * from t1;
Query OK, 1000000 rows affected (5.825 sec)
Records: 1000000 Duplicates: 0 Warnings: 0
obclient(root@obmysql)[db1]> commit;
Query OK, 0 rows affected (0.000 sec)
obclient(root@obmysql)[db1]> select count() from t3;
±---------+
| count() |
±---------+
| 1000000 |
±---------+
1 row in set (0.172 sec)
obclient(root@obmysql)[db1]> show create table t3;
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t3 | CREATE TABLE t3 (
id int(11) DEFAULT NULL
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = ‘lz4_1.0’ REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
±------±-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)
–合并后，t1表大小没变
obclient(root@obmysql)[db1]> SELECT /*+ READ_CONSISTENCY(WEAK) QUERY_TIMEOUT(50000000) */ a.svr_ip, a.svr_port, a.database_name, c.object_type, c.object_name, round(SUM(data_size) / 1024 / 1024) AS data_size_mb, round(SUM(required_size) / 1024 / 1024) AS required_size_mb FROM oceanbase.dba_ob_table_locations a JOIN ( SELECT tablet_id, svr_ip, svr_port, data_size, required_size FROM oceanbase.dba_ob_tablet_replicas ) b ON a.tablet_id = b.tablet_id AND a.database_name = ‘db1’ AND a.svr_ip = b.svr_ip AND a.svr_port = b.svr_port JOIN oceanbase.dba_objects c ON a.table_id = c.object_id AND c.object_type = ‘TABLE’ AND c.object_name In (‘t1’,‘t2’,‘t3’) GROUP BY a.svr_ip, a.svr_port, a.database_name, c.object_type, c.object_name ORDER BY required_size_mb DESC;
±----------------±---------±--------------±------------±------------±-------------±-----------------+
| svr_ip | svr_port | database_name | object_type | object_name | data_size_mb | required_size_mb |
±----------------±---------±--------------±------------±------------±-------------±-----------------+
| 192.168.116.168 | 2882 | db1 | TABLE | t1 | 5 | 10 |
| 192.168.116.168 | 2882 | db1 | TABLE | t2 | 2 | 2 |
| 192.168.116.168 | 2882 | db1 | TABLE | t3 | 2 | 2 |
±----------------±---------±--------------±------------±------------±-------------±-----------------+
【附件及日志】