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

2回目(post_idにインデックスをはった) #2

Open
mickey-DWS opened this issue Oct 19, 2023 · 0 comments
Open

2回目(post_idにインデックスをはった) #2

mickey-DWS opened this issue Oct 19, 2023 · 0 comments

Comments

@mickey-DWS
Copy link
Owner

score

{"pass":true,"score":22933,"success":21796,"fail":0,"messages":[]}

top

top - 20:31:20 up  1:18,  2 users,  load average: 2.32, 1.30, 0.54
Tasks: 115 total,   7 running, 108 sleeping,   0 stopped,   0 zombie
%Cpu(s): 72.9 us, 20.6 sy,  0.0 ni,  0.2 id,  0.0 wa,  0.0 hi,  6.3 si,  0.0 st
MiB Mem :   3827.7 total,   1762.9 free,    892.1 used,   1172.7 buff/cache
MiB Swap:      0.0 total,      0.0 free,      0.0 used.   2698.7 avail Mem

    PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
   1813 mysql     20   0 1808476 665372  36992 S  88.0  17.0   1:16.63 mysqld
   1234 isucon    20   0 1301444  39692   6912 S  82.1   1.0   1:07.91 app
   1556 www-data  20   0   56428   6636   4224 S  15.3   0.2   0:11.32 nginx
   1555 www-data  20   0   56132   6560   4224 S   1.0   0.2   0:01.84 nginx
     36 root      39  19       0      0      0 S   0.3   0.0   0:00.05 khugepaged
    994 root      20   0   11900   5632   4736 S   0.3   0.1   0:00.04 sudo
      1 root      20   0  100840  11512   8312 S   0.0   0.3   0:04.15 systemd
      2 root      20   0       0      0      0 S   0.0   0.0   0:00.00 kthreadd
      3 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_gp
      4 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 rcu_par_gp
      5 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 slub_flushwq
      6 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 netns
      8 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 kworker/0:0H-events_highpri
     10 root       0 -20       0      0      0 I   0.0   0.0   0:00.00 mm_percpu_wq
     11 root      20   0       0      0      0 I   0.0   0.0   0:00.00 rcu_tasks_rude_kthread
     12 root      20   0       0      0      0 I   0.0   0.0   0:00.00 rcu_tasks_trace_kthread
     13 root      20   0       0      0      0 R   0.0   0.0   0:00.14 ksoftirqd/0
     14 root      20   0       0      0      0 I   0.0   0.0   0:00.17 rcu_sched
     15 root      rt   0       0      0      0 S   0.0   0.0   0:00.01 migration/0

alp

