Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

2023/11/25(Sat)15:46:27 #36

Open
takeokunn opened this issue Nov 25, 2023 · 2 comments
Open

2023/11/25(Sat)15:46:27 #36

takeokunn opened this issue Nov 25, 2023 · 2 comments

Comments

@takeokunn
Copy link
Contributor

takeokunn commented Nov 25, 2023 •
edited by yshir

11,332
@takeokunn
Copy link
Contributor Author

alp:

+-------+--------+---------------------------------+-----+------+-----+-----+-----+---------+--------+-------+--------+
| COUNT | METHOD |               URI               | 1XX | 2XX  | 3XX | 4XX | 5XX |   SUM   |  AVG   |  MIN  |  MAX   |
+-------+--------+---------------------------------+-----+------+-----+-----+-----+---------+--------+-------+--------+
| 13    | GET    | /api/user/\w+/statistics        | 0   | 2    | 0   | 11  | 0   | 203.116 | 15.624 | 3.312 | 20.004 |
| 683   | GET    | /api/livestream/\w+/reaction    | 0   | 680  | 0   | 3   | 0   | 197.204 | 0.289  | 0.004 | 1.268  |
| 26    | POST   | /api/livestream/\w+/moderate    | 0   | 23   | 0   | 3   | 0   | 116.348 | 4.475  | 0.184 | 12.192 |
| 685   | POST   | /api/livestream/\w+/livecomment | 0   | 676  | 0   | 9   | 0   | 61.600  | 0.090  | 0.004 | 0.248  |
| 228   | POST   | /api/register                   | 0   | 225  | 0   | 2   | 1   | 51.244  | 0.225  | 0.004 | 0.592  |
| 83    | POST   | /api/livestream/\w+             | 0   | 79   | 0   | 4   | 0   | 37.972  | 0.457  | 0.152 | 1.308  |
| 648   | GET    | /api/livestream/\w+/livecomment | 0   | 648  | 0   | 0   | 0   | 31.276  | 0.048  | 0.004 | 0.172  |
| 130   | GET    | /api/livestream/\w+             | 0   | 127  | 0   | 3   | 0   | 27.716  | 0.213  | 0.008 | 4.696  |
| 599   | POST   | /api/livestream/\w+/reaction    | 0   | 599  | 0   | 0   | 0   | 15.232  | 0.025  | 0.004 | 0.116  |
| 7029  | GET    | /api/user/\w+/icon              | 0   | 7029 | 0   | 0   | 0   | 10.080  | 0.001  | 0.000 | 0.036  |
| 126   | GET    | /api/livestream                 | 0   | 126  | 0   | 0   | 0   | 9.424   | 0.075  | 0.008 | 0.208  |
| 1     | POST   | /api/initialize                 | 0   | 1    | 0   | 0   | 0   | 2.204   | 2.204  | 2.204 | 2.204  |
| 69    | GET    | /api/livestream/\w+/report      | 0   | 69   | 0   | 0   | 0   | 1.880   | 0.027  | 0.004 | 0.096  |
| 225   | POST   | /api/icon                       | 0   | 225  | 0   | 0   | 0   | 1.424   | 0.006  | 0.004 | 0.052  |
| 233   | POST   | /api/login                      | 0   | 231  | 0   | 2   | 0   | 1.372   | 0.006  | 0.000 | 0.060  |
| 68    | GET    | /api/tag                        | 0   | 68   | 0   | 0   | 0   | 0.580   | 0.009  | 0.004 | 0.044  |
| 65    | POST   | /api/livestream/\w+/enter       | 0   | 65   | 0   | 0   | 0   | 0.532   | 0.008  | 0.000 | 0.060  |
| 56    | DELETE | /api/livestream/\w+/exit        | 0   | 56   | 0   | 0   | 0   | 0.300   | 0.005  | 0.004 | 0.028  |
| 12    | GET    | /api/user/\w+/theme             | 0   | 12   | 0   | 0   | 0   | 0.092   | 0.008  | 0.000 | 0.024  |
| 4     | GET    | /api/user/\w+                   | 0   | 4    | 0   | 0   | 0   | 0.004   | 0.001  | 0.000 | 0.004  |
| 1     | GET    | /api/payment                    | 0   | 1    | 0   | 0   | 0   | 0.004   | 0.004  | 0.004 | 0.004  |
| 1     | GET    | /favicon.ico                    | 0   | 1    | 0   | 0   | 0   | 0.000   | 0.000  | 0.000 | 0.000  |
+-------+--------+---------------------------------+-----+------+-----+-----+-----+---------+--------+-------+--------+

