Details
-
Bug
-
Status: Needs Feedback (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.8
-
None
-
Debian Bookworm
Description
Our MariaDB servers with many thousands of clients sometimes encounter slow ALTER TABLE queries (up to 25 seconds) with adjacent read-only queries also being just as slow; all read-only queries apparently wait for the one ALTER TABLE to finish the commit.
Using two BPF scripts (https://github.com/iovisor/bcc/pull/5108 and https://github.com/iovisor/bcc/pull/5112), I pinpointed this to contention on dict_sys.latch; a common backtrace looks like this:
syscall
|
unlock_and_close_files(std::vector<pfs_os_file_t, std::allocator<pfs_os_file_t> > const&, trx_t*)
|
ha_innobase::commit_inplace_alter_table(TABLE*, Alter_inplace_info*, bool)
|
mysql_inplace_alter_table(THD*, TABLE_LIST*, TABLE*, TABLE*, Alter_inplace_info*, MDL_request*, st_ddl_log_state*, TRIGGER_RENAME_PARAM*, Alter_table_ctx*, bool&, unsigned long long&, bool) [clone .constprop.0]
|
mysql_alter_table(THD*, st_mysql_const_lex_string const*, st_mysql_const_lex_string const*, Table_specification_st*, TABLE_LIST*, Recreate_info*, Alter_info*, unsigned int, st_order*, bool, bool)
|
Sql_cmd_alter_table::execute(THD*)
|
mysql_execute_command(THD*, bool)
|
mysql_parse(THD*, char*, unsigned int, Parser_state*)
|
dispatch_command(enum_server_command, THD*, char*, unsigned int, bool)
|
do_command(THD*, bool)
|
The method ha_innobase::commit_inplace_alter_table indeed does most of its I/O while having dict_sys.latch locked exclusively. I believe that one should not do any I/O while holding such a (global) lock, one that is commonly locked for all queries, including read-only queries.
This is a major scalability issue for us, because it is easy for (unprivileged) MariaDB users to lock up the whole daemon (including all databases and all catalogs) and it could be used to DoS the MariaDB server.
Attachments
Issue Links
- relates to
-
MDEV-8069 DROP or rebuild of a large table may lock up InnoDB
- Closed
-
MDEV-15641 InnoDB crash while committing table-rebuilding ALTER TABLE
- Closed