+-------+-----+-------+------+-----+-----+--------+---------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+----------------+------------+
| COUNT | 1XX |  2XX  | 3XX  | 4XX | 5XX | METHOD |         URI         |  MIN  |  MAX  |   SUM   |  AVG  |  P90  |  P95  |  P99  | STDDEV | MIN(BODY) |  MAX(BODY)  |   SUM(BODY)    | AVG(BODY)  |
+-------+-----+-------+------+-----+-----+--------+---------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+----------------+------------+
|   310 |   0 |   310 |    0 |   0 |   0 | GET    | /posts              | 0.056 | 0.932 | 174.122 | 0.562 | 0.744 | 0.768 | 0.824 |  0.167 |  4938.000 |    5785.000 |    1655328.000 |   5339.768 |
|   323 |   0 |   323 |    0 |   0 |   0 | GET    | /@[0-9a-zA-Z]+      | 0.028 | 0.712 | 134.649 | 0.417 | 0.552 | 0.576 | 0.680 |  0.106 |  1485.000 |    5475.000 |    1030167.000 |   3189.372 |
|  1421 |   0 |  1421 |    0 |   0 |   0 | GET    | /                   | 0.052 | 0.664 | 531.022 | 0.374 | 0.496 | 0.528 | 0.588 |  0.092 |  2637.000 |    6056.000 |    4843256.000 |   3408.343 |
|     2 |   0 |     2 |    0 |   0 |   0 | GET    | /initialize         | 0.032 | 0.156 |   0.188 | 0.094 | 0.156 | 0.156 | 0.156 |  0.062 |     0.000 |       0.000 |          0.000 |      0.000 |
|   138 |   0 |     0 |  138 |   0 |   0 | POST   | /register           | 0.012 | 0.256 |  10.132 | 0.073 | 0.128 | 0.136 | 0.204 |  0.039 |     0.000 |       0.000 |          0.000 |      0.000 |
|  2128 |   0 |  2128 |    0 |   0 |   0 | GET    | /posts/[0-9a-zA-Z]+ | 0.004 | 0.224 | 110.893 | 0.052 | 0.108 | 0.132 | 0.176 |  0.039 |   721.000 |    1921.000 |    2757622.000 |   1295.875 |
|   238 |   0 |     0 |  119 | 119 |   0 | POST   | /                   | 0.004 | 0.280 |  11.671 | 0.049 | 0.136 | 0.160 | 0.252 |  0.056 |     0.000 |       0.000 |          0.000 |      0.000 |
|  1040 |   0 |     0 | 1040 |   0 |   0 | POST   | /login              | 0.032 | 0.172 |  49.072 | 0.047 | 0.092 | 0.112 | 0.140 |  0.033 |     0.000 |       0.000 |          0.000 |      0.000 |
|   130 |   0 |     0 |  130 |   0 |   0 | POST   | /comment            | 0.004 | 0.120 |   3.240 | 0.025 | 0.048 | 0.064 | 0.112 |  0.019 |     0.000 |       0.000 |          0.000 |      0.000 |
|   138 |   0 |     0 |    0 | 138 |   0 | GET    | /admin/banned       | 0.000 | 0.136 |   1.699 | 0.012 | 0.032 | 0.044 | 0.104 |  0.019 |     0.000 |       0.000 |          0.000 |      0.000 |
| 25617 |   0 | 25617 |    0 |   0 |   0 | GET    | /image/[0-9a-zA-Z]+ | 0.000 | 0.168 | 244.303 | 0.010 | 0.024 | 0.032 | 0.064 |  0.013 | 34383.000 | 1266560.000 | 7496498159.000 | 292637.630 |
|   356 |   0 |   356 |    0 |   0 |   0 | GET    | /login              | 0.004 | 0.080 |   1.640 | 0.005 | 0.012 | 0.020 | 0.048 |  0.009 |   615.000 |     615.000 |     218940.000 |    615.000 |
|   178 |   0 |     0 |  178 |   0 |   0 | GET    | /logout             | 0.000 | 0.056 |   0.544 | 0.003 | 0.008 | 0.012 | 0.040 |  0.006 |    24.000 |      24.000 |       4272.000 |     24.000 |
|  2849 |   0 |  2849 |    0 |   0 |   0 | GET    | /favicon.ico        | 0.000 | 0.060 |   7.204 | 0.003 | 0.008 | 0.012 | 0.028 |  0.005 |    43.000 |      43.000 |     122507.000 |     43.000 |
|  2849 |   0 |  2849 |    0 |   0 |   0 | GET    | /js/timeago.min.js  | 0.000 | 0.084 |   6.032 | 0.002 | 0.004 | 0.012 | 0.028 |  0.005 |  1915.000 |    1915.000 |    5455835.000 |   1915.000 |
|  2849 |   0 |  2849 |    0 |   0 |   0 | GET    | /js/main.js         | 0.000 | 0.088 |   5.060 | 0.002 | 0.004 | 0.008 | 0.024 |  0.005 |  1824.000 |    1824.000 |    5196576.000 |   1824.000 |
|  2849 |   0 |  2849 |    0 |   0 |   0 | GET    | /css/style.css      | 0.000 | 0.076 |   4.200 | 0.001 | 0.004 | 0.008 | 0.020 |  0.004 |  1549.000 |    1549.000 |    4413101.000 |   1549.000 |
+-------+-----+-------+------+-----+-----+--------+---------------------+-------+-------+---------+-------+-------+-------+-------+--------+-----------+-------------+----------------+------------+

pt-query-digest


# 35.2s user time, 70ms system time, 50.02M rss, 56.37M vsz
# Current date: Thu Oct 19 20:42:47 2023
# Hostname: ip-192-168-1-10
# Files: /var/log/mysql/mysql-slow.log
# Overall: 675.11k total, 32 unique, 1.65k QPS, 0.82x concurrency ________
# Time range: 2023-10-19T11:25:12 to 2023-10-19T11:32:00
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time           336s     1us   269ms   497us   596us     6ms    40us
# Lock time          547ms       0    10ms       0     1us    33us       0
# Rows sent         16.75M       0   9.82k   26.02    0.99  492.52       0
# Rows examine      70.62M       0  97.72k  109.68    6.98   2.35k       0
# Query size        85.85M      10   1.33M  133.35   80.10   9.16k   31.70

