| Bug #113439 | Alter database charset lead to cpu high load | ||
|---|---|---|---|
| Submitted: | 15 Dec 2023 11:03 | Modified: | 16 Dec 2023 2:25 |
| Reporter: | peng gao | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S3 (Non-critical) |
| Version: | 8.0.35 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
[15 Dec 2023 14:42]
MySQL Verification Team
Hi Mr. gao, Thank you for your bug report. Thank you also for your patches. However, we can not verify a bug with a fully reproducible test case. We would like to have the test case, so that our Development can evaluate your patches. Can't repeat.
[15 Dec 2023 14:42]
MySQL Verification Team
Hi Mr. gao, Thank you for your bug report. Thank you also for your patches. However, we can not verify a bug with a fully reproducible test case. We would like to have the test case, so that our Development can evaluate your patches. Can't repeat.
[16 Dec 2023 2:13]
peng gao
Version and Some parameter,
innodb_buffer_pool_size=2G
open_files_limit = 25536
innodb_open_files = 20000
max_connections=5000
table_open_cache=8192
table_definition_cache=4096
tablespace_definition_cache=4096
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.35 |
+-----------+
This step can be reproduced
1. first , testdb include 10000 tables
for ((i=1;i<20000;i++)) do mysql -S'/var/lib/mysql/mysql.sock' -u root -p'test' -e "create table testdb.test$i (id int); " ;done;
2. Execute alter database statement multiple times
alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;
3. Execute perf record during alter database execution
perf record -a -e cycles -o /tmp/perf_cycle.perf -g sleep 30
perf report -i /tmp/perf_cycle.perf > result.log
4. check result.log
--56.38%--mysql_alter_db
| |
| |--55.28%--lock_table_names
| | |
| | --54.76%--get_and_lock_tablespace_names
| | |
| | --54.13%--dd::fill_table_and_parts_tablespace_names
BTW. If the procedure collation and database collation are inconsistent, When there are many such procedures mysqldump may generates a large number of alter database statements.
see mysqldump code,
if （strcmp(current_db_cl_name, required_db_cl_name) != 0）
{...
fprintf(sql_file, "ALTER DATABASE %s CHARACTER SET %s COLLATE %s %s\n",
quoted_db_name, db_cl->csname, db_cl->m_coll_name, delimiter);
...
}
[16 Dec 2023 2:15]
peng gao
Version and Some parameter,
innodb_buffer_pool_size=2G
open_files_limit = 25536
innodb_open_files = 20000
max_connections=5000
table_open_cache=8192
table_definition_cache=4096
tablespace_definition_cache=4096
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.35 |
+-----------+
This step can be reproduced
1. first , testdb include 20000 tables
for ((i=1;i<20000;i++)) do mysql -S'/var/lib/mysql/mysql.sock' -u root -p'test' -e "create table testdb.test$i (id int); " ;done;
2. Execute alter database statement multiple times
alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;alter database testdb charset utf8;alter database testdb charset utf8mb4;
3. Execute perf record during alter database execution
perf record -a -e cycles -o /tmp/perf_cycle.perf -g sleep 30
perf report -i /tmp/perf_cycle.perf > result.log
4. check result.log
--56.38%--mysql_alter_db
| |
| |--55.28%--lock_table_names
| | |
| | --54.76%--get_and_lock_tablespace_names
| | |
| | --54.13%--dd::fill_table_and_parts_tablespace_names
BTW. If the procedure collation and database collation are inconsistent, When there are many such procedures mysqldump may generates a large number of alter database statements.
see mysqldump code,
if （strcmp(current_db_cl_name, required_db_cl_name) != 0）
{...
fprintf(sql_file, "ALTER DATABASE %s CHARACTER SET %s COLLATE %s %s\n",
quoted_db_name, db_cl->csname, db_cl->m_coll_name, delimiter);
...
}
[16 Dec 2023 2:25]
peng gao
These are some execution logs of my environment, Query OK, 1 row affected (4.23 sec) Query OK, 1 row affected, 1 warning (4.51 sec) Query OK, 1 row affected (4.99 sec) Query OK, 1 row affected, 1 warning (4.60 sec) Query OK, 1 row affected (4.72 sec) Query OK, 1 row affected, 1 warning (4.44 sec) Query OK, 1 row affected (4.78 sec) Query OK, 1 row affected, 1 warning (4.50 sec) Query OK, 1 row affected (3.89 sec) Query OK, 1 row affected, 1 warning (4.61 sec) Query OK, 1 row affected (5.03 sec) Query OK, 1 row affected, 1 warning (4.39 sec) Query OK, 1 row affected (4.68 sec) Query OK, 1 row affected, 1 warning (4.18 sec) Query OK, 1 row affected (3.98 sec)
[18 Dec 2023 11:23]
MySQL Verification Team
Hi Mr. gao, Can you please answer some questions to us. Why do you think that ALTERING 20.000 (twenty thousand) tables in N threads (BTW how many threads ???) would not cause a high CPU load ????? Next, high CPU load could be visible without raning any profiling tool. Next, what kind of computer are you running it on ????? What is a total number of CPU cores, what CPU is it, how much RAM do you dedicate to MySQL, are you using SSD or HDD and so on ..... Fourth but not last, why do you think that your patch would improve performance. Actually, what performance improvement did you measure with your patch being applied. Next, we see two patches . So which one is the one that you propose. Last, but not least, why do you think that MYSQL_ONLY_SCHEMA_CHANGE would not createe any regression bugs ????? Thanks in advance.
[18 Dec 2023 11:23]
MySQL Verification Team
Hi, One more question ......... We do not see what has mysqldump got to do with your test case ........
Description: Database include great many tables (e.g. 10000 tables ),alter database charset utf8 statment is relatively slow and Consuming a large amount of CPU on function dd::fill_table_and_parts_tablespace_names. |--63.45%--mysql_alter_db | | | |--56.64%--lock_table_names | | | | | |--53.94%--get_and_lock_tablespace_names | | | | | | | |--50.54%--dd::fill_table_and_parts_tablespace_names But I consider that the function dd::fill_table_and_parts_tablespace_names does not need to be called in this situation .Because all tables have been locked with MDL and only the schema attribute is modified here. Do we consider use a macro to skip this situation ? #define MYSQL_ONLY_SCHEMA_CHANGE 0x80000 diff --git a/sql_basebak.cc b/sql_base.cc index d3ebe41..fc20921 100644 --- a/sql_basebak.cc +++ b/sql_base.cc @@ -5258,7 +5258,7 @@ bool get_and_lock_tablespace_names(THD *thd, TABLE_LIST *tables_start, if (table->mdl_request.type != MDL_SHARED_READ_ONLY && (table->mdl_request.is_ddl_or_lock_tables_lock_request() || table->open_strategy == TABLE_LIST::OPEN_FOR_CREATE) && - !is_temporary_table_being_opened(table) && !table->is_system_view) { + !is_temporary_table_being_opened(table) && !table->is_system_view && (flags & MYSQL_ONLY_SCHEMA_CHANGE) == 0) { // We have basically three situations here: // // 1. Lock only the target tablespace name and tablespace diff --git a/newdata/mysql-8.0.23/sql/sql_dbbak.cc b/newdata/mysql-8.0.23/sql/sql_db.cc index cbb8b20..d8109fe 100644 --- a/newdata/mysql-8.0.23/sql/sql_dbbak.cc +++ b/newdata/mysql-8.0.23/sql/sql_db.cc @@ -573,7 +573,7 @@ bool mysql_alter_db(THD *thd, const char *db, HA_CREATE_INFO *create_info) { TABLE_LIST *tables = nullptr; if (find_db_tables(thd, *schema, db, &tables) || lock_table_names(thd, tables, nullptr, thd->variables.lock_wait_timeout, - 0)) + MYSQL_ONLY_SCHEMA_CHANGE)) return true; // Set new collation ID if submitted in the statement. How to repeat: Database include great many tables (e.g. 10000 tables ),alter database charset utf8. Suggested fix: #define MYSQL_ONLY_SCHEMA_CHANGE 0x80000 diff --git a/sql_basebak.cc b/sql_base.cc index d3ebe41..fc20921 100644 --- a/sql_basebak.cc +++ b/sql_base.cc @@ -5258,7 +5258,7 @@ bool get_and_lock_tablespace_names(THD *thd, TABLE_LIST *tables_start, if (table->mdl_request.type != MDL_SHARED_READ_ONLY && (table->mdl_request.is_ddl_or_lock_tables_lock_request() || table->open_strategy == TABLE_LIST::OPEN_FOR_CREATE) && - !is_temporary_table_being_opened(table) && !table->is_system_view) { + !is_temporary_table_being_opened(table) && !table->is_system_view && (flags & MYSQL_ONLY_SCHEMA_CHANGE) == 0) { // We have basically three situations here: // // 1. Lock only the target tablespace name and tablespace diff --git a/newdata/mysql-8.0.23/sql/sql_dbbak.cc b/newdata/mysql-8.0.23/sql/sql_db.cc index cbb8b20..d8109fe 100644 --- a/newdata/mysql-8.0.23/sql/sql_dbbak.cc +++ b/newdata/mysql-8.0.23/sql/sql_db.cc @@ -573,7 +573,7 @@ bool mysql_alter_db(THD *thd, const char *db, HA_CREATE_INFO *create_info) { TABLE_LIST *tables = nullptr; if (find_db_tables(thd, *schema, db, &tables) || lock_table_names(thd, tables, nullptr, thd->variables.lock_wait_timeout, - 0)) + MYSQL_ONLY_SCHEMA_CHANGE)) return true; // Set new collation ID if submitted in the statement.