MariaDB/query performance/coreproduction
< MariaDB | query performance
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