Ruby on Rails on MySQL チューニング入門

Ruby on Rails
on MySQL
チューニング入門

佐藤大資 (@eccyan)

Ruby on Rails を使えば簡単に誰でも
Web サービスを構築する事が出来ます。

Ruby on Rails を使えば簡単に誰でも
Web サービスを構築する事が出来ます。
しかし、SQLを理解せずに
Active Record を利用していると・・・

そこで今回は、

に絞ってチューニングの
取り掛り方法をお伝えします。

ActiveRecordって何？

ActiveRecord って何？


• DBテーブルの1行が1つのクラス


• 色々なRDBMSを同じソースコードで
(MySQL, PostgreSQL, SQLite,
SQL Server, Sybase, and Oracle)


• 色々なRDBMSを同じソースコードで
(MySQL, PostgreSQL, SQLite,
SQL Server, Sybase, and Oracle)
• SQL再利用の仕組みがある


ストアドプロシージャ？
DELIMITER //
DROP PROCEDURE IF EXISTS proc1//
CREATE PROCEDURE proc1()
BEGIN
SELECT VERSION();
END;
//
DELIMITER ;


ストアドプロシージャ？
• 処理速度は早いが、負荷集中する
• Master-Slave 構成の場合、更新系の
負荷分散が出来ない
• RDBMS毎に構文やノウハウが変わる

