読者です 読者をやめる 読者になる 読者になる

X260のGentooにSoftEther VPN Clientをインストールする

メモです

手順

  1. softether-vpnをlayman経由でインストールする
  2. vpnclientを起動する
  3. vpncmdで設定する
  4. 接続してrouteを書き換える

1. softether-vpnをlayman経由でインストールする

portage/softether-vpn-4.22-r9634.ebuild at master · yTakatsukasa/portage · GitHub をインストールするため、laymanにoverlayを追加する。

$ sudo layman -a yutetsu
$ sudo eix-update
$ sudo emerge -qa --autounmask-write "=softether-vpn-4.22-r9634"
$ dispatch-conf
$ sudo emerge -qa --autounmask-write "=softether-vpn-4.22-r9634"

# チェック
$ sudo /opt/vpnclient/vpnclient 
SoftEther VPN Client service program
Copyright (c) SoftEther VPN Project. All Rights Reserved.

vpnclient command usage:
 vpnclient start  - Start the SoftEther VPN Client service.
 vpnclient stop   - Stop the SoftEther VPN Client service if the service has been already started.

2. vpnclientを起動する

$ sudo /opt/vpnclient/vpnclient start

3. vpncmdで設定する

$ sudo /opt/vpnclient/vpncmd
vpncmd command - SoftEther VPN Command Line Management Utility
SoftEther VPN Command Line Management Utility (vpncmd command)
Version 4.22 Build 9634   (English)
Compiled 2016/11/27 15:23:56 by yagi at pc30
Copyright (c) SoftEther VPN Project. All Rights Reserved.

By using vpncmd program, the following can be achieved. 

1. Management of VPN Server or VPN Bridge 
2. Management of VPN Client
3. Use of VPN Tools (certificate creation and Network Traffic Speed Test Tool)

Select 1, 2 or 3: 2

Specify the host name or IP address of the computer that the destination VPN Client is operating on. 
If nothing is input and Enter is pressed, connection will be made to localhost (this computer).
Hostname of IP Address of Destination: 

Connected to VPN Client "localhost".

# tun0という名前の仮想Nicを作成
VPN Client>NicCreate tun0

VPN Client>NicEnable tun0

# VPN Serverに接続するアカウントの追加
VPN Client>AccountCreate
AccountCreate command - Create New VPN Connection Setting
Name of VPN Connection Setting: vpn # アカウント設定名(なんでもよい)

Destination VPN Server Host Name and Port Number: 1.2.3.4:3443 # VPN ServerのIPアドレス:ポート

Destination Virtual Hub Name: VPNHUB # VPN Server側のHub名

Connecting User Name: vpn_user # VPN Serverに接続するユーザ名

Used Virtual Network Adapter Name: tun0 # さっき作った仮想Nic名

# パスワード設定
VPN Client>AccountPasswordSet vpn # アカウント設定名
パスワード入れる

# VPN Serverに接続してみる
VPN Client>AccountConnect vpn # アカウント設定名

# 確認
VPN Client>AccountList
AccountList command - Get List of VPN Connection Settings
Item                        |Value
----------------------------+---------------------------------------------
VPN Connection Setting Name |vpn_user
Status                      |Connected
VPN Server Hostname         |1.2.3.4:443 (Direct TCP/IP Connection)
Virtual Hub                 |VPNHUB
Virtual Network Adapter Name|tun0
The command completed successfully.

# vpnclient起動時に接続するように設定
VPN Client>AccountStartupSet vpn

# 終了
VPN Client>exit

4. 接続してrouteを書き換える

$ sudo ifconfig
#=> vpn_tun0が追加されているはず

# 今のrouteを確認しておく
$ sudo route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         10.xxx.yyy.254  0.0.0.0         UG    2008   0        0 wlp4s0
10.xxx.yyy.0    0.0.0.0         255.255.255.0   U     2008   0        0 wlp4s0

# static routeを追加しておく
$ sudo route add -host 1.2.3.4 gw 10.xxx.yyy.254
$ sudo route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         10.xxx.yyy.254  0.0.0.0         UG    2008   0        0 wlp4s0
10.xxx.yyy.0    0.0.0.0         255.255.255.0   U     2008   0        0 wlp4s0
1.2.3.4         10.xxx.yyy.254   255.255.255.255 UGH   0      0        0 wlp4s0

# vpn_tun0にDHCPでアドレスを降らせる
$ sudo dhclient vpn_tun0

