MariaDB/query performance/coreproduction

From Wikitech
Jump to navigation Jump to search

db1018, Performance_Schema=Off

# 1094.7s user time, 9.4s system time, 141.22M rss, 205.21M vsz
# Current date: Wed Jul  1 07:32:28 2015
# Hostname: db1018
# Files: STDIN
# Overall: 4.66M total, 640 unique, 53.47 QPS, 0.02x concurrency _________
# Time range: 2015-06-30 07:00:10 to 2015-07-01 07:11:37
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          1320s     1us      3s   283us   332us     3ms   152us
# Lock time           238s       0    13ms    51us    93us    39us    52us
# Rows sent          5.02M       0   4.16k    1.13    1.96    8.69    0.99
# Rows examine       9.50M       0 232.93k    2.14    3.89  261.15    0.99
# Merge passes           0       0       0       0       0       0       0
# Query size         1.06G      17  67.89k  243.89  511.45  368.99  192.76
# Boolean:
# Filesort       8% yes,  91% no
# Full scan     94% yes,   5% no
# Priority que   3% yes,  96% no
# Tmp table     29% yes,  70% no
# Tmp table on   1% yes,  98% no

# Profile
# Rank Query ID           Response time  Calls   R/Call V/M   Item
# ==== ================== ============== ======= ====== ===== ============
#    1 0xSANITIZED        242.2765 18.4%  691005 0.0004  0.00 SELECT revision page user
#    2 0xSANITIZED        204.7052 15.5%   80863 0.0025  0.01 SELECT revision page user
#    3 0xSANITIZED        162.8476 12.3% 1025179 0.0002  0.00 SELECT page
#    4 0xSANITIZED         68.1164  5.2%   93928 0.0007  0.01 SELECT revision page user
#    5 0xSANITIZED         66.8302  5.1%  354562 0.0002  0.00 SELECT page revision
#    6 0xSANITIZED         57.0374  4.3%  211631 0.0003  0.00 SELECT page revision
#    7 0xSANITIZED         44.0751  3.3%    6925 0.0064  0.07 SELECT page categorylinks category
#    8 0xSANITIZED         35.0655  2.7%    9689 0.0036  0.00 SELECT text
#    9 0xSANITIZED         29.4363  2.2%  152259 0.0002  0.00 SELECT page
#   10 0xSANITIZED         24.1864  1.8%  176927 0.0001  0.00 SELECT msg_resource
#   11 0xSANITIZED         23.7016  1.8%  144807 0.0002  0.00 SELECT page_restrictions
#   12 0xSANITIZED         16.6547  1.3%   10135 0.0016  0.03 SELECT revision
#   13 0xSANITIZED         15.0564  1.1%  263809 0.0001  0.00 SET
#   14 0xSANITIZED         14.9844  1.1%   29634 0.0005  0.00 SELECT page page_props
#   15 0xSANITIZED         14.5375  1.1%     270 0.0538  0.31 SELECT revision
#   16 0xSANITIZED         14.0178  1.1%   47646 0.0003  0.00 SELECT page
#   17 0xSANITIZED         13.5231  1.0%   26407 0.0005  0.00 SELECT page_props
#   18 0xSANITIZED         13.4173  1.0%    1064 0.0126  0.00 SELECT templatelinks flaggedtemplates page flaggedpages
#   19 0xSANITIZED         12.8716  1.0%    8310 0.0015  0.01 SELECT flaggedrevs revision
#   20 0xSANITIZED         10.1552  0.8%    1093 0.0093  0.00 SELECT imagelinks flaggedimages page flaggedpages flaggedrevs

# Query 1: 7.93 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 1553864032
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2015-06-30 07:00:10 to 2015-07-01 07:11:37
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         14  691005
# Exec time     18    242s   163us    91ms   350us   348us   563us   301us
# Lock time     26     63s    47us     7ms    91us   103us    14us    84us
# Rows sent     12 657.18k       0       1    0.97    0.99    0.16    0.99
# Rows examine   6 657.18k       0       1    0.97    0.99    0.16    0.99
# Query size    31 345.42M     501     749  524.16  537.02    9.22  511.45
# String:
# Databases    itwiki (225976/32%), enwiktiona... (219461/31%)... 15 more
# Hosts
# Users        wikiuser
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  #
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `enwiktionary` LIKE 'revision'\G
#    SHOW CREATE TABLE `enwiktionary`.`revision`\G
#    SHOW TABLE STATUS FROM `enwiktionary` LIKE 'page'\G
#    SHOW CREATE TABLE `enwiktionary`.`page`\G
#    SHOW TABLE STATUS FROM `enwiktionary` LIKE 'user'\G
#    SHOW CREATE TABLE `enwiktionary`.`user`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* Revision::fetchFromConds SANITIZED */  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_sha1,
rev_content_format,rev_content_model,page_namespace,page_title,page_id,
page_latest,page_is_redirect,page_len,user_name  FROM `revision` 
INNER JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `user` ON 
((rev_user != 0) AND (user_id = rev_user))  WHERE page_namespace = '0' AND 
page_title = 'SANITIZED' AND (rev_id=page_latest)  LIMIT 1\G

