GBase 8a 空洞率,被删除数据的比例,shring space 释放磁盘空间

GBase 8a数据库在数据被删除时,并没有释放磁盘空间,而是只打了一个【已删除】的标记。,这就会导致有效数据,在磁盘上是不连续的,其比例就是空洞率。 在大数据场景,少量空洞是可以接受的,但如果已经对性能有了实质影响,则需要进行shrink space重整。

数据库的元数据表,提供了查询空洞率的方法,其中的DELETE_RATIO就是空洞率。

select * from performance_schema.tables where table_schema='DBNAME' and table_name='TBNAME';

如下是一个验证场景

场景

gbase> select * from  performance_schema.tables where table_schema='testdb' and table_name='hr';
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | MAX_ROWID | DELETE_ROWS | TABLE_ROWS | STORAGE_SIZE | DELETABLE_SIZE | SHRINKABLE_SIZE | DELETE_RATIO |
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
| testdb       | hr         |     31216 |           0 |      31216 |       651264 |              0 |               0 |            0 |
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> desc hr;
+----------+---------------+------+-----+---------+-------+
| Field    | Type          | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| id       | int(11)       | YES  |     | NULL    |       |
| name     | varchar(100)  | YES  |     | NULL    |       |
| joindate | date          | YES  |     | NULL    |       |
| salary   | decimal(10,2) | YES  |     | NULL    |       |
+----------+---------------+------+-----+---------+-------+

删除一部分数据,再查看

gbase> delete from hr where salary > 0.9;
Query OK, 3114 rows affected (Elapsed: 00:00:00.14)

gbase> select * from  performance_schema.tables where table_schema='testdb' and table_name='hr';
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | MAX_ROWID | DELETE_ROWS | TABLE_ROWS | STORAGE_SIZE | DELETABLE_SIZE | SHRINKABLE_SIZE | DELETE_RATIO |
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
| testdb       | hr         |     31216 |        3114 |      28102 |       659456 |          65785 |               0 |         9.98 |
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
1 row in set (Elapsed: 00:00:00.00)

解决方案

可以通过shrink space [full] 来解决。其中full是完整的重建,不带full是只删除一个数据文件内,全部都处于DELETE标识的数据,如果这个文件内有1行有效,则该文件不会被删除。

对于数据量极大的表,一般是按天做老化删除,建议不带FULL,对于频发变动的小表,一段时间后,建议用带FULL的处理一下。

gbase> alter table hr shrink space;
Query OK, 0 rows affected (Elapsed: 00:00:00.13)

gbase> select * from  performance_schema.tables where table_schema='testdb' and table_name='hr';
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | MAX_ROWID | DELETE_ROWS | TABLE_ROWS | STORAGE_SIZE | DELETABLE_SIZE | SHRINKABLE_SIZE | DELETE_RATIO |
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
| testdb       | hr         |     31216 |        3114 |      28102 |       659456 |          65785 |               0 |         9.98 |
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> alter table hr shrink space full;
Query OK, 0 rows affected (Elapsed: 00:00:00.29)

gbase> select * from  performance_schema.tables where table_schema='testdb' and table_name='hr';
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
| TABLE_SCHEMA | TABLE_NAME | MAX_ROWID | DELETE_ROWS | TABLE_ROWS | STORAGE_SIZE | DELETABLE_SIZE | SHRINKABLE_SIZE | DELETE_RATIO |
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
| testdb       | hr         |     28102 |           0 |      28102 |       561152 |              0 |               0 |            0 |
+--------------+------------+-----------+-------------+------------+--------------+----------------+-----------------+--------------+
1 row in set (Elapsed: 00:00:00.00)

发表评论

您的电子邮箱地址不会被公开。