| Bug #101818 | Huge memory usage while upgrading data dictionary with millions of tables | ||
|---|---|---|---|
| Submitted: | 1 Dec 2020 13:59 | Modified: | 17 Feb 2021 11:22 |
| Reporter: | Alexander Demidov | Email Updates: | |
| Status: | Closed | Impact on me: | |
| 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
[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 2021 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.
