MariaDB/query performance/parsercache

From Wikitech
Jump to navigation Jump to search

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