# vpn_tun0のエントリが増える
$ sudo route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         1.2.3.1         0.0.0.0         UG    0      0        0 vpn_tun0
0.0.0.0         10.xxx.yyy.254  0.0.0.0         UG    2008   0        0 wlp4s0
10.xxx.yyy.0    0.0.0.0         255.255.255.0   U     2008   0        0 wlp4s0
1.2.3.4         10.xxx.yyy.254   255.255.255.255 UGH   0      0        0 wlp4s0
1.2.3.0         0.0.0.0         255.255.255.0   U     0      0        0 vpn_tun0

# 0.0.0.0(default)のエントリを一旦どちらも消す
$ sudo route del default
$ sudo route del default
$ sudo route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
10.xxx.yyy.0    0.0.0.0         255.255.255.0   U     2008   0        0 wlp4s0
1.2.3.4         10.xxx.yyy.254   255.255.255.255 UGH   0      0        0 wlp4s0
1.2.3.0         0.0.0.0         255.255.255.0   U     0      0        0 vpn_tun0

# 0.0.0.0(default)のエントリをmetricを設定しつつ追加する
$ sudo route add default gw 1.2.3.1 dev vpn_tun0 metric 2008
$ sudo route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         1.2.3.1         0.0.0.0         UG    2008   0        0 vpn_tun0
10.xxx.yyy.0    0.0.0.0         255.255.255.0   U     2008   0        0 wlp4s0
1.2.3.4         10.xxx.yyy.254   255.255.255.255 UGH   0      0        0 wlp4s0
1.2.3.0         0.0.0.0         255.255.255.0   U     0      0        0 vpn_tun0

こんな感じで動いた

VPN接続をやめるとき

$ sudo route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         1.2.3.1         0.0.0.0         UG    2008   0        0 vpn_tun0
10.xxx.yyy.0    0.0.0.0         255.255.255.0   U     2008   0        0 wlp4s0
1.2.3.4         10.xxx.yyy.254   255.255.255.255 UGH   0      0        0 wlp4s0
1.2.3.0         0.0.0.0         255.255.255.0   U     0      0        0 vpn_tun0

# vpn_tun0のエントリと、途中で追加したstatic routeを消す
$ sudo route del default
$ sudo route del 1.2.3.4

# vpnclientを停止させる
$ sudo /opt/vpnclient/vpnclient stop

# routeを確認
$ sudo route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
10.xxx.yyy.0    0.0.0.0         255.255.255.0   U     2008   0        0 wlp4s0

# 元々のエントリを追加する
$ sudo route add default gw 10.xxx.yyy.254 dev wlp4s0 metric 2008
$ sudo route -n
Kernel IP routing table
Destination     Gateway         Genmask         Flags Metric Ref    Use Iface
0.0.0.0         10.xxx.yyy.254  0.0.0.0         UG    2008   0        0 wlp4s0
10.xxx.yyy.0    0.0.0.0         255.255.255.0   U     2008   0        0 wlp4s0

うまく復帰できない場合はむりやりsudo rc-service net.wlp4s0 restartで再起動させた

X260にGentooを入れた

ここしばらくはMacBookAir 13inchをフツーに使ってましたが、SSDが128GBだったので足りなくてきて厳しくなったり、Windowsで動くようなゲームをやってみたいなと思ったり、新しいMacBookProは端子がUSB type-Cしかなくて不便そうだと思ったりしたのと、この記事( MacBook Proを捨ててThinkpad T460sを買ってgentooを入れた - joker1007の日記 )を見て #gentooinstallbattle の懐かしさを感じてしまったのでX260にWindowsGentooを入れることにしました。

MacBookAirを使う前はX201sにGentooを入れて使ってたのでそんなに苦労することは無いだろうと思ってましたが、BIOSUEFIになって設定が分からなかったりと何度かつまづきました。

やっとこインストールできて環境が整ったので、いくつかメモを書き残します。

0. バックアップ作成

今回はWindowsも残してデュアルブートしたかったので、ミスってWindowsが消え飛んでしまった時用にバックアップを用意しました。

といっても、普通のUSBメモリに「回復ドライブの作成」を行っただけです。

1. Gentoo Live USBの作成

https://www.gentoo.org/downloads/ から、amd64版の「Hybrid ISO (LiveDVD)」をダウンロードしました。

本来は「Minimal Installation CD」で十分なのですが、久しぶりということもあり最悪インストール中にLiveDVDに入っているWebブラウザで検索もできると思ったのでLiveDVDにしました。

別のUSBメモリにこのLiveDVDのイメージ書き込んで、再起動します。

