MariaDB/query performance/parsercache
< MariaDB | query performance
(Redirected from MariaDB/parsercache)What does run on pc10[123] hosts and how fast?
on 5.5.34-MariaDB-1~precise-log
# 21.8s user time, 60ms system time, 39.32M rss, 111.25M vsz
# Current date: Mon May 25 17:20:34 2015
# Hostname: pc1001
# Files: STDIN
# Overall: 84.49k total, 8 unique, 33.57 QPS, 0.01x concurrency __________
# Time range: 2015-05-25 16:30:29 to 17:12:26
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 15s 1us 160ms 176us 881us 934us 19us
# Lock time 2s 0 29ms 26us 89us 117us 0
# Rows sent 15.25k 0 1 0.18 0.99 0.39 0
# Rows examine 16.69k 0 1 0.20 0.99 0.40 0
# Query size 52.01M 17 2.11M 645.39 1.26k 8.26k 31.70
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
# 1 0xSANITIZED 6.9244 46.5% 8785 0.0008 0.01 REPLACE pc?
# 2 0xSANITIZED 6.9232 46.5% 19109 0.0004 0.00 SELECT pc?
# 3 0xSANITIZED 0.3922 2.6% 18251 0.0000 0.00 SET
# 4 0xSANITIZED 0.3775 2.5% 1474 0.0003 0.00 DELETE pc?
# 5 0xSANITIZED 0.2530 1.7% 18234 0.0000 0.00 ADMIN INIT DB
# 6 0xSANITIZED 0.0264 0.2% 18633 0.0000 0.00 ADMIN QUIT
# 7 0xSANITIZED 0.0003 0.0% 5 0.0001 0.00 SHOW SLAVE STATUS
# 8 0xSANITIZED 0.0001 0.0% 3 0.0000 0.00 SHOW MASTER STATUS
# Query 1: 3.49 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 24160371
# Scores: V/M = 0.01
# Time range: 2015-05-25 16:30:30 to 17:12:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 10 8785
# Exec time 46 7s 96us 160ms 788us 3ms 3ms 247us
# Lock time 48 1s 25us 29ms 123us 316us 338us 69us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 91 47.62M 203 2.11M 5.55k 20.37k 25.10k 346.17
# String:
# Databases parsercache
# Hosts
# Users wikiuser
# Query_time distribution
# 1us
# 10us #
# 100us ################################################################
# 1ms ##################
# 10ms #
# 100ms #
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `parsercache` LIKE 'pc082'\G
# SHOW CREATE TABLE `parsercache`.`pc082`\G
REPLACE /* SqlBagOStuff::set SANITIZED */ INTO `pc082` (keyname,value,exptime) VALUES ('wikidatawiki:pcache:idhash:SANTITIZED-0!*!*!*!en!*!wb1','<binary blob>','20150624170018')\G
# Query 2: 7.59 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 36678172
# Scores: V/M = 0.00
# Time range: 2015-05-25 16:30:29 to 17:12:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 22 19109
# Exec time 46 7s 65us 26ms 362us 1ms 625us 144us
# Lock time 49 1s 21us 5ms 57us 80us 40us 57us
# Rows sent 100 15.25k 0 1 0.82 0.99 0.38 0.99
# Rows examine 91 15.25k 0 1 0.82 0.99 0.38 0.99
# Query size 4 2.60M 119 169 142.63 158.58 8.16 136.99
# String:
# Databases parsercache
# Hosts
# Users wikiuser
# Query_time distribution
# 1us
# 10us ###
# 100us ################################################################
# 1ms #####
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `parsercache` LIKE 'pc252'\G
# SHOW CREATE TABLE `parsercache`.`pc252`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* SqlBagOStuff::getMulti SANITIZED */ keyname,value,exptime FROM `pc252` WHERE keyname = 'commonswiki:pcache:idhash:SANTITIZED-0!*!0!!en!4!*'\G
# Query 3: 7.25 QPS, 0.00x concurrency, ID 0xSANTITIZED at byte 23685892
# Scores: V/M = 0.00
# Time range: 2015-05-25 16:30:29 to 17:12:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 21 18251
# Exec time 2 392ms 11us 172us 21us 23us 6us 20us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 1 588.17k 33 33 33 33 0 33
# String:
# Databases parsercache
# Hosts
# Users wikiuser
# Query_time distribution
# 1us
# 10us ################################################################
# 100us #
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
SET group_concat_max_len = 262144\G
# Query 4: 0.59 QPS, 0.00x concurrency, ID 0xD2F2BBE48936DBA3 at byte 528518
# Scores: V/M = 0.00
# Time range: 2015-05-25 16:30:39 to 17:12:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 1474
# Exec time 2 378ms 128us 12ms 256us 260us 638us 176us
# Lock time 2 53ms 21us 184us 36us 44us 10us 33us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 8 1.44k 1 1 1 1 0 1
# Query size 0 218.65k 134 178 151.90 166.51 8.70 143.84
# String:
# Databases parsercache
# Hosts
# Users wikiuser
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms #
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `parsercache` LIKE 'pc005'\G
# SHOW CREATE TABLE `parsercache`.`pc005`\G
DELETE /* SqlBagOStuff::getMulti SANTITIZED:SANITIZED:... */ FROM `pc005` WHERE keyname = 'commonswiki:pcache:idhash:SANITIZED-1!*!0!*!ca!4!*' AND exptime = '2015-05-24 02:24:27'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select * from `pc005` WHERE keyname = 'commonswiki:pcache:idhash:SANITIZED-1!*!0!*!ca!4!*' AND exptime = '2015-05-24 02:24:27'\G
# Query 5: 7.24 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 57432162
# Scores: V/M = 0.00
# Time range: 2015-05-25 16:30:29 to 17:12:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 21 18234
# Exec time 1 253ms 6us 161us 13us 16us 5us 12us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 1 534.20k 30 30 30 30 0 30
# String:
# Databases parsercache
# Hosts
# Users wikiuser
# Query_time distribution
# 1us #
# 10us ################################################################
# 100us #
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
administrator command: Init DB\G
# Query 6: 7.41 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 47451807
# Scores: V/M = 0.00
# Time range: 2015-05-25 16:30:30 to 17:12:26
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 22 18633
# Exec time 0 26ms 1us 61us 1us 1us 1us 1us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 491.30k 27 27 27 27 0 27
# String:
# Databases parsercach... (18624/99%), informatio... (9/0%)
# Hosts
# Users wikiuser (18624/99%), watchdog (9/0%)
# Query_time distribution
# 1us ################################################################
# 10us #
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
administrator command: Quit\G
# Query 7: 0.01 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 67843223
# Scores: V/M = 0.00
# Time range: 2015-05-25 17:00:55 to 17:11:45
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 5
# Exec time 0 271us 49us 57us 54us 54us 3us 54us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 85 17 17 17 17 0 17
# String:
# Databases information_schema
# Hosts
# Users watchdog
# Query_time distribution
# 1us
# 10us ################################################################
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
SHOW SLAVE STATUS\G
# Query 8: 0.01 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 48351508
# Scores: V/M = 0.00
# Time range: 2015-05-25 17:06:55 to 17:11:15
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 3
# Exec time 0 74us 23us 27us 24us 26us 1us 23us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 54 18 18 18 18 0 18
# String:
# Databases information_schema
# Hosts
# Users watchdog
# Query_time distribution
# 1us
# 10us ################################################################
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
SHOW MASTER STATUS\G
on 10.0.16-MariaDB-log
# 9.2s user time, 80ms system time, 29.29M rss, 87.27M vsz
# Current date: Thu May 28 09:23:17 2015
# Hostname: pc1001
# Files: MariaDB10-pc1001-slow.log
# Overall: 38.55k total, 8 unique, 34.83 QPS, 0.01x concurrency __________
# Time range: 2015-05-28 09:01:52 to 09:20:19
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 6s 1us 27ms 153us 839us 427us 20us
# Lock time 946ms 0 4ms 24us 84us 54us 0
# Rows sent 6.10k 0 1 0.16 0.99 0.37 0
# Rows examine 6.85k 0 1 0.18 0.99 0.38 0
# Query size 22.35M 17 285.14k 607.97 2.89k 3.95k 31.70
# Profile
# Rank Query ID Response time Calls R/Call V/M Item
# ==== ================== ============= ===== ====== ===== ===============
# 1 0xSANITIZED 2.9294 49.6% 4968 0.0006 0.00 REPLACE pc?
# 2 0xSANITIZED 2.4872 42.1% 7631 0.0003 0.00 SELECT pc?
# 3 0xSANITIZED 0.2168 3.7% 8275 0.0000 0.00 SET
# 4 0xSANITIZED 0.1566 2.6% 770 0.0002 0.00 DELETE pc?
# 5 0xSANITIZED 0.1041 1.8% 8449 0.0000 0.00 ADMIN INIT DB
# 6 0xSANITIZED 0.0136 0.2% 8453 0.0000 0.00 ADMIN QUIT
# 7 0xSANITIZED 0.0002 0.0% 3 0.0001 0.00 SHOW SLAVE STATUS
# 8 0xSANITIZED 0.0001 0.0% 3 0.0000 0.00 SHOW MASTER STATUS
# Query 1: 4.50 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 617951
# Scores: V/M = 0.00
# Time range: 2015-05-28 09:01:53 to 09:20:18
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 12 4968
# Exec time 49 3s 89us 27ms 589us 2ms 895us 247us
# Lock time 50 477ms 29us 4ms 96us 214us 114us 69us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 91 20.49M 196 285.14k 4.22k 15.20k 10.32k 346.17
# String:
# Databases parsercache
# Hosts
# Users wikiuser
# Query_time distribution
# 1us
# 10us #
# 100us ################################################################
# 1ms #############
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `parsercache` LIKE 'pc149'\G
# SHOW CREATE TABLE `parsercache`.`pc149`\G
REPLACE /* SqlBagOStuff::set SANITIZED */ INTO `pc149` (keyname,value,exptime) VALUES ('enwiki:pcache:idhash:SANITIZED-0!*!0!!en!4!*','<binaryblob>','20150529090238')\G
# Query 2: 6.89 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 13410299
# Scores: V/M = 0.00
# Time range: 2015-05-28 09:01:52 to 09:20:19
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 19 7631
# Exec time 42 2s 66us 14ms 325us 1ms 438us 159us
# Lock time 46 438ms 26us 1ms 57us 76us 20us 49us
# Rows sent 100 6.10k 0 1 0.82 0.99 0.38 0.99
# Rows examine 89 6.10k 0 1 0.82 0.99 0.38 0.99
# Query size 4 1.03M 119 168 141.84 158.58 8.42 136.99
# String:
# Databases parsercache
# Hosts
# Users wikiuser
# Query_time distribution
# 1us
# 10us #####
# 100us ################################################################
# 1ms ####
# 10ms #
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `parsercache` LIKE 'pc073'\G
# SHOW CREATE TABLE `parsercache`.`pc073`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT /* SqlBagOStuff::getMulti SANITIZED */ keyname,value,exptime FROM `pc073` WHERE keyname = 'wikidatawiki:pcache:idhash:SANITIZED-0!*!*!*!en!*!wb1'\G
# Query 3: 7.48 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 14306311
# Scores: V/M = 0.00
# Time range: 2015-05-28 09:01:52 to 09:20:19
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 21 8275
# Exec time 3 217ms 15us 294us 26us 31us 7us 26us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 1 266.67k 33 33 33 33 0 33
# String:
# Databases parsercache
# Hosts
# Users wikiuser
# Query_time distribution
# 1us
# 10us ################################################################
# 100us #
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
SET group_concat_max_len = 262144\G
# Query 4: 0.70 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 9193033
# Scores: V/M = 0.00
# Time range: 2015-05-28 09:01:54 to 09:20:17
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 1 770
# Exec time 2 157ms 133us 2ms 203us 260us 81us 194us
# Lock time 3 31ms 27us 107us 39us 52us 9us 38us
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 10 770 1 1 1 1 0 1
# Query size 0 114.54k 134 177 152.32 166.51 8.22 143.84
# String:
# Databases parsercache
# Hosts
# Users wikiuser
# Query_time distribution
# 1us
# 10us
# 100us ################################################################
# 1ms #
# 10ms
# 100ms
# 1s
# 10s+
# Tables
# SHOW TABLE STATUS FROM `parsercache` LIKE 'pc003'\G
# SHOW CREATE TABLE `parsercache`.`pc003`\G
DELETE /* SqlBagOStuff::getMulti SANITIZED */ FROM `pc003` WHERE keyname = 'bewiki:pcache:idoptions:SANITIZED' AND exptime = '2015-05-26 06:02:26'\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select * from `pc003` WHERE keyname = 'bewiki:pcache:idoptions:SANITIZED' AND exptime = '2015-05-26 06:02:26'\G
# Query 5: 7.63 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 2610397
# Scores: V/M = 0.00
# Time range: 2015-05-28 09:01:52 to 09:20:19
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 21 8449
# Exec time 1 104ms 6us 243us 12us 16us 4us 11us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 1 247.53k 30 30 30 30 0 30
# String:
# Databases parsercache
# Hosts
# Users wikiuser
# Query_time distribution
# 1us ##################
# 10us ################################################################
# 100us #
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
administrator command: Init DB\G
# Query 6: 7.64 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 31437507
# Scores: V/M = 0.00
# Time range: 2015-05-28 09:01:52 to 09:20:19
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 21 8453
# Exec time 0 14ms 1us 22us 1us 1us 0 1us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 222.88k 27 27 27 27 0 27
# String:
# Databases parsercach... (8444/99%), informatio... (9/0%)
# Hosts
# Users wikiuser (8444/99%), watchdog (9/0%)
# Query_time distribution
# 1us ################################################################
# 10us #
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
administrator command: Quit\G
# Query 7: 0.00 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 7336245
# Scores: V/M = 0.00
# Time range: 2015-05-28 09:03:35 to 09:19:25
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 3
# Exec time 0 162us 45us 70us 54us 69us 11us 44us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 51 17 17 17 17 0 17
# String:
# Databases information_schema
# Hosts
# Users watchdog
# Query_time distribution
# 1us
# 10us ################################################################
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
SHOW SLAVE STATUS\G
# Query 8: 0.00 QPS, 0.00x concurrency, ID 0xSANITIZED at byte 11868691
# Scores: V/M = 0.00
# Time range: 2015-05-28 09:04:55 to 09:19:15
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 0 3
# Exec time 0 92us 28us 33us 30us 31us 1us 30us
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 0 0 0 0 0 0 0 0
# Rows examine 0 0 0 0 0 0 0 0
# Query size 0 54 18 18 18 18 0 18
# String:
# Databases information_schema
# Hosts
# Users watchdog
# Query_time distribution
# 1us
# 10us ################################################################
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+
SHOW MASTER STATUS\G