Bug #101818 Huge memory usage while upgrading data dictionary with millions of tables
Submitted: 1 Dec 2020 13:59 Modified: 17 Feb 11:22
Reporter: Alexander Demidov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Dictionary Severity:S2 (Serious)
Version:8.0.20, 8.0.22 OS:Linux
Assigned to: CPU Architecture:x86
Tags: UPDATE, upgrade

[1 Dec 2020 13:59] Alexander Demidov
Description:
I want to upgrade MySQL from 8.0.20 to 8.0.21 or 8.0.22. My server contains 3000 databases with 1000 tables in each database, almost every table has indexes. Yes, I know this is a rare configuration. It is a bit like shared hosting.

I tryed to do this procedure on server with 64 Gb RAM. After starting mysqld on the new version, I see in the log for about 20 minutes:

"Data dictionary upgrading from version '80017' to '80021'"

And then all the available memory and swap on the server ends, and finally the mysqld process was killed by OOM Killer.

I took a bigger server - with 128 GB of memory. And tryed one more time.

At its peak, the mysqld process took about 106 GB. And in the end it was completed in about 1 hour.

How to repeat:
- MySQL 8.0.20.

- Create 3000 databases with 1000 tables in each database.

- Try to upgrade to 8.0.21 or 8.0.22.
[1 Dec 2020 17:51] lalit Choudhary
Test:

DD upgrade took ~28 mins for 1M tables

2020-12-01T17:08:05.091596Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80017' to '80022'.
2020-12-01T17:36:31.938776Z 1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80017' to '80022' completed.

MySQL 8.0.20
my.cnf:

innodb_buffer_pool_size=50G
table_definition_cache=5000

Load 1M tables (1000 dbs with 1000 tables in each DB). Use attached data_load.sh script, split DB count with multiple scripts for parallel data load.

Stop mysql and upgrade binaries to MySQL 8.0.22, and you will notice a slow DD upgrade with huge memory usage on OS. 

This could lead to OOM If OS has limited memory free.

2020-12-01T17:07:30.319546Z 0 [System] [MY-010116] [Server] /mnt/nvme/lalit/8.0.20/bin/mysqld (mysqld 8.0.22) starting as process 23213
2020-12-01T17:07:30.334283Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-01T17:08:05.082981Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-01T17:08:05.091596Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80017' to '80022'.

memory usage while DD upgrade in progress,

$ free -h
              total        used        free      shared  buff/cache   available
Mem:           188G         30G         75G        286M         82G        157G
Mem:           188G         35G         70G        286M         82G        152G
Mem:           188G         37G         68G        286M         82G        150G
Mem:           188G         39G         66G        286M         82G        148G
Mem:           188G         42G         63G        286M         82G        146G
Mem:           188G         44G         61G        294M         83G        143G
Mem:           188G         47G         58G        294M         83G        141G
Mem:           188G         50G         55G        294M         83G        137G
Mem:           188G         53G         50G        294M         85G        134G

2020-12-01T17:36:31.938776Z 1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80017' to '80022' completed.

mysql log:

2020-12-01T17:07:30.319546Z 0 [System] [MY-010116] [Server] /mnt/nvme/lalit/8.0.20/bin/mysqld (mysqld 8.0.22) starting as process 23213
2020-12-01T17:07:30.334283Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-12-01T17:08:05.082981Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-12-01T17:08:05.091596Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80017' to '80022'.

2020-12-01T17:36:31.938776Z 1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80017' to '80022' completed.
2020-12-01T17:37:14.401847Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 32691, socket: /tmp/mysqlx-32691.sock
2020-12-01T17:37:16.289299Z 4 [System] [MY-013381] [Server] Server upgrade from '80020' to '80022' started.
2020-12-01T17:37:54.576787Z 4 [System] [MY-013381] [Server] Server upgrade from '80020' to '80022' completed.
2020-12-01T17:37:55.325269Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-12-01T17:37:55.326452Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-12-01T17:37:55.420748Z 0 [System] [MY-010931] [Server] /mnt/nvme/lalit/8.0.20/bin/mysqld: ready for connections. Version: '8.0.22'  socket: '/tmp/mysql_sandbox22691.sock'  port: 22691  MySQL Community Server - GPL.

