| Bug #103393 | need efficient way to bulk import grants & privileges for many users/schemas | ||
|---|---|---|---|
| Submitted: | 21 Apr 2021 8:02 | ||
| Reporter: | Shane Bester (Platinum Quality Contributor) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Security: Privileges | Severity: | S5 (Performance) |
| Version: | 8.0.24 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[21 Apr 2021 8:02]
Shane Bester
[21 Apr 2021 16:19]
MySQL Verification Team
testcase that shows grants get slower and slower as more users are created
Attachment: mytest_setup_grants_sp.sql (application/sql, text), 2.00 KiB.
[21 Apr 2021 17:09]
MySQL Verification Team
Above testcase starts off by taking 0.5 seconds to grant privs to a DB.
It degrades more and more, eventually taking >15 seconds each. I ran "perf" on 8.0.24 to see where majority of time was spent, it seems sorting...
- 100.00% 100.00% mysqld
- 97.88% start_thread
- 97.88% pfs_spawn_thread
- 97.88% handle_connection
- 97.88% do_command
- 97.88% dispatch_command
- 97.88% dispatch_sql_command
- 97.88% mysql_execute_command
- 97.88% Sql_cmd_dml::execute
- 97.88% Sql_cmd_call::execute_inner
- sp_head::execute_procedure
- 97.87% sp_head::execute
- 97.65% sp_instr_stmt::execute
- 97.64% sp_lex_instr::validate_lex_and_execute_core
- 97.63% sp_lex_instr::reset_lex_and_exec_core
- 97.63% sp_instr_stmt::exec_core
- 97.61% mysql_execute_command
- 97.16% mysql_sql_stmt_execute
- 97.15% Prepared_statement::execute_loop
- 97.14% Prepared_statement::execute
- 97.10% mysql_execute_command
- 97.00% mysql_grant
- 95.16% replace_db_table
- 94.49% acl_insert_db
- 82.86% std::__introsort_loop<ACL_DB*, long, __gnu_cxx::__ops::_Iter_comp_iter<ACL_compare> >
- 63.10% std::__introsort_loop<ACL_DB*, long, __gnu_cxx::__ops::_Iter_comp_iter<ACL_compare> >
- 48.85% std::__introsort_loop<ACL_DB*, long, __gnu_cxx::__ops::_Iter_comp_iter<ACL_compare> >
- 37.72% std::__introsort_loop<ACL_DB*, long, __gnu_cxx::__ops::_Iter_comp_iter<ACL_compare> >
- 28.04% std::__introsort_loop<ACL_DB*, long, __gnu_cxx::__ops::_Iter_comp_iter<ACL_compare> >
- 19.43% std::__introsort_loop<ACL_DB*, long, __gnu_cxx::__ops::_Iter_comp_iter<ACL_compare> >
- 12.14% std::__introsort_loop<ACL_DB*, long, __gnu_cxx::__ops::_Iter_comp_iter<ACL_compare> >
- 6.60% std::__introsort_loop<ACL_DB*, long, __gnu_cxx::__ops::_Iter_comp_iter<ACL_compare> >
- 3.02% std::__introsort_loop<ACL_DB*, long, __gnu_cxx::__ops::_Iter_comp_iter<ACL_compare> >
acl_insert_db looks like this :
void acl_insert_db(const char *user, const char *host, const char *db,
ulong privileges) {
ACL_DB acl_db;
assert(assert_acl_cache_write_lock(current_thd));
acl_db.set_user(&global_acl_memory, user);
acl_db.set_host(&global_acl_memory, host);
acl_db.db = strdup_root(&global_acl_memory, db);
acl_db.access = privileges;
acl_db.sort = get_sort(3, acl_db.host.get_host(), acl_db.db, acl_db.user);
acl_dbs->push_back(acl_db);
std::sort(acl_dbs->begin(), acl_dbs->end(), ACL_compare());
}
[21 Apr 2021 17:10]
MySQL Verification Team
things get really slow as mysql.db size increases as per the testcase. mysql> select count(*) from mysql.db; +----------+ | count(*) | +----------+ | 273136 | +----------+ 1 row in set (0.08 sec)
[23 Apr 2021 5:29]
MySQL Verification Team
Testing a preliminary patch to address the std::sort issue: With attached testcase, I ran: call setup_grants(/*users*/400,/*schemas*/400); Timings: ordinary 8.0.24: ---------------- +------------------------------------------------+ | status | +------------------------------------------------+ | schema400 took 5.027612 seconds to grant privs | +------------------------------------------------+ 1 row in set (16 min 39.23 sec) +--------------------------+---------------------+ | done with granting privs | now() | +--------------------------+---------------------+ | done with granting privs | 2021-04-23 07:21:53 | +--------------------------+---------------------+ 1 row in set (16 min 39.23 sec) ----------------- patched 8.0.24: ----------------- +------------------------------------------------+ | status | +------------------------------------------------+ | schema400 took 0.068686 seconds to grant privs | +------------------------------------------------+ 1 row in set (43.50 sec) +--------------------------+---------------------+ | done with granting privs | now() | +--------------------------+---------------------+ | done with granting privs | 2021-04-23 07:27:13 | +--------------------------+---------------------+ 1 row in set (43.50 sec)
[23 Apr 2021 5:30]
MySQL Verification Team
dlenev: I used this patch on 8.0.24:
--- a/./mysqlcom-8.0.24/sql/auth/sql_auth_cache.cc
+++ b/./mysqlcom-8.0.24-bug103393/sql/auth/sql_auth_cache.cc
@@ -3022,8 +3022,8 @@ void acl_insert_db(const char *user, const char *host, const char *db,
acl_db.db = strdup_root(&global_acl_memory, db);
acl_db.access = privileges;
acl_db.sort = get_sort(3, acl_db.host.get_host(), acl_db.db, acl_db.user);
- acl_dbs->push_back(acl_db);
- std::sort(acl_dbs->begin(), acl_dbs->end(), ACL_compare());
+ auto upper_bound = std::upper_bound(acl_dbs->begin(), acl_dbs->end(), acl_db, ACL_compare());
+ acl_dbs->insert(upper_bound, acl_db);
}
void get_mqh(THD *thd, const char *user, const char *host, USER_CONN *uc) {
[23 Apr 2021 5:31]
MySQL Verification Team
Changing bug category to "S5 Performance"
[4 May 2021 18:22]
Simon Mudd
This patch looks to improve performance (doing bulk grant writes) by about 3x (e.g. time to apply grants reduced by 63%).
[27 May 2021 12:25]
Dmitry Lenev
Posted by developer: Hello! The problem with CPU overhead of GRANT statements due to std::sort has been split into separate bug #32934351 "GRANTS ARE GETTING SLOWER AND SLOWER WITH THOUSANDS USERS AND SCHEMAS". Let us use this report to track progress of the original Feature Request.
