[Wikidata-bugs] [Maniphest] T274413: Retrieve metric "% of API calls that are cached"

[Silvan_WMDE]

Silvan_WMDE added a comment.


  The cache hit ratio for API requests can be queried together with those for 
the entity pages (T274414 <https://phabricator.wikimedia.org/T274414>) and the 
WDQS requests (T274415 <https://phabricator.wikimedia.org/T274415>) by defining 
three separate "categories" like this:
  
    SELECT
     COUNT(*) as count,
     CASE WHEN is_pageview = 1 THEN 'entity_page'
     ELSE
      CASE WHEN uri_path LIKE '/w/api.php%' THEN 'api'
      ELSE CASE WHEN uri_host = 'query.wikidata.org' AND uri_path LIKE 
'%/sparql' THEN 'wdqs'
           ELSE 'other'
           END
      END
     END AS request_category,
     CASE WHEN cache_status IN ('pass','miss') THEN 0 ELSE 1 END AS is_cached
    FROM
      wmf.webrequest
    WHERE
      uri_host IN ('www.wikidata.org', 'query.wikidata.org')
      AND (is_pageview = TRUE OR uri_path LIKE '/w/api.php%' OR uri_path LIKE 
'%/sparql')
      AND year = 2021
      AND month = 5
      AND day = 6
      AND hour = 8
    GROUP BY
     (CASE WHEN is_pageview = 1 THEN 'entity_page'
     ELSE
      CASE WHEN uri_path LIKE '/w/api.php%' THEN 'api'
      ELSE CASE WHEN uri_host = 'query.wikidata.org' AND uri_path LIKE 
'%/sparql' THEN 'wdqs'
           ELSE 'other'
           END
      END
     END),
      (CASE WHEN cache_status IN ('pass','miss') THEN 0 ELSE 1 END)
    ORDER BY request_category, is_cached
    LIMIT 25;

TASK DETAIL
  https://phabricator.wikimedia.org/T274413

EMAIL PREFERENCES
  https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: Silvan_WMDE
Cc: Silvan_WMDE, Aklapper, darthmon_wmde, Invadibot, maantietaja, Akuckartz, 
Nandana, Lahi, Gq86, GoranSMilovanovic, QZanden, LawExplorer, Manuel, _jensen, 
rosalieper, Scott_WUaS, Wikidata-bugs, aude, Mbch331

_______________________________________________
Wikidata-bugs mailing list -- wikidata-bugs@lists.wikimedia.org
To unsubscribe send an email to wikidata-bugs-le...@lists.wikimedia.org