https://bugzilla.wikimedia.org/show_bug.cgi?id=67602
Bug ID: 67602
Summary: Performance problem on database server s5 using
commonswiki
Product: Wikimedia Labs
Version: unspecified
Hardware: All
OS: All
Status: NEW
Keywords: performance
Severity: normal
Priority: Unprioritized
Component: tools
Assignee: [email protected]
Reporter: [email protected]
CC: [email protected], [email protected]
Blocks: 48930, 58791
Web browser: ---
Mobile Platform: ---
Some queries are currently not runnable on s5.labsdb because of performance
problems. Probably this is caused by federated tables.
Either these queries have not finished after hours and i kill them or i am
getting errors caused by out of memory (lost connection, Table './mysql/proc'
is marked as crashed)
I have many many complex queries in my scripts using commonswiki.
Here is one example query my bot runs every four hours:
It returns a list of files requested for deletions on commons with a link to
deletion talk page section which are used on dewiki presentation namespace and
the deletion request page was modified within the last two weeks by a human.
(the "used on dewiki" is not 100% correct because of performance reason i am
using the big globalimagelinks in a second query, but that shouldn't matter
here - only if sb. wants to copy the query for his own tools)
----------
on WMDE toolserver
SELECT 'commonswiki', cp.page_id,
CONCAT('[[:Datei:',REPLACE(cp.page_title,'_',' '),']]<small>
[[:commons:Commons:Deletion
requests/',REPLACE(SUBSTRING(cl_sortkey_prefix,LOCATE('#',cl_sortkey_prefix)+1),'_','
'),'|CDR]]</small>')
FROM commonswiki_p.templatelinks
INNER JOIN commonswiki_p.page cp ON tl_from = cp.page_id
INNER JOIN commonswiki_p.recentchanges ON rc_cur_id = cp.page_id AND
rc_bot=0
INNER JOIN dewiki_p.imagelinks ON il_to = cp.page_title
INNER JOIN dewiki_p.page dp ON il_from = dp.page_id
INNER JOIN commonswiki_p.categorylinks ON cp.page_id=cl_from AND cl_to LIKE
'Deletion_requests_%'
WHERE tl_namespace=10 AND tl_title='Delete' AND cp.page_namespace=6
AND DATEDIFF(NOW(), rc_timestamp) < 14
AND dp.page_namespace IN (0,6,10,14,100) AND cl_sortkey_prefix LIKE '%#%'
GROUP BY cp.page_id
ORDER BY NULL;
Result ON TS: 85 rows in set (0.25 sec)
----------
Query for wmf Labs:
SELECT 'commonswiki', cp.page_id,
CONCAT('[[:Datei:',REPLACE(cp.page_title,'_',' '),']]<small>
[[:commons:Commons:Deletion
requests/',REPLACE(SUBSTRING(cl_sortkey_prefix,LOCATE('#',cl_sortkey_prefix)+1),'_','
'),'|CDR]]</small>')
FROM commonswiki_f_p.templatelinks
INNER JOIN commonswiki_f_p.page cp ON tl_from = cp.page_id
INNER JOIN commonswiki_f_p.recentchanges ON rc_cur_id = cp.page_id AND
rc_bot=0
INNER JOIN dewiki_p.imagelinks ON il_to = cp.page_title
INNER JOIN dewiki_p.page dp ON il_from = dp.page_id
INNER JOIN commonswiki_f_p.categorylinks ON cp.page_id=cl_from AND cl_to
LIKE 'Deletion_requests_%'
WHERE tl_namespace=10 AND tl_title='Delete' AND cp.page_namespace=6
AND DATEDIFF(NOW(), rc_timestamp) < 14
AND dp.page_namespace IN (0,6,10,14,100) AND cl_sortkey_prefix LIKE '%#%'
GROUP BY cp.page_id
ORDER BY NULL;
aborted by me after 3 hours.
Please optimize database performance for queries joining with commonswiki.
--
You are receiving this mail because:
You are on the CC list for the bug.
_______________________________________________
Wikibugs-l mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