# Query 2: 0.93 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 1291596153
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2015-06-30 07:00:16 to 2015-07-01 07:11:36
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1   80863
# Exec time     15    205s   168us   151ms     3ms     8ms     4ms   301us
# Lock time      3      7s    49us   628us    92us   108us    10us    89us
# Rows sent      1  78.97k       1       1       1       1       0       1
# Rows examine   0  78.97k       1       1       1       1       0       1
# Query size     3  36.88M     470     519  478.25  487.09    7.71  463.90
# String:
# Databases    zhwiki (16154/19%), itwiki (11523/14%)... 15 more
# Hosts
# Users        wikiuser
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  #################################
#  10ms  ###
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `eowiki` LIKE 'revision'\G
#    SHOW CREATE TABLE `eowiki`.`revision`\G
#    SHOW TABLE STATUS FROM `eowiki` LIKE 'page'\G
#    SHOW CREATE TABLE `eowiki`.`page`\G
#    SHOW TABLE STATUS FROM `eowiki` LIKE 'user'\G
#    SHOW CREATE TABLE `eowiki`.`user`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* Revision::fetchFromConds SANITIZED */  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_sha1,
rev_content_format,rev_content_model,page_namespace,page_title,page_id,
page_latest,page_is_redirect,page_len,user_name  FROM `revision` 
INNER JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `user` ON 
((rev_user != 0) AND (user_id = rev_user))  WHERE page_id = 'SANITIZED' 
AND rev_id = 'SANITIZED' LIMIT 1\G

# Query 3: 11.77 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 1176369717
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2015-06-30 07:00:10 to 2015-07-01 07:11:37
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         22 1025179
# Exec time     12    163s    78us    42ms   158us   185us   132us   152us
# Lock time     23     56s    28us     1ms    54us    63us     8us    52us
# Rows sent     18 931.89k       0       1    0.93    0.99    0.25    0.99
# Rows examine   9 931.89k       0       1    0.93    0.99    0.25    0.99
# Query size    17 192.90M     173     439  197.30  212.52   11.37  192.76
# String:
# Databases    itwiki (335043/32%), enwiktiona... (254801/24%)... 15 more
# Hosts
# Users        wikiuser (1025177/99%), wikiadmin (2/0%)
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  #
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `zhwiki` LIKE 'page'\G
#    SHOW CREATE TABLE `zhwiki`.`page`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* LinkCache::addLinkObj SANITIZED */  page_id,page_len,
page_is_redirect,page_latest,page_content_model  FROM `page`   
WHERE page_namespace = '10' AND page_title = 'SANITIZED'  LIMIT 1\G

# Query 4: 1.08 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 1126873621
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2015-06-30 07:00:11 to 2015-07-01 07:11:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2   93928
# Exec time      5     68s   159us   128ms   725us     4ms     2ms   287us
# Lock time      3      9s    51us    13ms    93us   113us    42us    84us
# Rows sent      1  91.69k       0       1    1.00    0.99    0.02    0.99
# Rows examine   0  91.69k       0       1    1.00    0.99    0.02    0.99
# Query size     3  41.25M     450     475  460.48  463.90   10.97  463.90
# String:
# Databases    itwiki (24666/26%), enwiktiona... (12838/13%)... 15 more
# Hosts
# Users        wikiuser
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ####
#  10ms  #
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `enwiktionary` LIKE 'revision'\G
#    SHOW CREATE TABLE `enwiktionary`.`revision`\G
#    SHOW TABLE STATUS FROM `enwiktionary` LIKE 'page'\G
#    SHOW CREATE TABLE `enwiktionary`.`page`\G
#    SHOW TABLE STATUS FROM `enwiktionary` LIKE 'user'\G
#    SHOW CREATE TABLE `enwiktionary`.`user`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* Revision::fetchFromConds SANITIZED */  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_sha1,
rev_content_format,rev_content_model,page_namespace,page_title,page_id,
page_latest,page_is_redirect,page_len,user_name  FROM `revision` 
INNER JOIN `page` ON ((page_id = rev_page)) LEFT JOIN `user` ON 
((rev_user != 0) AND (user_id = rev_user))  WHERE rev_id = 'SANITIZED'  
LIMIT 1\G