@takeokunn
Copy link
Contributor Author

pt-query-digest:


# 14.3s user time, 50ms system time, 36.29M rss, 42.68M vsz
# Current date: Sat Nov 25 06:46:45 2023
# Hostname: ip-192-168-0-12
# Files: /var/log/mysql/mysql-slow.log
# Overall: 194.98k total, 117 unique, 1.44k QPS, 3.64x concurrency _______
# Time range: 2023-11-25T06:43:24 to 2023-11-25T06:45:39
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           491s     1us   300ms     3ms    12ms     9ms   185us
# Lock time             1s       0    48ms     5us     1us   247us     1us
# Rows sent        290.89k       0   7.39k    1.53    4.96   42.89    0.99
# Rows examine      84.83M       0  14.02k  456.21   1.86k   1.15k    1.96
# Query size        28.51M       5   1.94M  153.32  346.17   4.68k   97.36

# Profile
# Rank Query ID                      Response time Calls R/Call V/M   Item
# ==== ============================= ============= ===== ====== ===== ====
#    1 0x42EF7D7D98FBCC9723BF896E... 85.7698 17.5%  3615 0.0237  0.02 SELECT records
#    2 0xF1B8EF06D6CA63B24BFF433E... 75.9464 15.5%  4546 0.0167  0.02 SELECT users livestreams livecomments
#    3 0xDB74D52D39A7090F224C4DEE... 71.4695 14.6%  4549 0.0157  0.02 SELECT users livestreams reactions
#    4 0x38BC86A45F31C6B1EE324671... 50.8217 10.3% 19719 0.0026  0.00 SELECT themes
#    5 0x4ADE2DC90689F1C4891749AF... 35.5564  7.2% 43484 0.0008  0.00 DELETE SELECT livecomments
#    6 0x3D83BC87F3B3A00D571FFC81... 30.7699  6.3%  3003 0.0102  0.01 SELECT records
#    7 0x64CC8A4E8E4B390203375597... 29.3210  6.0%   636 0.0461  0.02 SELECT ng_words
#    8 0x59F1B6DD8D9FEC059E55B3BF... 18.1156  3.7%   605 0.0299  0.01 SELECT reservation_slots
#    9 0xEA1E6309EEEFF9A6831AD2FB...  9.1493  1.9% 19482 0.0005  0.00 SELECT users
#   10 0xBB9B3D66E02B513839482132...  8.8194  1.8% 10052 0.0009  0.00 SELECT tags livestream_tags
#   11 0x9AC623FA477E73A44D191D29...  8.6833  1.8%   458 0.0190  0.02 SELECT records
#   12 0xC499D81D570D361DB61FC43A...  7.2961  1.5% 15809 0.0005  0.00 SELECT livestreams reactions
#   13 0x859BBB7E9D760686137A9444...  6.6835  1.4%   229 0.0292  0.01 DELETE records
#   14 0x9EAD6C0CE525E3693EE27FFC...  5.8579  1.2%   128 0.0458  0.01 SELECT livestreams
#   15 0xD6032FE08E1FE706A928B8B7...  5.8169  1.2% 12127 0.0005  0.00 SELECT livestreams
#   16 0xA3401CA3ABCC04C3AB221DB8...  5.5955  1.1%    79 0.0708  0.04 UPDATE reservation_slots
#   17 0x22279D81D51006139E0C7640...  5.0326  1.0%  3918 0.0013  0.01 SELECT domains domainmetadata
#   18 0x8F7679D452333ED3C7D60D22...  4.6511  0.9% 13570 0.0003  0.01 ADMIN RESET STMT
#   19 0x7F9C0C0BA9473953B723EE16...  3.6275  0.7%    81 0.0448  0.03 SELECT reservation_slots
# MISC 0xMISC                        22.1160  4.5% 38891 0.0006   0.0 <98 ITEMS>

