search

matrixorigin / matrixone

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

[Bug]: [snapshot performance] restore is too slow. #16099

Open Ariznawlll opened 1 month ago

Ariznawlll commented 1 month ago

Is there an existing issue for the same bug?

Branch Name

main

Commit ID

4257dc620db76e22495241cab066cb807c092296

Other Environment Information

- Hardware parameters:
- OS type:
- Others:

Actual Behavior

restore 一张 10亿数据量的表大约30min恢复了45539328（不到1/2）.

Expected Behavior

No response

Steps to Reproduce

集群环境：
create database big_data_test;
use big_data_test;
create table  if not exists big_data_test.table_basic_for_load_1B( col1 tinyint, col2 smallint, col3 int, col4 bigint, col5 tinyint unsigned, col6 smallint unsigned, col7 int unsigned, col8 bigint unsigned, col9 float, col10 double, col11 varchar(255), col12 Date, col13 DateTime, col14 timestamp, col15 bool, col16 decimal(16,6), col17 text, col18 json, col19 blob, col20 binary(255), col21 varbinary(255), col22 vecf32(3), col23 vecf32(3), col24 vecf64(3), col25 vecf64(3));
load data url s3option {'endpoint'='http://cos.ap-guangzhou.myqcloud.com','access_key_id'='***','secret_access_key'='***','bucket'='mo-load-guangzhou-1308875761', 'filepath'='mo-big-data/1000000000_20_columns_load_data_new.csv'} into table big_data_test.table_basic_for_load_1B fields terminated by '|' lines terminated by '\n' parallel 'true';
(具体的ak sk如有需要，请联系我)
create snapshot sp03 for account sys;

drop database big_data_test;

restore account sys database big_data_test table table_basic_for_load_1b from snapshot sp03;

Additional information

No response

YANGGMM commented 1 month ago

单机环境下恢复一个大约300w行的table 花费十几分钟

image
YANGGMM commented 1 month ago

显示执行

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists `bmsql_order_line`;
Query OK, 0 rows affected (0.05 sec)

mysql> use tpcc;
Database changed
mysql> 
mysql> CREATE TABLE `bmsql_order_line` (
    -> `ol_w_id` INT NOT NULL,
    -> `ol_d_id` INT NOT NULL,
    -> `ol_o_id` INT NOT NULL,
    -> `ol_number` INT NOT NULL,
    -> `ol_i_id` INT NOT NULL,
    -> `ol_delivery_d` TIMESTAMP DEFAULT null,
    -> `ol_amount` DECIMAL(6,2) DEFAULT null,
    -> `ol_supply_w_id` INT DEFAULT null,
    -> `ol_quantity` INT DEFAULT null,
    -> `ol_dist_info` CHAR(24) DEFAULT null,
    -> PRIMARY KEY (`ol_w_id`,`ol_d_id`,`ol_o_id`,`ol_number`)
    -> ) partition by key algorithm = 2 (ol_w_id);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into `tpcc`.`bmsql_order_line` SELECT * FROM `tpcc`.`bmsql_order_line` {snapshot = 'sp_01'};
Query OK, 2998810 rows affected (11 min 5.11 sec)

mysql> commit;
Query OK, 0 rows affected (5.93 sec)
YANGGMM commented 1 month ago

恢复一个带主键的表insert 30w条只用1.93sec，但是insert300w行的分区表时间太慢了

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table if exists `bmsql_history`;
Query OK, 0 rows affected (0.01 sec)

mysql> use tpcc;
Database changed
mysql> 
mysql> create table `bmsql_history` (`hist_id` INT NOT NULL AUTO_INCREMENT,`h_c_id` INT DEFAULT NULL,`h_c_d_id` INT DEFAULT NULL,`h_c_w_id` INT DEFAULT NULL,`h_d_id` INT DEFAULT NULL,`h_w_id` INT DEFAULT NULL,`h_date` TIMESTAMP DEFAULT NULL,`h_amount` DECIMAL(6,2) DEFAULT NULL,`h_data` VARCHAR(24) DEFAULT NULL,PRIMARY KEY (`hist_id`));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into `tpcc`.`bmsql_history` SELECT * FROM `tpcc`.`bmsql_history` {snapshot = 'sp_01'};
Query OK, 300000 rows affected (1.93 sec)

