reedy / query-digest

A dynamic code analysis tool that processes SQL queries logs

query-digest

This is a dynamic code analysis tool that processes last hour of SQL queries logs generated by MediaWiki and Perl backend scripts in SJC prod environment, SQL queries made by a given Pandora service and reports those made by given feature (use --path option) or involving given table (use --table option).

It then reports the following:

• the source host type (web, offline)
• normalized query
• method that called it
• median query time
• no of query calls
• sum of query times
• median of rows returned

Output modes

• a default one will output a table with all the data above
• --simple will emit a simplified view
• --csv will emit CSV-formatted statistics for further processing
• --data-flow will emit TSV suitable for visualization (an example)
• --sql-log will emit real queries SQL log suitable as index-digest input

Install

make install

Run it

DEBUG=1 env variable will enable debug level logging

source env/bin/activate
query-digest --path="/extensions/wikia/Wall"
query-digest --path="/extensions/wikia/Wall" --csv

query_digest --table=wall_notification
query_digest --table=wall_notification --csv

query_digest --table=image_review --data-flow

query_digest --service=content-entity-worker
query_digest --service=content-entity-worker --csv

query_digest --database=statsdb --sql-log

Visualizing the data flow

query-digest generates TSV file that can be consumed by data-flow-graph. Simply follow these three steps:

• run query_digest with --data-flow option
• copy generated TSV and pasted it on Gist
• visit data-flow-graph Gist viever and paste the Gist URL

Please note that TSV rows can be combined - i.e. you can have a graph of data-flow of two different features, services, databases on a single screen.

Here's an example with data flow around backend tables

Example output

