search

cockroachdb / cockroach

CockroachDB — the cloud native, distributed SQL database designed for high availability, effortless scale, and control over data placement.
https://www.cockroachlabs.com
Other
30.12k stars 3.81k forks source link

altering tables to set localities (REGIONAL BY ROW or TABLE) takes long to complete. #91682

Open massimocGH opened 2 years ago

massimocGH commented 2 years ago

Describe the problem

ALTER TABLE ... SET LOCALITY REGIONAL BY ... suffers from high latency, taking long to complete even on empty tables.

These are some examples of the performance of this type of schema changes on empty tables (table names are redacted):

[22:21:37.750] INFO (99488): setting table region details
[22:21:47.775] INFO (99488): set table region details in 10026ms
[22:21:59.068] INFO (99488): set table locality [1/74] <name> REGIONAL BY ROW in 11293ms
[22:22:11.368] INFO (99488): set table locality [2/74] <name> REGIONAL BY ROW in 12299ms
[22:22:24.054] INFO (99488): set table locality [3/74] <name> REGIONAL BY ROW in 12686ms
[22:22:36.676] INFO (99488): set table locality [4/74] <name> REGIONAL BY ROW in 12621ms
[22:22:42.906] INFO (99488): set table locality [5/74] <name> REGIONAL BY ROW in 6229ms
[22:22:49.036] INFO (99488): set table locality [6/74] <name> REGIONAL BY ROW in 6130ms
[22:22:55.194] INFO (99488): set table locality [7/74] <name> REGIONAL BY ROW in 6158ms
[22:23:09.619] INFO (99488): set table locality [8/74] <name> REGIONAL BY ROW in 14425ms
[22:23:21.661] INFO (99488): set table locality [9/74] <name> REGIONAL BY ROW in 12042ms
[22:23:27.967] INFO (99488): set table locality [10/74] <name> REGIONAL BY ROW in 6306ms
[22:23:36.164] INFO (99488): set table locality [11/74] <name> REGIONAL BY ROW in 8197ms
[22:23:48.956] INFO (99488): set table locality [12/74] <name> REGIONAL BY ROW in 12786ms
[22:23:56.283] INFO (99488): set table locality [13/74] <name> REGIONAL BY ROW in 7327ms
[22:24:02.486] INFO (99488): set table locality [14/74] <name> REGIONAL BY ROW in 6197ms
[22:24:08.924] INFO (99488): set table locality [15/74] <name> REGIONAL BY ROW in 6438ms
[22:24:15.200] INFO (99488): set table locality [16/74] <name> REGIONAL BY ROW in 6274ms
[22:24:21.376] INFO (99488): set table locality [17/74] <name> REGIONAL BY ROW in 6175ms
[22:24:27.808] INFO (99488): set table locality [18/74] <name> REGIONAL BY ROW in 6431ms
[22:24:34.010] INFO (99488): set table locality [19/74] <name> REGIONAL BY ROW in 6202ms
[22:24:40.238] INFO (99488): set table locality [20/74] <name> REGIONAL BY ROW in 6228ms
[22:24:45.992] INFO (99488): set table locality [21/74] <name> REGIONAL BY ROW in 5753ms
[22:24:52.304] INFO (99488): set table locality [22/74] <name> REGIONAL BY ROW in 6312ms
[22:24:58.604] INFO (99488): set table locality [23/74] <name> REGIONAL BY ROW in 6300ms
[22:25:04.833] INFO (99488): set table locality [24/74] <name> REGIONAL BY ROW in 6229ms
[22:25:11.024] INFO (99488): set table locality [25/74] <name> REGIONAL BY ROW in 6191ms
[22:25:17.487] INFO (99488): set table locality [26/74] <name> REGIONAL BY ROW in 6463ms
[22:25:23.818] INFO (99488): set table locality [27/74] <name> REGIONAL BY ROW in 6331ms
[22:25:30.218] INFO (99488): set table locality [28/74] <name> REGIONAL BY ROW in 6399ms
[22:25:36.986] INFO (99488): set table locality [29/74] <name> REGIONAL BY ROW in 6768ms
[22:25:43.262] INFO (99488): set table locality [30/74] <name> REGIONAL BY ROW in 6276ms
[22:25:49.444] INFO (99488): set table locality [31/74] <name> REGIONAL BY ROW in 6182ms
[22:26:01.122] INFO (99488): set table locality [32/74] <name> REGIONAL BY ROW in 11678ms
[22:26:07.842] INFO (99488): set table locality [33/74] <name> REGIONAL BY ROW in 6720ms
[22:26:13.992] INFO (99488): set table locality [34/74] <name> REGIONAL BY ROW in 6150ms
[22:26:27.064] INFO (99488): set table locality [35/74] <name> REGIONAL BY ROW in 13062ms
[22:26:33.065] INFO (99488): set table locality [36/74] <name> REGIONAL BY ROW in 6000ms
[22:26:38.980] INFO (99488): set table locality [37/74] <name> REGIONAL BY ROW in 5914ms
[22:26:44.937] INFO (99488): set table locality [38/74] <name> REGIONAL BY ROW in 5957ms
[22:26:57.456] INFO (99488): set table locality [39/74] <name> REGIONAL BY ROW in 12519ms
[22:27:03.617] INFO (99488): set table locality [40/74] <name> REGIONAL BY ROW in 6160ms
[22:27:09.763] INFO (99488): set table locality [41/74] <name> REGIONAL BY ROW in 6146ms
[22:27:10.534] INFO (99488): set table locality [42/74] <name> GLOBAL in 771ms
[22:27:18.263] INFO (99488): set table locality [43/74] <name> REGIONAL BY ROW in 7728ms
[22:27:24.651] INFO (99488): set table locality [44/74] <name> REGIONAL BY ROW in 6388ms
[22:27:31.054] INFO (99488): set table locality [45/74] <name> REGIONAL BY ROW in 6403ms
[22:27:37.717] INFO (99488): set table locality [46/74] <name> REGIONAL BY ROW in 6663ms
[22:27:44.172] INFO (99488): set table locality [47/74] <name> REGIONAL BY ROW in 6455ms
[22:27:50.410] INFO (99488): set table locality [48/74] <name> REGIONAL BY ROW in 6238ms
[22:27:56.906] INFO (99488): set table locality [49/74] <name> REGIONAL BY ROW in 6496ms
[22:28:03.009] INFO (99488): set table locality [50/74] <name> REGIONAL BY ROW in 6103ms
[22:28:09.421] INFO (99488): set table locality [51/74] <name> REGIONAL BY ROW in 6412ms
[22:28:10.115] INFO (99488): set table locality [52/74] <name> GLOBAL in 694ms
[22:28:17.243] INFO (99488): set table locality [53/74] <name> REGIONAL BY ROW in 7128ms
[22:28:29.229] INFO (99488): set table locality [54/74] <name> REGIONAL BY ROW in 11986ms
[22:28:35.884] INFO (99488): set table locality [55/74] <name> REGIONAL BY ROW in 6655ms
[22:28:42.638] INFO (99488): set table locality [56/74] <name> REGIONAL BY ROW in 6754ms
[22:28:53.507] INFO (99488): set table locality [57/74] <name> REGIONAL BY ROW in 10868ms
[22:29:07.248] INFO (99488): set table locality [58/74] <name> REGIONAL BY ROW in 13739ms
[22:29:14.075] INFO (99488): set table locality [59/74] <name> REGIONAL BY ROW in 6827ms
[22:29:20.667] INFO (99488): set table locality [60/74] <name> REGIONAL BY ROW in 6592ms
[22:29:27.314] INFO (99488): set table locality [61/74] <name> REGIONAL BY ROW in 6647ms
[22:29:39.372] INFO (99488): set table locality [62/74] <name> REGIONAL BY ROW in 12058ms
[22:29:45.801] INFO (99488): set table locality [63/74] <name> REGIONAL BY ROW in 6428ms
[22:29:53.517] INFO (99488): set table locality [64/74] <name> REGIONAL BY ROW in 7716ms
[22:29:59.954] INFO (99488): set table locality [65/74] <name> REGIONAL BY ROW in 6437ms
[22:30:10.737] INFO (99488): set table locality [66/74] <name> REGIONAL BY ROW in 10783ms
[22:30:17.568] INFO (99488): set table locality [67/74] <name> REGIONAL BY ROW in 6831ms
[22:30:28.921] INFO (99488): set table locality [68/74] <name> REGIONAL BY ROW in 11353ms
[22:30:35.775] INFO (99488): set table locality [69/74] <name> REGIONAL BY ROW in 6854ms
[22:30:42.741] INFO (99488): set table locality [70/74] <name> REGIONAL BY ROW in 6966ms
[22:30:54.343] INFO (99488): set table locality [71/74] <name> REGIONAL BY ROW in 11602ms
[22:31:06.616] INFO (99488): set table locality [72/74] <name> REGIONAL BY ROW in 12273ms
[22:31:18.082] INFO (99488): set table locality [73/74] <name> REGIONAL BY ROW in 11465ms
[22:31:30.169] INFO (99488): set table locality [74/74] <name> REGIONAL BY ROW in 12087ms
[22:31:30.169] INFO (99488): set table locality for 74 tables in 582394ms

Expected behavior It would be nice if these kind of operations completed faster.

Environment:

Jira issue: CRDB-21383

ajwerner commented 1 year ago

The root of the problem here is that many of these transitions require many internal transactions. If each such transactions takes a number of round-trips and the RTT is over 100ms, it's not surprising that it's slow. We'll track this in the backlog, but tuning the latency of this sort of thing is not a top priority.