Bug #109859 very slow converting table to row_format=compressed

• Submitted: 31 Jan 2023 9:36
• Modified: 6 Feb 2023 14:35
• Reporter: Egor Zagorskii
• Status: Can't repeat
• Category: MySQL Server: InnoDB storage engine
• Severity: S5 (Performance)
• Version: 8.0.30
• OS: Ubuntu (20.04)
• CPU Architecture: x86

[31 Jan 2023 9:36] Egor Zagorskii

Description:
Converting table from dynamic format to ROW_FORMAT=COMPRESSED, ALGORITHM=COPY performs only 4 mb/s. It does not depend on hardware: both on Intel Atom with hdd 5400 rpm and Intel Xeon Platinum with NVMe there is only 4 mb/s. 
There is no any other load on server and mysql instance. In perf top there are no "red" syscalls:

   4.72%  mysqld                                  [.] 0x0000000002c6e82c
   2.61%  mysqld                                  [.] rec_init_offsets_comp_ordinary
   2.33%  mysqld                                  [.] cmp_dtuple_rec_with_match_low
   2.22%  mysqld                                  [.] 0x0000000002c6e828
   1.55%  mysqld                                  [.] page_cur_search_with_match
   1.45%  libc-2.31.so                            [.] 0x000000000018ba51
   1.17%  mysqld                                  [.] Buf_fetch_normal::get
   1.10%  mysqld                                  [.] 0x0000000002c6e825
   1.08%  [kernel]                                [k] osq_lock
   1.05%  mysqld                                  [.] btr_cur_search_to_nth_level
   1.00%  mysqld                                  [.] 0x0000000002c6e80e
   0.94%  [kernel]                                [k] filemap_map_pages
   0.92%  mysqld                                  [.] cmp_dtuple_rec_with_match_bytes
   0.87%  [unknown]                               [.] 0000000000000000
   0.81%  [kernel]                                [k] native_queued_spin_lock_slowpath
   0.79%  mysqld                                  [.] Buf_fetch<Buf_fetch_normal>::single_page
   0.79%  mysqld                                  [.] page_zip_dir_delete
   0.79%  mysqld                                  [.] rec_get_offsets
   0.76%  mysqld                                  [.] btr_search_guess_on_hash
   0.70%  telegraf                                [.] 0x0000000000023c53
   0.68%  mysqld                                  [.] rec_init_offsets
   0.59%  mysqld                                  [.] buf_block_from_ahi
   0.58%  mysqld                                  [.] 0x0000000002c6e80a
   0.53%  mysqld                                  [.] page_cur_search_with_match_bytes
   0.51%  mysqld                                  [.] mlog_open_and_write_index
   0.49%  mysqld                                  [.] page_zip_rec_set_owned
   0.45%  mysqld                                  [.] 0x0000000002c6e817

How to repeat:
ALTER TABLE $t ENGINE=InnoDB ROW_FORMAT=COMPRESSED, ALGORITHM=COPY

[6 Feb 2023 14:35] MySQL Verification Team

Hi Mr. Zagorski,

Thank you for your bug report.

First, having in mind a VERY large number of operations that are performed in that DDL, 4 Mb/sec is an excellent result.

Since you have filed this as performance bug, what we would need is the output from profiling.

If it shows any inconsistency, then we shall change. the status of your report.