Pandora 3.0: Documentation ja: Anexo AjusteMySQL

From Pandora FMS Wiki
Jump to: navigation, search

Pandora FMS ドキュメント一覧に戻る

1 商用レベルのシステムのための MySQL チューニング

1.1 一般的に推奨する設定

まず最初に、2GBを超えるテーブルを持つ巨大なシステムを必要とするのであれば、いくつかのガイドラインに従うべきです。MySQLは64Bit版の利用を推奨します。さらに、一般的な推奨項目として、よりよいパフォーマンスを得るために十分なメモリとCPUを用意すべきです。

われわれの経験によれば、CPUよりもメモリのほうが重要です。もし、DBサーバ用に割り当てるメモリ量を1GB以下にしようと考えているのであれば、考え直してください。エンタープライズレベルであれば最低2GB必要です。巨大なシステムであれば、4GB割り当てるというのもよいでしょう。メモリが十分あれば、利用されるインデックスの大半をメモリ上に置くことでインデックスの更新が高速化されるということを忘れてはいけません。

その他アドバイスとして、テーブルの転送を使っているかどうか不明であったり、大きなハードディスクを使っていて、長時間のファイルチェックを避けたいのであれば、UPS を使うと良いでしょう。これはシステム障害を回避するには良い考えです。また、特定のサーバにデータベースがあるようなシステムでは、1G のネットワークを使うべきです。遅延は、パフォーマンスにとって重要です。

ディスクの最適化は、とても大きなデータベースにとっては大変重要です。データベースおよび手ープルを異なるディスクに分割すべきです。MySQL では、シンボリックリンクを使うことができます。システムおよび、データベースで異なるディスクを使い、一つのハードディスクのアクセスを減らすようにすることが重要です。アプリケーションは、ディスクアクセスに左右され、データが増えていくと N log N で増加していきます。

GNU/Linux では、ディスクに対して一定時間に複数のセクタを読み書きするように、hdparm-m16 -d1 を起動時に設定してください。これにより、応答時間が 5-50% 改善します。別のアイデアとしては、ディスクのパラメータに async (事前設定) や noatime を設定するのも良いです。これらの設定では、ファイルの読み書き時にアクセス時間を更新しません。特定のアプリケーションでは、特定のテーブルに対して RAM ディスクを設定をするのも良い考えです。ただし、不揮発性ディスクに書き込まずに電源 OFF になった場合のリスクはあります。良く検討をお願いします。

可能であれば、--skip-locking (いくつかのシステムでは事前設定で有効かします) を使ってください。これにより外部ロックを行わず、パフォーマンスが向上します。

もし、MySQLサーバとクライアントを同じマシン上で起動するなら、TCP/IPのかわりにsocketを使用してMySQLサーバへ接続しましょう(これにより7.5%性能が改善されます)。MySQLサーバに接続する際、ホスト名を指定しないかlocalhostと指定することでsocket経由で接続することができます。MySQLサーバを1台しか起動しないのであれば、binary logの出力とレプリケーションを無効にしましょう。

一般的なパフォーマンス向上のためのアドバイスとしては、以下を確認してください。

  • レプリケーションを利用しない場合は、バイナリログを出力しない。
  • slowquery または debug ログを出力しない。
  • MySQL の設定ファイルを確認。デフォルトの値は *遅い* です。

1.1.1 MySQL のバージョンについて

何人かのユーザは、よりパフォーマンスの高い Percona 修正版の MySQL 使って、高負荷の Pandora FMS サーバを運用しています。

MySQL のパフォーマンスはまた、最新バージョン(5.5)で向上しています。バージョン 5.0 よりも約 20%ほどパフォーマンスが上がります。

1.2 バイナリログ出力の停止

多くの Linux ディストリビューションにおいてデフォルトで有効になっています。無効にするには、my.cnf ファイル (通常、/etc/my.cnf にあります) を編集し、次の行をコメントアウトします。

 # log-bin=mysql-bin
 # binlog_format=mixed