2. インストールする

GentooのHandbook( https://wiki.gentoo.org/wiki/Handbook:Main_Page )を読みながら進めていけば良い、、、はずですが何度かハマりました。

https://wiki.gentoo.org/wiki/Handbook:AMD64/Full/Installation/ja このページに、インストールの全手順が1ページにまとめられているので便利です。

2.1. ネットワーク

いきなり無線を使うことはかなり難しいだろうと思ってたので、おとなしく有線LANにつなぎました。

普通にDHCPでIPが降ってきて繋がる感じでした。

2.2. パーティション

元々は、

  • /dev/sda1 に EFI system partition
  • /dev/sda2 に Microsoft reserved parition
  • /dev/sda3 に Basic data partition (WindowsのCドライブ)

が入っていたので、/dev/sda3を縮小させつつ、そこに

を追加して、最終的に次のようになりました。

x260 ~ # parted
GNU Parted 3.2
Using /dev/sda
Welcome to GNU Parted! Type 'help' to view a list of commands.
(parted) print                                                            
Model: ATA SAMSUNG MZ7LN512 (scsi)
Disk /dev/sda: 512GB
Sector size (logical/physical): 512B/512B
Partition Table: gpt
Disk Flags:

Number  Start   End    Size    File system     Name                          Flags
 1      1049kB  274MB  273MB   fat32           EFI system partition          boot, hidden, esp
 2      274MB   290MB  16.8MB                  Microsoft reserved partition  msftres
 3      290MB   336GB  336GB   ntfs            Basic data partition          msftdata
 4      336GB   399GB  62.9GB  ntfs            Share data partition          msftdata
 5      399GB   504GB  105GB   btrfs           Linux data partition
 6      504GB   512GB  8503MB  linux-swap(v1)  Linux swap partition

2.3. make.conf

stage3 tarballのダウンロード、展開だのが終わったら、/etc/portage/make.confの編集です。 (前に入れた時は/etc/make.confだった気がしましたが、結構前に変更されていたらしい。)

$ cat /etc/portage/make.conf
CFLAGS="-march=native -O2 -pipe"
CXXFLAGS="${CFLAGS}"
CHOST="x86_64-pc-linux-gnu"
CPU_FLAGS_X86="mmx mmxext sse sse2 sse3"
USE="X aac acpi alsa ao bindist bluetooth bzip2 \
cairo cdda cdr cjk cli cracklib crypt curl cxx \
dbus directfb dri dvd dvdr exif fbcon ffmpeg flac \
gdbm gif gimp git gnultls gpm graphviz gtk gzip \
iconv imagemagick inotify ipv6 javascript jit jpeg jpeg2k \
lame lcms libffi libnotify lua luajit lzma lzo \
mmx mmxext modules multilib mp3 mp4 mpeg multilib mysql \
ncurses networkmanager nls nptl ocaml opengl openmp oss \
pcre pdf png posix pulseaudio python raw readline ruby \
sasl seccomp session sound sse sse2 sse3 ssl svg syslog \
tiff udev unicode usb videos vim-syntax vnc vorbis \
webp wifi x264 xattr xcb xml zlib zsh-completion"
PORTDIR="/usr/portage"
DISTDIR="${PORTDIR}/distfiles"
PKGDIR="${PORTDIR}/packages"
MAKEOPTS="-j5"
LINGUAS="en ja"
GENTOO_MIRRORS="rsync://ftp.iij.ad.jp/pub/linux/gentoo/"
GRUB_PLATFORMS="efi-64"

2.4. カーネルの設定

インストールした当時は4.4.39がmaskされていない最新のカーネルだったのですが、どうせ近いうちにアップデートするだろうと思ったのでunmaskして4.9.0にしました。

.configはこんな感じです。

https://gist.github.com/rkmathi/0d5778723474e35ed33eed316d2345f7

ミニマムの限界を狙う元気はなかったので、わりとおおらかな感じになっています。

make && make modules_install && make installしたあとに、genkernel --install initramfsしておきました。

2.5. fstab & grub

fstabはこうなりました。

/dev/sda1       /boot/efi       vfat    defaults,discard,noatime        0 2
/dev/sda5       /               btrfs   defaults,discard,noatime        0 1
/dev/sda6       none            swap    sw                              0 0

「2.2. パーティション」で書いた通りの内容です。

grubのインストール(あとカーネルをアップデートした時に打つやつ)は

$ grub-install --target=x86_64-efi --efi-directory=/boot/efi
$ grub-mkconfig -o /boot/grub/grub.cfg

途中、何度か起動したのにディスプレイが映らないだの、「Loading initial ramdisk...」で止まってしまうだのしましたが、カーネルコンフィグでi915の設定が抜けていたりframe bufferの設定が抜けたりが原因でした。

3. インストール後の環境構築

  • ターミナルはx11-terms/sakura
  • ウィンドウマネージャはx11-wm/awesome ( https://awesomewm.org/ )
  • 日本語入力はapp-i18n/mozc
  • Webブラウザはwww-client/google-chrome-beta
  • (一応)GUIのエディタはapp-editors/atom
  • GUIのファイラはx11-misc/xfe
  • 無線はnet-wireless/wpa_supplicant

という感じで諸々インストールしました。

3.1. xorgの設定

とりあえず「A」キーの隣のCapsLockはCtrlになってもらいました。

x260 ~ # cat /etc/X11/xorg.conf.d/10capslock.conf 
Section "InputClass"
        Identifier      "Keyboard Setting"
        MatchIsKeyboard "on"
        # CapsLock -> Ctrl
        Option  "XkbOptions"    "ctrl:nocaps"
EndSection

ThinkpadなのでTrackPointとタッチパッドの設定が必要だったので、ArchWikiだのを見ながら設定しました。

x260 ~ # cat /etc/X11/xorg.conf.d/40synaptics.conf 
Section "InputClass"
        Identifier      "Touchpad"
        Driver  "synaptics"
        MatchProduct    "SynPS/2 Synaptics TouchPad"
        MatchDevicePath "/dev/input/event*"
        Option  "MaxTapTime"    "150"
        Option  "TapButton1"    "1"
        Option  "TapButton2"    "3"
        Option  "VertTwoFingerScroll"   "on"
        Option  "VertScrollDelta"       "-100"
        Option  "HorizTwoFingerScroll"  "on"
        Option  "HorizScrollDelta"      "-100"
        Option  "MinSpeed"      "4"
        Option  "MaxSpeed"      "5"
        Option  "AccelFactor"   "0.005"
        Option  "CoastingSpeed" "0"
        Option  "CoastingFriction"      "0"
        Option  "PalmDetect"    "on"
EndSection
x260 ~ # cat /etc/X11/xorg.conf.d/30trackpoint.conf 
Section "InputClass"
        Identifier      "Trackpoint Wheel Emulation"
        MatchProduct    "TPPS/2 IBM TrackPoint"
        MatchDevicePath "/dev/input/event*"
        Option  "EmulateWheel"          "1"
        Option  "EmulateWheelButton"    "2"
        Option  "Emulate3Buttons"       "0"
        Option  "EmulateWheelTimeout"   "100"
        Option  "XAxisMapping"          "6 7"
        Option  "YAxisMapping"          "4 5"
EndSection

ちなみにTrackPointの速設定がxorg.confにうまく書けなかったので、それだけは.xinitrcに書いてしまっています。

xinput set-prop "TPPS/2 IBM TrackPoint" "Device Accel Constant Deceleration" 0.2

3.2. 過充電を防ぐやつを導入

X260には内蔵バッテリーと外付けバッテリーの2つが付いているので、 https://github.com/teleshoes/tpacpi-bat これを入れて充電し過ぎないようにする設定を入れました。

# 内蔵バッテリーは75%を切ったら充電開始し、85%を超えたら充電停止する。
$ sudo ./tpacpi-bat -g ST 1
75 (relative percent)
$ sudo ./tpacpi-bat -g SP 1
85 (relative percent)

# 外付けバッテリーは85%を切ったら充電開始し、95%を切ったら充電停止する。
$ sudo ./tpacpi-bat -g ST 2
85 (relative percent)
$ sudo ./tpacpi-bat -g SP 2u
95 (relative percent)

3.3. powertopの導入

powertopをインストールして、思考停止でTunablesのすべての設定をGoodにしました。

SlackだのChromeだのを起動した状態の様子を比べると、消費電力が半分くらいになっているので効果がありそうです。

BEFORE

The battery reports a discharge rate of 9.27 W

AFTER

The battery reports a discharge rate of 4.93 W

3.4. 画面を暗くする

標準の設定だと、画面がめちゃくちゃ明るいので

echo 21 > /sys/class/backlight/intel_backlight/brightness

とすることで画面を暗くしました。

4. 既知の問題

4.1. 無線ドライバの読み込みが必ず失敗する

こんな感じで

x260 ~ # dmesg | grep 'iwl'
[    8.623480] iwl4965: Intel(R) Wireless WiFi 4965 driver for Linux, in-tree:
[    8.623481] iwl4965: Copyright(c) 2003-2011 Intel Corporation
[    8.623527] iwl3945: Intel(R) PRO/Wireless 3945ABG/BG Network Connection driver for Linux, in-tree:s
[    8.623528] iwl3945: Copyright(c) 2003-2011 Intel Corporation
[    8.623957] iwlwifi 0000:04:00.0: enabling device (0000 -> 0002)
[    8.628062] iwlwifi 0000:04:00.0: Direct firmware load for iwlwifi-8000C-26.ucode failed with error -2
[    8.628065] iwlwifi 0000:04:00.0: Falling back to user helper
[   70.675492] iwlwifi 0000:04:00.0: loaded firmware version 22.361476.0 op_mode iwlmvm
[   70.675505] iwlwifi 0000:04:00.0: Detected Intel(R) Dual Band Wireless AC 8260, REV=0x208
[   70.677519] iwlwifi 0000:04:00.0: L1 Enabled - LTR Enabled
[   70.678394] iwlwifi 0000:04:00.0: L1 Enabled - LTR Enabled
[   70.814110] iwlwifi 0000:04:00.0 wlp4s0: renamed from wlan0

起動直後に存在しないはずのiwlwifi-8000C-26.ucodeを探しにいって失敗してしまいます。

/lib/firmware以下にあるドライバは

x260 ~ # ls -l /lib/firmware/iwlwifi-8000C-*
-rw-r--r-- 1 root root 1745176 Jan  1 19:57 /lib/firmware/iwlwifi-8000C-13.ucode
-rw-r--r-- 1 root root 2351636 Jan  1 19:57 /lib/firmware/iwlwifi-8000C-16.ucode
-rw-r--r-- 1 root root 2394060 Jan  1 19:57 /lib/firmware/iwlwifi-8000C-21.ucode
-rw-r--r-- 1 root root 2120860 Jan  1 19:57 /lib/firmware/iwlwifi-8000C-22.ucode

*-22.ucodeが最新なので、これを見つけるまで62秒毎に26→25→24→23→22とリトライがかかってしまっていました。

これだと起動してから無線が使えるまで5分位待たないといけないので、雑にln -s iwlwifi-8000C-22.ucode iwlwifi-8000C-25.ucodeとリンクを貼ってみたところ、一応(70秒は待たないといけないが)繋がるようになったので良しとしています。


こんな感じで仕上がりました。

一応Windowsは起動するこできたことが確認できたので、デュアルブートもうまくできているようです。

git worktreeでGitHub Pagesの管理を楽にする

git github

書いてから気づいた...

イマドキのGitHubはgh-pagesブランチだけでなく、 masterブランチの/docsディレクトリ以下をドキュメントとする 機能があった。

f:id:rkmathii:20161106133450p:plain

git worktreeを使わずとも最初からこれで良かった…( ˘ω˘)


やりたかったこと

  • ドキュメントの公開をGitHub Pagesで行おうとした際に、イチイチ
    • masterブランチからgh-pagesブランチに移動して、
    • gh-pagesブランチでドキュメント書いて、
    • git commit & git pushして、
  • という手順が面倒だったのでどうにかしたかった。

git v2.5以降が導入されているならばgit worktreeというコマンドを使うことで「特定のディレクトリ以下を別のブランチとして扱う」ことができるので、これで「masterブランチの/documentsディレクトリ以下をgh-pagesブランチとして扱う」ということができるようになる。

手順例

# よしなにリポジトリを準備する
$ git init git-worktree-gh-pages
$ touch README.md && git add README.md && git commit -m 'Initial commit'

# gh-pagesブランチをチェックアウト
$ git checkout -b gh-pages

# masterブランチに含まれていたファイルを消しつつ、ドキュメント用のindex.htmlを追加してコミット
$ git rm *
$ touch index.html
$ git add index.html && git commit -m 'Add index.html'

# masterブランチをチェックアウトして、/documentsディレクトリをgh-pagesとして使うようにする
$ git checkuot master
$ git worktree add documents gh-pages
$ git add documents && git commit -m 'Add worktree documents (gh-pages branch)'

こうすることで、documentsディレクトリ以下に移動したときは自動的にgh-pagesブランチに対して操作していることになるので、gh-pagesブランチをチェックアウトすることなくそのままdocumentsディレクトリ以下に対してドキュメントを生成することでGitHub Pagesを作ることができる。

MySQLの範囲検索でレコードが多すぎるとフルテーブルスキャンになってしまう現象

mysql innodb

created_atが何月何日から何月何日のレコードをSELECTする場合に、ある量を超えるとEXPLAIN上ではフルテーブルスキャンの判定になってしまったのでどうしてなのか実験してみました。

実験は次のような環境で行いました。

OS: OS X El Capitan 10.11.3
CPU: 1.2 GHz Intel Core M
Memory: 8GB 1600 MHz DDR3

$ mysql --version
mysql  Ver 14.14 Distrib 5.6.28, for osx10.11 (x86_64) using  EditLine wrapper

準備

まず、range_testという名前のテーブルを作ります。

int型、bigint型、datetime型のカラムを作成して、それぞれ比較してみました。

カラム名 取りうる値の範囲
id bigint unsigned (PK) 1 〜 5,000,000
int_col int 0 〜 10,000
bigint_col bigint 1 〜 9,999,999
datetime_col datetime 2000-01-01 00:00:00 〜3000-01-08 00:00:00

インデックスが効くかどうかの実験なので、それぞれのカラムにインデックスを貼っておきます。

mysql> create database db_range_test;  
Query OK, 1 row affected (0.03 sec)

mysql> use db_range_test;
Database changed

mysql> create table range_test (
    -> id bigint unsigned not null auto_increment,
    -> int_col int,
    -> bigint_col bigint,
    -> datetime_col datetime,
    -> primary key (id),
    -> key idx_int (int_col),
    -> key idx_bigint (bigint_col),
    -> key idx_datetime (datetime_col)
    -> ) engine=InnoDB default charset utf8;
Query OK, 0 rows affected (0.11 sec)

こんな感じのスクリプトを書いて、MySQLに5,000,000件のダミーデータをINSERTします。

require 'sequel'
require 'mysql2'
require 'active_support'
require 'active_support/core_ext'

DB = Sequel.connect('mysql2://root@localhost/db_range_test')

(1..5_000_000).each do |i|
  int_r = rand(0..10_000)
  big_r = rand(0..10_000_000)
  dat_r = rand(0..1_000)
  DB[:range_test].insert(
    int_col: int_r,
    bigint_col: big_r,
    datetime_col: DateTime.new(2000, 1, 1).since(dat_r.years)
  )
end

5,000,000件入りました。 SELECT COUNT(*)に5秒弱かかるとは。。。

mysql> select count(*) from range_test;
+----------+
| count(*) |
+----------+
|  5000000 |
+----------+
1 row in set (4.96 sec)

mysql> select * from range_test limit 10;
+----+---------+------------+---------------------+
| id | int_col | bigint_col | datetime_col        |
+----+---------+------------+---------------------+
|  1 |    7521 |    8840230 | 2988-01-08 00:00:00 |
|  2 |    8951 |     617956 | 2454-01-03 12:00:00 |
|  3 |    4677 |    4115021 | 2111-01-01 18:00:00 |
|  4 |    8235 |    7919478 | 2906-01-07 12:00:00 |
|  5 |    5710 |    2487144 | 2958-01-07 12:00:00 |
|  6 |    9313 |       3383 | 2805-01-06 06:00:00 |
|  7 |    3602 |    9241458 | 2336-01-04 00:00:00 |
|  8 |    8950 |    8957220 | 2970-01-07 12:00:00 |
|  9 |    9052 |     731523 | 2865-01-06 06:00:00 |
| 10 |    5988 |    5204737 | 2121-01-01 06:00:00 |
+----+---------+------------+---------------------+
10 rows in set (0.00 sec)

実験

int

mysql> explain select * from range_test where 0 <= int_col and int_col <= 7;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | range_test | range | idx_int       | idx_int | 5       | NULL | 3849 | Using index condition |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.01 sec)

mysql> select count(*) from range_test where 0 <= int_col and int_col <= 7;
+----------+
| count(*) |
+----------+
|     3850 |
+----------+
1 row in set (0.00 sec)


mysql> explain select * from range_test where 0 <= int_col and int_col <= 8;
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra                            |
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------+
|  1 | SIMPLE      | range_test | range | idx_int       | idx_int | 5       | NULL | 4355 | Using index condition; Using MRR |
+----+-------------+------------+-------+---------------+---------+---------+------+------+----------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from range_test where 0 <= int_col and int_col <= 8;
+----------+
| count(*) |
+----------+
|     4356 |
+----------+
1 row in set (0.01 sec)


mysql> explain select * from range_test where 0 <= int_col and int_col <= 1009;
+----+-------------+------------+-------+---------------+---------+---------+------+--------+----------------------------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows   | Extra                            |
+----+-------------+------------+-------+---------------+---------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | range_test | range | idx_int       | idx_int | 5       | NULL | 929646 | Using index condition; Using MRR |
+----+-------------+------------+-------+---------------+---------+---------+------+--------+----------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from range_test where 0 <= int_col and int_col <= 1009;
+----------+
| count(*) |
+----------+
|   505016 |
+----------+
1 row in set (0.20 sec)


mysql> explain select * from range_test where 0 <= int_col and int_col <= 1010;
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | range_test | ALL  | idx_int       | NULL | NULL    | NULL | 4986946 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from range_test where 0 <= int_col and int_col <= 1010;
+----------+
| count(*) |
+----------+
|   505547 |
+----------+
1 row in set (0.15 sec)

途中でExtraにUsing MRRというものが出るようになったのですが、どうやらMySQL 5.6から入ったMulti-Range Readというものがあるらしい。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 8.2.1.13 Multi-Range Read の最適化

検索条件に合致するレコード数 全レコード数から占める割合 type Extra
〜 3,850 〜 0.0770% range Using index condition
4,356 〜 505,016 0.0871%〜10.1% range Using index condition; Using MRR
505,547 〜 10.1% 〜 ALL Using where

bitint

mysql> explain select * from range_test where 0 <= bigint_col and bigint_col <= 7843;
+----+-------------+------------+-------+---------------+------------+---------+------+------+-----------------------+
| id | select_type | table      | type  | possible_keys | key        | key_len | ref  | rows | Extra                 |
+----+-------------+------------+-------+---------------+------------+---------+------+------+-----------------------+
|  1 | SIMPLE      | range_test | range | idx_bigint    | idx_bigint | 9       | NULL | 3989 | Using index condition |
+----+-------------+------------+-------+---------------+------------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> select count(*) from range_test where 0 <= bigint_col and bigint_col <= 7843;
+----------+
| count(*) |
+----------+
|     3990 |
+----------+
1 row in set (0.00 sec)


mysql> explain select * from range_test where 0 <= bigint_col and bigint_col <= 7844;
+----+-------------+------------+-------+---------------+------------+---------+------+------+----------------------------------+
| id | select_type | table      | type  | possible_keys | key        | key_len | ref  | rows | Extra                            |
+----+-------------+------------+-------+---------------+------------+---------+------+------+----------------------------------+
|  1 | SIMPLE      | range_test | range | idx_bigint    | idx_bigint | 9       | NULL | 3990 | Using index condition; Using MRR |
+----+-------------+------------+-------+---------------+------------+---------+------+------+----------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from range_test where 0 <= bigint_col and bigint_col <= 7844;
+----------+
| count(*) |
+----------+
|     3991 |
+----------+
1 row in set (0.00 sec)


mysql> explain select * from range_test where 0 <= bigint_col and bigint_col <= 898823;
+----+-------------+------------+-------+---------------+------------+---------+------+--------+----------------------------------+
| id | select_type | table      | type  | possible_keys | key        | key_len | ref  | rows   | Extra                            |
+----+-------------+------------+-------+---------------+------------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | range_test | range | idx_bigint    | idx_bigint | 9       | NULL | 982422 | Using index condition; Using MRR |
+----+-------------+------------+-------+---------------+------------+---------+------+--------+----------------------------------+
1 row in set (0.00 sec)

mysql> select count(*) from range_test where 0 <= bigint_col and bigint_col <= 898823;
+----------+
| count(*) |
+----------+
|   450211 |
+----------+
1 row in set (0.18 sec)

mysql> explain select * from range_test where 0 <= bigint_col and bigint_col <= 898824;
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | range_test | ALL  | idx_bigint    | NULL | NULL    | NULL | 4986946 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from range_test where 0 <= bigint_col and bigint_col <= 898824;
+----------+
| count(*) |
+----------+
|   450212 |
+----------+
1 row in set (0.13 sec)
検索条件に合致するレコード数 全レコード数から占める割合 type Extra
〜 3,990 〜 0.0798% range Using index condition
3,991 〜 450,211 0.0798% 〜 9.00% range Using index condition; Using MRR
450,212 〜 9.00% 〜 ALL Using where

datetime

mysql> explain select * from range_test where '2000-01-01 00:00:00' <= datetime_col and datetime_col <= '2079-01-01 00:00:00';
+----+-------------+------------+-------+---------------+--------------+---------+------+--------+----------------------------------+
| id | select_type | table      | type  | possible_keys | key          | key_len | ref  | rows   | Extra                            |
+----+-------------+------------+-------+---------------+--------------+---------+------+--------+----------------------------------+
|  1 | SIMPLE      | range_test | range | idx_datetime  | idx_datetime | 6       | NULL | 905046 | Using index condition; Using MRR |
+----+-------------+------------+-------+---------------+--------------+---------+------+--------+----------------------------------+
1 row in set (0.01 sec)

mysql> select count(*) from range_test where '2000-01-01 00:00:00' <= datetime_col and datetime_col <= '2079-01-01 00:00:00';
+----------+
| count(*) |
+----------+
|   398998 |
+----------+
1 row in set (0.18 sec)


mysql> explain select * from range_test where '2000-01-01 00:00:00' <= datetime_col and datetime_col <= '2080-01-01 00:00:00';
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | range_test | ALL  | idx_datetime  | NULL | NULL    | NULL | 4986946 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.00 sec)

