Define the placement of data by SQL statements

[scsldb]

Description

TiDB supports placement rules, which can define the placement of data in a more flexible and more granular way. But it only provides configuration files to define them, and it’s complicated.

This issue tracks our progress in adding support via SQL.

• Weekly Report
• RFC document

The use cases for Placement Rules generally fit into two broad categories:

1. Optimization (local stale reads) and High Availability
2. Compliance / Geographical Restrictions

We are designing the syntax with both of these features in mind, but anticipate that initially use-case (1) will be better handled.

Sub Tasks

Named Placement Policy

(Previously: #26580 )

• Parser: {CREATE|ALTER} PLACEMENT POLICY placement_options parser: support create and alter placement policy parser#1313
• Parser: SHOW CREATE PLACEMENT POLICY x, DROP PLACEMENT POLICY [IF EXISTS] x; parser: support drop placement policy parser#1295 parser: support show create placement policy parser#1298
• Design table in mysql.* for persisting placement policies. meta: support storing placement policy into meta #27251
• Ensure {CREATE|ALTER|DROP} PLACEMENT POLICY requires the PLACEMENT_ADMIN privilege Ensure {CREATE|ALTER|DROP} PLACEMENT POLICY requires the PLACEMENT_ADMIN privilege #27603
• Support ALTER PLACEMENT POLICY #27714
• support the inheritance of DB/Table/Partition placement policy ddl: implement the placement rules inheritance logic #28365
• postpone the clean logic of the placement policy to gc phase ddl: postpone cleaning placement rules to gc-worker #28687

Support SHOW PLACEMENT

(Previously: #26582 )

• Parser: `SHOW PLACEMENT LIKE 'x';
• Parser: Add SHOW PLACEMENT FOR [{DATABASE | SCHEMA} schema_name] [TABLE table_name] [PARTITION partition_name]
• SHOW PLACEMENT LIKE should return a list of PLACEMENT POLICIES that have been loaded in the TiDB server.
  • *: Support SHOW PLACEMENT for placement policies #27531
• SHOW PLACEMENT FOR Add SHOW PLACEMENT FOR .. syntax #27976
  • *: Add SHOW PLACEMENT support for tables #28266
  • *: Add SHOW PLACEMENT support for DB #28366
  • *: Add SHOW PLACEMENT support for partitions #28830
• Privileges for SHOW PLACEMENT and SHOW PLACEMENT FOR Add privilege checks for SHOW PLACEMENT FOR / LIKE #27977

• Fetch the placement's scheduling state from PD #28199

Information_schema

• Placement policy will be lost after restarting the tidb server #28058
• Support information_schema.placement_rules #26681
• conver primaryRegion and regions to xxx_constraints for convenience, refer Support information_schema.placement_rules #26681 (comment)
• executor: add placement policy name / direct placement to is.tables/partitions #28798

Direct Placement Options / General Meta data

(Previously: #26581 )

• *: support new placement rules syntax parser#1299
  parser: Support the following as table_options in CREATE|ALTER TABLE and CREATE|ALTER DATABASE contexts. This should also work for partitions, such as ALTER TABLE t PARTITION p0.

table_option: {
..
 | FOLLOWERS [=] n
 | VOTERS [=] n
 | LEARNERS  [=] n
 | PRIMARY_REGION [=] "str"
 | REGIONS  [=] "str"
 | SCHEDULE [=] "str"
 | CONSTRAINTS  [=] "str"
 | FOLLOWER_CONSTRAINTS  [=] "str"
 | VOTER_CONSTRAINTS  [=] "str"
 | LEARNER_CONSTRAINTS  [=] "str"
 | PLACEMENT POLICY  [=] "str"
}

• After the support of new syntax for parser, the old syntax should be removed. Remove old legacy code for placement options #27971
• Ensure that table_options are persisted in TiDB DDL, ddl: support table placement option #27799
• Ensure the database_options for placement are persisted in TiDB DDL Ensure the database_options for placement are persisted in TiDB DDL #27972 @mjonss
• ALTER DATABASE support Add placement rules for a schema via ALTER DATABASE  #27975 @sylzd
• Ensure the partitionDef options (usually stored as tableOption in ast) for placement are persisted in TiDB DDL Ensure the partitionDef options for placement are persisted in TiDB DDL #27973
• And visible in SHOW CREATE TABLE but always enclosed in a version specific comment (/*T![placement]). executor: Add support for placement in SHOW CREATE TABLE #27792
• Ensure that table_options respect "Policy Validation" and "Skipping Policy Validation" sections of proposal. Ensure that table_options respect "Policy Validation" and "Skipping Policy Validation" #27974

SHOW PLACEMENT LABELS

• Add SHOW PLACEMENT LABELS syntax #27007
  • @lcwangchao

New Placement Rules Engine

(Previously: #27011 )

This is a sub-task of Placement Rules in SQL: #18030. Depends on #26580 and #26581. Once metadata has been finished, we need to add a new middle state to all related DDL jobs to sync rules to PD. Previous, we have already implemented similar code, so the work is more of a migration.

This requires the following subtasks:

• placement: construct bundle from options #27814, the pre-PR for other subtasks
• alter db/table/partition support changing TABLE placement options through ALTER TABLE #28833
• flashback/recover table/partition, since metadata is stored in meta, we can revert ddl: handle placement rule cache for drop/truncate/recover/flashback table #20622 and special infoschema treatment for placement rules

• ALTER PLACEMENT POLICY should update placement rules for all tables in scope in PD.
• Forbid DROP PLACEMENT POLICY if it is in use #27985
• Make ddl operations for placement rule atomic #28159
• placement: refine the bundle contruction logic #28450
• Policy Validation
• always get an explicitly configured SCHEDULE option, refer placement: refine the bundle contruction logic #28450 (comment)
• ddl will write the default pd policy bundle again #28364
• placement: give default 2 followers for non-sugar syntax #31000

Rule Removal

• Support removing all placement rules from a {table,partition,database} #18203
• Dropping the placement rules when the table is dropped #18206
• Altering placement rules when truncating a table #18208

Others

• Placement behavior for temporary table #28309
• Support SHOW CREATE PLACEMENT POLICY #28766
• Placement option of FOLLOWERS=4 not supported #28767
• order of placement rules on partitioned tables is too strict #28832
• placement policy is not supported in CREATE TABLE for list COLUMNS partitioning #28834
• remove information_schema.placement_policy #28890
• Create table with placement policy complains ERROR 1105 (HY000) #29006
• Add feature flag support for placement rules #29088
• Placement Policy support for dumpling #29371
• infoschema.placement_rules table uses wrong flen values #29482
• Add TIDB_PLACEMENT_POLICY_NAME and TIDB_DIRECT_PLACEMENT to information_schema.schemata #29758
• Add Metrics to Monitor PD API call exection time #29821
• Update proposal to include maximum lengths for PLACEMENT options. #29822
• Add CREATE OR REPLACE PLACEMENT POLICY syntax. #29853
• Modify placement rule index to reserve some future index for default/system rules #29986
• Handle incompatible meta when recovering a table referring a policy #30417
• FOLLOWERS=99 should be illegal #30453
• Forbid tiflash replica on the table that has placement settings #30741
• Add tests to test placement requests to PD #30620
• pd not applying placement to partitions of new table tikv/pd#4467
• Can we improve the error message of incorrect dict syntax? #30454
• placement: mirror issue of tikv/pd#4467 #30858, remove isolation level
• Remove variable tidb_enable_alter_placement #31053
• Add tidb_placement_mode #31092
• Should not allow specify FOLLOWERS/LEARNERS when FOLLOWER_CONSTRAINTS/LEARNER_CONSTRAINTS is using dict syntax #31127
• placement: wrong quorum number #31186
• Sometimes add partition will not add placement rules in PD #31153
• Consider make rule location_labels be empty #31052
• Create table failed with placement rule when SCHEDULE="majority_in_primary" and PRIMARY_REGION and REGIONS are the same #31271
• Add placement to special comment for ddl SQL in binlog #31453
• BR restore table with a policy failed even if the target tidb already has a policy with the same name #31455
• Inconsistent placement rules when retrying truncating tables/partitions #31540
• Update official document and add syntax tree for placement ddl #31559
• followers in information_schema.placement_rules is not right when do not specify followers in placement #31702
• support telemetry for "Placement Rules in SQL" #33188

Remove direct placement options

This is to track the changes required for #31423

• Add sys variable to disable direct placement temporarily #31622
• Clear placement settings when recover/flashback table #31668
• Rename infoschema.placement_rules to infoschema.placement_policies and make changes (different columns, only includes policies, different nullability values) #31425
• Remove TIDB_DIRECT_PLACEMENT from infoschema.tables, infoschema.partitions, infoschema.schemata #31426
• Remove from SHOW CREATE TABLE/SHOW CREATE DATABASE. This should mean any existing installs upgrading will eventually lose their direct placement options. #31427
• Remove unit tests #31485
• Update documentation (main overview) #31486
• Remove direct placement options from parser (table, partition, database schema context) #31424

Tools Behaviors

• Update Dumpling Behavior to not dump policies by default #31760
• BR support for placement rules #32290
• CDC support for placement rules #32291
• Binlog support for placement rules #32828
• Lightning support for placement rules #32292
• Add CreatePlacementPolicyWithInfo to interface ddl.DDL to support br restore #32951

Future work

We will plan for future delivery:

• support deterministic failover schedule for placement rules #37251
• Show placement information in SHOW TABLE x REGIONS #32458
• Add Support for "Built In Placement Policies" #29677
• Can we use PD placement rules cache? https://github.com/tikv/pd/blob/701b94fb06fb5486958b673f5a344b2e36f0455d/server/config/config.go#L1029-L1030
• Adjust the output of EXPLAIN / EXPLAIN ANALYZE to show some placement info #29421.

• Parser: Add support for PRIMARY_ZONE / ZONES / PRIMARY_RACK / RACKS / PRIMARY_HOST / HOSTS. Add support for PRIMARY_ZONE / ZONES / PRIMARY_RACK / RACKS / PRIMARY_HOST / HOSTS #28697

• Better handle invalid rules (error messages from PD) #30415
• Investigate duplicate placement options handling behavior #30452
• placement: show a better hint message for schduleing state #30960
• probably let user set location_labels?
• probably recursively delete/unassign placement policies under database, from INCALL users
• support followers=0 syntax #31001
• Learner peers cannot be scheduled using placement settings #31173
• Support placement for autoid #31270
• Documentation updates needed for placement rules #31487
• Evaluate PlacementScheduleStatePending state for placement scheduling #35314

[zz-jason]

@djshow832 How about changing the PLACEMENT to PLACEMENT POLICY, changing label keyword to locality and changing COUNT to REPLICAS? With this, a statement that alters the geo partition policy is:

ALTER TABLE users ALTER PARTITION west
ADD PLACEMENT POLICY
    LOCALITY="+zone=west_dc"
    ROLE="leader"
    REPLICAS=1;

IMO, locality is more meaningful than label to the user, so as replicas compared with count.

[zz-jason]

CockroachDB uses CONFIGURE ZONE USING clause, they use constraints to specify label, num_replicas to specify count, for example:

ALTER PARTITION chicago OF INDEX users@*
CONFIGURE ZONE USING
    num_replicas = 3,
    constraints = '{"+region=us-central":1}',
    lease_preferences = '[[+region=us-central]]';

See https://www.cockroachlabs.com/docs/stable/topology-geo-partitioned-leaseholders.html for details.

[djshow832]

There's a problem for locality: when adding tiflash replica through placement rules, it's often specified in the format "+engine=tiflash", it's not locality. @zz-jason

[zz-jason]

There's a problem for locality: when adding tiflash replica through placement rules, it's often specified in the format "+engine=tiflash", it's not locality. @zz-jason

We use the following SQL command to add 2 tiflash replicas for table tpch50.lineitem:

ALTER TABLE `tpch50`.`lineitem` SET TIFLASH REPLICA 2

Let's regard SQL commands as the user interface, placement rules in PD as the implementation util.

In this issue, we need to design a SQL command for the user to place a partition of the table to a specific geographic position(Rack, AZ, Region, etc.). Although we are able to use this same SQL command to add TiFlash replicas, it's better not to do it this way. I prefer to use the specific SQL command to do the specific thing.

[zz-jason]

I prefer to use locality, it's more specific than constraints.

[djshow832]

So you mean, we do not support adding TiFlash replicas through the ALTER PARTITION statement? @zz-jason