Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Duplicate
-
10.1.36, 10.4.10, 10.2(EOL), 10.3(EOL)
-
Docker hub image MariaDB 10.1.36 to 10.4.10
RAM: 58 GB
CPU(s): 8
Model name: Intel(R) Xeon(R) Platinum 8167M CPU @ 2.00GHz
CPU MHz:1995.312
Number of Databases: 100
Description
When I am trying to upgrade >=10.1.36 to 10.4.10, following issue is coming.
Count of open_tables increasing beyond 100K, if load increases it is going beyond 200K.
Due to this queries execution becomes slow, number of slow queries are increasing and after some time mariaDB is unable to handle requests.
This issue is not occurring with 10.1.35 and count of open_tables not crossing default limit(400),working perfectly with 10.1.35.
Suspicious commit:https://github.com/MariaDB/server/commit/64a23c1c8a826a6f58f8a415f60a0e3cc0e0375f
After commenting out the if-statement-block in sql/sql_base.cc from above commit issue is resolved and working properly.
if-statement : if (table_list->table->file->referenced_by_foreign_key())
Attachments
Issue Links
- is duplicated by
-
MDEV-22180 Planner opens unnecessary tables when updated table is referenced by foreign keys
- Closed
Thanks for the report.
MTR test case which demonstrates the problem is below.
It is for debugging purposes only, do not put it into the regression suite.
--source include/have_innodb.inc
) ENGINE=InnoDB;
--let $num= 500
while ($num)
{
}
--let $num= 32
while ($num)
{
LOOP
}
--connection default
--let $num= 10
while ($num)
{
}
10.1.36
show variables like 'table_open_cache';
Variable_name Value
table_open_cache 421
SHOW GLOBAL STATUS LIKE 'open_tables';
Variable_name Value
Open_tables 5868
SHOW GLOBAL STATUS LIKE 'open_tables';
Variable_name Value
Open_tables 7899
SHOW GLOBAL STATUS LIKE 'open_tables';
Variable_name Value
Open_tables 7442
SHOW GLOBAL STATUS LIKE 'open_tables';
Variable_name Value
Open_tables 8460
...
10.1.35
show variables like 'table_open_cache';
Variable_name Value
table_open_cache 421
SHOW GLOBAL STATUS LIKE 'open_tables';
Variable_name Value
Open_tables 52
SHOW GLOBAL STATUS LIKE 'open_tables';
Variable_name Value
Open_tables 54
SHOW GLOBAL STATUS LIKE 'open_tables';
Variable_name Value
Open_tables 54
SHOW GLOBAL STATUS LIKE 'open_tables';
Variable_name Value
Open_tables 54
...