sql1609 - Githubissues

++ dirname ./perf-tools/bench.sh
+ cd ./perf-tools
+ sudo cp /dev/null /var/log/nginx/access.log
+ sudo systemctl restart nginx
+ sudo cp /dev/null /var/log/mysql/mysql-slow.log
+ cd ../webapp/go
+ make build
CGO_ENABLED=0 GOOS=linux GOARCH=amd64  go build -o ./isupipe -ldflags "-s -w"
+ sudo systemctl restart isupipe-go.service
isucon@ip-192-168-0-11:~$ ls
env.sh  go  local  perf-tools  webapp
isucon@ip-192-168-0-11:~$ ./perf-tools/slowquery.sh 
++ dirname ./perf-tools/slowquery.sh
+ cd ./perf-tools
+ rotate_filepath=
+ '[' 0 -eq 1 ']'
+ sudo mysqldumpslow -s t /var/log/mysql/mysql-slow.log
+ head -n 100

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 320259  Time=0.00s (49s)  Lock=0.00s (0s)  Rows=0.6 (189502), 2users@localhost
  #

Count: 5594  Time=0.01s (41s)  Lock=0.00s (0s)  Rows=0.0 (80), isudns[isudns]@localhost
  SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and name='S' and domain_id=N

Count: 5435  Time=0.01s (35s)  Lock=0.00s (0s)  Rows=0.1 (349), isudns[isudns]@localhost
  SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and type='S' and name='S'

Count: 568  Time=0.05s (26s)  Lock=0.00s (0s)  Rows=0.8 (431), isucon[isucon]@localhost
  SELECT id, user_id, livestream_id, word FROM ng_words WHERE user_id = N AND livestream_id = N

Count: 26175  Time=0.00s (22s)  Lock=0.00s (0s)  Rows=0.8 (21872), isucon[isucon]@localhost
  SELECT image FROM icons WHERE user_id = N

Count: 986  Time=0.02s (21s)  Lock=0.00s (0s)  Rows=1.0 (986), isucon[isucon]@localhost
  SELECT slot FROM reservation_slots WHERE start_at = N AND end_at = N

Count: 4030  Time=0.00s (17s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  COMMIT

Count: 49731  Time=0.00s (16s)  Lock=0.00s (0s)  Rows=1.0 (49731), isucon[isucon]@localhost
  SELECT * FROM tags WHERE id = N

Count: 239  Time=0.05s (11s)  Lock=0.00s (0s)  Rows=0.9 (217), isucon[isucon]@localhost
  SELECT * FROM livestreams WHERE user_id = N

Count: 21415  Time=0.00s (9s)  Lock=0.00s (0s)  Rows=1.0 (21415), isucon[isucon]@localhost
  SELECT * FROM themes WHERE user_id = N

Count: 21060  Time=0.00s (7s)  Lock=0.00s (0s)  Rows=1.0 (21060), isucon[isucon]@localhost
  SELECT * FROM users WHERE id = N

Count: 345  Time=0.02s (6s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  delete from records where domain_id=N and name='S' and type='S'

Count: 12557  Time=0.00s (5s)  Lock=0.00s (0s)  Rows=4.0 (49744), isucon[isucon]@localhost
  SELECT * FROM livestream_tags WHERE livestream_id = N

Count: 109  Time=0.05s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  UPDATE reservation_slots SET slot = slot - N WHERE start_at >= N AND end_at <= N

Count: 111  Time=0.04s (4s)  Lock=0.00s (0s)  Rows=8.9 (989), isucon[isucon]@localhost
  SELECT * FROM reservation_slots WHERE start_at >= N AND end_at <= N FOR UPDATE

Count: 1000  Time=0.00s (4s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO users (id, name, display_name, description, password) VALUES (N, 'S', 'S', 'S', 'S')

Count: 690  Time=0.01s (4s)  Lock=0.00s (0s)  Rows=0.5 (345), isudns[isudns]@localhost
  SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=N and type='S' and name='S' and domain_id=N

Count: 96  Time=0.05s (4s)  Lock=0.00s (0s)  Rows=0.6 (56), isucon[isucon]@localhost
  SELECT * FROM ng_words WHERE user_id = N AND livestream_id = N ORDER BY created_at DESC

Count: 346  Time=0.01s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  commit

Count: 344  Time=0.01s (3s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO icons (user_id, image) VALUES (N, 'S')

Count: 7665  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=1.0 (7665), isucon[isucon]@localhost
  SELECT * FROM livestreams WHERE id = N

Count: 510  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO themes (user_id, dark_mode) VALUES (N, true)

Count: 490  Time=0.00s (2s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO themes (user_id, dark_mode) VALUES (N, false)

Count: 5142  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=1.0 (5141), isucon[isucon]@localhost
  SELECT * FROM users WHERE name = 'S'

Count: 430  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=8.9 (3837), isucon[isucon]@localhost
  SELECT * FROM livecomments WHERE livestream_id = N ORDER BY created_at DESC

Count: 4805  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  ROLLBACK

Count: 8845  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  START TRANSACTION

Count: 463  Time=0.00s (1s)  Lock=0.00s (0s)  Rows=7.0 (3226), isucon[isucon]@localhost
  SELECT * FROM reactions WHERE livestream_id = N ORDER BY created_at DESC

Count: 76  Time=0.01s (0s)  Lock=0.01s (1s)  Rows=0.0 (0), isucon[isucon]@localhost
  DELETE FROM livecomments
  WHERE
  livestream_id = N AND
  (SELECT COUNT(*)
  FROM
  (SELECT comment AS text) AS texts
  INNER JOIN
  (SELECT CONCAT('S', 'S', 'S') AS pattern) AS patterns
  ON texts.text LIKE patterns.pattern) >= N

Count: 1625  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isudns[isudns]@localhost
  insert into records (content,ttl,prio,type,domain_id,disabled,name,ordername,auth) values ('S',N,N,'S',N,N,'S',NULL,N)

Count: 103  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), isucon[isucon]@localhost
  INSERT INTO tags(name) VALUES ('S')
hedwig100 / isucon13

sql1609 #19
