isucon mysql のスロークエリを調査する

Friendly Assistance Offered.md

mysql の設定をカスタマイズするときは、 /etc/mysql/mysql.conf.d/mysqld?.conf を設定する。

（mysql client と mysqld deamon でそれぞれ設定ファイルが分かれている）

以下のように記載することで mysql-slow-log を有効化する。

code:mysql:/etc/mysql/mysql.cnf.d/mysqld.cnf

mysqld

slow_query_log=1

slow_query_log_file=/var/log/mysql/mysql-slow.log

long_query_time=0

有効化するときは

sudo systemctl restart mysql で mysql デーモンを再起動する。

スロークエリログを人間がそのまま読むのは難しいため、 mysqldumpslow か slp を使って分析する。

他にも pt-query-digest もある。

code:shell

ubuntu@ip-172-31-25-201:~$ sudo mysqldumpslow /var/log/mysql/mysql-slow.log

Reading mysql slow query log from /var/log/mysql/mysql-slow.log

Count: 2 Time=0.01s (0s) Lock=0.00s (0s) Rows=10000.0 (20000), isuconpisuconp@localhost

SELECT id, user_id, body, mime, created_at FROM posts ORDER BY created_at DESC

Count: 40 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (40), isuconpisuconp@localhost

SELECT * FROM posts WHERE id = N

Count: 44 Time=0.00s (0s) Lock=0.00s (0s) Rows=3.0 (132), isuconpisuconp@localhost

SELECT * FROM comments WHERE post_id = N ORDER BY created_at DESC LIMIT N

Count: 44 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (44), isuconpisuconp@localhost

SELECT COUNT(*) AS count FROM comments WHERE post_id = N

Count: 3 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), isuconpisuconp@localhost

SET NAMES utf8mb4

Count: 176 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (176), isuconpisuconp@localhost

SELECT * FROM users WHERE id = N

Count: 609 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.6 (392), isuconpisuconp@localhost

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

administrator command: Close stmt

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

administrator command: Prepare

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

administrator command: Quit

code:sh

sudo cat /var/log/mysql/mysql-slow.log | sudo slp my -r

mysql-slow ログを rotate する

code: sh

#!/bin/bash

set -euo pipefail

DT=$(date "+%Y-%m-%d-%H-%M-%S")

FOLDER=/var/log/slp-analytics/$DT

LOGFILE=/var/log/mysql/mysql-slow.log

mkdir -p $FOLDER

SORT_OPTIONS=("sum-query-time" "avg-query-time" "count")

for v in "${SORT_OPTIONS@}"; do

cat $LOGFILE | slp my -r --sort=$v > $FOLDER/$v.log

done

rm -rf $LOGFILE

mysqladmin flush-logs

#mysql #isucon #query