Create a Table and Specify the Number of Regions, but Why Are All Regions Allocated to One Node?

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 创建一张表并指定region的个数,但是region为啥都分配到一个节点上

| username: TiDBer_yBunUeUc

[TiDB Usage Environment] Production Environment
Issue: Creating a table and specifying the number of regions, but all regions are allocated to one node.
Configuration: Production TiDB cluster, 3 TiKV and PD nodes, 2 TiFlash nodes, hardware with 32-core CPU, 32GB memory, 500GB FC storage (HDD and SSD mixed)
Screenshot:

CREATE TABLE `requests` (
  `request_id` bigint(20) NOT NULL,
  `last_update_date` datetime DEFAULT NULL,
  `last_updated_by` bigint(20) DEFAULT NULL,
  `request_date` datetime DEFAULT NULL,
  `requested_by` bigint(20) DEFAULT NULL,
  `phase_code` varchar(1) DEFAULT NULL,
  `status_code` varchar(1) DEFAULT NULL,
  `priority_request_id` bigint(20) DEFAULT NULL,
  `priority` bigint(20) DEFAULT NULL,
  `requested_start_date` datetime DEFAULT NULL,
  `hold_flag` varchar(1) DEFAULT NULL,
  `enforce_seriality_flag` varchar(1) DEFAULT NULL,
  `single_thread_flag` varchar(1) DEFAULT NULL,
  `has_sub_request` varchar(1) DEFAULT NULL,
  `is_sub_request` varchar(1) DEFAULT NULL,
  `implicit_code` varchar(1) DEFAULT NULL,
  `update_protected` varchar(1) DEFAULT NULL,
  `queue_method_code` varchar(1) DEFAULT NULL,
  `argument_input_method_code` varchar(1) DEFAULT NULL,
  `oracle_id` bigint(20) DEFAULT NULL,
  `program_application_id` bigint(20) DEFAULT NULL,
  `concurrent_program_id` bigint(20) DEFAULT NULL,
  `responsibility_application_id` bigint(20) DEFAULT NULL,
  `responsibility_id` bigint(20) DEFAULT NULL,
  `number_of_arguments` smallint(6) DEFAULT NULL,
  `number_of_copies` bigint(20) DEFAULT NULL,
  `save_output_flag` varchar(1) DEFAULT NULL,
  `nls_compliant` varchar(1) DEFAULT NULL,
  `last_update_login` bigint(20) DEFAULT NULL,
  `nls_language` varchar(30) DEFAULT NULL,
  `nls_territory` varchar(30) DEFAULT NULL,
  `printer` varchar(30) DEFAULT NULL,
  `print_style` varchar(30) DEFAULT NULL,
  `print_group` varchar(1) DEFAULT NULL,
  `request_class_application_id` bigint(20) DEFAULT NULL,
  `concurrent_request_class_id` bigint(20) DEFAULT NULL,
  `parent_request_id` bigint(20) DEFAULT NULL,
  `conc_login_id` bigint(20) DEFAULT NULL,
  `language_id` bigint(20) DEFAULT NULL,
  `description` varchar(240) DEFAULT NULL,
  `req_information` varchar(240) DEFAULT NULL,
  `resubmit_interval` decimal(15,10) DEFAULT NULL,
  `resubmit_interval_unit_code` varchar(30) DEFAULT NULL,
  `resubmit_interval_type_code` varchar(30) DEFAULT NULL,
  `resubmit_time` varchar(8) DEFAULT NULL,
  `resubmit_end_date` datetime DEFAULT NULL,
  `resubmitted` varchar(1) DEFAULT NULL,
  `controlling_manager` bigint(20) DEFAULT NULL,
  `actual_start_date` datetime DEFAULT NULL,
  `actual_completion_date` datetime DEFAULT NULL,
  `completion_text` varchar(240) DEFAULT NULL,
  `outcome_product` varchar(20) DEFAULT NULL,
  `outcome_code` bigint(20) DEFAULT NULL,
  `cpu_seconds` decimal(15,3) DEFAULT NULL,
  `logical_ios` bigint(20) DEFAULT NULL,
  `physical_ios` bigint(20) DEFAULT NULL,
  `logfile_name` varchar(255) DEFAULT NULL,
  `logfile_node_name` varchar(30) DEFAULT NULL,
  `outfile_name` varchar(255) DEFAULT NULL,
  `outfile_node_name` varchar(30) DEFAULT NULL,
  `argument_text` varchar(240) DEFAULT NULL,
  `argument1` varchar(240) DEFAULT NULL,
  `argument2` varchar(240) DEFAULT NULL,
  `argument3` varchar(240) DEFAULT NULL,
  `argument4` varchar(240) DEFAULT NULL,
  `argument5` varchar(240) DEFAULT NULL,
  `argument6` varchar(240) DEFAULT NULL,
  `argument7` varchar(240) DEFAULT NULL,
  `argument8` varchar(240) DEFAULT NULL,
  `argument9` varchar(240) DEFAULT NULL,
  `argument10` varchar(240) DEFAULT NULL,
  `argument11` varchar(240) DEFAULT NULL,
  `argument12` varchar(240) DEFAULT NULL,
  `argument13` varchar(240) DEFAULT NULL,
  `argument14` varchar(240) DEFAULT NULL,
  `argument15` varchar(240) DEFAULT NULL,
  `argument16` varchar(240) DEFAULT NULL,
  `argument17` varchar(240) DEFAULT NULL,
  `argument18` varchar(240) DEFAULT NULL,
  `argument19` varchar(240) DEFAULT NULL,
  `argument20` varchar(240) DEFAULT NULL,
  `argument21` varchar(240) DEFAULT NULL,
  `argument22` varchar(240) DEFAULT NULL,
  `argument23` varchar(240) DEFAULT NULL,
  `argument24` varchar(240) DEFAULT NULL,
  `argument25` varchar(240) DEFAULT NULL,
  `crm_thrshld` bigint(20) DEFAULT NULL,
  `crm_tstmp` datetime DEFAULT NULL,
  `critical` varchar(1) DEFAULT NULL,
  `request_type` varchar(1) DEFAULT NULL,
  `oracle_process_id` varchar(30) DEFAULT NULL,
  `oracle_session_id` bigint(20) DEFAULT NULL,
  `os_process_id` varchar(240) DEFAULT NULL,
  `print_job_id` varchar(240) DEFAULT NULL,
  `output_file_type` varchar(4) DEFAULT NULL,
  `release_class_app_id` double DEFAULT NULL,
  `release_class_id` double DEFAULT NULL,
  `stale_date` datetime DEFAULT NULL,
  `cancel_or_hold` varchar(1) DEFAULT NULL,
  `notify_on_pp_error` varchar(255) DEFAULT NULL,
  `cd_id` double DEFAULT NULL,
  `request_limit` varchar(1) DEFAULT NULL,
  `crm_release_date` datetime DEFAULT NULL,
  `post_request_status` varchar(1) DEFAULT NULL,
  `completion_code` varchar(30) DEFAULT NULL,
  `increment_dates` varchar(1) DEFAULT NULL,
  `restart` varchar(1) DEFAULT NULL,
  `enable_trace` varchar(1) DEFAULT NULL,
  `resub_count` double DEFAULT NULL,
  `nls_codeset` varchar(30) DEFAULT NULL,
  `ofile_size` bigint(20) DEFAULT NULL,
  `lfile_size` bigint(20) DEFAULT NULL,
  `stale` varchar(1) DEFAULT NULL,
  `security_group_id` double DEFAULT NULL,
  `resource_consumer_group` varchar(30) DEFAULT NULL,
  `exp_date` datetime DEFAULT NULL,
  `queue_app_id` bigint(20) DEFAULT NULL,
  `queue_id` bigint(20) DEFAULT NULL,
  `ops_instance` bigint(20) DEFAULT NULL,
  `interim_status_code` varchar(1) DEFAULT NULL,
  `root_request_id` bigint(20) DEFAULT NULL,
  `origin` varchar(1) DEFAULT NULL,
  `nls_numeric_characters` varchar(2) DEFAULT NULL,
  `pp_start_date` datetime DEFAULT NULL,
  `pp_end_date` datetime DEFAULT NULL,
  `org_id` bigint(20) DEFAULT NULL,
  `run_number` int(11) DEFAULT NULL,
  `node_name1` varchar(30) DEFAULT NULL,
  `node_name2` varchar(30) DEFAULT NULL,
  `connstr1` varchar(255) DEFAULT NULL,
  `connstr2` varchar(255) DEFAULT NULL
) SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=4;

image

| username: h5n1 | Original post link

Enabling tidb_scatter_region=on will evenly distribute the scheduling.

| username: 小龙虾爱大龙虾 | Original post link

By default, it is asynchronous. After setting tidb_scatter_region=on, it will wait for the scheduling to complete before returning a successful creation.

| username: TiDBer_yBunUeUc | Original post link

After enabling region uniform distribution, the time taken to insert the original 1 million records changed from 49 seconds to 50 seconds. The insertion efficiency has not improved.

| username: TiDBer_yBunUeUc | Original post link

Node distribution:
image

| username: h5n1 | Original post link

The inserted data must fall within the range of multiple regions; if it is only scattered on one or two, it is useless.

| username: dba远航 | Original post link

Use this parameter tidb_scatter_region=on

| username: zhanggame1 | Original post link

I have tested SHARD_ROW_ID_BITS=4 and PRE_SPLIT_REGIONS=4; it doesn’t seem to be useful.

| username: andone | Original post link

tidb_scatter_region=on

| username: zhanggame1 | Original post link

Your table doesn’t have a primary key. If it had one, you could consider using a clustered index table directly, which should be much faster. Stop messing around with SHARD_ROW_ID_BITS=4 and PRE_SPLIT_REGIONS=4.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.