Page MenuHomePhabricator
Create Task
Maniphest T137539

Use individual select queries to determin which Entities are in the wbc_entity_usage table
Closed, ResolvedPublic
Actions

Description

In EntityUsageTable::getUsedEntityIdStrings, we currently use a bulk query to see which Entity ID from a given set are actually present in the wbc_entity_usage table:

SELECT DISTINCT eu_entity_id  FROM `wbc_entity_usage` 
WHERE eu_entity_id IN ('Q148475', 'Q54919', 'Q423048', 'Q2494649', 'Q13219454', 'Q131454', 'Q36578', 'Q1798125', 'Q2597810', 'Q19938912', 'Q193563', 'Q2553334', 'Q477675', 'Q623578', 'Q19675', 'Q13481868', 'Q384602')

This can lead to extremely slow queries and high resource consumption in some cases, as described in T116404#2370849.

We should instead use individual selects with LIMIT 1 for each ID:

SELECT eu_entity_id FROM wbc_entity_usage WHERE eu_entity_id = 'Q623578' LIMIT 1;

@jcrespo suggest that this is the better approach in T116404#2371094.

Details

SubjectRepoBranchLines +/-
EntityUsageTable: Run all subqueries at once with UNION on MySQLmediawiki/extensions/Wikibasemaster+50 -12
Use one query per entity id in getUsedEntityIdStringsmediawiki/extensions/Wikibasemaster+21 -9
Customize query in gerrit

Related Objects
Search...

Event Timeline

daniel created this task.Jun 10 2016, 11:09 AM
Restricted Application added a subscriber: Zppix. · View Herald TranscriptJun 10 2016, 11:09 AM
daniel added a parent task: T136598: Wikidata master database connection issue.Jun 10 2016, 11:10 AM
daniel mentioned this in T136598: Wikidata master database connection issue.
jcrespo added a comment.Jun 10 2016, 11:24 AM

A slight comment here: I know the second query is way better than the the first one- there could be an even better solution; but just doing this change would be a huge improvement (at least 100x faster).

Aklapper renamed this task from Use inidividual select queries to determin which Entities are in the wbc_entity_usage table to Use individual select queries to determin which Entities are in the wbc_entity_usage table.Jun 10 2016, 11:56 AM
hoo claimed this task.Jun 10 2016, 2:36 PM
gerritbot subscribed.Jun 10 2016, 2:55 PM

Change 293736 had a related patch set uploaded (by Hoo man):
WIP: Use one (sub)query per entity id in getUsedEntityIdStrings

https://gerrit.wikimedia.org/r/293736

gerritbot added a project: Patch-For-Review.Jun 10 2016, 2:55 PM
hoo added a comment.EditedJun 10 2016, 3:03 PM

Short test:

hoo@terbium:~$ mwscript eval.php --wiki=zhwiki                                                                                                                                                                                                                         
> $idStrings = [];                                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                                                               
> for ( $i = 1; $i < 1001; $i++ ) { $idStrings[] = "Q$i"; }                                                                                                                                                                                                                    
                                                                                                                                                                                                                                                                               
> $subQueries = [];                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                               
> $connection = wfGetDB( DB_SLAVE );                                                                                                                                                                                                                                           
                                                                                                                                                                                                                                                                               
> foreach ( $idStrings as $idString ) { $subQueries[] = $connection->selectSQLText( 'wbc_entity_usage', 'eu_entity_id', [ 'eu_entity_id' => $idString ], '', [ 'LIMIT' => 1 ] ); }                                                                                             
                                                                                                                                                                                                                                                                               
> $sql = $connection->unionQueries( $subQueries, false );                                                                                                                                                                                                                      
                                                                                                                                                                                                                                                                               
> $t0 = microtime( true ); $res = $connection->query( $sql ); echo microtime( true ) - $t0;
0.10685896873474



hoo@terbium:~$ mwscript eval.php --wiki=zhwiki
> $idStrings = [];

> for ( $i = 1001; $i < 2001; $i++ ) { $idStrings[] = "Q$i"; }

> $connection = wfGetDB( DB_SLAVE );

> $t0 = microtime( true ); foreach ( $idStrings as $idString ) { $connection->select( 'wbc_entity_usage', 'eu_entity_id', [ 'eu_entity_id' => $idString ], '', [ 'LIMIT' => 1 ] ); }; echo microtime( true ) - $t0;
1.093230009079
gerritbot added a comment.Jun 10 2016, 3:52 PM

Change 293745 had a related patch set uploaded (by Hoo man):
EntityUsageTable: Run all subqueries at once with UNION on MySQL

https://gerrit.wikimedia.org/r/293745

hoo mentioned this in T116404: EntityUsageTable::getUsedEntityIdStrings query on wbc_entity_usage table is sometimes fast, sometimes slow.Jun 10 2016, 4:11 PM
gerritbot added a comment.Jun 13 2016, 3:09 PM

Change 293736 merged by jenkins-bot:
Use one query per entity id in getUsedEntityIdStrings

https://gerrit.wikimedia.org/r/293736

daniel added a comment.Jun 13 2016, 3:28 PM

Note: the naive "one query per ID" patch (293736) is merged, and should already fix the performance issue. The "use a UNION" patch (293745) is still pending comments and testing.

The second patch isn't strictly necessary, but provides another 10x speedup (for large sets of IDs), according to Marius' tests. I'd leave this task open until the second patch is either merged or abandoned.

jcrespo added a comment.Jun 13 2016, 3:29 PM

I like your approach @daniel, starting simple, then optimize with a more complex option. Thank you!

gerritbot added a comment.Jun 14 2016, 2:14 AM

Change 293745 merged by jenkins-bot:
EntityUsageTable: Run all subqueries at once with UNION on MySQL

https://gerrit.wikimedia.org/r/293745

adrianheine closed this task as Resolved.Jun 14 2016, 7:18 AM
adrianheine moved this task from Proposed to Done on the Wikidata-Sprint-2016-05-24 board.
jcrespo mentioned this in T137972: Use UNION ALL instead of UNION for bulk wbc_entity_usage queries.Jun 16 2016, 3:55 PM
jcrespo created subtask T137972: Use UNION ALL instead of UNION for bulk wbc_entity_usage queries.
hoo closed subtask T137972: Use UNION ALL instead of UNION for bulk wbc_entity_usage queries as Resolved.Oct 7 2016, 5:43 PM
Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL