You can improve the data storage and computing efficiency by using an appropriate table group and an appropriate shard count. This topic describes how to manage a table group and specify a shard count in Hologres.
Query the metadata of table groups
- Query the default table group.
SELECT * FROM hologres.hg_table_group_properties WHERE tablegroup_name IN ( SELECT tablegroup_name FROM hologres.hg_table_group_properties WHERE property_key = 'is_default_tg' AND property_value = '1' );Note In the returned result, the is_default_tg property indicates whether the table group is the default table group, and the shard_count property indicates the shard count of the table group.
tablegroup_name | property_key | property_value -----------------+------------------+---------------- test_tg_default | tg_version | 1 test_tg_default | table_num | 1 test_tg_default | is_default_tg | 1 test_tg_default | shard_count | 3 test_tg_default | replica_count | 1 test_tg_default | created_manually | 0 (6 rows)
- Query table groups in the current database.
SELECT tablegroup_name FROM hologres.hg_table_group_properties GROUP BY tablegroup_name;
tablegroup_name ----------------- test_tg_default (1 row)
- Query the shard count of a table group.
SELECT property_value AS shard_count FROM hologres.hg_table_group_properties WHERE property_key = 'shard_count' AND tablegroup_name ='<tg_name>';
shard_count ------------- 3 (1 row)
- Query tables in a table group.
SELECT table_namespace AS schema_name, table_name FROM hologres.hg_table_properties WHERE property_key = 'table_group' AND property_value = '<tg_name>';
schema_name | table_name -------------+------------ public | a (1 row)
- Query the table group to which a table belongs.
SELECT property_value AS table_group_name FROM hologres.hg_table_properties WHERE property_key = 'table_group' AND table_name = '<table_name>';
table_group_name ------------------ test_tg_default (1 row)
Create a table group
- After a new table group is generated, the original tables and data still belong to the original table group and are not automatically migrated to the new table group.
- The original table group becomes ineffective only after its tables and data are migrated to the new table group or deleted.
The following table describes the parameters.
CALL HG_CREATE_TABLE_GROUP ('<new_tg_name>', <shard_count>);
|new_tg_name||Text||The name of the table group to be created.|
|shard_count||INT4||The shard count for the table group.|
-- Create a table group that is named tg_8 and has eight shards. CALL HG_CREATE_TABLE_GROUP ('tg_8', 8);
Change the default table group
A default table group with a specific number of shards is created for each database in a Hologres instance. For more information, see Instance types. If the database contains multiple table groups, and you want to change the default table group to store subsequently created tables in the new default table group, you can execute the following statement.
The following table describes the parameter.
CALL HG_UPDATE_DATABASE_PROPERTY ('default_table_group', '<tg_name>');
|tg_name||TEXT||The name of the new default table group. The shard count of the table group determines the number of shards in the table group.|
-- In Hologres V0.9 and later, you can change the default table group. After the setting takes effect, subsequently created tables are stored in the specified table group. CALL HG_UPDATE_DATABASE_PROPERTY ('default_table_group', 'tg_8');
Create a table and add it to a specified table group
The following table describes the parameters.
BEGIN; CREATE TABLE table_name ( col type, .... ); CALL SET_TABLE_PROPERTY('table_name', 'table_group', '<tg_name>'); COMMIT;
|table_name||TEXT||The name of the table to be created.|
|tg_name||TEXT||The name of the default table group. The shard count of the table group determines the number of shards in the table group.|
-- Create a table named tbl1 and add it to the table group named tg_8. BEGIN; CREATE TABLE tbl1 ( col1 text ); CALL SET_TABLE_PROPERTY('tbl1', 'table_group', 'tg_8'); COMMIT;
Use the resharding feature to migrate tables to a new table group
In Hologres, shards are used to improve the concurrency of data processing. You can set the shard count to an appropriate value to improve query or write performance. In most scenarios, the default shard count for a Hologres instance can meet your requirements. You do not need to change the shard count.
After a Hologres instance is scaled up, such as from 32 CPU cores to 128 CPU cores, the shard count does not change for existing databases in the Hologres instance. If you want to improve the performance of a database, we recommend that you increase the shard count for the database. If you create a database in the Hologres instance, the default shard count for the current instance type applies. For more information about instance types and shards, see Instance types.
After a Hologres instance is scaled up or down, the shard count does not change for existing databases. You can execute statements to change the shard count and import data again. Hologres provides the resharding feature. After you change the shard count, you can use the resharding feature to rebalance the existing tables and data among shards, without the need to create tables and import data again. This simplifies the resharding procedure and achieves optimal performance.
- The resharding feature is supported only in Hologres V0.10 and later. You can check the version of your Hologres instance on the instance details page in the Hologres console. If the version of your instance is earlier than V0.10, submit a ticket to update your instance.
- Resharding is performed on a single table at a time. When you perform resharding on a table, you must stop writing data to the table by using Flink or Data Integration. You can still query data in the table.
- Resharding consumes a specific number of resources. We recommend that you use the resharding feature during off-peak hours.
- If binary logging is enabled for a table, you must disable binary logging before you perform resharding on the table. You can re-enable binary logging after resharding is complete. For more information, see Subscribe to Hologres binlogs.
- You cannot perform resharding on a table if the table contains an auto-increment field of the SERIAL data type or the table is created with the DEFAULT constraint. If the table contains an auto-increment field of the SERIAL data type, an error occurs during resharding. If the table is created with the DEFAULT constraint, the DEFAULT constraint becomes ineffective after resharding.
- You cannot perform resharding on a table if the table depends on an object, such as a view. You must remove the relevant dependencies before you perform resharding.
You can execute one of the following statements to migrate some business tables to a new table group.Note
- You must create a table group before you can migrate tables to the table group. For more information about how to create a table group, see Create a table group.
- When you migrate tables, you must stop writing data to the tables. You can still query data in these tables.
- After all tables in the original table group are migrated, the original table group is automatically deleted. If you need to create multiple table groups based on your business requirements, we recommend that you set the shard count to an appropriate value for each table group.
- When you migrate partitioned tables, you need to migrate only parent partitioned tables.
-- Syntax in Hologres V1.1 and later CALL HG_MOVE_TABLE_TO_TABLE_GROUP('<table_name>','<new_table_group_name>'); -- Syntax in Hologres V0.10 CALL HG_UPDATE_TABLE_SHARD_COUNT('<table_name>','<new_table_group_name>');
Parameter Description Example table_name The name of the table that you want to migrate. You can execute one of the preceding statements to migrate a single table at a time. If you want to migrate multiple tables, execute the statement multiple times. If the table that you want to migrate is a partitioned table, you need to migrate only the parent partitioned table. new_table table_group_name The name of the table group to which the table is to be migrated. new_tg
Delete a table group
-- Delete the table group named tg_8. CALL HG_DROP_TABLE_GROUP('tg_8');
In most cases, we recommend that you do not create a table group or change the shard count of the table group. If you need to create table groups based on your business requirements, you can make plans on the table groups based on the best practices. For more information, see Best practices for specifying table groups.
Troubleshooting related to resharding
Resharding is a complex process that involves multiple steps, such as the creation of temporary tables, changes in the Read Only state of source tables, data writes to destination tables, changes in table names, and recording of the synchronization status. If one or more steps encounter exceptions and the system enters an unknown state, you can use the following solution for troubleshooting.
internal error: Get rundown is not allowed in recovering stateerror message is returned, the table that is being updated is in the Read Only state. In this case, you cannot perform an insert, update, or delete operation on the table unless the table is not in the Read Only state.
- Execute the following statement to query the table that is in the Read Only table:
select * from hologres.hg_table_properties where property_key ='readonly' and property_value='true';
- Execute the following statement to disable Read Only for the table:
table_name specifies the name of the table for which you want to disable Read Only.