2017-02-03 14:31:01 kibana                              INFO     500000 rows returned in 4599 ms
2017-02-03 14:31:56 query_digest                        INFO     Processing 500000 queries...
2017-02-03 14:31:58 query_digest                        INFO     Got 38 kinds of queries
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| query                                                                                                                                                                                                                                                                                                                                                                     | method                                                                                       | dbname     | source_host   |   count |      time_sum |   time_median |   rows_sum |   rows_median |
+===========================================================================================================================================================================================================================================================================================================================================================================+==============================================================================================+============+===============+=========+===============+===============+============+===============+
| SELECT * FROM `comments_index` WHERE comment_id = X LIMIT N                                                                                                                                                                                                                                                                                                               | CommentsIndex::selectRow                                                                     | local      | ap            |  322667 | 145401        |      0.334978 |     322582 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT min(rev_id) FROM `revision` WHERE rev_page = X LIMIT N                                                                                                                                                                                                                                                                                                             | ArticleComment::getFirstRevID                                                                | local      | ap            |   70004 |  29422.8      |      0.265837 |      70004 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT * FROM `page` WHERE page_id = X LIMIT N                                                                                                                                                                                                                                                                                                                            | Title::newFromID                                                                             | local      | ap            |   22152 |   9650.71     |      0.309944 |      22047 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT N FROM `wall_related_pages` LIMIT N                                                                                                                                                                                                                                                                                                                                | DatabaseBase::tableExists                                                                    | local      | ap            |    9647 |   5778.94     |      0.279188 |       8193 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| COMMIT                                                                                                                                                                                                                                                                                                                                                                    | ExternalUser_Wikia::linkToLocal                                                              | local      | ap            |   15280 |   5283.3      |      0.228882 |       7379 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT * FROM `user_properties` WHERE up_user = X                                                                                                                                                                                                                                                                                                                         | User::loadOptions                                                                            | wikicities | ap            |    8009 |   3669.72     |      0.306845 |     176211 |            16 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT props FROM `page_wikia_props` WHERE page_id = X AND propname = X                                                                                                                                                                                                                                                                                                   | wfGetWikiaPageProp                                                                           | local      | ap            |    6535 |   3654.12     |      0.335932 |       2266 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT ug_group FROM `user_groups` WHERE ug_user = X                                                                                                                                                                                                                                                                                                                      | Wikia\Service\User\Permissions\PermissionsServiceImpl::loadLocalGroups                       | local      | ap            |    8399 |   3458.95     |      0.266075 |       2989 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT * FROM `wikicities_cN`.`user` WHERE user_id = X LIMIT N                                                                                                                                                                                                                                                                                                            | User::loadFromDatabase                                                                       | local      | ap            |    7642 |   3306.17     |      0.294209 |       7639 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT * FROM `user` WHERE user_id = X LIMIT N                                                                                                                                                                                                                                                                                                                            | ExternalUser_Wikia::initFromCond                                                             | wikicities | ap            |    5770 |   2266.82     |      0.256062 |       5767 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT blob_text FROM `blobs` WHERE blob_id = X LIMIT N                                                                                                                                                                                                                                                                                                                   | ExternalStoreDB::fetchBlob                                                                   | blobs20141 | ap            |    2378 |   1916.49     |      0.611544 |       2378 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (User::loadFromDatabase)                                                 | local      | ap            |    5857 |   1662.11     |      0.170946 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT old_text,old_flags FROM `text` WHERE old_id = X LIMIT N                                                                                                                                                                                                                                                                                                            | Revision::loadText                                                                           | local      | ap            |    2290 |   1361.15     |      0.338078 |       2289 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (DatabaseBase::tableExists)                                              | local      | ap            |    6000 |   1259.57     |      0.144005 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| Important table write - UPDATE `page` SET page_touched = X WHERE page_id = X                                                                                                                                                                                                                                                                                              | Title::invalidateCache                                                                       | local      | ap            |    1826 |    800.683    |      0.300884 |       1825 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (Wikia\Service\User\Permissions\PermissionsServiceImpl::loadLocalGroups) | local      | ap            |    2435 |    684.73     |      0.174999 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT user_editcount FROM `wikicities_cN`.`user` WHERE user_id = X LIMIT N                                                                                                                                                                                                                                                                                               | User::edits                                                                                  | local      | ap            |    1598 |    562.134    |      0.234485 |       1597 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT user_name FROM `wikicities_cN`.`user` WHERE user_id = X LIMIT N                                                                                                                                                                                                                                                                                                    | User::whoIs                                                                                  | local      | ap            |     834 |    307.828    |      0.249505 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| INSERT INTO `wall_history` (parent_page_id,post_user_id,post_ns,post_user_ip,is_reply,comment_id,parent_comment_id,metatitle,reason,action,revision_id) VALUES (X,X,X,NULL,'XNXNXFun facts #NXThread movedXNXN')                                                                                                                                                          | DatabaseBase::insert                                                                         | local      | ap            |     236 |    258.164    |      0.57745  |        236 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| COMMIT                                                                                                                                                                                                                                                                                                                                                                    | WikiPage::doPurge                                                                            | local      | ap            |      21 |     30.3991   |      1.08814  |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (Title::newFromID)                                                       | local      | ap            |     101 |     27.3879   |      0.123978 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT page_id, count(*) as cnt FROM `wall_related_pages` WHERE comment_id = X GROUP BY page_id ORDER BY order_index                                                                                                                                                                                                                                                      | WallRelatedPages::getMessagesRelatedArticleIds                                               | local      | ap            |      59 |     23.833    |      0.316143 |         17 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT * FROM `comments_index` WHERE comment_id = X LIMIT N                                                                                                                                                                                                                                                                                                               | CommentsIndex::selectRow                                                                     | local      | task          |      70 |     23.2508   |      0.306487 |         39 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT page_id,page_len,page_is_redirect,page_latest FROM `page` WHERE page_namespace = X AND page_title = X LIMIT N                                                                                                                                                                                                                                                      | LinkCache::addLinkObj                                                                        | local      | ap            |      41 |     19.6617   |      0.247955 |         41 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (DatabaseBase::insert)                                                   | local      | ap            |      53 |     11.0331   |      0.135899 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (CommentsIndex::selectRow)                                               | local      | ap            |      24 |     11.0061   |      0.157952 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT * FROM `page_restrictions` WHERE pr_page = X                                                                                                                                                                                                                                                                                                                       | Title::loadRestrictions                                                                      | local      | ap            |      23 |      8.36468  |      0.31805  |         20 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT rev_id,rev_page,rev_text_id,rev_timestamp,rev_comment,rev_user_text,rev_user,rev_minor_edit,rev_deleted,rev_len,rev_parent_id,rev_shaN,page_namespace,page_title,page_id,page_latest,user_name FROM `revision` INNER JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `wikicities_cN`.`user` ON ((rev_user != N) AND (user_id = rev_user)) WHERE rev_id = X LIMIT N | Revision::fetchFromConds                                                                     | local      | ap            |       8 |      7.44081  |      0.385523 |          7 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT distinct comment_id FROM comments_index WHERE parent_comment_id = X AND comment_id > X ORDER BY comment_id LIMIT N                                                                                                                                                                                                                                                 | WallThread:getReplyIdsFromDB                                                                 | local      | ap            |       3 |      3.60012  |      0.788927 |         77 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (User::edits)                                                            | local      | ap            |      10 |      2.00319  |      0.215054 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (Title::invalidateCache)                                                 | local      | ap            |       8 |      1.82891  |      0.247955 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SHOW SLAVE STATUS                                                                                                                                                                                                                                                                                                                                                         | DatabaseMysqlBase::getLagFromSlaveStatus                                                     | blobs20141 | ap            |       5 |      1.31226  |      0.200987 |          5 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT * FROM `page` WHERE page_id = X LIMIT N                                                                                                                                                                                                                                                                                                                            | Title::newFromID                                                                             | local      | task          |       4 |      1.11771  |      0.27144  |          4 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT * FROM `ipblocks` WHERE ipb_address = X                                                                                                                                                                                                                                                                                                                            | Block::newLoad                                                                               | local      | ap            |       2 |      0.793219 |      0.396609 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| SELECT page_id,page_namespace,page_title,page_restrictions,page_counter,page_is_redirect,page_is_new,page_random,page_touched,page_latest,page_len FROM `page` WHERE page_id = X LIMIT N                                                                                                                                                                                  | WikiPage::newFromID                                                                          | local      | ap            |       2 |      0.625134 |      0.312567 |          2 |             1 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (ExternalStoreDB::fetchBlob)                                             | blobs20132 | ap            |       3 |      0.608921 |      0.202179 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (ArticleComment::getFirstRevID)                                          | local      | ap            |       2 |      0.452995 |      0.226498 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
| BEGIN                                                                                                                                                                                                                                                                                                                                                                     | DatabaseBase::query (ExternalUser_Wikia::initFromCond)                                       | wikicities | ap            |       2 |      0.236034 |      0.118017 |          0 |             0 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------------------------------------------------------------------------+------------+---------------+---------+---------------+---------------+------------+---------------+
Note: times are in [ms], queries are normalized