# Query 1: 41.08 QPS, 0.97x concurrency, ID 0x42EF7D7D98FBCC9723BF896EBFC51D24 at byte 26651579
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-11-25T06:44:11 to 2023-11-25T06:45:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    3615
# Exec time     17     86s    95us   140ms    24ms    71ms    23ms    16ms
# Lock time      0    10ms       0     2ms     2us     1us    40us     1us
# Rows sent      0   1.55k       0       1    0.44    0.99    0.49       0
# Rows examine   5   4.84M       1   1.47k   1.37k   1.39k   78.61   1.33k
# Query size     1 494.41k     128     210  140.05  158.58   10.17  136.99
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us  #
# 100us  #
#   1ms  ##################################
#  10ms  ################################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
#    SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='*.u.isucon.dev' and domain_id=11\G

# Query 2: 56.83 QPS, 0.95x concurrency, ID 0xF1B8EF06D6CA63B24BFF433E06CCEB22 at byte 59541147
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-11-25T06:44:19 to 2023-11-25T06:45:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2    4546
# Exec time     15     76s     2ms   141ms    17ms    56ms    19ms     6ms
# Lock time      1    19ms       0     8ms     4us     1us   126us     1us
# Rows sent      1   4.44k       1       1       1       1       0       1
# Rows examine  11   9.64M   1.96k   3.05k   2.17k   2.62k  317.45   1.96k
# Query size     2 731.18k     163     166  164.70  158.58       0  158.58
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  #######################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'users'\G
#    SHOW CREATE TABLE `isupipe`.`users`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestreams'\G
#    SHOW CREATE TABLE `isupipe`.`livestreams`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT IFNULL(SUM(l2.tip), 0) FROM users u
		INNER JOIN livestreams l ON l.user_id = u.id	
		INNER JOIN livecomments l2 ON l2.livestream_id = l.id
		WHERE u.id = 200\G

# Query 3: 56.86 QPS, 0.89x concurrency, ID 0xDB74D52D39A7090F224C4DEEAF3028C9 at byte 49241830
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-11-25T06:44:19 to 2023-11-25T06:45:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          2    4549
# Exec time     14     71s     1ms   133ms    16ms    53ms    19ms     4ms
# Lock time      3    34ms     1us    19ms     7us     1us   274us     1us
# Rows sent      1   4.44k       1       1       1       1       0       1
# Rows examine  11   9.64M   1.96k   2.99k   2.17k   2.62k  309.28   1.96k
# Query size     2 642.82k     143     146  144.70  143.84    1.08  143.84
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms  #################################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'users'\G
#    SHOW CREATE TABLE `isupipe`.`users`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livestreams'\G
#    SHOW CREATE TABLE `isupipe`.`livestreams`\G
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'reactions'\G
#    SHOW CREATE TABLE `isupipe`.`reactions`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) FROM users u
		INNER JOIN livestreams l ON l.user_id = u.id
		INNER JOIN reactions r ON r.livestream_id = l.id
		WHERE u.id = 131\G

# Query 4: 146.07 QPS, 0.38x concurrency, ID 0x38BC86A45F31C6B1EE324671506C898A at byte 56373890
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:43:24 to 2023-11-25T06:45:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         10   19719
# Exec time     10     51s   267us    66ms     3ms     9ms     3ms   839us
# Lock time      9    91ms       0    10ms     4us     1us   117us     1us
# Rows sent      6  19.26k       1       1       1       1       0       1
# Rows examine  24  21.11M    1000   1.20k   1.10k   1.14k   53.24   1.04k
# Query size     2 789.49k      38      41   41.00   40.45    0.08   40.45
# String:
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ####################################################
#  10ms  ######
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'themes'\G
#    SHOW CREATE TABLE `isupipe`.`themes`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM themes WHERE user_id = 1096\G

