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).
You can provide a raw SQL log file via --file option. Each line should consist a single SQL query.
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
- a default one will output a table with all the data above
--simplewill emit a simplified view--csvwill emit CSV-formatted statistics for further processing--data-flowwill emit TSV suitable for visualization (an example)--sql-logwill emit real queries SQL log suitable asindex-digestinput
sudo apt-get install python-pip virtualenv
virtualenv env
source env/bin/activate
make install
DEBUG=1env variable will enable debug level logging
query_digest --file=/var/log/queries.log
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
query_digest generates TSV file that can be consumed by data-flow-graph. Simply follow these three steps:
- run
query_digestwith--data-flowoption - copy generated TSV and pasted it on Gist
- visit
data-flow-graphGist 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
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