/**
* ページングクエリ(LIMIT 付き) を実行する
*
* 注: $sql は SELECT で始めてください。
* $sql に SELECT SQL_CALC_FOUND_ROWS を入れないでください。
*
* ページングパラメータ($paging) について
* <pre>
* page
ページ番号(1-) empty や 0 以下の場合は 1 が仮定される
* pagesize
ページサイズ empty や 0 以下の場合は最後まで
* no_count
true: カウント不要, empty: カウント必要
* </pre>
*
* @param stdclass $db
DB ハンドラ
* @param string $sql
SQL
* @param string $params パラメータ
* @param array $paging ページングパラメータ
* @param string $key
null: 結果は array of array
*
$key を指定すると結果をある列の配列で返す
* @return array
array('total' => 全件数, 'list' => 結果)
*
ただし、 $pageing['no_count'] が true の場合は
*
結果のみを返します。
*/
public function doPagingQuery($db, $sql, $params, $paging, $key = null)
{
if (empty($paging['no_count'])) {
$sql = preg_replace('/SELECT/i',
'SELECT SQL_CALC_FOUND_ROWS', $sql, 1);
}

オレオレライブラリ？

// ページング条件
$this->createLimitClause($limitClause, $params, $paging);
$sql .= ' ' . $limitClause;
// 実行
$list = $db->query($sql, $params)->result_array();
if ($key) {
foreach ($list as &$value) {
$value = $value[$key];
}
}
if (empty($paging['no_count'])) {
// 全件数を取得する
$totalResult = $db->query('SELECT FOUND_ROWS() AS total')
->row_array();
$total = $totalResult['total'];
}


オレオレライブラリ？
• 属人性が高い
• OSSに比べて枯れていない
• プラグマブルで無く、代替出来ない


ARで再利用可能なコードを書こう
• Arel
• Relation
• Scope


Scope
scope :active, where(active: true)
scope :inactive, where(active: false)


Scope
scope :active, where(active: true)
scope :inactive, where(active: false)
scope :adult_categories, lambda {
# 大人向けカテゴリのIDは 1, 5, 6
active.where(category_id: [1, 5, 6])
}

負荷の少ないクエリとは？



• 検索処理が軽い


• 並び替え処理が軽い


• 結合処理が軽い


• 結合処理が軽い
EXPLAIN で調べよう


EXPLAIN
• インデックスが必要か確認できる
• 結合順序が最適か確認できる


EXPLAIN
mysql> EXPLAIN SELECT ànswers`.ìd` AS t0_r0, ànswers`.òld_id` AS t0_r1, ànswers`.`question_id` AS t0_r2,
ànswers`.ùser_id` AS t0_r3, ànswers`.`question_user_id` AS t0_r4, ànswers`.`to_user_id` AS t0_r5,
ànswers`.`parent_id` AS t0_r6, ànswers`.`content` AS t0_r7, ànswers`.`ng_word` AS t0_r8, ànswers`.`check` AS
t0_r9, ànswers`.àctive` AS t0_r10, ànswers`.`created_at` AS t0_r11, ànswers`.ùpdated_at` AS t0_r12,
`questions`.ìd` AS t1_r0, `questions`.òld_id` AS t1_r1, `questions`.òld_random_key` AS t1_r2,
`questions`.`parent_category_id` AS t1_r3, `questions`.`category_id` AS t1_r4, `questions`.ùser_id` AS t1_r5,
`questions`.`to_user_id` AS t1_r6, `questions`.`type_id` AS t1_r7, `questions`.ìs_serious` AS t1_r8,
`questions`.`content` AS t1_r9, `questions`.`ng_word` AS t1_r10, `questions`.`display` AS t1_r11, `questions`.`check`
AS t1_r12, `questions`.àccess_count` AS t1_r13, `questions`.ùse_image` AS t1_r14, `questions`.ìmage_url` AS
t1_r15, `questions`.`link_url` AS t1_r16, `questions`.àctive` AS t1_r17, `questions`.ànswer_last_updated_at` AS
t1_r18, `questions`.`created_at` AS t1_r19, `questions`.ùpdated_at` AS t1_r20, `questions`.`last_answer_id` AS t1_r21
FROM ànswers` INNER JOIN ùsers` ON ùsers`.ìd` = ànswers`.ùser_id` INNER JOIN `questions` ON
`questions`.ìd` = ànswers`.`question_id` WHERE ànswers`.àctive` = 1 AND (answers.user_id = 1) AND
(answers.question_user_id != 1) AND (answers.to_user_id = 1 OR answers.to_user_id is NULL) AND
(questions.active = 1) AND (questions.id IN ( SELECT temp_a.question_id FROM answers as temp_a WHERE
temp_a.user_id = 1 )) AND (answers.ng_word IN (0, 1)) GROUP BY answers.question_id ORDER BY
questions.answer_last_updated_at desc;


EXPLAIN
+----+--------------------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
| id | select_type
| table | type
| possible_keys
| key
| key_len | ref
| rows | Extra
|
+----+--------------------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
| 1 | PRIMARY
| users | const
| PRIMARY
| PRIMARY
|4
| const
| 1 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY
| answers | ref
|
index_answers_on_question_id_and_user_id_and_created_at,index_answers_on_question_id_and_created_at,index_answers_on_user_id_and_created_at,index_answers_on_
to_user_id_and_created_at,index_answers_on_question_user_id_and_created_at | index_answers_on_user_id_and_created_at
|4
| const
| 196
| Using where
|
| 1 | PRIMARY
| questions | eq_ref
| PRIMARY,index_questions_on_id
| PRIMARY
|4
| rio_production.answers.question_id | 1 | Using where
|
| 2 | DEPENDENT SUBQUERY | temp_a | index_subquery |
index_answers_on_question_id_and_user_id_and_created_at,index_answers_on_question_id_and_created_at,index_answers_on_user_id_and_created_at
| index_answers_on_question_id_and_user_id_and_created_at | 8
| func,const
| 4 | Using index; Using where
|
+----+--------------------+-----------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------+---------+------------------------------------+------+----------------------------------------------+
4 rows in set (0.01 sec)


select_type
SIMPLE キーまたはJOIN
PRIMARY 外部クエリを示す
SUBQUERY 相関関係のないサブクエリ
DEPENDENT 相関関係のあるサブクエリ
SUBQUERY
UNCACHEABLE 実行毎に結果が変わるサブクエリ
SUBQUERY
DERIVED FROM句のサブクエリ


サブクエリ
• SQL内部で更にSQLを発行し取得する事
• 遅いのは相関関係のあるサブクエリ


相関サブクエリ
クエリとサブクエリが相互関係している
EXPLAIN
SELECT * FROM questions
WHERE questions.id IN
(SELECT answers.question_id FROM answers
WHERE question_user_id = questions.user_id
);


さて、このクエリは？
EXPLAIN
(SELECT question_id FROM answers
WHERE question_user_id = 1
);


EXPLAIN
(SELECT question_id FROM answers
);
| id | select_type
|
| 1 | PRIMARY
|
| 2 | DEPENDENT SUBQUERY |


EXPLAIN
WHERE EXISTS
(SELECT 1 FROM answers
AND answers.question_id = questions.id
);


サブクエリの問題点
• 可読性が減る
• アルゴリズムの変動リスク
• インデックス等の環境による遅延リスク


type
const PRIMARY KEY/UNIQUE を利用するアクセス
eq_ref JOINでPRIMARY KEY/UNIQUE を利用するアクセス
ref PRIMARY KEY/UNIQUE 以外のインデックスで等価検
索を利用するアクセス
range インデックスを利用する範囲検索。
index インデックス全体をスキャンするアクセス
(フルインデックススキャン)
ALL テーブル全体をスキャンするアクセス
(フルテーブルスキャン)

フルインデックススキャン
• Index condition pushdown を狙う
高頻度の場合
• 実行タイミングを変更
– 更新時に集計やソートを行う
– キュー等を利用して非同期化

• 仕様の変更
– 件数に制限をかける
– 別テーブルやデータストアに保存


フルテーブルスキャン
• インデックスを追加する
• Index Condition Pushdown を狙う
高頻度の場合
• フルインデックススキャンと同じ


Index Condition Pushdown(ICP)
• MySQL 5.6 から
• マルチカラムインデックスを有効活用す
る


ICPを使わない場合(Sex ＝ 1, 10 ≦ Age ＜ 20)
Sex

Age

Id

1

15

2

1

30

2

18

1

40

2

27

2

13

1

3
4
5
6

7
8
9
10
11

1

14

1

24

13

1

50

15

12

14

16

Sex

Age


ICPを使う場合(Sex ＝ 1, 10 ≦ Age ＜ 20)
Sex

Age

Id

1

15

2

1

30

2

18

1

40

2

27

2

13

1

3
4
5
6

7
8
9
10
11

1

14

1

24

13

1

50

15

12

14

16

Sex

Age


Extra
Using where WHEREの検索条件がインデックスだけで
は解決出来ない。
Using index インデックスだけで条件を解決できる。
Using filesort クイックソートでソートを行っている。
Using temporary 実行にテンポラリテーブルが必要。


Using filesort
• インデックスを追加する
• ソート条件を1テーブルに集中させる
• ソート実行を早めにさせる


Using filesort
mysql> SHOW CREATE TABLE users G;
*************************** 1. row ***************************
Table: users
Create Table: CREATE TABLE ùsers` (
ìd` int(11) unsigned NOT NULL AUTO_INCREMENT,
・・・
PRIMARY KEY (ìd`),
KEY ìndex_users_on_old_id` (òld_id`),
KEY ìndex_users_on_provider_and_uid` (`provider`,ùid`),
KEY ìndex_users_on_old_random_key` (òld_random_key`)
) ENGINE=InnoDB AUTO_INCREMENT=174849 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
ERROR:
No query specified


