[MDEV-13678] DELETE with CASCADE takes a long time when Galera is enabled Created: 2017-08-30  Updated: 2020-08-25  Resolved: 2017-09-18

Status: Closed
Project: MariaDB Server
Component/s: Galera
Affects Version/s: 10.2.8
Fix Version/s: 10.2.9

Type: Bug Priority: Major
Reporter: Marko Mäkelä Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
Labels: performance

Issue Links:
Blocks
is blocked by MDEV-13498 DELETE with CASCADE constraints takes... Closed
Relates
relates to MDEV-15611 Due to the failure of foreign key det... Closed

 Description   

MDEV-13498 fixed a bug that DELETE with CASCADE was very slow due to a Galera-related check even when Galera was not enabled.

After that fix, the operation is still slow when Galera is enabled.

When I notified seppo about MDEV-13498, on August 17 he mentioned that the purpose of this condition is to avoid evaluating a potentially expensive condition. Now it seems that evaluating this filtering condition may cost more than the cost of evaluating the supposedly-expensive condition.
I suggested the use of a lossy filter that would use a much simpler and faster data structure and algorithm than the std::find() on the std::deque, something like a hash table. This lossy filter would be allowed to fail safely, causing unnecessary evaluation of the supposedly-expensive condition.

 Comments   
Comment by Jan Lindström (Inactive) [ 2017-09-13 ]

After a fix with test case provided on MDEV-13498:

jan@jan-laptop-asus:~/mysql/10.2/mysql-test$ ./mtr --vardir=/dev/shm galera_fk_big innodb_fk_big
 
Logging: ./mtr  --vardir=/dev/shm galera_fk_big innodb_fk_big
 
vardir: /dev/shm
 
Checking leftover processes...
 
 - found old pid 15383 in 'mysqld.2.pid', killing it...
 
   process did not exist!
 
Removing old var directory...
 
couldn't chmod(0777, /dev/shm): Operation not permitted at /usr/share/perl/5.24/File/Find.pm line 511.
 
Couldn't remove directory '/dev/shm': Permission denied at /usr/share/perl/5.24/File/Find.pm line 511.
 
Creating var directory '/dev/shm'...
 
Checking supported features...
 
MariaDB Version 10.2.9-MariaDB
 
 - SSL connections supported
 
Collecting tests...
 
Installing system database...
 
 
 
==============================================================================
 
 
 
TEST                                      RESULT   TIME (ms) or COMMENT
 
--------------------------------------------------------------------------
 
 
 
worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 16000..16019
 
galera.galera_fk_big 'innodb'            [ pass ]  48717
 
innodb.innodb_fk_big 'innodb'            [ pass ]  35732
 
--------------------------------------------------------------------------
 
The servers were restarted 1 times
 
Spent 84.449 of 124 seconds executing testcases
 
 
 
Completed: All 2 tests were successful.

Comment by Jan Lindström (Inactive) [ 2017-09-13 ]

Ported fix from mysql-wsrep-bugs branch, can't add the actual test case as a
part of mtr as it is too big.

https://github.com/MariaDB/server/commits/bb-10.2-MDEV-13678

Comment by Marko Mäkelä [ 2017-09-13 ]

The patch is reintroducing code duplication that was removed in the MDEV-13498 fix. Please merge the change to the function wsrep_must_process_fk().

Comment by Jan Lindström (Inactive) [ 2017-09-13 ]

See above link fixed.

Comment by Marko Mäkelä [ 2017-09-14 ]

I would like to see some further reduction of code duplication, and a test that covers this change.

Comment by Jan Lindström (Inactive) [ 2017-09-18 ]

commit 16b374b978afaff79aba041ea3d3c2ffe53a739a
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Wed Sep 13 19:51:30 2017 +0300

MDEV-13678: DELETE with CASCADE takes a long time when Galera is enabled

Use wsrep_must_process_fk function to check if foreign key
constraint needs to be processed in case of Galera is enabled.

commit 6a524ca56b2932f38cc4b328abd38aed023b1e8a
Author: sjaakola <seppo.jaakola@iki.fi>
Date: Fri Aug 25 11:33:13 2017 +0300

MW-402 cascading FK issue (5.7 version)

Optimizing cascade node list traversal, which could turn out as
performance bottleneck
Even this current cascade node check could be skipped, but a dedicated
mtr test is needed to confirm that

commit efb673fe5f2b59bc383806e2efb24aa7ab6b81d1
Author: sjaakola <seppo.jaakola@iki.fi>
Date: Thu Aug 24 10:34:21 2017 +0300

MW-402 cascading FK issues

  • created tests focusing in multi-master conflicts during cascading foreign key
    processing
  • in row0upd.cc, calling wsrep_row_ups_check_foreign_constraints only when
    running in cluster
  • in row0ins.cc fixed regression from MW-369, which caused crash with MW-402.test
Generated at Thu Feb 08 08:07:28 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.