Pref report:
#
    59.66%     0.00%  mysqld   libpthread-2.17.so          [.] start_thread
            |
            ---start_thread
               |
                --59.66%--pfs_spawn_thread
                          |
                           --59.66%--handle_bootstrap
                                     |
                                     |--58.73%--dd::upgrade_57::do_pre_checks_and_initialize_dd
                                     |          |
                                     |          |--57.84%--dd::upgrade_57::restart_dictionary
                                     |          |          dd::bootstrap::restart
                                     |          |          |
                                     |          |          |--52.43%--dd::upgrade::do_server_upgrade_checks
                                     |          |          |          |
                                     |          |          |          |--26.47%--dd::cache::Dictionary_client::fetch_schema_components<dd::Table>
                                     |          |          |          |          |
                                     |          |          |          |           --26.43%--dd::cache::Dictionary_client::fetch<dd::Table>
                                     |          |          |          |                     |
                                     |          |          |          |                     |--12.70%--dd::Open_dictionary_tables_ctx::open_tables
                                     |          |          |          |                     |          |
                                     |          |          |          |                     |           --12.13%--open_tables
                                     |          |          |          |                     |                     |
                                     |          |          |          |                     |                      --11.32%--open_table
                                     |          |          |          |                     |                                |
                                     |          |          |          |                     |                                |--7.06%--open_table_from_share
                                     |          |          |          |                     |                                |          |
                                     |          |          |          |                     |                                |          |--3.43%--handler::ha_open
                                     |          |          |          |                     |                                |          |          |
                                     |          |          |          |                     |                                |          |           --3.17%--ha_innobase::open
                                     |          |          |          |                     |                                |          |                     |         
                                     |          |          |          |                     |                                |          |                      --1.34%--dd_table_match<dd::Table>
                                     |          |          |          |                     |                                |          |
                                     |          |          |          |                     |                                |           --0.73%--get_new_handler
                                     |          |          |          |                     |                                |                     |
                                     |          |          |          |                     |                                |                      --0.67%--ha_innobase::ha_innobase
                                     |          |          |          |                     |                                |
[1 Dec 2020 17:57] lalit Choudhary
data_load

Attachment: data_load.sh (application/x-shellscript, text), 376 bytes.

[3 Dec 2020 6:35] MySQL Verification Team
Hello Alexander Demidov,

Thank you for the report and test case.
Thank you Lalit for the test case, I slightly changed test case(10 db's, sysbench to create 100000 tables in each db) assuming it would be faster but it took longer than expected.

- 8.0.20 to 8.0.22 upgrade with 10 dbs and each with 100000
--- 5:26 -> 6:09 ~minutes and memory ~40+G during the course

2020-12-03T05:26:44.718319Z 1 [System] [MY-011090] [Server] Data dictionary upgrading from version '80017' to '80022'.
2020-12-03T05:26:44.721245Z 1 [Note] [MY-013327] [Server] MySQL server upgrading from version '80020' to '80022'.
2020-12-03T05:26:45.951246Z 1 [Note] [MY-012357] [InnoDB] Reading DD tablespace files
.
.
2020-12-03T05:51:42.409561Z 0 [Note] [MY-012356] [InnoDB] Thread# 2 - Validated 62500/62500  tablespaces
2020-12-03T06:09:53.129653Z 1 [System] [MY-013413] [Server] Data dictionary upgrade from version '80017' to '80022' completed.
2020-12-03T06:10:00.630700Z 1 [Note] [MY-013327] [Server] MySQL server upgrading from version '80020' to '80022'.
2020-12-03T06:10:03.401567Z 1 [Note] [MY-010006] [Server] Using data dictionary with version '80022'.
2020-12-03T06:10:41.674081Z 0 [Note] [MY-011332] [Server] Plugin mysqlx reported: 'IPv6 is available'
2020-12-03T06:10:41.692679Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. bind-address: '::' port: 33060'
2020-12-03T06:10:41.706002Z 0 [Note] [MY-011323] [Server] Plugin mysqlx reported: 'X Plugin ready for connections. socket: '/tmp/mysqlx.sock''
2020-12-03T06:10:41.718291Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2020-12-03T06:10:46.056302Z 4 [System] [MY-013381] [Server] Server upgrade from '80020' to '80022' started.
2020-12-03T06:10:46.085578Z 4 [Note] [MY-013386] [Server] Running queries to upgrade MySQL server.
2020-12-03T06:21:16.057384Z 4 [Note] [MY-013387] [Server] Upgrading system table data.
2020-12-03T06:21:17.068536Z 4 [Note] [MY-013385] [Server] Upgrading the sys schema.
2020-12-03T06:21:18.524400Z 4 [Note] [MY-013400] [Server] Upgrade of help tables started.
2020-12-03T06:21:18.718445Z 4 [Note] [MY-013400] [Server] Upgrade of help tables completed.
2020-12-03T06:21:18.732926Z 4 [Note] [MY-013394] [Server] Checking 'mysql' schema.
2020-12-03T06:21:19.029187Z 4 [Note] [MY-013394] [Server] Checking 'sys' schema.
2020-12-03T06:21:19.080254Z 4 [System] [MY-013381] [Server] Server upgrade from '80020' to '80022' completed.

regards,
Umesh
[17 Feb 11:22] Erlend Dahl
[6 Jan 2021 14:27] Daniel T Price 

Fixed as of the upcoming 8.0.24 release, and here's the proposed changelog
entry from the documentation team:
 
Upgrading a MySQL instance with a very large number of tables consumed an
excessive amount of memory. Memory allocated to analyze data dictionary
entities for possible upgrade was not released until all entities were
processed.