MySQLのチューニング

別に遅いと感じたことは無いのですが、phpMyAdminなどで状態を見ると
赤字(問題有)で表示されている項目がある。
貧乏性な私には、損しているぞ～ 損しているぞ～と言われている気がして気持ちが悪い。
よってMySQLにチューニングを施すことにした。
設定は以下のファイルにて行う。
（/etc/mysql/my.cnf）

#key_buffer
#検索に使われるインデックスをバッファに保存する際のメモリサイズです。
#メモリに余裕がある場合増やすとパフォーマンスが向上します。
#MySQL ABでは，マシンに搭載しているメモリーの1/4を推奨値としている。
#key_buffer = 16M　（変更前）
key_buffer = 1024M (変更後)

#table_cache
#データのキャッシュサイズです。
#値を大きくすることでディスクのI/Oが減りパフォーマンスが向上します。
#接続数が少ない人はあんまり関係ないみたいだが、そのうち増やすかも
#table_cache = 64　(デフォルトのまま：変更なし)
table_cache = 256


#tmp_table_size
#構文実行中にサーバによって自動的にディスクに作成された一時テーブルの数です。
#Created_tmp_disk_tables が大きい場合、原因の一時テーブルをディスクベースの変わりにメモリベースにさせるように、
#値 tmp_table_size を増やしたいでしょう。
#tmp_table_size メモリ内のテンポラリテーブルがこのサイズを超えると、
#MySQL は自動的にこれをディスク上の MyISAM テーブルに変換する。詳細な GROUP BY クエリを頻繁に行い、
#メモリに余裕がある場合は、tmp_table_size 値を大きくする。
tmp_table_size = 256MB

#最大接続数
#max_connections = 100
max_connections = 8 (そもそも私以外が使うことはあまり考えていないので下げる)

#sort_buffer_size
#この値はコネクションごとに必要となるので注意
sort_buffer_size = 4M


そしてここまでやると赤字はほとんど消えたが、以下の２点の赤字が消えない。
Handler_read_rnd_next 3,385
Opened_tables 144

Opened_tablesに関しては、テーブルキャッシュの値を上げれば消えるらしいのだが、
いくら上げても赤字は変わらないので、放置。

Handler_read_rnd_next　は照会がインデックスを利用するように書かれていないことを示唆するらしい。
とは言っても、再起動直後ですでに赤字になっているのはなぁ・・・
こういう場合、普通はSQLを調査する。ただし今回の場合には調査すら不可能。

SQL文に問題が無いかを調べるにはSQL文の前に explainキーワードをつける。

実行結果
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY system 1
1 PRIMARY system 1
1 PRIMARY BJ ref PRIMARY PRIMARY 4 const 2804
1 PRIMARY DJ eq_ref PRIMARY PRIMARY 8 const,lfll.BJ.code 1 Using where
3 DERIVED Select tables optimized away
2 DERIVED BD ALL 8 Using where

■統計情報の更新
MyISAMでは定期的に統計情報の更新を行う必要がある。
統計情報の更新を行わない場合には、適切なIndexが使用されない為
開発者がforce indexなどでindexを指定する必要がある。

統計情報の更新を行うためにはanalyze table やoptimizeテーブル、
indexの作成などの方法がある。
それぞれ相当重い処理であるため、注意が必要である。


■ストレージエンジンのMemoryの速度
やっていくうちにストレージエンジンをMemoryにしたくなって来たが
以下のベンチマークを見ているうちにMyISAMでいいかと思えた。

http://www.thinkit.co.jp/free/article/0608/1/6/

Memoryのストレージエンジンが思ったよりも速くないんですよね。
転送速度だけで考えるのならば、メモリ(6.4～12.8GB/s)とHDD(60～90MB/s)では
速度差が100倍近くあるはずなのですが、
それ以外の計算で時間がかかっているため、このような結果になっているのでしょう。

MySQL/Rubyを用いてMySQLへのデータベースアクセス

ruby からMySQL/Rubyを用いてデータベースアクセス。

rubyでデータベースアクセスする必要が出てきたので、MySQL/Rubyを試してみた。
よくよく読んで見るとMySQL/RubyはMySQL の C API をラップした形で実装しているらしく
MySQLが提供しているAPIはほとんど使用可能らしい。
以下手順

1, mysql/rubyのダウンロード
wget http://rubyforge.org/frs/download.php/29755/mysql-ruby-2.8pre1.tar.gz

2, 解凍
% tar zxvf mysql-ruby-2.8pre1.tar.gz

3, configure
% ruby extconf.rb --with-mysql-dir=/usr/lib/mysql --with-mysql-include=/usr/include/mysql

4, ビルド
% make

5, テスト
問題発生。どうやらパラメータの指定は以下らしいのだが、うまくいかない。
% ruby ./test.rb -- [hostname [user [passwd [dbname [port [socket [flag]]]]]]]
% ruby ./test.rb localhost user password dbname

実行すると以下のようなエラーが出る。
Loaded suite ./test
Started
.FE...FE.FE.EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE.........
Finished in 0.042105 seconds.