# Query 5: 724.73 QPS, 0.59x concurrency, ID 0x4ADE2DC90689F1C4891749AF54FB8D14 at byte 60313449
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: 2023-11-25T06:44:39 to 2023-11-25T06:45:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         22   43484
# Exec time      7     36s    99us    63ms   817us     3ms     2ms   167us
# Lock time     14   150ms       0    12ms     3us     1us    95us     1us
# Rows sent      0       0       0       0       0       0       0       0
# Rows examine   0  84.94k       2       4    2.00    1.96    0.02    1.96
# Query size    48  13.70M     263     505  330.47  363.48   27.42  313.99
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us  ################################################################
#   1ms  ################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'livecomments'\G
#    SHOW CREATE TABLE `isupipe`.`livecomments`\G
DELETE FROM livecomments
			WHERE
			id = 1098 AND
			livestream_id = 7537 AND
			(SELECT COUNT(*)
			FROM
			(SELECT 'この無憂煙霧網、安全？' AS text) AS texts
			INNER JOIN
			(SELECT CONCAT('%', '落雷鯨', '%')	AS pattern) AS patterns
			ON texts.text LIKE patterns.pattern) >= 1\G
# Converted for EXPLAIN
# EXPLAIN /*!50100 PARTITIONS*/
select * from  livecomments
			WHERE
			id = 1098 AND
			livestream_id = 7537 AND
			(SELECT COUNT(*)
			FROM
			(SELECT 'この無憂煙霧網、安全？' AS text) AS texts
			INNER JOIN
			(SELECT CONCAT('%', '落雷鯨', '%')	AS pattern) AS patterns
			ON texts.text LIKE patterns.pattern) >= 1\G

# Query 6: 26.81 QPS, 0.27x concurrency, ID 0x3D83BC87F3B3A00D571FFC8104A6E50C at byte 43440483
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T06:43:47 to 2023-11-25T06:45:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          1    3003
# Exec time      6     31s   101us    98ms    10ms    31ms    11ms     6ms
# Lock time      3    33ms       0    24ms    11us     1us   424us     1us
# Rows sent      0   1.57k       0       1    0.54    0.99    0.49    0.99
# Rows examine   4   4.02M       0   1.47k   1.37k   1.39k   89.02   1.33k
# Query size     1 388.67k     126     208  132.53  151.03    9.08  124.25
# String:
# Databases    isudns
# Hosts        localhost
# Users        isudns
# Query_time distribution
#   1us
#  10us
# 100us  ##
#   1ms  ################################################################
#  10ms  ##########################################
# 100ms  #
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isudns` LIKE 'records'\G
#    SHOW CREATE TABLE `isudns`.`records`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='u.isucon.dev'\G

# Query 7: 8.71 QPS, 0.40x concurrency, ID 0x64CC8A4E8E4B390203375597CE4D611F at byte 42785252
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.02
# Time range: 2023-11-25T06:44:26 to 2023-11-25T06:45:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     636
# Exec time      5     29s     4ms   184ms    46ms    91ms    27ms    42ms
# Lock time      0     1ms       0   447us     2us     1us    18us     1us
# Rows sent      0     230       0       2    0.36    0.99    0.54       0
# Rows examine  10   8.70M  14.00k  14.02k  14.01k  13.78k       0  13.78k
# Query size     0  61.48k      97      99   98.99   97.36    0.26   97.36
# String:
# Databases    isupipe
# Hosts        localhost
# Users        isucon
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ####
#  10ms  ################################################################
# 100ms  ###
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isupipe` LIKE 'ng_words'\G
#    SHOW CREATE TABLE `isupipe`.`ng_words`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = 1021 AND livestream_id = 7539\G

# Query 8: 7.56 QPS, 0.23x concurrency, ID 0x59F1B6DD8D9FEC059E55B3BFD624E8C3 at byte 20918502
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.01
# Time range: 2023-11-25T06:44:19 to 2023-11-25T06:45:39
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     605
# Exec time      3     18s     2ms   110ms    30ms    65ms    20ms    27ms
# Lock time      0   652us       0    26us     1us     1us     1us     1us
# Rows sent      0     605       1       1       1       1       0       1
# Rows examine   5   5.05M   8.55k   8.55k   8.55k   8.55k       0   8.55k

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
1 participant
@takeokunn