DELETION queries which are joining with replication databases for selection are really slow on mariadb 10.
example test Query:
CREATE TEMPORARY TABLE IF NOT EXISTS CCats (pid INT, cat VARCHAR(255), PRIMARY KEY (pid, cat), INDEX(cat)) ENGINE=Aria PAGE_CHECKSUM=0 TRANSACTIONAL=0;
INSERT IGNORE INTO CCats (pid, cat)
SELECT ip.page_id, cl_to
FROM commonswiki_p.page fp INNER JOIN commonswiki_p.templatelinks ON fp.page_id=tl_from INNER JOIN commonswiki_p.image ON TRIM(LEADING 'Featured_picture_candidates/File:' FROM tl_title) = img_name INNER JOIN commonswiki_p.page ip ON img_name = ip.page_title AND ip.page_namespace=6 INNER JOIN commonswiki_p.categorylinks ON ip.page_id=cl_from WHERE fp.page_namespace=4 AND fp.page_title = 'Featured_picture_candidates' AND tl_namespace=4 AND tl_title LIKE 'Featured_picture_candidates/File:%';
DELETE cc
FROM CCats cc
INNER JOIN commonswiki_p.page ON page_namespace=14 AND page_title = cat INNER JOIN commonswiki_p.categorylinks ON cl_from = page_id
WHERE cl_to='Hidden_categories' AND page_namespace=14;
Execution time of this small query is about 30 seconds on labs, 0.32 seconds on old toolserver.
I already talked with sean about this problem on irc. There might be a patch available on september.
A more general tokudb bug is reported at https://github.com/Tokutek/tokudb-engine/issues/75 . Different to tokudb bug in my case toku db is only used for selection and no deletion is performed on these tables.
Version: unspecified
Severity: major
URL: https://github.com/Tokutek/tokudb-engine/issues/75