Probably related, queries like this are causing thousands of errors a day:

Hits 	Tmax 	Tavg 	Tsum 	Hosts 	Users 	Schemas
11910	33	4	52,440	db1087	wikiuser	wikidatawiki
SELECT /* Wikibase\Lib\Store\Sql\SqlEntityInfoBuilder::collectTermsForEntities */ term_entity_type, term_entity_id, term_type, term_language, term_text FROM `wb_terms` WHERE term_entity_type = 'property' AND term_entity_id IN ('21', '143', '244', '214', '227', '31', '569', '268', '27', '106', '1440', '1006', '248', '813', '854', '19', '735', '1087', '585', '641', '1344', '54', '580', '582', '1665', '1412', '2962', '18') AND term_type IN ('label', 'description') AND term_language = 'en' /* ba7efbdd5ad83b5cce3089a4db829fe1 db1087 wikidatawiki 4s */

Blocking all main s5 servers.

It is clear to me that the direct cause for connection issues is pilups coming from queries such as:

      Id: 6451784424
    User: wikiuser
    Host: 10.64.32.35:38688
      db: wikidatawiki
 Command: Query
    Time: 18
   State: Sending data
    Info: SELECT /* Wikibase\Lib\Store\Sql\SqlEntityInfoBuilder::collectTermsForEntities  */  term_entity_type,term_entity_id,term_type,term_language,term_text  FROM `wb_terms`    WHERE term_entity_type = 'property' AND term_entity_id IN ('373','374','248','143','17','131','582','580','625','813','31','646','577','1566','18','214','268','856','2046','94','281','242','47','1082','585')  AND term_type IN ('label','description')  AND term_language = 'en'
Progress: 0.000

That take over the available connections, as they can take 10-30 seconds to run.

We will disable these Jobs for (most of) Wikidata with T126977. I'll try to get this done and deployed ASAP.

Mentioned in SAL (#wikimedia-operations) [2016-10-10T12:06:49Z] <hoo@tin> Synchronized php-1.28.0-wmf.21/extensions/Wikidata: Update Wikibase, add EntityHandler::supportsCategories (T147748) (duration: 02m 25s)

The change has been deployed and the number of Jobs scheduled on Wikidata should drop significantly.

It will take some time before the existing queue has dried off, though. You might want to lower the priority to HIGH.

Thank you for the quick response, seeing the effect already, I am more than ok with putting this as high or normal (sorry, I didn't see it soon).

Is the slow Wikibase\Lib\Store\Sql\SqlEntityInfoBuilder::collectTermsForEntities related to the job or is it a separate issue?

In T147748#2704018, @jcrespo wrote:

Is the slow Wikibase\Lib\Store\Sql\SqlEntityInfoBuilder::collectTermsForEntities related to the job or is it a separate issue?

Separate issue… I already looked into this briefly before and think I have a potential fix. Will probably pick this up tomorrow (it's high on my to do list).

I've benchmarked the above query very briefly (picked db1092 for that, all queries were run five times in a row(!)).

Original query

SELECT /* Wikibase\Lib\Store\Sql\SqlEntityInfoBuilder::collectTermsForEntities  */  term_entity_type,term_entity_id,term_type,term_language,term_text  FROM `wb_terms`    WHERE term_entity_type = 'property' AND term_entity_id IN ('373','374','248','143','17','131','582','580','625','813','31','646','577','1566','18','214','268','856','2046','94','281','242','47','1082','585')  AND term_type IN ('label','description')  AND term_language = 'en';

5.324s on average

One query per term_type (above query but term_type = 'description'/ term_type = 'label' rather than the term_type IN ('label','description')):
0.028s on average (for both queries combined).

I conclude that we should run one query per term type here and simply merge the result in the application code.

Change 315616 had a related patch set uploaded (by Hoo man):
Use one query per term type in SqlEntityInfoBuilder::collectTermsForEntities

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

I conclude that we should run one query per term type here and simply merge the result in the application code.

Yes, this is a property of Btree indexes- 2 ranges cannot be handled efficiently- you can only handle one with an index at a time. You could even merge both into UNION ALLs, but again, as usual, I suggest going for the largest gain ASAP.

In T147748#2712102, @jcrespo wrote:

You could even merge both into UNION ALLs, but again, as usual, I suggest going for the largest gain ASAP.

Sadly this is still a major headache in tests because we use temporary tables there… which makes MySQL sad.

Change 315616 merged by jenkins-bot:
Use one query per term type in SqlEntityInfoBuilder::collectTermsForEntities

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