Using filesort
EXPLAIN
SELECT * FROM users
WHERE active = 1
ORDER BY type_id;


Using temporary
• GROUP BY / DISTINCTを見直す
• 行数を考慮してJOIN条件の見直す
• サブクエリを検討する
• テーブル/カラム追加を検討する

高速化の仕組み

• インデックス
• パーティション


インデックスとは？
• 辞書の索引を付けること
• B-tree を利用している
• 正確には最下層にデータ保持するB+tree


B-treeとは?
定義
• 根は葉であるか 2~m の子をもつ
• 根・葉以外の節は m/2 以上の子をもつ
• 根から葉までの深さが等しい


B-treeとは?
探索方法
• 最左値より小さければ最左部分木へ進む
• 最左値より大きければ次の値と比較し、
小さければ最左の次の部分木へ進む
• 上記を反復する


B-treeとは?

10


B-treeとは?

5

10


B-treeとは?

20

5

10

20


B-treeとは?
10

47
5

20


B-treeとは?
10

50
5

20

47

50


B-treeとは?
10

7
5

47

20

50


B-treeとは?
10

32
5

7

47

20

50


B-treeとは?
10

9
5

7

9

47

20

32

50


B-treeとは?
10

7

5

9

47

20

32

50


MySQL(InnoDB)のインデックス探索
• リーフページが大量の場合は打ち切り
ルートから探索を行う
• 9ページまで読み打ち切る(MySQL 5.6.4)
• リーフページサイズは16KB


