Modify table locality

This topic describes how to modify the locality of a specified table in OceanBase Database.

For more information about how to modify the locality of a tenant, see Modify tenant locality.

You can specify the table locality in the following two ways:

• When you create a table, specify the table locality by using the following syntax:

  CREATE TABLE table_name LOCALITY [=] "locality description";
  

• You can also specify the table locality by using the following ALTER TABLE syntax:

  ALTER TABLE table_name [SET] LOCALITY [=] "locality description";
  

Scope of table locality modifications

For tables whose locality is unspecified, modifications to the locality of the tenant also apply to these tables in the tenant. For a table that has a non-empty locality, you can modify its locality to adjust the distribution of the replicas. Therefore, the object of table locality modification must be a table that has a non-empty locality.

Considerations

• For tables that have non-empty localities, you can perform locality modifications on these tables at the same time.

• When you modify the non-empty locality of a table, the locality of the tenant to which the table belongs cannot be modified before your modification to the table locality is completed. That is, if you execute the ALTER TENANT statement to modify the tenant locality at this time, the execution of the statement is about to fail.

• For tables that have non-empty localities, to change the number of read-only replicas that do not participate in log voting as members of the Paxos group, you must modify the locality of the corresponding tenant. The load-balancing thread of the RootService performs the task that increases or decreases the number of replicas.

• For tables that do not have a locality, to increase the number of replicas that do not participate in log voting as members of the Paxos group, you can execute the ALTER TABLE statement to modify the tables. For example, assume that a tenant has the following locality: tenant Locality=F@z1,F@z2,F@z3. The tenant has a table whose locality is unspecified. You can execute the ALTER TABLE statement to set the locality of the table to Locality=F@z1,F@z2,R@z3. After the modification, the table has a locality. The load-balancing thread of the RootService performs the task that increases or decreases the number of replicas.

• You cannot change the non-empty locality of a table to empty.

• The localities of system tables in the sys tenant must be consistent with that of the SYS tenant. This means that the localities of the system tables are always unspecified, and you cannot modify their localities.

• To increase, decrease, or modify the locality of every table in a tenant, perform the following two steps:

  1. Modify the locality of the tenant.

  2. Modify the localities of all tables that have non-empty localities in the tenant.

  Notice

  After you perform the first step, you must perform the second step to manually modify the localities of the tables. If you skip the second step, unpredictable errors may occur during the next tenant locality modification.

Locality modification process and modification progress query

The operations of modifying the table and tenant localities are the same. You can modify the localities of tables in only one zone each time. The new table localities must match the locality of the tenant. The processes of modifying the table and tenant localities are the same. However, the object of a table locality modification can only be a single table.

After you perform a locality modification, you can log on to the sys tenant and execute an SQL statement to query the modification progress. Sample code:

obclient> SELECT gmt_create, gmt_modified, tenant_id, table_name, job_type, job_status
       FROM oceanbase.__all_rootservice_job
       WHERE job_type LIKE '%LOCALITY%'
       ORDER BY job_id DESC;
+----------------------------+----------------------------+-----------+------------+-------------------------------+------------+
| gmt_create                 | gmt_modified               | tenant_id | table_name | job_type                      | job_status |
+----------------------------+----------------------------+-----------+------------+-------------------------------+------------+
| 2022-01-07 15:54:18.663746 | 2022-01-07 15:54:21.373025 |      1002 | NULL       | ALTER_TENANT_LOCALITY         | SUCCESS    |
| 2022-01-07 15:46:12.874074 | 2022-01-07 15:46:14.385552 |      NULL | tbl1       | ROLLBACK_ALTER_TABLE_LOCALITY | SUCCESS    |
| 2022-01-07 15:41:24.844597 | 2022-01-07 15:46:12.872406 |      NULL | tbl1       | ALTER_TABLE_LOCALITY          | FAILED     |
| 2022-01-07 15:41:19.280518 | 2022-01-07 15:41:19.280518 |      NULL | tbl2       | ALTER_TABLE_LOCALITY          | INPROGRESS |
+----------------------------+----------------------------+-----------+------------+-------------------------------+------------+
4 rows in set

Note

You can also use the preceding statement to query the progress of a tenant locality modification. In the returned result, tenant_id and table_name indicate the values of the modified objects. For a table locality modification, the value of the tenant_id parameter is NULL in the returned result.

Examples

Assume that a cluster has three IDCs in Hangzhou: hz1@Hangzhou, hz2@Hangzhou, and hz3@Hangzhou. A tenant has the following three default localities for full-featured replicas: F@hz1, F@hz2, and F@hz3.

You can create a table whose read-only replicas are distributed on each OBServer in the cluster by executing the following SQL statement:

obclient> CREATE TABLE t1 LOCALITY = 'F@hz1, F@hz2, F@hz3, R{all_server}@hz1, R{all_server}@hz2, R{all_server}@hz3';

You can modify the table locality by executing the following ALTER TABLE statement:

obclient> ALTER TABLE t1 SET LOCALITY = 'F@hz1, F@hz2, F@hz3, R{all_server}@hz1, R{all_server}@hz2, R{all_server}@hz3';

When the status of the corresponding task record in the __all_rootservice_job table becomes SUCCESS, the table creation or locality modification is completed.

Notice

Table and tenant locality modifications are subject to the following constraints:

• When a tenant locality modification is not finished, the next tenant locality modification cannot be performed.

• When a table with a non-empty locality is undergoing a locality modification, the locality of the tenant that hosts the table cannot be modified.

• If a table has a non-empty locality, a modification to this locality cannot be performed in a zone if the tenant locality modification is not initiated in the zone.

In a word, you cannot perform two or more tenant locality modifications at the same time. Before you perform a table or tenant locality modification, ensure that no modification is in progress.