More than 1 year has passed since last update.

isucon practice

Last updated at 2022-02-22Posted at 2022-02-22

MYSQL

ログ書き込み設定

通常はトランザクション単位でログを書き出す設定だが、
下記で、1秒おきなど一定期間ごとにまとめてログを書き出すようになる。

下記でDBの設定を確認できる

はやくなる設定.sql

$ sudo mysql -u root
mysql> SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

下記でDBに設定

/etc/mysql/my.conf

[mysqld]
innodb_flush_log_at_trx_commit=2

外部アドレスからの接続を許可

ユーザーの設定を確認

$ sudo mysql -u root
mysql> SELECT user, host FROM mysql.user where user = 'isucon';
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| isucon           | localhost |
+------------------+-----------+

どこからでも接続できるユーザーを作成

$ sudo mysql -u root

> CREATE USER "isucon"@"%" IDENTIFIED BY "isucon";
> GRANT ALL PRIVILEGES ON *.* TO "isucon"@"%";
> SELECT user, host FROM mysql.user where user = 'isucon';
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| isucon           | %         |
| isucon           | localhost |
+------------------+-----------+

設定ファイルを編集

$ sed -i 's/bind-address/bind-address = 0.0.0.0/' /etc/mysql/mysql.conf.d/mysqld.cnf
$ sudo service mysql restart

チューニング(index編)

slow queryの設定

下記でDBの設定を確認できる

$ sudo mysql -u root
mysql> show variables like 'slow_query%';

下記でDBに設定

/etc/mysql/my.conf

[mysqld]
slow_query_log=1
slow_query_log_file=’/tmp/slow.log’
long_query_time=0 ## 本当は１秒とかを設定する？

slow queryを確認

## 合計時間
$ sudo mysqldumpslow -s t /tmp/slow.log | head
## 平均実行時間
$ sudo mysqldumpslow -s at /tmp/slow.log | head
## クエリ件数
$ sudo mysqldumpslow -s c /tmp/slow.log | head

遅いクエリを見つける
対象のSQLをEXPLAINをつけて実行

mysql> EXPLAIN select * from chair where height_category = 1 and kind = '座椅子'  and color = '青' and stock > 0 order by popularity desc, id asc limit 10 offset 1;

+----+-------------+-------+------------+------+-----------------------------------+---------------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys                     | key                 | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+-----------------------------------+---------------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | chair | NULL       | ref  | idx_height_category,idx_sample_id | idx_height_category | 5       | const | 6177 |     0.33 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+------+-----------------------------------+---------------------+---------+-------+------+----------+----------------------------------------------------+

適当な解説

SQLの結果のpossible_keysが使用可能なindexのこと
keyが使用したindexのこと

indexを貼る

mysql> create index idx_custom on isuumo.chair(height_category,kind,stock,id);

再度、対象のSQLをEXPLAINをつけて実行

mysql> EXPLAIN select * from chair where height_category = 1 and kind = '座椅子'  and color = '青' and stock > 0 order by popularity desc, id asc limit 10 offset 1;
+----+-------------+-------+------------+-------+----------------------------------------------+------------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys                                 | key        | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+----------------------------------------------+------------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | chair | NULL       | range | idx_height_category,idx_sample_id,idx_custom | idx_custom | 267     | NULL | 1511 |    10.00 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+-------+----------------------------------------------+------------+---------+------+------+----------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

想定したindexがkeyになってればOK
keyが設定したidx_saikyo_cutumになっってるのでコレはOK
なってなければ頑張る

idを良い感じにしてindexを効かせる

下記のようなSQLが処理の中にあったとする。

select * from estate where (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) order by popularity desc, id asc limit 10;

indexを貼る

create index idx_e_dw_dh_pd_id  on isuumo.estate(door_width,door_height,popularity desc,id);

EXPAINで効いてるか確認

+----+-------------+--------+------------+------+-------------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys     | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+--------+------------+------+-------------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | estate | NULL       | ALL  | idx_e_dw_dh_pd_id | NULL | NULL    | NULL | 28452 |    45.61 | Using where; Using filesort |
+----+-------------+--------+------------+------+-------------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

idx_e_dw_dh_pd_idは使用されていない。

idを取得したあとに取得する用に変更

