UJP - 技術情報1

Life is fun and easy!
不正IP報告数

Okan Sensor
Home Information Service Tech(Free) Tech(Member) Blog FAQ
 
メイン
ログイン

ユーザー名:


パスワード:





パスワード再発行手続き  |無料会員入会手続へ...
ブログ　カテゴリ一覧
MySQLのmy.cnfを設定

MySQLのmy.cnfを設定

０．改訂履歴

  • 2005.11.02　新規作成
  • 2007.03.26　チューニング的な要素の追加
  • 2007.03.27　mysqldumpについての記載の追加
  • 2007.07.09　「チューニング的な要素」への追記
  • 2007.07.25　ログファイルオプションについて説明

１．はじめに

　このドキュメントでは，MySQLの動作状態を設定するための，my.cnfファイルについて説明する． key_buffer,max_allowed_packet,table_cache,sort_buffer_size,,read_buffer_size,read_rnd_buffer_size, myisam_sort_buffer_size等のサイズの説明なども含まれる．

２．my.cnfの基本

  • RPMでMySQLをインストールした場合，テンプレートとなるmy.cnfファイルが保存されている． 
[root@mars www]# locate  my- | grep .cnf
/usr/share/doc/MySQL-server-standard-4.1.15/my-huge.cnf
/usr/share/doc/MySQL-server-standard-4.1.15/my-innodb-heavy-4G.cnf
/usr/share/doc/MySQL-server-standard-4.1.15/my-large.cnf
/usr/share/doc/MySQL-server-standard-4.1.15/my-medium.cnf
/usr/share/doc/MySQL-server-standard-4.1.15/my-small.cnf
/usr/share/mysql/my-large.cnf
/usr/share/mysql/my-huge.cnf
/usr/share/mysql/my-innodb-heavy-4G.cnf
/usr/share/mysql/my-medium.cnf
/usr/share/mysql/my-small.cnf
[root@mars www]#
  • データベースの大きさ（small.medium,large,fuge,）別と，innodbエンジンを使う場合によってテンプレートが分かれている．
  • この場合の「大きさ」とは，データベースサーバの物理メモリサイズ，同時ユーザ数等を視野に入れているが，ファイルの中身を観て，確認する必要がある．
  • 自分の利用に合ったテンプレートを見つけたら，/etc/my.cnfに複写して利用するのが基本的な使い方である． 
[root@mars www]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf
[root@mars www]#
  • メモリ2G程度のマシンの場合，my-medium.cnfが丁度良いと考えられるので，それをコピーする．
  • ただし，コピーしただけでは後でハマるので，以下の文書を引き続き熟読する．

２．１．セクションについて

  • my.cnfファイルは，幾つかのセクションによって分かれている．
  • セクション毎に，設定したパラーメータの有効範囲を変更することができる．　たとえば，文字コードをサーバとクライアントでは違うものをデフォルトとして設定することができる．
  • セクションは，先頭から[keyword]で記述されているものとなる．
  • サンプルとして，テンプレートから，セクションを荒い出してみる． 
[root@mars bin]# cat /usr/share/mysql/my-medium.cnf | grep "\["
[client]
[mysqld]
[mysqldump]
[mysql]
[isamchk]
[myisamchk]
[mysqlhotcopy]
[root@mars bin]#
  • データベースサーバ本体の設定をする際には，[mysqld]，mysqlモニタは[mysql]を設定する．
  • たとえば，my-medium.cnfのmysqldでは，次のようなパラメータが設定されている．
  • ちなみに，先頭に#が入っているのは，コメントなので評価されない． 
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin
server-id       = 1