mysql> select count(*) from range_test where '2000-01-01 00:00:00' <= datetime_col and datetime_col <= '2080-01-01 00:00:00';
+----------+
| count(*) |
+----------+
|   404030 |
+----------+
1 row in set (0.16 sec)
検索条件に合致するレコード数 全レコード数から占める割合 type Extra
〜 398,998 〜 7.98% range Using index condition; Using MRR
404,030 〜 8.08% 〜 ALL Using where

単純な実験ですが、全レコードの約9%より多いレコードを取得しようとすると、EXPLAIN上ではフルテーブルスキャンの戦略が選ばれてしまうようでした。

フルテーブルスキャンの戦略が選ばれたクエリを打ってみても、あまり実行時間に変化がない(0.02 sec前後)ので、本当にフルテーブルスキャンしているのかが気になりました。

途中まで実験してから思ったのですが、MySQLのパラメータをいじったら変化が出るのではないかとかも考えたのですが、追いきれなかったのでまた今度。。。


追記

http://spring-mt.hatenablog.com/entry/2016/02/18/010911

optimizer_traceなんてものがあるのですね…!

railsへの執着はもはや煩悩の域であり、開発者一同は瞑想したほうがいいと思います。 というチームでしたがISUCON5予選ダメでした #isucon

isucon

