alter4

[kohkubo]

root@env2-basil:~/42HoursTuningTheBackend# ls
LICENSE    analyzed-slow.log          development  init.sh  patch.sh      scoring
README.md  analyzed-slow_msaita3.log  document     local    provisioning  volume
root@env2-basil:~/42HoursTuningTheBackend# cat analyzed-slow.log 
Reading from STDIN ...

# 800ms user time, 40ms system time, 1.00k rss, 34.00k vsz
# Current date: Sun Apr 17 02:14:27 2022
# Hostname: 9202713f7986
# Files: STDIN
# Overall: 2.78k total, 34 unique, 6.83 QPS, 4.84x concurrency ___________
# Time range: 2022-04-17T02:07:36 to 2022-04-17T02:14:23
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          1970s     3us     45s   708ms    48ms      5s   467us
# Lock time            6ms       0   849us     2us     3us    16us     1us
# Rows sent          4.17k       0      10    1.54    9.83    2.28    0.99
# Rows examine      50.36M       0 878.93k  18.55k    9.83 117.65k    0.99
# Query size       206.14k       8     414   75.93  183.58   59.33   44.60

# Profile
# Rank Query ID                            Response time   Calls R/Call  V
# ==== =================================== =============== ===== ======= =
#    1 0xFE1B4AC9EFFC83215943A13BF37CF837  1914.6097 97.2%   105 18.2344 13.92 SELECT record
#    2 0x558167E9A6843F0636E81318073DB5D5    39.7970  2.0%    10  3.9797  0.24 SELECT record
# MISC 0xMISC                                15.1560  0.8%  2665  0.0057   0.0 <32 ITEMS>

# Query 1: 0.43 QPS, 7.91x concurrency, ID 0xFE1B4AC9EFFC83215943A13BF37CF837 at byte 501668
# This item is included in the report because it matches --limit.
# Scores: V/M = 13.92
# Time range: 2022-04-17T02:10:21 to 2022-04-17T02:14:23
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          3     105
# Exec time     97   1915s   460ms     45s     18s     40s     16s      5s
# Lock time      3   228us     1us     5us     2us     2us       0     1us
# Rows sent     24   1.00k       1      10    9.76    9.83    1.37    9.83
# Rows examine  94  47.40M  97.66k 878.93k 462.26k 871.90k 386.57k  97.04k
# Query size     5  10.35k      99     102  100.90   97.36       0   97.36
# String:
# Databases    app
# Hosts        192.168.176.4
# Users        backend
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  #
#    1s  ################################################################
#  10s+  ####################################################
# Tables
#    SHOW TABLE STATUS FROM `app` LIKE 'record'\G
#    SHOW CREATE TABLE `app`.`record`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from record where status = "closed" order by updated_at desc, record_id asc limit 10 offset 0\G

# Query 2: 0.04 QPS, 0.18x concurrency, ID 0x558167E9A6843F0636E81318073DB5D5 at byte 273605
# This item is included in the report because it matches --outliers.
# Scores: V/M = 0.24
# Time range: 2022-04-17T02:10:32 to 2022-04-17T02:14:15
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          0      10
# Exec time      2     40s      2s      5s      4s      5s   976ms      4s
# Lock time      0    20us     1us     4us     2us     2us       0     1us
# Rows sent      2     100      10      10      10      10       0      10
# Rows examine   1 976.84k  97.67k  97.70k  97.68k  97.04k       0  97.04k
# Query size     0   1.85k     181     190  189.10  183.58    2.62  183.58
# String:
# Databases    app
# Hosts        192.168.176.4
# Users        backend
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `app` LIKE 'record'\G
#    SHOW CREATE TABLE `app`.`record`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from record where status = "open" and (category_id, application_group) in ( (2, 6), (2, 7), (2, 8), (2, 9), (2, 10) ) order by updated_at desc, record_id  limit 10 offset 0\G

[kohkubo]

CREATE INDEX index_item
    ON record_item_file (linked_record_id ASC);

ALTER TABLE group_member ADD INDEX index_group_member(user_id);

ALTER TABLE user ADD INDEX index_user(user_id);

ALTER TABLE group_info ADD INDEX index_group_info(group_id);

ALTER TABLE category ADD INDEX index_category(category_id);

ALTER TABLE record_last_access ADD INDEX index_item(user_id, record_id);

ALTER TABLE session ADD INDEX index_session(value);

ALTER TABLE record_comment ADD INDEX index_record_comment(linked_record_id);

ALTER TABLE record ADD INDEX index_record(status, created_by);