MySQL Bugs: #116455: Server upgrade progress will open all tablespaces and not close

Bug #116455	Server upgrade progress will open all tablespaces and not close
Submitted:	23 Oct 2024 3:31	Modified:	14 Nov 2024 8:19
Reporter:	George Ma (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	8.0.40	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
After upgrade my MySQL instance from 8.0.25 to 8.0.28, I found the Innodb_num_open_files is too big (400k+), actually the table number is nearly 400k. I have checked the /proc/$PID/fd and found all 400k+ ibd files have been opened.

I'm curious about why all tablespaces have been opened and not closed, so I just restart the MySQL instance, but then found the Innodb_num_open_files become normal.

After check the source code, I found that the server upgrade progress is a little confused:

|--> bootstrap::handle_bootstrap
|    |--> dd::upgrade::upgrade_system_schemas
|    |    |--> dd::upgrade::(anonymous namespace)::fix_mysql_tables
|    |    |    |--> dd::upgrade::(anonymous namespace)::ignore_error_and_execute (select ENCRYPTION from information_schema.INNODB_TABLESPACES where NAME='mysql')
|    |    |    |    |--> Ed_connection::execute_direct
|    |    |    |    |    |--> ...
|    |    |    |    |    |    |--> do_fill_information_schema_table
|    |    |    |    |    |    |    |--> i_s_innodb_tablespaces_fill_table
|    |    |    |    |    |    |    |    |--> i_s_dict_fill_innodb_tablespaces
|    |    |    |    |    |    |    |    |    |--> fil_space_get_first_path
|    |    |    |    |    |    |    |    |    |    |--> Fil_shard::space_load
|    |    |    |    |    |    |    |    |    |    |    |--> Fil_shard::prepare_file_for_io
|    |    |    |    |    |    |    |    |    |    |    |    |--> Fil_shard::open_file

The upgrage progress will execute some inner SQL(select ENCRYPTION from information_schema.INNODB_TABLESPACES where NAME='mysql'), which will open all tablespaces.

Actually, if not upgrade but just execute this SQL by manual, it will also open all tablespaces, and I think this logic can be optimized.

How to repeat:
Init and start a MySQL instance with lower version, then create 400k tables, then upgrade it to higher version.

Or just build in debug mode, and restart with --upgrade=FORCE --debug="+d,force_fix_user_schemas"

Hello George Ma,

Thank you for the report and feedback.
If possible could you please share ulimit -a from your environment along with exact configuration file in use if not on default? Thank you.

regards,
Umesh

#ulimit -a
core file size          (blocks, -c) 786432
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 506307
max locked memory       (kbytes, -l) unlimited
max memory size         (kbytes, -m) unlimited
open files                      (-n) 655360
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) unlimited
cpu time               (seconds, -t) unlimited
max user processes              (-u) 655360
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

What's more, the config of open_files_limit and innodb_open_files are:

open_files_limit = 655350
innodb_open_files = 655350