# Query 5: 4.07 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 1143476236
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2015-06-30 07:00:10 to 2015-07-01 07:11:37
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          7  354562
# Exec time      5     67s    90us    19ms   188us   214us    37us   185us
# Lock time      9     22s    32us     1ms    61us    69us     8us    57us
# Rows sent      5 297.51k       0       1    0.86    0.99    0.35    0.99
# Rows examine   3 297.51k       0       1    0.86    0.99    0.35    0.99
# Query size     7  82.28M     220     271  243.32  246.02    8.12  234.30
# String:
# Databases    zhwiki (176759/49%), plwiki (42221/11%)... 15 more
# Hosts
# Users        wikiuser
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  #
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `nlwiki` LIKE 'page'\G
#    SHOW CREATE TABLE `nlwiki`.`page`\G
#    SHOW TABLE STATUS FROM `nlwiki` LIKE 'revision'\G
#    SHOW CREATE TABLE `nlwiki`.`revision`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* ResourceLoaderWikiModule::getTitleInfo SANITIZED */ 
page_namespace,page_title,rev_len,rev_sha1  FROM `page` INNER JOIN 
`revision` ON ((page_latest=rev_id))  
WHERE (page_namespace = '8' AND page_title = 'SANITIZED')\G

# Query 6: 2.43 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 1800534818
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2015-06-30 07:00:10 to 2015-07-01 07:11:37
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4  211631
# Exec time      4     57s   140us    81ms   269us   316us   208us   247us
# Lock time      5     13s    35us     1ms    63us    73us     8us    60us
# Rows sent     11 566.96k       0      23    2.74    4.96    1.21    1.96
# Rows examine  11   1.11M       0      46    5.49    9.83    2.39    3.89
# Query size     5  57.16M     233     815  283.23  381.65   36.43  271.23
# String:
# Databases    zhwiki (117896/55%), ptwiki (34628/16%)... 15 more
# Hosts
# Users        wikiuser
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  #
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `zhwiki` LIKE 'page'\G
#    SHOW CREATE TABLE `zhwiki`.`page`\G
#    SHOW TABLE STATUS FROM `zhwiki` LIKE 'revision'\G
#    SHOW CREATE TABLE `zhwiki`.`revision`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* ResourceLoaderWikiModule::getTitleInfo SANITIZED */
page_namespace,page_title,rev_len,rev_sha1  FROM `page` INNER JOIN 
`revision` ON ((page_latest=rev_id))  WHERE (page_namespace = '2' AND 
page_title IN ('SANITIZED','SANITIZED','SANITIZED','SANITIZED') )\G

# Query 7: 0.08 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 1614050032
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.07
# Time range: 2015-06-30 07:00:32 to 2015-07-01 07:11:14
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    6925
# Exec time      3     44s   175us   404ms     6ms    28ms    22ms   332us
# Lock time      0   626ms    54us   246us    90us   108us    11us    84us
# Rows sent      1  53.87k       0     201    7.97   36.69   25.61       0
# Rows examine   1 114.65k       0     603   16.95   80.10   53.55       0
# Query size     0   3.11M     443     689  470.69  487.09   16.09  463.90
# String:
# Databases    zhwiki (2504/36%), ptwiki (646/9%)... 15 more
# Hosts
# Users        wikiuser
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  #############
#  10ms  ###########
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `enwiktionary` LIKE 'page'\G
#    SHOW CREATE TABLE `enwiktionary`.`page`\G
#    SHOW TABLE STATUS FROM `enwiktionary` LIKE 'categorylinks'\G
#    SHOW CREATE TABLE `enwiktionary`.`categorylinks`\G
#    SHOW TABLE STATUS FROM `enwiktionary` LIKE 'category'\G
#    SHOW CREATE TABLE `enwiktionary`.`category`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* CategoryViewer::doCategoryQuery 104.162.217.SANITIZED */  
page_id,page_title,page_namespace,page_len,page_is_redirect,cl_sortkey,
cat_id,cat_title,cat_subcats,cat_pages,cat_files,cl_sortkey_prefix,
cl_collation  FROM `page` INNER JOIN `categorylinks` 
FORCE INDEX (cl_sortkey) ON ((cl_from = page_id)) LEFT JOIN `category` 
ON ((cat_title = page_title) AND page_namespace = '14')  
WHERE cl_to = 'SANITIZED' AND cl_type = 'page'  ORDER BY cl_sortkey 
LIMIT 201\G