両方の行をコメントアウトし、MySQL サーバを再起動します。

1.3 トランザクションごとのディスク書き込みの回避

デフォルトでは、MySQLは各接続開始時のautocommitの値を1にしています。MyISAMの場合、更新結果がディスクに保存されることを保証していないため、この設定はそんなに悪い設定ではありません。しかしInnoDBの場合、InnoDBテーブルへのあらゆるinsert/update/deleteによって、ディスクへの書き込みが発生するということを意味します。

さて, なぜディスクへの書き込みがよくないのでしょうか? そんなことはありません。ディスクへの書き込みは何らかのcommitment後、データベースが障害後再起動したときもデータが存在することを保障します。問題は、DBのパフォーマンスがディスクの物理的な速度によって制限を受けることです。書き込みを確認する前にデータをディスクに書き込まなければならないことを考えると、少し時間がかかるでしょう。

ディスクの書き込みに平均9msかかるとすると、おおよそ1秒当たり67コミットに制限されます。これは非常に遅いです。そして、ディスクのあるセクタに書き込み中は、そのセクタを読み込むことはできません。InnoDBはこれらの制限のいくつかを複数の書き込みを同時に実行することで回避しています。しかしそれでも制約が存在します。

システムによる“自動”書き込み(およそ1秒ごとに書き込みを行う)を利用することで、各トランザクションが完了するたびにディスクへ書き込むことを回避することができます。問題が発生した場合、最近1秒のデータが失われますが、パフォーマンス向上を得ようとしていることを考慮すれば、許容できる範囲でしょう。

innodb_flush_log_at_trx_commit = 0


参考: http://tag1consulting.com/InnoDB_Performance_Tuning

1.4 KeyBuffer の大きさ

システムに搭載された物理メモリ量に依存しますが、非常に重要なグローバル変数であり、これを設定することでDELETEおよびSELECTの実行速度が改善されます。

key_buffer = 400M

1.5 それぞれのテーブルでのテーブルスペースの利用

( MySQLのマニュアル http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html より )

MySQL 5.0では、各InnoDBテーブルとそのインデックスごとにデータファイルを生成し保存することができます。各テーブルがそれぞれテーブルスペースを持つことから、この特徴は「multiple tablespace」と呼ばれています。

multiple tablespaceを使用することで、特定のテーブルを別の物理ディスクに移動したり、他のInnoDBテーブルの利用を妨げることなくあるテーブルをバックアップから復元したりするのに役に立ちます。

my.cnfのmysqldセクションに以下の行を追加することで、multiple tablespaceを有効にすることができます。

[mysqld]
innodb_file_per_table

サーバ再起動後、InnoDBは新規に作成されたテーブルをそのテーブルが属するデータベースディレクトリ内にあるname_table.ibdという名前のファイルに保存します。これはMyISAMストレージエンジンの動きと似ていますが、MyISAMはテーブルをデータファイルtbl_name.MYDとインデックスファイルtbl_name.MYIに分割して保存します。InnoDBのデータとインデックスは.ibdファイル内に一緒に保存されます。tbl_name.frmファイルは通常通り作成されます。

my.cnf中のinnodb_file_per_tableの行をコメントアウトし、サーバを再起動すると、InnoDBは再び共有テーブルスペースファイルにテーブルを作成します。

innodb_file_per_tableはテーブル作成にのみ影響します。このオプションを有効にしてサーバを起動すると、新規テーブルは.ibdファイルを使用する状態で作成されますが、共有テーブルスペース内にあるテーブルにアクセスすることも可能です。このオプションを無効にすると、新規テーブルは共有テーブルスペース内に作成されますが、multiple tablespaceを使用しているテーブルにアクセスすることも可能です。

1.6 データベースの再構成