３．mysqldセクション　もっとも基本的な設定

  • コピーしたテンプレートで有効な行を取り出すと，以下の様になる． 
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
log-bin
server-id       = 1
  • 特に何も考えない場合でも，最低限以下の設定を行っておく． 
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#log-bin
server-id       = 1
old_passwords
default-character-set=sjis
  • まず，log-binは，レプリケーションを使うときに必要なので，少し上級用なので，それを利用しない場合は，コメントアウトする．
    • コメントアウトを忘れると，バイナリログファイルが書き出されて，ハードディスクを食いつぶすことになり，忘れた頃にディスクフルとなって障害をもたらす事になる．
  • old_passwordsは，MySQLのユーザ認証方式を4.0互換とするパラメータである．
    • MySQL4.0と4.1以降では，認証方式（パスワード長等）が変わっており，4.0までの認証方式を用いたクライアントソフト（PHP等）から接続できない問題が発生する．
    • よって，全てのクライアントソフトがMySQL4.1以降の認証方式に対応している場合以外は，old_passwordsを設定する事になる．
    • どちらが推奨かというと，新しい方がより良いのだが，色々なしがらみでそうはいかないというのが実際の所である．
  • default-character-setは，名前の通りデフォルトの文字コードを指定する．
    • ここでは，sjisとしているが，EUCコードにする場合はujis，他にasciiやlatain1等がある．
    • クライアントで文字コードを何も指定しない場合は，mysqldのデフォルト文字コードが採用され，create table等を行った場合に，デフォルトが設定される．
    • MySQL4.1以降は，内部文字コードはUTFとなっている．

４．mysqldセクション　もうちょっと踏み込んで知っておきたいとき

  • 次に説明するのは，もう少し管理を行いたいときのもの． 
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
#log-bin
server-id       = 1
old_passwords
default-character-set=sjis

datadir=/db/DBfile
max_connections=100
log-slow-queries=/db/slow.log
long_query_time = 2
log-queries-not-using-indexes
log=/db/SQL.log
  • datadirによって，データベースの格納ディレクトリを指定する．
    • デフォルトでは，/var/lib/mysql以下にデータが保存されている．
      • インストールした方法（RPMやソース），プラットホームなどにより異なる場合がある．
    • パーティションやディスク容量の関係で，データを保存する場所を変更したい場合には，
      1. データベースを停止．
      2. 移動させたいディレクトリを作成
      3. ディレクトリのパーミッションを変更（通常，chown -R mysql:msql TARGET ）
      4. そのディレクトリにデータベースファイルを全部移動する．
      5. my.cnfにdatadirを指定する．
      6. データベースを起動する
        • ※現在稼働中のデータベースのディレクトリを確認する場合は，ps -ef | grep mysqldでプロセスリストを表示させると，パラメータが確認できる．
  • max_connectionsによって，データベースに接続できる最大コネクション数を指定できる．
    • 現在のこの設定の場合，データベースに接続できるのは同時100となる．
    • 銀行の窓口業務等だと，最大数を数える事も可能であるが，インターネットだとどれくらい接続してくるか不明であるので，悩みどころ．
    • 設定値が少なすぎると，サービス中にサーバの処理能力があるにもかかわらず接続エラーがでる事がある．
    • 設定値が大きすぎると，前の処理が済んでないのに新たに接続を許す形になり，どんどん処理ができない状態に突入する事になる．
    • 基本的には，パフォーマンステストを実行した後に値を決めて設定するのが望ましいが，そう行かない場合も多いと思うので，ざっくりと込めることになる．
    • ちなみに，この値はデータベースを停止しないと反映されないパラメータなので，注意する．
  • log-slow-queriesとlong_query_time
    • パフォーマンスの悪いSQL文を探すときに役に立つ仕組み．
    • 実行の際にlong_query_timeで指定した時間（秒）以上の SQL文がログファイルに書き込まれる
  • log-queries-not-using-indexes
    • log-slow-queriesと一緒に利用する．
    • インデックスを使わずに実行されたSQL文が，ログファイルに記録される．
    • ログファイルは，log-slow-queriesで指定したファイル．
    • MySQl4.1で追加された機能である．
  • log
    • リクエストされたSQL文を全て記録するログに吐き出す機能．
    • ファイル名を指定する．

５．mysqldセクション　もっとチューニング的な話

  • MySQLのチューニングを行うには，/etc/my.cnfの各種パラメータを適切に設定することが必要である．
  • ただし，一番有効なのは，言うまでもなくアプリケーションのチューニングである．
  • 以下は，あるサーバの設定をコピーしたもの． 
[mysqld]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