# Query 8: 0.11 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 691582957
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2015-06-30 07:00:29 to 2015-07-01 07:11:37
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    9689
# Exec time      2     35s    93us    67ms     4ms     9ms     4ms     3ms
# Lock time      0   517ms    30us   415us    53us    66us     9us    49us
# Rows sent      0   9.45k       0       1    1.00    0.99    0.04    0.99
# Rows examine   0   9.45k       0       1    1.00    0.99    0.04    0.99
# Query size     0   1.02M     100     128  110.91  112.70    7.13  112.70
# String:
# Databases    enwiktiona... (2983/30%), zhwiki (1359/14%)... 15 more
# Hosts
# Users        wikiuser (9514/98%), wikiadmin (175/1%)
# Query_time distribution
#   1us
#  10us  #
# 100us  ###########################################
#   1ms  ################################################################
#  10ms  ####
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `plwiki` LIKE 'text'\G
#    SHOW CREATE TABLE `plwiki`.`text`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* Revision::loadText SANITIZED */  old_text,old_flags  FROM `text`   
WHERE old_id = 'SANITIZED'  LIMIT 1\G

# Query 9: 1.75 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 333131377
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2015-06-30 07:00:10 to 2015-07-01 07:11:37
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3  152259
# Exec time      2     29s    90us    39ms   193us   214us   307us   176us
# Lock time      3      9s    32us   910us    62us    76us    11us    57us
# Rows sent      2 131.20k       0       1    0.88    0.99    0.32    0.99
# Rows examine   1 131.20k       0       1    0.88    0.99    0.32    0.99
# Query size     3  42.35M     269     524  291.69  313.99   16.20  284.79
# String:
# Databases    zhwiki (25190/16%), itwiki (24748/16%)... 15 more
# Hosts
# Users        wikiuser (152255/99%), wikiadmin (4/0%)
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  #
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `svwiki` LIKE 'page'\G
#    SHOW CREATE TABLE `svwiki`.`page`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* WikiPage::pageData SANITIZED */  page_id,page_namespace,
page_title,page_restrictions,page_is_redirect,page_is_new,page_random,
page_touched,page_links_updated,page_latest,page_len,page_content_model  
FROM `page`   WHERE page_namespace = '0' AND page_title = 'SANITIZED'  
LIMIT 1\G

# Query 10: 2.03 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 1036902626
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2015-06-30 07:00:10 to 2015-07-01 07:11:35
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3  176927
# Exec time      1     24s    75us     1ms   136us   152us    14us   131us
# Lock time      3      9s    26us   424us    50us    60us     7us    49us
# Rows sent      3 172.78k       0       1    1.00    0.99    0.00    0.99
# Rows examine   1 172.78k       0       1    1.00    0.99    0.00    0.99
# Query size     2  27.86M     144     199  165.11  166.51    3.71  158.58
# String:
# Databases    zhwiki (65181/36%), itwiki (19038/10%)... 15 more
# Hosts
# Users        wikiuser
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `zhwiki` LIKE 'msg_resource'\G
#    SHOW CREATE TABLE `zhwiki`.`msg_resource`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* MessageBlobStore::getFromDB SANITIZED */  mr_blob,mr_resource,
mr_timestamp  FROM `msg_resource`   WHERE mr_resource = 'user.tokens' AND 
mr_lang = 'zh-hk'\G

# Query 11: 1.66 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 1673196116
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2015-06-30 07:00:10 to 2015-07-01 07:11:37
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3  144807
# Exec time      1     24s    87us     1ms   163us   194us    19us   159us
# Lock time      2      7s    25us   521us    48us    57us     7us    47us
# Rows sent      0  18.93k       0       2    0.13    1.96    0.49       0
# Rows examine   0  18.93k       0       2    0.13    1.96    0.49       0
# Query size     1  18.74M     124     174  135.69  143.84    7.34  130.47
# String:
# Databases    itwiki (24835/17%), ptwiki (24187/16%)... 15 more
# Hosts
# Users        wikiuser (144804/99%), wikiadmin (3/0%)
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  #
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `ptwiki` LIKE 'page_restrictions'\G
#    SHOW CREATE TABLE `ptwiki`.`page_restrictions`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* Title::loadRestrictions SANITIZED */  pr_type,pr_expiry,pr_level,
pr_cascade  FROM `page_restrictions`   WHERE pr_page = 'SANITIZED'\G