MySQL Bugs: #103980: High memory allocation for DROP DATABASE

Bug #103980	High memory allocation for DROP DATABASE
Submitted:	11 Jun 2021 13:20	Modified:	15 Jun 2021 6:16
Reporter:	Sveta Smirnova (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	8.0.23, 8.0.25	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
MySQL allocates too much memory when dropping a database that has many partitioned tables.

How to repeat:
1. Start mysqld. For example, you can use this command:

./mtr --start innodb --mysqld=--innodb_buffer_pool_size=1G --mysqld=--innodb_log_file_size=512M --mysqld=--innodb-write-io-threads=64  --mysqld=--innodb_open_files=65535 &

2. Make sure innodb_file_per_table is ON
3. Start watching memory usage of the mysqld process:

pidstat -r -p `cat var/run/mysqld.1.pid` 2 > pidstat.`cat var/run/mysqld.1.pid`.8023 &

4. Open a new terminal and run the attached test case. It creates 100 tables, each having 8100 partitions, then drops the database holding these tables.
5. You will notice that memory usage will increase. In my case, it was 0,37% RAM after the first few tables were created, 12.19% while DROP DATABASE was running, and 9.23% when DROP DATABASE finished on one test machine with a large amount of memory. On a 32G Digital Ocean droplet, it allocated up to 95% RAM while DROP was running and lowered down to 55% once it is finished. Memory usage did not decrease further.

Version 5.7 did not allocate much memory for the same operation.

Suggested fix:
1. Do not allocate too much memory for the DROP DATABASE command.
2. Free memory after the command finished.

SQL to repeat the bug

Attachment: PS-7676.sql (application/sql, text), 2.71 KiB.

jemalloc seems to help reclaim more memory than standard malloc.

Hello Sveta,

Thank you for the report and feedback.

regards,
Umesh

Posted by developer:
 
Hello!

Note that series of patches addressing bug #29634540 "DROP DATABASE OF 1 MILLION TABLES RESULTED CRASH OF MYSQLD"
which were pushed mysql-8.0 branch and which will be part of 8.0.26 release should alleviate this problem to
some extent. For example, in my smaller test case I have observed that memory consumption were reduced by half.

I think that it makes sense to check if these patches are good enough for real life use-case which made you
report this bug once 8.0.26 is published.