# Profile
# Rank Query ID                     Response time  Calls  R/Call V/M   Ite
# ==== ============================ ============== ====== ====== ===== ===
#    1 0x4858CF4D8CAA743E839C127... 143.4670 42.7%   1421 0.1010  0.02 SELECT posts
#    2 0x7A12D0C8F433684C3027353...  37.3508 11.1%    310 0.1205  0.02 SELECT posts
#    3 0xCDEB1AFF2AE2BE51B2ED5CF...  30.2067  9.0%    323 0.0935  0.01 SELECT comments
#    4 0xDA556F9115773A1A99AA016...  27.6491  8.2% 219513 0.0001  0.00 ADMIN PREPARE
#    5 0x19759A5557089FD5B718D44...  25.7706  7.7%  27745 0.0009  0.00 SELECT posts
#    6 0x396201721CD58410E070DA9...  23.5401  7.0% 107300 0.0002  0.00 SELECT users
#    7 0x624863D30DAC59FA1684928...  12.2150  3.6%  38361 0.0003  0.00 SELECT comments
#    8 0xE83DA93257C7B787C67B1B0...  10.6426  3.2%    323 0.0329  0.01 SELECT posts
#    9 0x422390B42D4DD86C7539A5F...   9.9716  3.0%  40489 0.0002  0.00 SELECT comments
# MISC 0xMISC                        14.8440  4.4% 239329 0.0001   0.0 <23 ITEMS>

# Query 1: 6.73 QPS, 0.68x concurrency, ID 0x4858CF4D8CAA743E839C127C71B69E75 at byte 133338907
# Scores: V/M = 0.02
# Time range: 2023-10-19T11:28:28 to 2023-10-19T11:31:59
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0    1421
# Exec time     42    143s    14ms   269ms   101ms   180ms    46ms    91ms
# Lock time      0     3ms       0   494us     2us     1us    19us     1us
# Rows sent     81  13.59M   9.77k   9.82k   9.79k   9.80k      30   9.33k
# Rows examine  38  27.18M  19.53k  19.65k  19.59k  19.40k       0  19.40k
# Query size     0 127.67k      92      92      92      92       0      92
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  #############################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` ORDER BY `created_at` DESC\G

# Query 2: 1.47 QPS, 0.18x concurrency, ID 0x7A12D0C8F433684C3027353C36CAB572 at byte 19540099
# Scores: V/M = 0.02
# Time range: 2023-10-19T11:28:29 to 2023-10-19T11:32:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     310
# Exec time     11     37s    21ms   261ms   120ms   198ms    51ms   116ms
# Lock time      0     3ms       0     3ms     9us     1us   139us     1us
# Rows sent     17   2.93M   9.57k   9.76k   9.66k   9.33k       0   9.33k
# Rows examine   8   5.89M  19.34k  19.58k  19.46k  19.40k  118.50  19.40k
# Query size     0  42.99k     142     142     142     142       0     142
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ##############################
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `created_at` <= '2016-01-02T11:46:23+09:00' ORDER BY `created_at` DESC\G

# Query 3: 1.53 QPS, 0.14x concurrency, ID 0xCDEB1AFF2AE2BE51B2ED5CF03D4E749F at byte 90557501
# Scores: V/M = 0.01
# Time range: 2023-10-19T11:28:28 to 2023-10-19T11:31:59
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     323
# Exec time      8     30s    12ms   210ms    94ms   155ms    37ms    91ms
# Lock time      0   411us       0    39us     1us     1us     2us     1us
# Rows sent      0     323       1       1       1       1       0       1
# Rows examine  43  30.81M  97.66k  97.72k  97.69k  97.04k       0  97.04k
# Query size     0  19.54k      61      62   61.93   59.77    0.00   59.77
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms  ################################################################
# 100ms  ###################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) AS count FROM `comments` WHERE `user_id` = 860\G

# Query 4: 1.04k QPS, 0.13x concurrency, ID 0xDA556F9115773A1A99AA0165670CE848 at byte 11735108
# Scores: V/M = 0.00
# Time range: 2023-10-19T11:28:28 to 2023-10-19T11:32:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         32  219513
# Exec time      8     28s    17us   145ms   125us   403us   617us    42us
# 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     7   6.28M      30      30      30      30       0      30
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ########
#   1ms  #
#  10ms  #
# 100ms  #
#    1s
#  10s+
administrator command: Prepare\G