1) Failure:
test_connect(TC_Mysql) [./test.rb:33]:
Exception raised:
Class:
Message: <"\245\346\241\274\245\266\241\274 'user'@'localhost' \244\362\265\361\310\335\244\267\244\336\244\271.uUsing password: NO)">
---Backtrace---
./test.rb:33:in `connect'
./test.rb:33:in `test_connect'
./test.rb:33:in `test_connect'
---------------

2) Error:
test_connect2(TC_Mysql):
Mysql::Error: ユーザー 'user'@'localhost' を拒否します.uUsing password: NO)
./test.rb:90:in `connect'
./test.rb:90:in `test_connect2'

・・・こんなエラーが延々とでる。
調べてみるとパラメータがうまく渡っていないようだ。
暫定処理として、Rubyのコードに直接パラメータを書くようにした。

@host, @user, @pass, db, port, sock, flag = ARGV
@host = "localhost"
@user = "user"
@pass = "password"
@db = "dbname"
@port = 3306

% ruby ./test.rb
Loaded suite ./test
Started
..................................................................................................................
Finished in 0.105784 seconds.

114 tests, 381 assertions, 0 failures, 0 errors

OKらしい。OKなのか？
ここでエラーがでても別に次の作業で影響が出るわけではないので、
これを見ている人でtest.rbでエラーが出ている人はとっとと
次のインストール作業に進んだほうがいいです。

6, インストール
% sudo make install

7, コードを書いて実際にテストしてみる

=====================================
#!/usr/bin/ruby -Ku

require 'mysql'

my = Mysql.new("localhost", "user", "password", "DBname")
st = my.prepare("SELECT * FROM XXX WHERE XXX=DDD")
st.execute
p st.fetch
st.close
=====================================
試してみると文字化けする。
あれ？CSEだとUTF8で読み書きできるのになぁ。
以下の方法でコード上からMySQLの文字コードを取得する。

=========================================
#!/usr/bin/ruby -Ku

require 'mysql'

my = Mysql.init()
my.real_connect("localhost", "user", "password", "DBname")
p my.character_set_name()
=========================================

結果："latin1"らしい。ああ、そうですか。そうですか。
どうやらMySQLはデフォルトではLatin-1を用いるそうです。
phpmysqladminに頼りっきりで、気がつきませんでした。
文字コードを変更するために　/etc/mysql/my.cnf　の[client]と
[mysqld]セクションの両方に以下の記載を追加しました。


[client]
default-character-set = utf8
[mysqld]
default-character-set = utf8

再起動後、以下のコマンドを入力することで、確認可能です。
mysql> status;
--------------
mysql Ver 14.12 Distrib 5.0.32, for pc-linux-gnu (i486) using readline 5.2

Connection id: 6
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.32-Debian_7etch5-log Debian etch distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8
Db characterset: utf8
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /var/run/mysqld/mysqld.sock
Uptime: 14 sec

Threads: 1 Questions: 141 Slow queries: 0 Opens: 139 Flush tables: 1 Open tables: 21 Queries per second avg: 10.071
--------------


よし！できた。と思い、もう一度試す。
結果："latin1" 。困ったなぁ。
MySQL/RubyのAPIを読んでいても文字コードを設定できそうな場所は、無かったので
今度はMySQLのC用APIを読んでいたら見つけた。

http://dev.mysql.com/doc/refman/5.1/ja/mysql-options.html

mysql_options()のパラメータで文字コードを設定できるらしい。
試してみる。

=========================================
#!/usr/bin/ruby -Ku

require 'mysql'

my = Mysql.init()
my.options(Mysql::SET_CHARSET_NAME, "utf8")
my.real_connect("localhost", "user", "password", "DBname")
p my.character_set_name()
=========================================

結果：utf8になりました。
そして作成したコードが以下。
無事にutf8で結果を取得+出力できました。

=========================================
#!/usr/bin/ruby -Ku

require 'mysql'

#Mysql クラスオブジェクトを取得
my = Mysql.init()

#文字コードの設定
my.options(Mysql::SET_CHARSET_NAME, "utf8")

#接続開始
my.real_connect("localhost", "user", "password", "DBname")

#SQL文の実行
st = my.prepare("SELECT * FROM TABLE ORDER BY CODE")
st.execute

#結果の行数文ループする
for i in 0...st.num_rows()
#一行取得
data = st.fetch()

#3番目のカラムのみ出力
p data[3]
end

#接続を切断する
st.close
=========================================

そして、思う。スクリプト言語って、試しながら書けるからいいですね。
やはりCやJavaでは手順が色々とからむせいもあって、待ち時間を含めてイライラすることが多い。
Rubyが楽しいプログラミングと言われるのもわかる気がしました。

debianにmysql-server-5.0 + phpmyadminのインストール

無料のoracle10g xeの4Gの容量制限は自分の使い方ではすぐに一杯になってしまった。
なので代わりに mysql-server-5.0 + phpmyadminをDebianへインストールする。
とはいっても、Debianなので以下のコマンドのみでインストールは完了。

% sudo aptitude install mysql-server-5.0 phpmyadmin

その後、rootでログインし、mysql、ルート権限のパスワードの変更。

% mysql -u root
mysql> SET PASSWORD FOR root@localhost=PASSWORD('password');

後の設定はphpmyadmin上から行えるので、
phpmyadminの設定を行う。

変更箇所は一行。
わざわざonly neededって書いてある。
% sudo vim /etc/phpmyadmin/config.inc.php
$cfg['Servers'][$i]['password'] = 'password'; // MySQL password (only needed

あとはブラウザから以下のようにアクセスすれば画面が出る。
http://サーバーのIP/phpmyadmin/index.php




おお。日本語だ。




まだ、それほど触ってはいないのですが、
少なくてもOracle10 XEのApplication Expressの数倍は高機能な感じです。

＋セキュリティ設定
このままではphpmyadminに誰からでもアクセスできてしまうので、制限をかける。

% sudo vim /etc/phpmyadmin/config.inc.php
$cfg['Servers'][$i]['AllowDeny']['order'] // Host authentication order, leave blank to not use = 'deny,allow';

192.168.1.0/24　のネットワーク以外からの接続を拒否する。
$cfg['Servers'][$i]['AllowDeny']['rules'] // Host authentication rules, leave blank for defaults = array('deny % from all','allow % from 192.168.1.0/24');