Tuningathon
1/19(土)のTuningathonに友人の@murahumと参加しました。うろ覚えですが詳細をまとめておきます。
お題
単一スレッドで5つのクエリを走らせ、どれだけ早く処理できるかを競いました。
状況確認
CPU、メモリともかなり潤沢。。。
CPU4コアのメモリ16GBでした(1.6GBと見間違えました
AWSの【M1 Extra Large】インスタンスが配布されていたようです。
最初にベンチを取ると140秒近くかかってました。
基本戦略の決定
・インデックスの最適化
⇛テーブル数の割に計測スクリプトで参照しているテーブルが少ないので「計測スクリプト対策INDEX」を揃えれば効果あるかも
・できるだけDBをメモリに載せる
⇛メモリがかなり余っていたのでBuffer poolにDBを全て載せれば早くなるだろうと推測し採用
innodbは業務でも使用したことがあるので、ベンチ取りながら知っているパラメータを試してみようと考えてました。
序盤
とりあえず思いつくinnoDBのパラメータを設定するとスコアが大幅に改善
140秒⇛18秒
次にINDEXを確認
参照しているテーブルはpage,revisionのみだったので確認すると色々INDEXが貼られていました。
相棒と話し合った結果、取り敢えず全部消して、1から貼り直そうという話になりました。
一旦SQLdumpを取得後、page,revisionテーブルのINDEXを主キー以外全て削除したところ、またまたスコアが改善
18秒⇛10秒
※このスコアで3回目の計測1位に!!!!(二人ともテンション上がりまくりでしたwww
中盤
・ここから実機のMySQL5.5を5.6にバージョンアップしようと試みるがソースコンパイル久方ぶり過ぎて不安になり断念。
・page,revisionに通常のINDEXを貼っていく。
が、貼れば貼るほど遅くなる。。。なにこれ・・・
MySQLは一つのクエリにつき、一つのINDEXしか使用できないので複数ある場合はあまり効率のよい動きはしないみたい。
そこで複合INDEXを採用してみることにして適当にぺたぺた
10秒⇛17秒に!!!
なぜにwwww
泣く泣くrevisionのINDEXのみ削除。pageのINDEXは上手く効くいていたようで
create index red_name_touched on page(page_is_redirect,page_namespace,page_touched);
17秒⇛9秒に
終盤
ここからは試行錯誤で色々やりましたが中々効果がでず。。。
運営からは6秒台とのアナウンスもあって焦る焦る。。。
効果が出たのは@murahumのINDEX
●revisionに以下のクエリで複合INDEXを追加
create index user_page_time on revision(rev_user,rev_page,rev_timestamp(6));
9秒⇛7秒に
※【SUBSTRING(rev_timestamp,1,6)】の部分で6バイト目までしか参照しないのでINDEXもそこで止めてます。これによりCardinality値を下げる効果ああります。
groupbyにINDEXが効くのは知識として知っていましたが、INDEXを6バイト目で止める部分は自分も考えつかなかった。。。さすがの@murahumクォリティ
@murahumの方で検証したところ、
create index user_page_time on revision(rev_user,rev_page,rev_timestamp);
でも測定値はあまり変わらなかったそうです。
以下によると一意なら参照も早くなったかもですが、timestampは一意ではなかったのであまり変化はなかった模様。勉強になりました!
http://dev.mysql.com/doc/refman/5.1/ja/create-index.html
>もしカラム内の名前の最初の10文字が違っていれば、このインデックスは name カラム全体から作成されたインデックスよりも遅くは無いはずです。また、部分的なカラムをインデックスに利用する事でインデックス ファイルを小さくする事ができるので、ディスクのスペースを節約し、 INSERT 操作を早くする事ができます。
この後は試行錯誤。。。。
MyISAMのパラメータを入れてストレージエンジンをMyISAMに変更
7秒⇛45秒(即切り戻し)
インデックスの探索タイプをBTREE⇛HASHへ変更
⇛なぜかUSINGパラメータが効かず断念
最後にmysqldにniceかけてログの出力を全て止めてタイムアップ
結果
なんと「7.32306790352」で総合三位でした!!!!
イケメン相棒のINDEXが素敵だったのが勝因でしたwwwあざすwww
そして、沖縄勢が10位以内を4つの枠を占める大健闘!
考察&反省点
1位との差は5つ目のクエリの部分だったと思います。
5つめのクエリをEXPLAINしたらtypeがALLになっていたのは気づいていたのですが全然手が思い浮かびませんでした。
インタビューでは主キーを変更したといっていたのでインスタンスが生きているうちにちょろっと調べてみたところ、rev_pageを主キーに指定することができました。
でした。まさかrev_pageが一意な値だったとは。。。。
そしてうっかりして主キーに変更後のベンチをとれなかった。。。
create index id on revision(rev_id); create index user_time on revision(rev_user,rev_timestamp); ALTER TABLE revision drop PRIMARY KEY; ALTER TABLE revision ADD PRIMARY KEY(rev_page);
・クエリ5
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | revision | ALL | NULL | NULL | NULL | NULL | 1620793 | Using temporary; Using filesort |
1 | SIMPLE | page | eq_ref | PRIMARY,red_name_touched | PRIMARY | 4 | wikipedia.revision.rev_page | 1 | Using where |
・クエリ5(主キー変更後)
id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
1 | SIMPLE | page | ref | PRIMARY,red_name_touched | red_name_touched | 5 | const,const | 822882 | Using index; Using temporary; Using filesort |
1 | SIMPLE | revision | eq_ref | PRIMARY | PRIMARY | 4 | wikipedia.page.page_id | 1 |
my.cnfは以下のとおり
DB自体は800MBほどしか容量がありませんでしたが、最終的にbuffer poolは5GB割り当て
その他のbuffer_sizeたちはあまり多く割り当てすぎると若干遅くなった気がしたので
以下のようにしてます。一応、単一スレッドの処理速度を計る今回の競技には全く関係ありませんがdefaultのmax_connections150きても耐えうる感じにはしてみました。
正直buffer pool以外は効果薄だったかも。
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Global Area innodb_file_per_table innodb_buffer_pool_size=5120M innodb_log_buffer_size=8M innodb_log_file_size=128M innodb_flush_method=O_DIRECT query_cache_size=100M table_open_cache=1024 # Thread Area sort_buffer_size=16M read_rnd_buffer_size=16M max_allowed_packet=16M join_buffer_size=8M key_buffer_size=8M [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid
ということで大分ふわふわした道筋でしたが、3位だったので大満足です!
運営の方お疲れ様でした。次回も参加したいと思いますのでよろしくお願いいたします。
追記:
運営よりAMIが公開されたので主キーを張り替えて試してみました。
結果「6.32319498062」で優勝には届かず。。。
他にオーバーヘッドがないか確認し、pageのINDEXを以下のように張り替えたら「5.8722589016」がでました。満足。
Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
page | 0 | PRIMARY | 1 | page_id | A | 1714494 | NULL | NULL | BTREE | |||
page | 1 | name_red | 1 | page_namespace | A | 18 | NULL | NULL | BTREE | |||
page | 1 | name_red | 2 | page_is_redirect | A | 18 | NULL | NULL | BTREE | |||
page | 1 | touched | 1 | page_touched | A | 206 | NULL | NULL | BTREE |
論理ボリュームの削除&容量の拡張
CentOSをデフォルトのままインストールしたところ/home200GBも割り当てられている。。。。
サーバー用途の為/homeでそんなに容量を食う予定はないので削除して/rootに割り当てます。
※結構雑に作業しているので実施する際は調査の上実施することを推奨します。
現状確認
[root@hoge zabbix]# df -h Filesystem Size Used Avail Use% マウント位置 /dev/mapper/vg_hoge-lv_root 50G 4.9G 44G 10% / tmpfs 1.9G 148K 1.9G 1% /dev/shm /dev/sda1 485M 64M 396M 14% /boot /dev/mapper/vg_hoge-lv_home 222G 200M 211G 1% /home
物理ボリュームの確認
[root@hoge zabbix]# pvdisplay -C PV VG Fmt Attr PSize PFree /dev/sda2 vg_hoge lvm2 a-- 278.88g 0
ボリュームグループの確認
[root@hoge zabbix]# vgdisplay -C VG #PV #LV #SN Attr VSize VFree vg_hoge 1 3 0 wz--n- 278.88g 0
論理ボリュームの確認
[root@hoge zabbix]# lvdisplay -C LV VG Attr LSize Pool Origin Data% Move Log Copy% Convert lv_home vg_hoge -wi-ao-- 225.05g ←こいつを消して lv_root vg_hoge -wi-ao-- 50.00g ←こいつにあまった容量を割り当てます lv_swap vg_hoge -wi-ao-- 3.83g
/home/以下のデータを退避
[root@hoge ~]# cp -a /home /tmp/
※論理ボリュームを削除すると配下のファイルは吹っ飛ぶのでバックアップ。
論理ボリューム/homeの削除
/homeを使用しているプロセスの確認
[root@hoge zabbix]# lsof |grep home ~~~gnome関連&VNC関連など大量に出力~~~ うおっ ***ランレベル切り替え >|sh| [root@hoge zabbix]# init 3 ***VNCを停止 >|sh| [root@hoge zabbix]# /etc/init.d/vncserver stop VNC サーバー を停止中: 2:ochadmin [ OK ]
再確認
[root@hoge zabbix]# lsof |grep home bash 30269 ochadmin cwd DIR 253,2 4096 7602177 /home/ochadmin su 30293 root cwd DIR 253,2 4096 7602177 /home/ochadmin
ぬは、rootでログインして回避しよう。
[root@hoge zabbix]# vim /etc/ssh/sshd_config - PermitRootLogin no + PermitRootLogin yes [root@hoge zabbix]# /etc/init.d/sshd restart sshd を停止中: [ OK ] sshd を起動中: [ OK ]
再々確認
[root@hoge ~]# lsof |grep home [root@hoge ~]#
うし。いなくなった。
/homeをアンマウント
[root@hoge ~]# umount /dev/mapper/vg_hoge-lv_home
論理ボリューム削除
[root@hoge ~]# lvdisplay -C LV VG Attr LSize Pool Origin Data% Move Log Copy% Convert lv_home vg_hoge -wi-a--- 225.05g lv_root vg_hoge -wi-ao-- 50.00g lv_swap vg_hoge -wi-ao-- 3.83g [root@hoge ~]# lvremove /dev/mapper/ control vg_hoge-lv_home vg_hoge-lv_root vg_hoge-lv_swap [root@hoge ~]# lvremove /dev/mapper/vg_hoge-lv_home Do you really want to remove active logical volume lv_home? [y/n]: y Logical volume "lv_home" successfully removed [root@hoge ~]# lvdisplay -C LV VG Attr LSize Pool Origin Data% Move Log Copy% Convert lv_root vg_hoge -wi-ao-- 50.00g lv_swap vg_hoge -wi-ao-- 3.83g
余った容量の再割り当て
[root@hoge ~]# lvextend -l +100%FREE /dev/mapper/vg_hoge-lv_root Extending logical volume lv_root to 275.05 GiB Logical volume lv_root successfully resized [root@hoge ~]# lvdisplay -C LV VG Attr LSize Pool Origin Data% Move Log Copy% Convert lv_root vg_hoge -wi-ao-- 275.05g lv_swap vg_hoge -wi-ao-- 3.83g
ファイルシステムの拡張
※本環境はファイルシステムがext4なのでオンラインでのリサイズが可能です。
オンラインリサイズ対応:ext3,4
オンラインリサイズ非対応:ext2
[root@hoge ~]# resize2fs /dev/mapper/vg_hoge-lv_root resize2fs 1.41.12 (17-May-2010) Filesystem at /dev/mapper/vg_hoge-lv_root is mounted on /; on-line resizing required old desc_blocks = 4, new_desc_blocks = 18 Performing an on-line resize of /dev/mapper/vg_hoge-lv_root to 72101888 (4k) blocks. The filesystem on /dev/mapper/vg_hoge-lv_root is now 72101888 blocks long. [root@hoge ~]# [root@hoge ~]# df -h Filesystem Size Used Avail Use% マウント位置 /dev/mapper/vg_hoge-lv_root 271G 4.9G 264G 2% / tmpfs 1.9G 112K 1.9G 1% /dev/shm /dev/sda1 485M 64M 396M 14% /boot
/home配下のデータを設置
[root@hoge ~]# cp -a /tmp/home/* /home/
FSTabの編集
[root@hoge home]# vim /etc/fstab - /dev/mapper/vg_hoge-lv_home /home ext4 defaults 1 2
※FSTabを編集し忘れると、再起動時に『/homeねーぞ!』となり、ディスク読み取り専用の状態で起動するか確認されます。
その場合は
mount -o remount,rw /dev/mapper/vg_hoge-lv_root /
でマウントしなおしてfstabを編集後、再起動します。
VMESXiのパフォーマンス値をとってみる
Rubyの勉強がてらVMAPIのRuby用ラッパー『RbVmomi』を利用して『VMware vSphere Client』で閲覧できるパフォーマンスチャートの値を取得するスクリプトを作ってみました。
結構ふわふわな知識で書いてるので間違ってる箇所があるかも
●パフォーマンスチャート
Zabbix連携
id:taishinさんの以下のエントリーでzabbixサーバのローカルに取得した値を
保存するという上手いやり方を紹介されていたので真似てみました。
http://taishin.github.com/blog/2012/11/11/zabbixde/
出力形式は合わせたので、zabbix_agentd.d以下にユーザーパラメーター用のファイルを作成し、zabbix上でテンプレートを用意することで監視が行えます。
調査の流れ
ホストOS&ゲストOS情報の取得や電源ON/OFFメソッドの実行等のサンプルコードはrbvmomiのgithub(https://github.com/rlane/rbvmomi)にありますが、パフォーマンス関連の情報はないのでとりあえずVMAPIのドキュメントを漁ったところ『QueryPerf』メソッドの説明で以下の文面を発見
An array of PerfQuerySpec objects. Each PerfQuerySpec object specifies a managed object reference for an entity, plus optional criteria for filtering results. Only metrics for entities that can be resolved and that are valid performance providers are returned in any result.
Each PerfQuerySpec object in the array submitted in this operation can query for different metrics. Or, select all types of statistics for a single managed entity.
managed Objectなるものとフィルタ条件を渡して実行するっぽいことが書いてあります。
このメソッドを実行している箇所をRbVmomiのソースから探してみます。
その前に。。。Managed Objectとは???
VMAPI上で管理しているものほぼ全てのオブジェクトっぽいです。
http://www.vmware.com/support/developer/vc-sdk/visdk25pubs/ReferenceGuide/vim.ManagedEntity.html
Managed Objectを渡すことで対象のentity(実体)を特定するぜーといった感じ
サンプルコードを見ると以下の様な感じでManaged Objectの配列がとれるみたい。
dc.hostFolder.children.first.host.grep(RbVmomi::VIM::HostSystem) # => [HostSystem("ha-host")]
VMマシンが2台存在すれば以下のような感じ。
dc.vmFolder.childEntity.grep(RbVmomi::VIM::VirtualMachine) # =>[VirtualMachine("1"), VirtualMachine("2")]
一個ずつループで処理
dc.vmFolder.childEntity.grep(RbVmomi::VIM::VirtualMachine).each |x| 〜ほげほげ end
調査続き
RbVmomiのドキュメントを漁っているとPerfQueryを叩いている箇所を発見!(^ω^)
http://www.rdoc.info/github/vmware/rbvmomi/master/RbVmomi/VIM/PerformanceManager:retrieve_stats
def retrieve_stats objects, metrics, opts = {}
渡す引数は objects,metrics,optsですね。
optsは指定しないでもいいようなのでhostのManagedObjectとmetrics(取得する項目)を渡してみる。
metricsではvSphere Clientでいうチャートの項目を指定します。
※ちなみにチャート設定でチェックが入ってなくてもAPIで値は取得できます。
dc.vmFolder.childEntity.grep(RbVmomi::VIM::VirtualMachine).each |x| vim.serviceContent.perfManager.retrieve_stats(x, "cpu.usage") end
んでこんなのが取れます。
{HostSystem("ha-host")=> {:metrics=>{"cpu.usagemhz"=>[147]}, :sampleInfo=> [PerfSampleInfo( dynamicProperty: [], interval: 20, timestamp: Fri Nov 30 11:03:20 UTC 2012 )]}}
FuelPHP + Gitで幸せになれた。
概要
背景
FuelPHPをはじめるにあたり、ソースコードをGitで管理しようと思い立ち、敢行。
Gitについては、ちょっと調べては『どうせ一人で開発してるし不要かなー』と挫折するというヘタレ具合だったので少しまとめてみました。
環境
- ローカル:mac
- リモート:さくらVPS
※gitのバージョンは両方1.7.1です。
リポジトリ作成〜ローカルへコピー(clone)
リモートでの作業
FuelPHPでプロジェクトを作成
# oil create hogehoge # cd hogehoge
gitの設定&Official-PJで管理したいディレクトリを一旦削除
# rm -rf .git .gitmodules *.md docs fuel/core fuel/packages
リポジトリの作成
# git init
Official-PJで管理したいディレクトリをサブモジュールとして登録
# git submodule add git://github.com/fuel/core.git fuel/core # git submodule add git://github.com/fuel/oil.git fuel/packages/oil # git submodule add git://github.com/fuel/auth.git fuel/packages/auth # git submodule add git://github.com/fuel/parser.git fuel/packages/parser # git submodule add git://github.com/fuel/orm.git fuel/packages/orm # git submodule add git://github.com/fuel/email.git fuel/packages/email # git submodule add git://github.com/fuel/docs.git docs
※これでFuelPHP関連のファイルはOfficial-PJで管理することができる。
アップデートはgit submodule foreach 'git pull'で一撃。幸せ。
チェックアウト
# git submodule foreach 'git checkout 1.4/master'
※foreachでサブモジュール全てに対して''内のコマンドを実行してくれる。
ローカルリポジトリにコミット
# git add . # git commit -m "first commit"
これでリモート上にリポジトリが作られました。
ローカルでの作業
# git clone --recursive root@49.212.164.105:~/hogehoge/
※--recursiveでサブモジュールごと引っ張ってこれました。
これでローカルにクローンが作成されました。
ローカルで編集〜リモートへアップ
参考
以下を参考にさせて頂きました。ありがとうございました!
http://d.hatena.ne.jp/Kenji_s/20111202/1322783183http://transitive.info/