MySQLは他のデータベースシステム、たとえばOracle (tm)と同様、時間がたつにつれ、性能が劣化していきます。これは大きなテーブルに対してデータの削除と追加を続けることによって発生するデータのフラグメンテーションによるものです。大量のトラフィックが発生する大きな環境において、性能の改善および劣化を防ぐ非常に簡単な方法があります。それは定期的にデータベースの再構築を実施することです。

そのために、1時間程度のサービス停止を計画すべきです。

計画的なサービス停止時に、Pandora FMS Webコンソールとサーバも停止すべきです (注意! tentacleサーバはデータを受け取れるようにしておき、サーバが復旧次第データを処理できるようにします)

サービス停止後、データベースのダンプを取得(エクスポート)します。

mysqldump -u root -p pandora3 > /tmp/pandora3.sql
Enter password:

データベースを削除します。

> mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3279346
Server version: 5.0.67-Max SUSE MySQL RPM

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop database pandora3;
Query OK, 87 rows affected (1 min 34.37 sec)

データベースを作成し、先ほどエクスポートしたデータをインポートします。

mysql> create database pandora3;
Query OK, 1 row affected (0.01 sec)

mysql> source /tmp/pandora3.sql

1500エージェントと約100,000モジュールからなるシステムで、この処理全体で10~30分かかります。もし、システムが巨大だったり、ハードウェアの性能が劣っている場合は、それ以上かかるでしょう。この処理は自動化可能ですが、細心の注意が必要なため、毎月もしくは半月に一度、手動で実行することをお勧めします。

1.7 インデックスオプション

他のシステムリソースを犠牲にして、MySQL パフォーマンスを最適化できるいくつかの場合があります。

以下のインデックスの最適化はグラフ生成を(とても)高速化しますが、多くのディスクスペースを必要とします。また、インデックスのオーバーヘッドにより、若干 INSERT/DELETE 処理が遅くなります。

ALTER TABLE `pandora`.`tagente_datos`  ADD  INDEX  `id_agente_modulo_utimestamp`  (  `id_agente_modulo`  , `utimestamp`  );

1.8 スロウクエリ

いくつかのシステムでは保持している情報によって、通常よりもシステムのパフォーマンスが悪いスロウクエリが見られることがあります。テーブルを最適化するために、(システムのパフォーマンスに影響する)一定時間を超えたこのタイプのクエリをログに記録するようにできます。これを有効にするには次のようにします。

my.cnf を編集し、次の設定を加えます。

 slow_query_log = 1
 long_query_time = 2
 slow_query_log_file = / var / log / mysql_slow.log

コマンドラインから次のように設定します。

 touch / var / log / mysql_slow.log
 chmod 777 / var / log / mysql_slow.log

mysql を再起動します。

1.9 特定のテーブルのオプティマイズ

他のあまり“過激”ではないフラグメンテーションの問題を解決する方策として、Pandora FMSのテーブルのいくつかに対してMYSQL OPTIMIZEツールを使用するというのがあります。

OPTIMIZE table tagente_datos;
OPTIMIZE table tagente;
OPTIMIZE table tagente_datos_string;
OPTIMIZE table tagent_access;
OPTIMIZE table tagente_modulo;
OPTIMIZE table tagente_estado;

この作業によって、パフォーマンスが改善されるでしょう。この作業は1週間に複数回実行する必要はありません。システムが稼働中にさっと完了するでしょう。 巨大な環境においては、OPTIMIZEコマンドがブロックされるかもしれません。このような場合、一番よい代替案はDBの再構築です。

これらの作業を実施した後、以下のコマンドを実行すべきです。

FLUSH TABLES;

MySQLのマニュアルから、

InnoDBのテーブルに対してはOPTIMIZE TABLEはALTER TABLEにマップされており、インデックス統計の更新とクラスタされたインデックス内の使用されていないスペースを解放するためにテーブルが再構築されます。

1.10 設定例1

このサンプル設定は4CPU、4GBメモリのサーバに2個のInnoDBデータベース(どちらも頻繁にアクセスされる)を作成したシステムで利用する想定で作成しました。 各変数に割り当てたメモリの合計がシステム全体のメモリ量の80%を超えないよう注意してください。 起動したMySQLサーバの状況を確認し、インストールしたシステムにあわせて調整してください。