mysql> commit;
Query OK, 0 rows affected (0.48 sec)
YANGGMM commented 1 month ago

still working

Ariznawlll commented 1 month ago

集群环境 commit：0a99a7a5fc08c9927172bc34fd5089fe8538e4ea 系统租户向普通租户恢复数据 9w需要3min+

image

详细测试步骤，请联系我

YANGGMM commented 1 month ago

still working

YANGGMM commented 1 month ago 
mysql> create snapshot sp01 for account sys;
Query OK, 0 rows affected (0.04 sec)

run tpcc test

image 
mysql> restore account sys database tpcc_10 table bmsql_new_order from snapshot sp01;
YANGGMM commented 1 month ago

ppof

image

cpu.pdf

YANGGMM commented 1 month ago 
mysql> insert into `bmsql_new_order_new` select * from `bmsql_new_order` {snapshot = 'sp01'};
Query OK, 90000 rows affected (0.34 sec)

mysql> 
mysql> restore account sys database tpcc_10 table `bmsql_new_order` from snapshot sp01;
Query OK, 0 rows affected (1 min 47.18 sec)
aressu1985 commented 1 month ago

和松哥沟通，挪到1.3.0进行解决

YANGGMM commented 3 weeks ago

还在看

YANGGMM commented 3 weeks ago

still

YANGGMM commented 2 weeks ago

还在看

YANGGMM commented 2 weeks ago

需要等这个https://github.com/matrixorigin/MO-Cloud/issues/3518 解决

YANGGMM commented 1 week ago 
mysql> select count(*) from bmsql_oorder;
+----------+
| count(*) |
+----------+
|   306280 |
+----------+
1 row in set (0.02 sec)

CREATE TABLE `bmsql_oorder_test` (
  `o_w_id` INT NOT NULL,
  `o_d_id` INT NOT NULL,
  `o_id` INT NOT NULL,
  `o_c_id` INT DEFAULT NULL,
  `o_carrier_id` INT DEFAULT NULL,
  `o_ol_cnt` INT DEFAULT NULL,
  `o_all_local` INT DEFAULT NULL,
  `o_entry_d` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`o_w_id`,`o_d_id`,`o_id`)
);

mysql> insert into `bmsql_oorder_test` SELECT * FROM `tpcc_10`.`bmsql_oorder` {MO_TS = 1718781027351683000 };
Query OK, 306280 rows affected (3.49 sec)

但是 back execute

mysql> restore account sys database tpcc_10 table bmsql_oorder from snapshot sp01; Query OK, 0 rows affected (5 min 29.27 sec)

{"level":"INFO","time":"2024/06/19 15:27:20.624918 +0800","name":"log-service.frontend","caller":"frontend/snapshot.go:787","msg":"[sp01] insert select table: bmsql_oorder, insert sql: insert into tpcc_10.bmsql_oorder SELECT * FROM tpcc_10.bmsql_oorder {MO_TS = 1718781027351683000 }, cost: 5m28.657294416s","uuid":"7c4dccb4-4d3c-41f8-b482-5251dc7a41bf"}

YANGGMM commented 1 week ago
image image

@jensenojs 还是跟之前一样的问题

YANGGMM commented 1 week ago

mysql> restore account sys database tpcc_10 table bmsql_oorder from snapshot sp01; Query OK, 0 rows affected (1 min 32.10 sec)

{"level":"INFO","time":"2024/06/19 16:23:54.340143 +0800","name":"log-service.frontend","caller":"frontend/snapshot.go:787","msg":"[sp01] insert select table: bmsql_oorder, cost: 1m31.718547458s","uuid":"7c4dccb4-4d3c-41f8-b482-5251dc7a41bf"}

YANGGMM commented 1 week ago

还在看

YANGGMM commented 5 days ago

mysql> restore account sys database tpcc_10 table bmsql_new_order from snapshot sp01; Query OK, 0 rows affected (6 min 13.66 sec)

{"level":"INFO","time":"2024/06/26 15:33:11.252127 +0800","name":"log-service.frontend","caller":"frontend/snapshot.go:787","msg":"[sp01] insert select table: bmsql_new_order, cost: 6m13.329385791s","uuid":"7c4dccb4-4d3c-41f8-b482-5251dc7a41bf"}

YANGGMM commented 4 days ago

