FAT47の底辺インフラ議事録

学んだことのメモ帳です

MySQL5.5で意図的にレプリケーションを遅延させるpt-slave-delay

サービス運用している時にレプリケーション遅延が発生してアプリケーションがバグった。
という事態にならないように、開発環境のDBは遅延を発生させてを遅延を考慮したアプリケーションが開発できるようにします。

※MySQL5.6の場合は↓をするだけです。

STOP SLAVE;
CHANGE MASTER TO MASTER_DELAY=360;
START SLAVE;

Percona Toolkit
今回はPercona Toolkitのpt-slave-delayを利用します。

Percona Toolkitのインストール

wget http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.5-2.noarch.rpm

※依存パッケージ
yum install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL
 
rpm -ivh percona-toolkit-2.2.4-1.noarch.rpm

利用方法

pt-slave-delay --delay 1m --interval 10s --socket=/var/lib/mysql/mysql.sock -u root localhost

中身は自動的に遅延秒数見ながらSTOP SLAVE、START SLAVEを繰り返しているだけです。

Ctrl+Cで実行終了すれば、勝手にSTOP SLAVEは解除されます。

    • delayが遅延させる秒数
    • intervalが遅延状態の確認間隔

その他は接続情報
詳細オプションはこちら
http://www.percona.com/doc/percona-toolkit/2.1/pt-slave-delay.html

該当レコードがなければINSERT、あったらUPDATEをするON DUPLICATE KEY UPDATE

普段はまったくSQL書かないんですが、ちょっと必要なタスクができたので書きました。
要件は下記。
・挿入データのPRIMARY KEYが重複していなければINSERT（新規追加）
・PRIMARY KEYが重複していればUPDATE（以下条件のときのみ）
・条件：挿入データの更新日付が、重複しているデータの更新日付よりも新しかった場合のみUPDATEかける

まずは例のSQLを。
例：

INSERT INTO user_login (
　user_id, 
  code,  
  last_update_datetime, 
  ins_datetime, 
)
VALUES (
  10001, 
  'game1', 
  "2013-03-28 21:12:34",  
  now()
) 
ON DUPLICATE KEY UPDATE
  user_id=values(user_id), 
  code=values(code), 
  last_update_datetime= IF(last_update_datetime < values(last_update_datetime), values(last_update_datetime),last_update_datetime);

＞・挿入データのPRIMARY KEYが重複していなければINSERT（新規追加）
＞・PRIMARY KEYが重複していればUPDATE
この要件はON DUPLICATE KEY UPDATEを利用すると満たせる。

＞・条件：挿入データの更新日付が、重複しているデータの更新日付よりも新しかった場合のみUPDATEかける
この要件は、
IF(last_update_datetime < values(last_update_datetime), values(last_update_datetime),last_update_datetime);

で満たせる。
values(hogehoge)という値の指定の仕方は
INSERT時に入るはずだった値を参照することができます。

つまり今回の場合だと、values(user_id)は10001であり、
values(last_update_datetime)は"2013-03-28 21:12:34"が入っているということです。

MySQL5.6からdumpしたデータをMySQL5.5に入れたらエラー出た

mysql5.6からdumpして

mysqldump -uroot --all-databases > /tmp/20131031.dump

mysql5.5にリストアしたら

mysql -uroot < /tmp/20131031.dump

STATS_PERSISTENT=0がなんとかかんとか！っていうエラーが出てしまう。
5.5にはこのパラメータがないので、外してあげます。

sed 's/STATS_PERSISTENT=0//' /tmp/20131031.dump > /tmp/20131031_mysql5.5.dump

あとはリストア

mysql -uroot < /tmp/20131031_mysql5.5.dump

【今更】MySQLのInnoDB利用時のウォームアップ方法


Webサービスを運用していると、メンテナンス時にDBサーバの再起動が必要になるケースがいつか出てきます。
その際、再起動後そのままの状態でDBサーバをサービスインしてしまうと障害が起きてしまうことがあるので、
その対処法をメモしておきます。（InnoDB利用時編）


■InnoDB buffer pool
MySQLのストレージエンジンであるInnoDBはinnodb buffer poolに書込/読込データを一時的に保管していて、
定期的にibdataに書き込みを行うことでI/O性能を向上させています。
my.cnfのinnodb_buffer_pool_sizeでバッファサイズの指定ができるので、搭載メモリの50%〜70%ぐらいを指定します。


■ウォームアップ対象の洗い出し
buffer poolのサイズは有限なので、できるだけ多い回数呼び出されてるテーブルやINDEXをバッファに乗せておくと良いです。
洗い出す方法はなんでもいいのですが、今回はslow logを使って一定時間に呼ばれたクエリから集計します。

※クエリ記録中は負荷が増大するので、DBへの参照頻度を下げたりするなどしましょう。
また記録が終わったら忘れずにすぐ元の設定に戻しましょう。

全クエリの記録（MySQL5.1以上のみ）
my.cnfでslow logが有効になっているか確認

slow_query_log = 1
slow_query_log_file = mysql-slow.log
long_query_time = 1

閾値を0秒にする（全クエリ記録）

mysql > set global long_query_time = 0;

適当な時間記録し続ける（1時間とか）

記録が終わったら戻す

mysql > set global long_query_time = 1;