# Query 5: 130.87 QPS, 0.12x concurrency, ID 0x19759A5557089FD5B718D440CBBB5C55 at byte 21929009
# Scores: V/M = 0.00
# Time range: 2023-10-19T11:28:28 to 2023-10-19T11:32:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4   27745
# Exec time      7     26s    71us    39ms   928us     4ms     2ms   224us
# Lock time     10    59ms       0     5ms     2us     1us    37us     1us
# Rows sent      0  27.09k       1       1       1       1       0       1
# Rows examine   0  27.09k       1       1       1       1       0       1
# Query size     1   1.04M      37      40   39.45   38.53    0.26   38.53
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us  #
# 100us  ################################################################
#   1ms  ################
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `posts` WHERE `id` = 4133\G

# Query 6: 506.13 QPS, 0.11x concurrency, ID 0x396201721CD58410E070DA9421CA8C8D at byte 56371066
# Scores: V/M = 0.00
# Time range: 2023-10-19T11:28:28 to 2023-10-19T11:32:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         15  107300
# Exec time      7     24s    30us    53ms   219us   761us   710us    63us
# Lock time     49   272ms       0    10ms     2us     1us    65us     1us
# Rows sent      0 104.79k       1       1       1       1       0       1
# Rows examine   0 104.79k       1       1       1       1       0       1
# Query size     4   3.88M      36      39   37.89   36.69    0.17   36.69
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  #################
#   1ms  ###
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'users'\G
#    SHOW CREATE TABLE `isuconp`.`users`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `users` WHERE `id` = 482\G

# Query 7: 180.95 QPS, 0.06x concurrency, ID 0x624863D30DAC59FA16849282195BE09F at byte 19539508
# Scores: V/M = 0.00
# Time range: 2023-10-19T11:28:28 to 2023-10-19T11:32:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5   38361
# Exec time      3     12s    50us    47ms   318us     1ms   891us   108us
# Lock time     16    89ms       0     5ms     2us     1us    44us     1us
# Rows sent      0  44.01k       0       3    1.17    2.90    1.41       0
# Rows examine   0 189.26k       0      24    5.05   15.25    6.43       0
# Query size     3   3.02M      79      83   82.62   80.10    0.11   80.10
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us  ###################################################
# 100us  ################################################################
#   1ms  #######
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT * FROM `comments` WHERE `post_id` = 9990 ORDER BY `created_at` DESC LIMIT 3\G

# Query 8: 1.54 QPS, 0.05x concurrency, ID 0xE83DA93257C7B787C67B1B05D2469241 at byte 171004828
# Scores: V/M = 0.01
# Time range: 2023-10-19T11:28:28 to 2023-10-19T11:31:58
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0     323
# Exec time      3     11s     4ms    94ms    33ms    68ms    17ms    28ms
# Lock time      0   404us       0    30us     1us     1us     1us     1us
# Rows sent      0   3.09k       2      20    9.80   14.52    2.89    9.83
# Rows examine   4   3.09M   9.77k   9.84k   9.80k   9.80k   33.50   9.80k
# Query size     0  35.94k     113     114  113.93  112.70       0  112.70
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  #
#  10ms  ################################################################
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'posts'\G
#    SHOW CREATE TABLE `isuconp`.`posts`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT `id`, `user_id`, `body`, `mime`, `created_at` FROM `posts` WHERE `user_id` = 859 ORDER BY `created_at` DESC\G

# Query 9: 190.99 QPS, 0.05x concurrency, ID 0x422390B42D4DD86C7539A5F45EB76A80 at byte 178937412
# Scores: V/M = 0.00
# Time range: 2023-10-19T11:28:28 to 2023-10-19T11:32:00
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          5   40489
# Exec time      2     10s    49us    27ms   246us   881us   674us    84us
# Lock time     20   110ms       0     7ms     2us     1us    69us     1us
# Rows sent      0  39.54k       1       1       1       1       0       1
# Rows examine   0 164.81k       0      21    4.17   13.83    5.19       0
# Query size     2   2.53M      62      66   65.58   65.89    1.54   65.89
# String:
# Databases    isuconp
# Hosts        localhost
# Users        isuconp
# Query_time distribution
#   1us
#  10us  ################################################################
# 100us  ###############################
#   1ms  ####
#  10ms  #
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `isuconp` LIKE 'comments'\G
#    SHOW CREATE TABLE `isuconp`.`comments`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT COUNT(*) AS `count` FROM `comments` WHERE `post_id` = 10074\G
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
@mickey-DWS