一昨年去年と出てて、今年が3回目のISUCONでした。 去年までは学生枠だったのですが、今年から社会人になってしまったので一般枠になってしまいました。

今年も、去年と同じ id:cnosuke さんと id:k0kubun さんと出ました。

競技中についてはk0kubunさんが大体全部書いてくれました → http://k0kubun.hatenablog.com/entry/2015/09/27/225243

上の記事のとおりですが、k0kubunさんががアプリケーション周りを全体的に、cnosukeさんがnginxとかredis周りとか、自分がMySQL周りを、なんとなく3人で分担してました。

したこと

ひたすらボトルネックを探してそこを潰す、潰したらまた探して潰す、、、という感じの流れでした。 k0kubunさんの記事に書いてあるようにジワジワとスコアが上がっていくのは見ていて楽しかったです。

New Relicとrack-lineprofがひたすら便利でした。 あと、cnosukeさんが書いたnginxのログからアクセス頻度見るツールとか使ってました。

自分はMySQL周りを見ていたので、slow queryを吐き出させてそこから頻出クエリと時間がかかっているクエリを探して、インデックス貼ったりクエリ改善できるかを模索したりしてました。 slow queryを吐き出す設定が何故かファイルに書いても動かなかったので、こんな感じで直接打ってました(適当)。

mysql> SET GLOBAL slow_query_log = 1; # オフにするときは0にする
mysql> SET GLOBAL slow_query_log_file = /var/log/mysql/slow.log;
mysql> SET GLOBAL long_query_time = 0;

以前は適当に手当たり次第「これ直りそうじゃない?」みたいなものを見つけて改善しようとしてましたが、 それだと当たり前ですが効果が全然出ないことが多いので、「推測するな、計測せよ」がメチャメチャ大切だと思いました。


結局最終的にスコア11045で終わりましたが、終了直前にベンチ回したのでもしかしたら若干違うかもです。

足切りラインが14000くらいだったので、もうちょっとで本戦にいけるかもしれなかったので悔しいです(ヽ´ω`)

今年は残念でしたが、また来年開催されましたらぜひ参加したいです!!!