Hologres:Best practices for specifying table groups

In practice, you may need to determine the most suitable range of the shard count when the data amount is predictable. The most suitable shard count is not only related to the amount of data to be stored, but also related to the actual access frequency, the actual amount of accessed data, the computing load type such as point query or data analysis, the write throughput, and the number of tables in the table group. Therefore, you cannot obtain the accurate value for the most suitable shard count. The following table describes the recommended shard counts and instance specifications that correspond to specific data amount ranges. You can select suitable settings based on the estimated data amount.

You can determine whether to create a table group and how to select a table group for your tables based on the best practices in the following plans:

• Plan 1: Use the default table group

  If your Hologres instance meets the following conditions, we recommend that you use the default table group. After you upgrade or downgrade the specifications of a Hologres instance, the shard count of the default table group does not change. You can execute the following statement to query the shard count:

  SELECT * FROM hologres.hg_table_group_properties;
  
  -- Sample result 
   tablegroup_name | property_key  | property_value
  -----------------+---------------+----------------
   test_tg_default | is_default_tg | 1
   test_tg_default | shard_count   | 40
   test_tg_default | tg_version    | 1
   test_tg_default | table_num     | 1
  (4 rows)

  • Data amount

    The shard count of the default table group meets the requirement of the data amount. In this case, you can create tables in the default table group.

  • Overall size

    The total data amount of all tables is controllable and predictable. The usage mode does not significantly change.

  • Local join

    You need to perform efficient local join operations on tables in the default table group.

• Plan 2: Create a table group

  The default table group cannot meet your requirements, and you may need multiple table groups. In most cases, multiple table groups may be required in your instance under the following conditions:

  • Data amount

    The shard count of the existing table group is inappropriate for the estimated data amount in the current table. If the shard count is large and the data amount is small, excessive small files are generated and the I/O overhead is high. If the shard count is small and the data amount is large, the concurrency of queries is reduced. In this case, multiple table groups are required.

  • Independent load

    The existing table group contains a large number of tables, and data needs to be simultaneously written to most of the tables. As a result, the instance load is high. In addition, the table to be created requires high query and write throughput. In this case, multiple table groups are required to make data write and query independent to some extent. For more information, see topics about how to isolate computing resources. If you determine that the existing table group cannot meet the write and query requirements, multiple table groups are also required.

  • Table correlation

    If a set of tables in your business have a unique data write or query pattern, have or will have local join requirements, and have few or no correlation with tables in the existing table group, you can create multiple independent table groups for the set of tables. Local join operations can be performed only on tables with the join key as the distribution key and in the same table group. You can create a table group for a set of tables that have a strong correlation with each other but have few correlation and a low probability of local join with tables in the existing table group.

  • Instance resource scaling

    If your instance has been scaled in or out by more than five times, the original shard count may no longer meet the requirements. In this case, you can change the default table group. The number of shards must be greater than the number of compute nodes and less than 60% of the total number of CPU cores.

• Plan 3: Add tables to multiple table groups

  If you need to plan multiple table groups, we recommend that you plan the roles and significance of the table groups and the table group to which each table belongs before stress testing and production. You can consider the following factors during planning:

  • Data amount

    The shard count is determined by the amount of data to be stored in a table. A table group that has a large shard count is appropriate for large tables, and a table group that has a small shard count is appropriate for small and medium tables.

  • Required write performance

    The shard count has a positive correlation with data write performance. The write capability of a single shard has an upper limit. More shards indicate higher write concurrency and throughput. If you need to write data to a table at a high records per second (RPS), a larger shard count may be required. If the CPU utilization for a single core is 100%, a single shard in Hologres writes data at an RPS of 3,000 to 5,000 (1 KB per record). You can estimate the required shard count based on your required RPS. Each shard also needs to perform read operations such as data query. Therefore, the CPU utilization for data write cannot reach 100%. A shard that uses a 1/3 CPU core writes data at an RPS of 1,000 (1 KB per record). For example, if you want to write data at an RPS of 60,000 and the size of each record is 1 KB, the shard count must be greater than 60. The shard count can be fine-tuned.

  • Load of each table group

    When you create a table group, you must consider the number of tables to be added to this table group. If a large number of tables will be added to this table group in the future, and most of the tables are frequently accessed, a small shard count may fail to support highly concurrent queries.