mysqldumpslowを使って、呼び出し回数が多い順にソートする

mysqldumpslow -s c /var/lib/mysql/mysql-slow.log  | less

Count: 3034  Time=2.25s (6839s)  Lock=0.00s (0s)  Rows=70.2 (213050), hoge_user[hoge_user]@[10.*.*.*]
  select sum_c, total_sum_c from contest where c_id = N and user_id = N order by contest_id desc

Count: 1338  Time=2.46s (3287s)  Lock=0.00s (0s)  Rows=0.8 (1016), hoge_user[hoge_user]@[10.*.*.*]
  select gift_datetime from gift_history where user_id = N and type = N and i_id = N and a_type = N

■ウォームアップ用クエリの作成
呼び出し回数が多くてデータ件数が多いものを優先的にバッファの載せるようにしましょう。

テーブルデータ確認

show table status LIKE 'i_hogeuser' \G;
rowsとかを確認する。

テーブル構成確認

SHOW CREATE TABLE i_hogeuser \G;
→PRIMARY KEYとINDEX確認
 PRIMARY KEY (`user_id`,`p_type_id`),
  KEY `Index_i_hogeuser_request1` (`p_id`,`f_type`)

PRIMARY KEYつかって全データ読み込み

select count('user_id'),count('p_type_id') from i_hogeuser;

設定されたINDEX使って全読み込み

select count(p_id),count(f_type) from (select p_id,f_type from i_hogeuser order by p_id,f_type) as t1;

EXPLAINでINDEXが利用されているかチェック

mysql> explain select count(p_id),count(f_type) from (select p_id,f_type from i_hogeuser order by p_id,f_type) as t1;
+----+-------------+---------------------+-------+---------------+----------------------------+---------+------+-------+------------------------------+
| id | select_type | table               | type  | possible_keys | key                        | key_len | ref  | rows  | Extra                        |
+----+-------------+---------------------+-------+---------------+----------------------------+---------+------+-------+------------------------------+
|  1 | PRIMARY     | NULL                | NULL  | NULL          | NULL                       | NULL    | NULL |  NULL | Select tables optimized away |
|  2 | DERIVED     | i_hogeuser | index  | NULL  | Index_i_hogeuser_request1 | 16             | NULL    |      |  37527| Using index    
+----+-------------+---------------------+-------+---------------+----------------------------+---------+------+-------+------------------------------+
2 rows in set (0.02 sec)

といった手順でどんどんウォームアップクエリを作っていきます。



でも本当はめんどうくさいので、
PRIMARY KEYだけでクエリ生成してぶん投げます　（ゝω・）

select concat('select ','SUM(',column_name,') from ', table_name,';') from information_schema.statistics where TABLE_SCHEMA = 'スキーマ名';
+------------------------------------------------------------------------------------------------+
| select SUM(collect_id) from t_user;                                             　　　　　　　 |
| select SUM(history_id) from t_user_history;　　　　　　　　　　　　　　　　　　             　 |
| select SUM(vcomment_id) from t_comment;                                                        |
| select SUM(v_id) from t_visit;                                                                 |
| select SUM(touch_id) from touch;                                                               |
+------------------------------------------------------------------------------------------------+

稼働中のDBからmysqldumpとってスレーブをつくる

参照が外せるDBがない状態で、MySQLのスレーブを増やしたいときは
以下のコマンドでdumpを取得する。

mysqldump -uroot --single-transaction --master-data=2 --all-databases

\--single-transactionは
dump時にsnapショットを取得してそこかデータを取得するため、
ダンプ時のデータのトランザクションの一貫性を保つことができる。
その際、更新や参照にロックがかからない。

\--master-data=2は
CHANGE MASTER TOコマンドをdumpファイルの先頭に付加する。
–master-data=2を指定するとCHANGE MASTER TOがコメントアウトされた状態。
–maser-data=1と指定するとコメントアウトされずにダンプされる。

他サーバからのMySQLへの接続を一時的に遮断する

本番DBサーバ構築中で、万が一他のサーバから参照されたら困るような時には、
iptablesで他サーバからの接続を遮断しておきました。

vim /etc/sysconfig/iptables
*filter
:INPUT ACCEPT [0:0]
:FORWARD ACCEPT [0:0]
:OUTPUT ACCEPT [0:0]
-A INPUT -s 127.0.0.1 -p tcp -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j REJECT --reject-with icmp-port-unreachable
COMMIT


構築がすべて完了したらこのiptablesを外すのを忘れずに。

apacheのPUT,DELETEメソッドの制限

PUTとかDELETEとかまぁ普通使わないし、とりあえず防いでおこう。

<Limit PUT DELETE>
    Order deny,allow
    Deny from all
</Limit>

バーチャルホスト切ってたら、こんな感じにLocationの中へ

<Location />
  <Limit PUT DELETE>
    Order deny,allow
    Deny from all
  </Limit>
</Location>

確認方法
libwww-perlを使うのでインストール

yum -y install perl-libwww-perl

DELETEメソッドやってみる

GET -f -m DELETE http://hogehoge/hogehoge.html

アクセスログ確認

192.168.*.* - hoge [13/Jun/2013:09:56:39 +0900] "DELETE /hogehoge.html HTTP/1.1" 405 319

HTTPステータスコード405になっているので成功。
405, Method Not Allowed, 禁止メソッド