パーティションとは？
• 水平分割（行による分割）


リスク
• 分割方法によっては速度が遅くなる
• 既存のクエリに分割キー条件を
追加しなければならない場合がある
• 分割キーをPKにする必要がある
• パーティション変更はサービス停止が必
要


リターン
• メモリへの読み込みが早くなる
• 上記に伴いディスクアクセスも少なくな
る
• 可用性の高い設計になる


テルミーの例
新着が遅くなった
• 元々 created_at で分割をしていた
• クエリに時間による制限や
ソート以外の条件が多かった
• 上記の理由でIDによる分割に変更


テルミーの例
EXPLAIN PARTITIONS
SELECT `questions`.* FROM `questions`
WHERE `questions`.`active` = 1
ORDER BY created_at
LIMIT 10 OFFSET 0 G;


テルミーの例
id: 1
select_type: SIMPLE
table: questions
partitions: p0,p1,p2,p3,…,p509,p510,p511
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1179993
Extra: Using where; Using filesort


テルミーの例
• IDによるソートを行うように
• 取得時にIDの範囲を指定するように


テルミーの例
EXPLAIN PARTITIONS
AND (id > 3) AND (id < 14)


テルミーの例
EXPLAIN PARTITIONS
AND (id > 3) AND (id < 14)
id: 1
select_type: SIMPLE
table: questions
partitions: p4,p5,p6,p7,p8,p9,p10,p11,p12,p13
type: range
possible_keys: PRIMARY,index_questions_on_id
key: PRIMARY
key_len: 4
ref: NULL
rows: 9
Extra: Using where


テルミーの例
id: 1

select_type: SIMPLE
table: questions
partitions: p4,p5,p6,p7,p8,p9,p10,p11,p12,p13
type: range
possible_keys: PRIMARY,index_questions_on_id
key: PRIMARY
key_len: 4
ref: NULL
rows: 9
Extra: Using where

Railsでのチューニング

• ActiveRecordが生成するSQLを知る


• Eager Loadingを利用する


• Eager Loadingを利用する
• 更新時コールバックでの集計
• バックグラウンドでの集計


ActiveRecordが生成するSQLを知る


to_sql
pry(main)> Question.uniq.to_sql


to_sql
pry(main)> Question.uniq.to_sql
=> "SELECT DISTINCT `questions`.* FROM `questions` "


explain
pry(main)> Question.uniq.explain


実装を行う前にpry等で
• 生成されるクエリ(to_sql)
• 実行計画(explain)
を確認する。


NewRelic
• Transaction tracing
• Slow SQL
の設定をオンにすれば


NewRelic


rack-mini-profiler
• https://github.com/MiniProfiler/rack-miniprofiler
• 環境毎にgemを入れるだけで簡単


rack-mini-profiler


mysqldumpslow
• スローログを合計時間で集計してくれる
• ログがあればローカルで実行できる
• gzipも読み込んでくれる
• MySQL デフォルト


mysqldumpslow
$ mysqldumpslow -t 5 -s t mysql-slow.log*
Reading mysql slow query log from mysql-slow.log

Count: 14591 Time=1.57s (22851s) Lock=0.00s (5s) Rows=10.0 (145910),
rio_slave[rio_slave]@8hosts
SELECT `questions`.* FROM `questions` WHERE `questions`.`display` = 'S'
AND `questions`.`to_user_id` IS NULL AND `questions`.`active` = N AND
(answer_last_updated_at != created_at) AND (questions.created_at >= 'S') AND
(ng_word IN (N, N)) ORDER BY answer_last_updated_at desc LIMIT N
OFFSET N
…


Eager Loadingを利用する


Eager Loading とは？
• 「積極的に読み込む」
• 先にデータを取得しておくこと
• インスタンス変数による自動キャッシュ


Question.limit(10).each do |q|
p “#{q.user} posted #{q.content}”
end


Question.limit(10).each do |q|
end
Question Load (9.8ms) SELECT `questions`.* FROM `questions`
User Load (5.2ms) SELECT `users`.* FROM `users` WHERE