测试结果已经贴在pr中了，麻烦测试一下大数据量 @Ariznawlll

Ariznawlll commented 16 hours ago

测试中

Ariznawlll commented 13 hours ago

恢复测试，数据量大小为36.37G： 测试机器规模： https://github.com/matrixorigin/mo-nightly-regression/blob/big_data/big-data-tke.yaml

image

耗时：

image
YANGGMM commented 12 hours ago
image
YANGGMM commented 11 hours ago

测试记录

mysql> select git_version();
+---------------+
| git_version() |
+---------------+
| 5f72428       |
+---------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `table_basic_for_load_100m_new` (
    ->   `col1` TINYINT DEFAULT NULL,
    ->   `col2` SMALLINT DEFAULT NULL,
    ->   `col3` INT DEFAULT NULL,
    ->   `col4` BIGINT DEFAULT NULL,
    ->   `col5` TINYINT UNSIGNED DEFAULT NULL,
    ->   `col6` SMALLINT UNSIGNED DEFAULT NULL,
    ->   `col7` INT UNSIGNED DEFAULT NULL,
    ->   `col8` BIGINT UNSIGNED DEFAULT NULL,
    ->   `col9` FLOAT DEFAULT NULL,
    ->   `col10` DOUBLE DEFAULT NULL,
    ->   `col11` VARCHAR(255) DEFAULT NULL,
    ->   `col12` DATE DEFAULT NULL,
    ->   `col13` DATETIME DEFAULT NULL,
    ->   `col14` TIMESTAMP NULL DEFAULT NULL,
    ->   `col15` BOOL DEFAULT NULL,
    ->   `col16` DECIMAL(16,6) DEFAULT NULL,
    ->   `col17` TEXT DEFAULT NULL,
    ->   `col18` JSON DEFAULT NULL,
    ->   `col19` BLOB DEFAULT NULL,
    ->   `col20` BINARY(255) DEFAULT NULL,
    ->   `col21` VARBINARY(255) DEFAULT NULL,
    ->   `col22` VECF32(3) DEFAULT NULL,
    ->   `col23` VECF32(3) DEFAULT NULL,
    ->   `col24` VECF64(3) DEFAULT NULL,
    ->   `col25` VECF64(3) DEFAULT NULL
    -> );
Query OK, 0 rows affected (0.12 sec)

mysql> select count(*) from `table_basic_for_load_100m`;
+-----------+
| count(*)  |
+-----------+
| 100000000 |
+-----------+
1 row in set (17.02 sec)

mysql>  insert into `table_basic_for_load_100m_new` select * from `table_basic_for_load_100m`;
Query OK, 100000000 rows affected (40.38 sec)

mysql> create snapshot dev_sp for account sys;
Query OK, 0 rows affected (0.20 sec)

mysql>  restore account sys database big_data_test  table `table_basic_for_load_100m` from snapshot dev_sp;

{"level":"INFO","time":"2024/07/01 05:27:43.074995 +0000","name":"cn-service.frontend","caller":"frontend/snapshot.go:845","msg":"[sp01] insert select table: table_basic_for_load_100m, cost: 2h34m4.986256277s","uuid":"34353333-6633-6262-3037-646336306538"}
YANGGMM commented 11 hours ago

https://grafana.ci.matrixorigin.cn/explore?panes=%7B%22cg0%22:%7B%22datasource%22:%22pyroscope%22,%22queries%22:%5B%7B%22groupBy%22:%5B%5D,%22labelSelector%22:%22%7Bnamespace%3D%5C%22mo-big-data-20240701%5C%22%7D%22,%22queryType%22:%22both%22,%22refId%22:%22A%22,%22profileTypeId%22:%22memory:inuse_space:bytes:space:bytes%22,%22datasource%22:%7B%22type%22:%22grafana-pyroscope-datasource%22,%22uid%22:%22pyroscope%22%7D%7D%5D,%22range%22:%7B%22from%22:%22now-6h%22,%22to%22:%22now%22%7D%7D%7D&schemaVersion=1&orgId=1

jensenojs commented 11 hours ago
image

从cpu的结果来看, gc占用的时间是绝对的大头.

看着这个行为似乎和下面这个issue观察到的一些现象相关, @YANGGMM 钊哥辛苦对比一下测试的commit和我在那个issue下观察到的一些值得关注的commit.