bug: long time spent in beginning of `COPY INTO`

[yufan022]

Search before asking

• I had searched in the issues and found no similar issues.

Version

1.0.9

What's Wrong?

| id | type | host | user | command | database | extra_info | memory_usage | data_read_bytes | data_write_bytes | scan_progress_read_rows | scan_progress_read_bytes | mysql_connection_id | time | status |
ac8ee750-c404-4707-811c-dedf8986a105 MySQL xx:xx write Query default COPY INTO xx from @XX pattern='.*[.]csv$' FILE_FORMAT = (TYPE = 'CSV' FIELD_DELIMITER = '\t' RECORD_DELIMITER = '\n') PURGE=true -242324186 0 0 0 0 9 3482

scan_progress_read_rows=0, It seems that too much time was spent in the list on s3. There are about 100000 files in S3.

100000/3482=28/s

How to Reproduce?

No response

Are you willing to submit PR?

• Yes I am willing to submit a PR!

[BohuTANG]

v1.0.10:

list @stage;

....

195329 rows in set (8.42 sec)

23198.2185273 files per second of the list.

[BohuTANG]

Just test list a million files:

list @bomb;
... ...
1171905 rows in set (1 min 18.59 sec)

14834 files per second.

The files generated by:

create stage bomb;
copy into @bomb from (select * from numbers(1000000000)) file_format=(type=parquet) max_file_size=32;

[yufan022]

v1.0.10

If there are many files in S3, it will take a long time(more than 3000 seconds) then SCAN_PROGRESS_ROWS will start to grow.

Is that normal?

[BohuTANG]

It seems to purge the file cost time.

Just did a test, if we delete 97664 files, it took 11 min 49.74 sec:

list @bomb;
97664 rows in set (6.43 sec)

remove @bomb;
Query OK, 0 rows affected (11 min 49.74 sec)

[BohuTANG]

It seems to purge the file cost time.

Just did a test, if we delete 97664 files, it took 11 min 49.74 sec:

list @bomb;
97664 rows in set (6.43 sec)

remove @bomb;
Query OK, 0 rows affected (11 min 49.74 sec)

Oh, I test it using remove @stage, the code is not the same as COPY ... purge=true.
With this patch:
#10499

It's fast enough:

list @bomb;
97664 rows in set (4.40 sec)

mysql> remove @bomb;
Query OK, 0 rows affected (33.12 sec)

But we can make it faster in parallel batch delete.

[yufan022]

take a long time to start scan. At first of execution, not in the end.

It looks like the purge hasn't started yet, purge should be in the final stage.

[yufan022]

list stage

but copy:


SCAN_PROGRESS_ROWS took a long time to start to grow.

[BohuTANG]

If the stage has many files, to check whether the file copied or not, the meta API is not in batch:
https://github.com/datafuselabs/databend/blob/ade74d6cc3cd5736ef900452ecddc90efca241c9/src/meta/api/src/schema_api_impl.rs#L1834-L1845

We will improve it to batch.

cc @drmingdrmer