パフォーマンスを評価するための一般的な見方として、以下の事項がパフォーマンスに大きく影響することに注意しましょう。

  • MySQLサーバでレプリケーション設定を使用する予定がないのであれば、binary logを出力しない
  • 一般クエリログやスロークエリーログを出力しない
  1. 大規模なシステム(4GBメモリ、4CPU)のMySQL設定ファイル例
	[mysqld]
	skip-locking
	key_buffer = 256M
 
	# Very important to avoid "MySQL has gone away" messages
	max_allowed_packet = 32M
 
	table_cache = 2048
	sort_buffer_size = 16M
	read_buffer_size = 32M
	read_rnd_buffer_size = 32M
	myisam_sort_buffer_size = 64M
	thread_cache_size = 32
	query_cache_size = 64M
	query_cache_limit = 2M 
	thread_stack = 192K
	join_buffer_size = 8M
	max_heap_table_size = 64M
	binlog_cache_size = 1M
 
	# Very important if you have several servers / consoles       
	max_connections        = 500
 
	# OS Buffer to let connections waiting for Mysql thread.
	back_log = 100
 
	# Try number of CPU’s*2 for thread_concurrency
	thread_concurrency = 8
 
	# Print warnings to the error log file.
	log_warnings
 
	# You can set .._buffer_pool_size up to 50 - 80 %
	# of RAM but beware of setting memory usage too high
	innodb_buffer_pool_size = 2G
	innodb_additional_mem_pool_size = 16M
	innodb_thread_concurrency = 16
 
 
	[mysqld_safe]
	log-error=/var/log/mysqld.log

1.11 設定例2

ハードウエア: Xeon Quadcore Processor, 24GB RAM, 4xSAS disks (RAID10) 15k

binlog_cache_size=5M
default_table_type=InnoDB
ft_min_word_len=5
innodb_additional_mem_pool_size=256M
innodb_buffer_pool_size=19G
innodb_data_file_path=ibdata1:100M:autoextend
innodb_file_per_table
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=16M
innodb_log_files_in_group=2
innodb_log_file_size=1G
innodb_status_file=0
innodb_thread_concurrency=1
innodb_flush_method = O_DIRECT
innodb_io_capacity=800
innodb_write_io_threads = 32
innodb_read_io_threads = 32
join_buffer_size=1M
max_allowed_packet=16M
max_connections=4000
max_heap_table_size=64M
max_prepared_stmt_count=1000000
query_cache_size=0
skip_grant_tables
skip_locking
sort_buffer_size=64K
table_cache = 65536
thread_cache_size=16
thread_concurrency=16
thread_stack=196K
tmp_table_size=64M
transaction_isolation=REPEATABLE-READ
local-infile=0
key_buffer = 256M
key_buffer_size = 16M
max_allowed_packet = 16M
table_cache = 65536
net_buffer_length = 8K
read_buffer_size = 4M
sort_buffer_size = 64M
myisam_sort_buffer_size = 64M
thread_cache_size = 128
max_heap_table_size = 32M
read_rnd_buffer_size = 32M
bulk_insert_buffer_size = 8M
query_cache_limit = 1M
query_cache_size = 64M
query_cache_type = 1
query_prealloc_size = 512K
query_alloc_block_size = 1M
low_priority_updates = 1
open_files_limit        = 50000
expire-logs-days        = 14
log-bin=/var/lib/mysql/log-bin/mysql-bin
relay-log=/var/lib/mysql/log-bin/mysql-relay-bin
innodb_data_home_dir = /var/lib/mysql
innodb_log_group_home_dir = /var/lib/mysql
innodb_log_arch_dir = /var/lib/mysql
slave_net_timeout = 10
innodb_lock_wait_timeout = 50

1.12 参考情報

参考: