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