default-character-set=sjis
old_passwords
datadir=/db/DBfile
log-slow-queries=/db/slow.log
long_query_time = 2
max_connections=300
table_cache = 1024
thread_cache_size = 68
back_log=200
  • ここで各パラメータについて説明を行う．
パラメータ名 値 説明
key_buffer
256M
 　インデックスをメモリに格納するときの領域のサイズ．　インデックスキーを頻繁に利用する検索をする場合は，このサイズを大きくすることで処理スピードが上がる．

　このパラメータは，サーバ全体に有効なグローバルパラメータである．
max_allowed_packet
1M
 　1パケットの最大サイズ．　画像データ等をやり取りしていない場合は，1MBあればよい．BLOB型を用いている場合は，その最大サイズと同等にする．
sort_buffer_size
1M
 　ソートで利用するメモリ空間．　大量のデータをソートすることが分かっている場合は，この領域を増やす事でディスクを使ったソートが行われないので，高速となる．

　ただし，ほぼ全ての接続ユーザはソートを行うので，その文メモリを消費する事に注意．　これが全てではないが，同時ユーザ×sort_buffer_sizeという感じ．

　このパラメータは，スレッド毎に有効なパラメータである．
read_buffer_size
1M
 　順スキャン(インデックスを使わない)を行う時に読み込んでおくメモリ空間．

　 順スキャンするコネクションが，スキャンするテーブル毎にこのサイズのバッファを割り当てる．　1コネクションあたりのユーザが利用するメモリが大量になる事に注意．
read_rnd_buffer_size
4M
 　ランダムアクセス（インデックス利用）時に利用する．

　Group byやOrder byを大きいテーブルで行う際に，ソート後の読み取り速度が高速化される．
myisam_sort_buffer_size
64M
 　repairや，create indexやalter indexするときに割り当てられる一時的な領域．
query_cache_size
16M
 　古いクエリの結果を保存する為の領域．サーバ全体で共有される．　０として設定すると，キャッシュされなくなる．
thread_concurrency
CPU×2
 　同時に実行するスレッド数．　CPUの個数の2倍を設定する．

　Hyper threadやmulti core等のアーキテクチャの場合，パフォーマンスを確認して，調整が必要かと．
table_cache
1024
 　MySQLでは一度度開いたファイルのポインタをtable_cacheに保存して，次回からのアクセスを高速化する．

　show statusのOpened_tables変数の数が多い場合は，保存するポインタを大きくすることで高速化できる可能性がある．

　算出方法は，max_connectionsにも影響する．　最大100セッションで1ユーザあたり3テーブルなら，300テーブルとか．

・MyISAMエンジンの場合，1テーブル毎に３つ使う．
・２つのスレッドが１つのテーブルを開く場合，６つのファイルを開くことになる．
thread_cache_size
30
 　スレッドをキャッシュする．　スレッドは，ユーザが接続したときに生成され，切断されると終了する．　いわゆるプロセスが起動するよりもコンピュータに対してストレスは高くないが，頻繁に接続／切断が繰り返される利用方法の場合は，スレッドを取っておいて（プーリング），再利用した方が効率がよい場合がある．　値は，取っておくスレッドの数を指定する．
back_log
200
 　MySQLが持てる未解決の接続要求の数．短時間に大量接続が行われたときに有効となる．

　通常，接続要求が行われてスレッドが開始されるまでに，少し時間がかかるが，その間に待っていられるコネクション数．

５．１．back_logについて

  • back_logは，MySQLのパラメータだけでなく，OSのカーネル設定に依存している．
  • Linuxの場合，以下のコマンドでカーネルのTCP設定を確認できる． 
[root@mars root]# /sbin/sysctl -A|grep tcp_max_syn_backlog   
net.ipv4.tcp_max_syn_backlog = 1024
[root@mars root]#
  • このtcp_max_syn_backlogは，ネットワーク接続(SYN)を受けているが，相手からACKを受け取って無い状態のコネクションをいくつ保持するかを示す．
    • TCPは3ウェイハンドシェイクで繋がっている．
    • ３ウェイハンドシェイクとは，サーバからみると，クライアントからのSYNを受け取りACK等を返し，さらにACKを受けることで接続を確立する基本的なもの．
    • OS的には，1024までOKとなっているので，この値以下に設定することとなる．

