search

matrixorigin / matrixone

Hyperconverged cloud-edge native database
https://docs.matrixorigin.cn/en
Apache License 2.0
1.78k stars 276 forks source link

[Bug]: `alter table` is very slow #19207

Open DanielZhangQD opened 2 weeks ago

DanielZhangQD commented 2 weeks ago

Is there an existing issue for the same bug?

Branch Name

main

Commit ID

v2.0.0-e08947032-2024-10-09

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

Table Info:

mysql> select count(*) from billing_records;
+----------+
| count(*) |
+----------+
|   301412 |
+----------+
1 row in set (6.29 sec)

mysql> desc billing_records;
+----------------------+-------------+------+------+---------+----------------+---------+
| Field                | Type        | Null | Key  | Default | Extra          | Comment |
+----------------------+-------------+------+------+---------+----------------+---------+
| id                   | BIGINT(64)  | NO   | PRI  | NULL    | auto_increment |         |
| bill_id              | VARCHAR(64) | YES  | UNI  | null    |                |         |
| account_period       | VARCHAR(32) | YES  |      | null    |                |         |
| start_time           | DATETIME(0) | YES  | MUL  | null    |                |         |
| end_time             | DATETIME(0) | YES  |      | null    |                |         |
| organization_id      | VARCHAR(20) | YES  | MUL  | null    |                |         |
| instance_id          | VARCHAR(64) | YES  | MUL  | null    |                |         |
| instance_plan_type   | VARCHAR(20) | YES  |      | null    |                |         |
| bill_type            | TINYINT(8)  | YES  |      | null    |                |         |
| price                | DECIMAL(18) | YES  |      | null    |                |         |
| unit_name            | VARCHAR(32) | YES  | MUL  | null    |                |         |
| unit_info            | TEXT(0)     | YES  |      | null    |                |         |
| units                | TEXT(0)     | YES  |      | null    |                |         |
| discount             | DECIMAL(4)  | NO   |      | '1'     |                |         |
| usage                | DECIMAL(18) | NO   |      | '0'     |                |         |
| amount               | DECIMAL(18) | NO   | MUL  | '0'     |                |         |
| derate_amount        | DECIMAL(18) | NO   | MUL  | '0'     |                |         |
| round_down_amount    | DECIMAL(18) | NO   |      | '0'     |                |         |
| payable_amount       | DECIMAL(18) | NO   | MUL  | '0'     |                |         |
| owed_amount          | DECIMAL(18) | NO   | MUL  | '0'     |                |         |
| cash_amount          | DECIMAL(18) | NO   | MUL  | '0'     |                |         |
| coupon_amount        | DECIMAL(18) | NO   | MUL  | '0'     |                |         |
| status               | TINYINT(8)  | YES  |      | null    |                |         |
| created_at           | DATETIME(0) | YES  |      | null    |                |         |
| updated_at           | DATETIME(0) | YES  |      | null    |                |         |
| payment              | TEXT(0)     | YES  |      | null    |                |         |
| instance_charge_type | TINYINT(8)  | NO   |      | '0'     |                |         |
+----------------------+-------------+------+------+---------+----------------+---------+
27 rows in set (6.94 sec)

mysql> show create table billing_records;
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table           | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| billing_records | CREATE TABLE `billing_records` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `bill_id` varchar(64) DEFAULT NULL,
  `account_period` varchar(32) DEFAULT NULL,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `organization_id` varchar(20) DEFAULT NULL,
  `instance_id` varchar(64) DEFAULT NULL,
  `instance_plan_type` varchar(20) DEFAULT NULL,
  `bill_type` tinyint DEFAULT NULL,
  `price` decimal(18,8) DEFAULT NULL,
  `unit_name` varchar(32) DEFAULT NULL,
  `unit_info` text DEFAULT NULL,
  `units` text DEFAULT NULL,
  `discount` decimal(4,3) NOT NULL DEFAULT '1',
  `usage` decimal(18,8) NOT NULL DEFAULT '0',
  `amount` decimal(18,6) NOT NULL DEFAULT '0',
  `derate_amount` decimal(18,6) NOT NULL DEFAULT '0',
  `round_down_amount` decimal(18,6) NOT NULL DEFAULT '0',
  `payable_amount` decimal(18,2) NOT NULL DEFAULT '0',
  `owed_amount` decimal(18,2) NOT NULL DEFAULT '0',
  `cash_amount` decimal(18,2) NOT NULL DEFAULT '0',
  `coupon_amount` decimal(18,2) NOT NULL DEFAULT '0',
  `status` tinyint DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  `payment` text DEFAULT NULL,
  `instance_charge_type` tinyint NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_bill_id` (`bill_id`),
  KEY `unitname` (`unit_name`),
  KEY `idx_instance` (`instance_id`),
  KEY `idx_organization_instance_bill_id_start_time` (`organization_id`,`instance_id`,`bill_id`,`start_time`),
  KEY `instance` (`instance_id`),
  KEY `idx_time` (`start_time`),
  KEY `idx_org_ins_unit` (`organization_id`,`instance_id`,`unit_name`),
  KEY `idx_coupon_amount` (`coupon_amount`),
  KEY `idx_time_instance` (`start_time`,`instance_id`),
  KEY `idx_amount` (`amount`),
  KEY `idx_derate_amount` (`derate_amount`),
  KEY `idx_payable_amount` (`payable_amount`),
  KEY `idx_owed_amount` (`owed_amount`),
  KEY `idx_cash_amount` (`cash_amount`)
) |
+-----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.10 sec)

Migration duration:

{"level":"WARN","time":"2024/10/10 06:31:08.013749 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"23.700772839s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `account_period` varchar(32)"}
{"level":"WARN","time":"2024/10/10 06:31:43.725803 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"35.711949517s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `bill_id` varchar(64)"}
{"level":"WARN","time":"2024/10/10 06:32:07.889512 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"23.737021355s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `start_time` datetime"}
{"level":"WARN","time":"2024/10/10 06:32:31.614029 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"23.42154378s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `end_time` datetime"}
{"level":"WARN","time":"2024/10/10 06:33:05.975594 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"34.361444719s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `organization_id` varchar(20)"}{"level":"WARN","time":"2024/10/10 06:33:32.696779 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"26.40453851s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `instance_id` varchar(64)"}
{"level":"WARN","time":"2024/10/10 06:33:58.946956 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"25.927281007s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `instance_plan_type` varchar(20)"}
{"level":"WARN","time":"2024/10/10 06:34:34.624574 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"35.677443262s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `bill_type` tinyint(4)"}
{"level":"WARN","time":"2024/10/10 06:34:58.020055 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"23.395308336s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `price` decimal(18,8)"}
{"level":"WARN","time":"2024/10/10 06:35:24.562918 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"26.542732198s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `unit_name` varchar(32)"}
{"level":"WARN","time":"2024/10/10 06:35:51.198140 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"26.375868388s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `unit_info` text"}
{"level":"WARN","time":"2024/10/10 06:36:16.151638 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"24.953395971s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `payment` text"}
{"level":"WARN","time":"2024/10/10 06:36:40.855377 +0000","caller":"migrator/migrator.go:74","msg":"trace","elapsed":"24.703638135s","rows":0,"sql":"ALTER TABLE `billing_records` MODIFY COLUMN `units` text"}

It takes much longer than MySQL.

Expected Behavior

SQL returns in reasonable time.

Steps to Reproduce

See detail in Actual Behavior

Additional information

Cloud dev env

qingxinhome commented 1 week ago

got it

qingxinhome commented 1 week ago

排队处理

qingxinhome commented 2 days ago

排队处理