SlowQueryと戦う - Githubissues

yamachu commented 4 years ago

mysql> show variables like 'long%'
    -> ;
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.100000 |
+-----------------+----------+
1 row in set (0.00 sec)

mysql> show variables like 'slow%';
+---------------------+----------------+
| Variable_name       | Value          |
+---------------------+----------------+
| slow_launch_time    | 2              |
| slow_query_log      | ON             |
| slow_query_log_file | mysql-slow.log |
+---------------------+----------------+
3 rows in set (0.00 sec)

mysql>

設定してあった

現在の設定だと 0.1秒らしい

yamachu commented 4 years ago

[isucon@ip-172-31-23-17 src]$  cat /tmp/mysql-slow.log

[isucon@ip-172-31-23-17 src]$

nothing slow queries...

yamachu commented 4 years ago

[isucon@ip-172-31-23-17 isucon3-mod]$ sudo cat /var/lib/mysql/mysql-slow.log

お前お前々〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜ここか〜〜〜〜〜〜〜〜〜

FuwariSprit commented 4 years ago

dumpした結果

Count: 1045  Time=0.05s (56s)  Lock=0.00s (0s)  Rows=99.8 (104309), isucon[isucon]@localhost
  SELECT id, content, is_private, created_at, updated_at FROM memos WHERE user=N  ORDER BY created_at

Count: 490  Time=0.10s (47s)  Lock=0.00s (0s)  Rows=100.0 (49000), isucon[isucon]@localhost
  SELECT * FROM memos WHERE is_private=N ORDER BY created_at DESC, id DESC LIMIT N OFFSET N

Count: 983  Time=0.04s (40s)  Lock=0.00s (0s)  Rows=1.0 (983), isucon[isucon]@localhost
  SELECT count(*) AS c FROM memos WHERE is_private=N

Count: 493  Time=0.06s (31s)  Lock=0.00s (0s)  Rows=100.0 (49300), isucon[isucon]@localhost
  SELECT * FROM memos WHERE is_private=N ORDER BY created_at DESC, id DESC LIMIT N

Count: 188  Time=0.07s (12s)  Lock=0.00s (0s)  Rows=50.9 (9560), isucon[isucon]@localhost
  SELECT id, content, is_private, created_at, updated_at FROM memos WHERE user=N AND is_private=N ORDER BY created_at

Count: 197  Time=0.06s (11s)  Lock=0.00s (0s)  Rows=101.9 (20067), isucon[isucon]@localhost
  SELECT id, content, is_private, created_at, updated_at FROM memos WHERE user=N ORDER BY created_at DESC

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), 0users@0hosts

yamachu commented 4 years ago

memosテーブルのwhereでuserを毎回叩いてるし、しかもこのuserってusersテーブルのidと一致しているので、外部キー制約をかけるか、普通にindex貼るといいのかもしれないとか思った

FuwariSprit commented 4 years ago

user, created_atに貼ろうかなって考えてます

okashoi / isucon-practice-20200718

SlowQueryと戦う #14