５．２．メモリの最大容量の計算方法

  • MySQLが使用するメモリ容量は，以下の計算式で算出できる． 
key_buffer_size + ( ( sort_buffer_size +  read_buffer_size ) * max_connections )
  • 現在の値を確認する． 
[root@localhost (none)]: >show variables like '%key_buffer_size';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| key_buffer_size | 134217728 | 
+-----------------+-----------+
1 row in set (0.00 sec)

[root@localhost (none)]: >
  • 134217728なので，128MB 
[root@localhost (none)]: >show variables like '%sort_buffer_size';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| myisam_sort_buffer_size | 67108864 | 
| sort_buffer_size        | 524280   | 
+-------------------------+----------+
2 rows in set (0.00 sec)

[root@localhost (none)]: >
  • 524280なので約512KB． 
[root@localhost (none)]: >show variables like '%read_buffer_size';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| read_buffer_size | 258048 | 
+------------------+--------+
1 row in set (0.00 sec)

[root@localhost (none)]: >
  • 258048なので約256KB． 
[root@localhost (none)]: >show variables like '%max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 100   | 
+-----------------+-------+
1 row in set (0.01 sec)

[root@localhost (none)]: >
  • １００コネクション．
  • これらの数値を計算すると，次のようになる．
    • sort_buffer_size(512KB) + read_buffer_size(256KB)なので，1ユーザあたり756KB．
    • 同時100コネクションなので，約74MB．
    • key_buffer_size(128M)なので，202MBとなる．

５．３．key_buffer_sizeが適切か調べる

  • key_buffer_sizeは，インデックスを読み込む為のメモリ空間であるが，それが適切な値として設定されているか確認する．
  • メーカのMySQL ABでは，サーバの搭載メモリの1/4程度を推奨している．データベースしか稼働していないサーバの場合は，それでもOKだと考えられる．
  • まずは，現在の状態を表示する． 
[root@localhost (none)]: >show status like 'key%';
+------------------------+-----------+
| Variable_name          | Value     |
+------------------------+-----------+
| Key_blocks_not_flushed | 0         | 
| Key_blocks_unused      | 0         | 
| Key_blocks_used        | 115980    | 
| Key_read_requests      | 182897180 | 
| Key_reads              | 514183    | 
| Key_write_requests     | 16546073  | 
| Key_writes             | 173508    | 
+------------------------+-----------+
7 rows in set (0.00 sec)

[root@localhost (none)]: >
  • それぞれの意味は，次の通り．
Key_buffer_size関連ステータス
パラメータ 意味
Key_blocks_not_flushed 変更されているがディスクへ書き出されていないキーブロック数
Key_blocks_unused キーキャッシュの未使用ブロック数．
Key_blocks_used キーキャッシュの使用ブロック数．
Key_read_requests キャッシュからのキーブロック読み取り要求回数
Key_reads ディスクからのキーブロック物理読み込み回数
Key_write_requests キャッシュへのキーブロック書き込み要求回数
Key_writes ディスクへのキーブロックの物理書き込み回数
  • 効果については，以下の式で計算できる． 
効果% = 100 - ( ( Key_reads / Key_read_requests ) * 100 )
  • これを数値を当てはめると，次の通りになる． 
100 - ( ( 514183 / 182897180 ) * 100　＝ 99.72%
  • この計算では，全体の要求数のうちディスクを使わないで処理された率を示しており，99.72%ということで優秀であると考えられる．
  • ただし，Key_blocks_unusedが0と言うことは未使用領域が無いと言うことなので，メモリ全体の中で余裕があれば，key_buffer_sizeを増やす．

５．４．クエリキャッシュ

　クエリキャッシュの調整に関しては，ドキュメント「MySQLで検索キャッシュサイズを設定してみる」を参照のこと．

６．mysqldumpセクション

  • mysqldumpは，文字コードがutf8に変換されてしまうようなので，変換されて困る場合には，binaryを付けておく． 
[mysqldump]
default-character-set=binary

広告スペース
Google