Question.includes(:user).limit(10).each do |q|
end


Question.includes(:user).limit(10).each do |q|
end
User Load (5.0ms) SELECT ùsers`.* FROM ùsers` WHERE ùsers`.ìd` IN (724,
1402, 2277, 3154, 3696, 4180, 4551, 5375, 6090, 6890)


# 指定リレーショナルオブジェクトを先読み
Question.includes(:user)
Question.includes(:user, :category)
# 指定リレーションオブジェクトのフィールドを先読み
Question.includes(category: [:parent_category])
Question.includes(category:
[:parent_category, :answer_good_logs])

# 更に入れ子にも出来ます
・・・


Eager Loadingチューニングのフロー
1. キャッシュを切る
2. rack-mini-profilerで大量発行SQLを探す
3. 対象のコントローラを探す
4. Includes を追加する
5. rack-mini-profilerで確認


Eager Loadingチューニングのフロー
スローログに乗らない細かいSQLも
大きな負荷削減になることが結構有ります


インスタンス変数によるキャッシュ


1接続内に複数回SQLが発行される場合
Question.first.tap do |q|
10.times { "answers count is #{q.answers.count}" }
end
(4.2ms) SELECT COUNT(*) FROM `answers` WHERE


class Question < ActiveRecord::Base
・・・
def answers_count
@answers_count ||= self.answers.count
end
・・・
end


Question.first.tap do |q|
10.times { "answers count is #{q.answers_count}" }
end


更新時コールバックでの集計
• カウント集計
• 最後に更新のあった解答


• カウント集計
– 更新時に親へカウントアップ/ダウン
• 最後に更新のあった投稿
– 更新時に親へ保存


どの様なテーブル設計の場合なの？
Users (Master)

Posts (Transaction)

name

user_id

email
…

1:n

content
…


Users (Master)

Posts (Transaction)

name

user_id

email

1:n

…

content
…

UserPostStatus (Transaction)
user_id
last_post_id
posted_count
…


Users (Master)

Posts (Transaction)

name

user_id

email

1:n

…

content
…

1:1

user_id
last_post_id
posted_count
…


Users (Master)

Posts (Transaction)

name

user_id

email

1:n

…

content
…

1:1

user_id
last_post_id
posted_count
…

1:1


Users (Master)

Posts (Transaction)

name

user_id

email

1:n

…

content
…

1:1

user_id

1:1

last_post_id

都度更新

posted_count

都度カウントアップ/カウントダウン

…


Users (Transaction)
name

Posts (Transaction)
1:1 or n

user_id

email

content

last_post_id

…

posted_count
…


どの様に更新処理を行うのか？
class User < ActiveRecord::Base
attr_accessible :last_post_id
belongs_to :last_post,
class_name: Post.to_s, foreign_key: :last_answer_id
def update_last_post(post = nil)
new_last_post = post || Post.last_post(self.id).first
self.update_column :last_post_id, new_last_post.try(:id)
end
・・・
end


どの様に更新処理を行うのか？
class Post < ActiveRecord::Base
after_save { user.update_last_post(self) }
・・・
end


注意点
• 冗長性が高いものであること
• 再集計が可能であること

総括

総括
• データストアの知識が重要
• プロファイルでボトルネックを探せるか
• メンテナンス性とトレードオフにならない
ように設計
• データベース設計を変更する勇気を持とう

総括

参考文献
漢のコンピュータ道
http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
http://nippondanji.blogspot.jp/2009/03/using-filesort.html
http://nippondanji.blogspot.jp/2012/10/mysql-56.html
MySQL Practive Wiki
http://www.mysqlpracticewiki.com/index.php/Extra_field
SH2の日記
http://d.hatena.ne.jp/sh2/20111217
十番目のムーサ
http://d.hatena.ne.jp/psappho/20111101/1320152348
MySQL SQLオプティマイザのコスト計算アルゴリズム
http://dbstudy.info/files/20120310/mysql_costcalc.pdf
MySQL Reference Manuals
http://dev.mysql.com/doc/

Ruby on Rails on MySQL チューニング入門

More Related Content

Ruby on Rails on MySQL チューニング入門