select * from estate where id in (
  select id from estate where (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110) or (door_width >= 10 and door_height >= 110)
) order by popularity desc, id asc limit 10;

EXPLAINで確認

+----+-------------+--------+------------+--------+---------------------------+-------------------+---------+------------------+-------+----------+-----------------------------------------------------------+
| id | select_type | table  | partitions | type   | possible_keys             | key               | key_len | ref              | rows  | filtered | Extra                                                     |
+----+-------------+--------+------------+--------+---------------------------+-------------------+---------+------------------+-------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | estate | NULL       | range  | PRIMARY,idx_e_dw_dh_pd_id | idx_e_dw_dh_pd_id | 8       | NULL             | 14226 |    91.22 | Using where; Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | estate | NULL       | eq_ref | PRIMARY                   | PRIMARY           | 4       | isuumo.estate.id |     1 |   100.00 | NULL                                                      |
+----+-------------+--------+------------+--------+---------------------------+-------------------+---------+------------------+-------+----------+-----------------------------------------------------------+

idx_e_dw_dh_pd_idが使われていることが分かる。

SQL_CALC_FOUND_ROWS と FOUND_ROWS()

下記のような同じ条件のSQLを実行していて、rowと件数の取得を実行しているものがあるとする。
最初のSQLで全件取得するとI/Oで時間がかかる。

let estates = conn.exec(    
    format!(
        "select * from estate where {} order by popularity desc, id asc limit ? offset ?",    
        search_condition
    ),
    &params,  
)?;
let row = conn.exec_first("select count(*) where {} order by popularity desc, id asc limit ? offset ?", ())?;    
let count = row.map(|(c,)| c).unwrap_or(0);

SQL_CALC_FOUND_ROWSを使用してSQLを実行するとlimitやoffsetに関係なく、whereで絞り込んだ条件の件数を保存してくれる。直後にFOUND_ROWS()を呼ぶとその件数を参照できる。

そのため上記のSQLは下記で高速化出来る。

let estates = conn.exec(    
    format!(
        "select SQL_CALC_FOUND_ROWS * from estate where {} order by popularity desc, id asc limit ? offset ?",    
        search_condition
    ),
    &params,  
)?;
let row = conn.exec_first("select FOUND_ROWS()", ())?;    
let count = row.map(|(c,)| c).unwrap_or(0);

値の量子化と仮想カラム

下記のようなSQLが実行されるとする。

select * from estate where width >= 80 and width < 110

また、widthの値は80以下,80,110,150,150以上しか指定されない。

修正

ASの値に式を書くことで、仮想カラムを追加する。
VITUALだと計算コストがかかるのでSTOREDに設定して実値を保存する。

ALTER TABLE chair ADD width_category INTEGER AS (
case
  when                  width <  80 then 0
  when 80  <= width and width < 110 then 1
  when 110 <= width and width < 150 then 2
  when 150 <= width                 then 3
  else null
end
) STORED;

indexを貼る。

create index idx_width_category  on isuumo.chair(width_category);

修正したSQL

上記の設定を利用すると、SQLをは下記のように変更できる

select * from estate where width_category = 1

空間Index と仮想カラム

下記で貼れる

ALTER TABLE isuumo.estate ADD point POINT AS (POINT(latitude, longitude)) STORED NOT NULL;
CREATE SPATIAL INDEX idx_point on isuumo.estate(point);

insertしてlatitude, longitudeに値を保存した後にカラムを作ろうすると、下記みたいなエラーで作れない。

ERROR xxxx (xxxxxx) at line XX: Invalid use of NULL value

参考文献

MYSQLクエリ設定
https://www.slideshare.net/kazeburo/isucon-summerclass2014action2final

他
https://www.slideshare.net/kazeburo/mysql-casual7isucon
https://qiita.com/kkyouhei/items/d2c40d9e3952c7049ca3

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up

isucon practice

MYSQL

ログ書き込み設定

外部アドレスからの接続を許可

チューニング(index編)

slow queryの設定

slow queryを確認

適当な解説

indexを貼る

idを良い感じにしてindexを効かせる

idを取得したあとに取得する用に変更

SQL_CALC_FOUND_ROWS と FOUND_ROWS()

値の量子化と仮想カラム

修正

修正したSQL

空間Index と 仮想カラム

参考文献

空間Index と仮想カラム