InnoDBテーブルのみのビジーなデータベースがあり、サイズは約5GBです。データベースはSSDディスクを使用したDebianサーバ上で動作しており、最大接続数を800に設定していますが、これが時々飽和してサーバを停止させてしまいます。1秒あたりの平均クエリ数は約2.5Kです。そのため、メモリ使用量を最適化して、可能な限り最大の接続数を確保する必要があります。
innodb_buffer_pool_sizeは総メモリの80%までにすべきだという提案を見たことがあります。一方で、tuning-primer スクリプトから次のような警告が出ます。
Max Memory Ever Allocated : 91.97 G
Configured Max Per-thread Buffers : 72.02 G
Configured Max Global Buffers : 19.86 G
Configured Max Memory Limit : 91.88 G
Physical Memory : 94.58 G
私の現在のinnodbの変数は以下の通りです。
| innodb_adaptive_flushing | ON |
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 20971520 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_size | 20971520000 |
| innodb_change_buffering | all |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Antelope |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_io_capacity | 200 |
| innodb_large_prefix | OFF |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 4194304 |
| innodb_log_file_size | 524288000 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_max_dirty_pages_pct | 75 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 0 |
| innodb_open_files | 300 |
| innodb_purge_batch_size | 20 |
| innodb_purge_threads | 0 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | ON |
| innodb_stats_sample_pages | 8 |
| innodb_strict_mode | OFF |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 4 |
| innodb_thread_sleep_delay | 10000 |
| innodb_use_native_aio | ON |
| innodb_use_sys_malloc | ON |
| innodb_version | 1.1.8 |
| innodb_write_io_threads | 4 |
関連するかもしれない副次的なメモです。別のウェブサーバにあるDrupalからデータベースに大きな記事(例えば10KB以上)を挿入しようとすると、いつまでも続き、ページが正しく戻らないことがあります。
これらについて、最適なパフォーマンスを得るためには、innodb_buffer_pool_sizeをどのようにすべきか悩んでいます。このシナリオのために、このパラメータや他のパラメータを最適に設定するためのご提案をお願いします。
あなたのinnodb_buffer_pool_sizeは巨大です。20971520000`に設定されています。これは19.5135GBです。InnoDBのデータとインデックスが5GBしかないのであれば、約8GBしかないはずです。これでも大きすぎるかもしれません。
そこで、次のようにします。まず、このクエリを実行します。
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;
これにより、RIBPS(すべてのInnoDBデータとインデックスに60%を追加したものに基づく推奨InnoDBバッファプールサイズ)が得られます。
例
mysql> SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
-> (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
-> FROM information_schema.tables WHERE engine='InnoDB') A;
+-------+
| RIBPS |
+-------+
| 8 |
+-------+
1 row in set (4.31 sec)
mysql>
この出力では、/etc/my.cnfで次のように設定します。
[mysqld]
innodb_buffer_pool_size=8G
次に、service mysql restart
を実行します。
再起動後、1~2週間ほどmysqlを動かします。その後、このクエリを実行します。
SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;
これにより、現時点でInnoDBのバッファプールでデータが使用されているメモリの実際のGB数がわかります。 InnoDB バッファプール内のデータが、現時点で何GB のメモリを使用しているかがわかります。
これについては以前にも書いたことがあります : https://dba.stackexchange.com/questions/19164/what-to-set-innodb-buffer-pool-and-why/19181#19181
再設定して再起動して1週間待つよりも、今すぐこのDataGB
クエリを実行すればいいのです。
この DataGB
という値は、InnoDB バッファプールの大きさ + (innodb_change_buffer_max_size で指定されている割合)に近いものです。これは、現在予約している20000Mよりもはるかに少ないと思います。RAMの節約は、以下のような他のもののチューニングに使用できます。
これは非常に重要な注意点です : 時に、InnoDBはinnodb_buffer_pool_sizeの値よりもさらに10%を必要とする場合があります。これについて、MySQL Documentationには次のように書かれています。
この値を大きくすると、テーブルのデータにアクセスする際のディスクI/Oが少なくなります。 テーブル内のデータへのアクセスに必要なディスクI/Oが減少します。専用のデータベースサーバでは、この値を最大で 最大でマシンの物理メモリサイズの80%まで設定することができます。この値は、他の条件が満たされた場合 このような問題が発生した場合は、この値を縮小する準備をしてください。
物理メモリの奪い合いにより、オペレーティング・システムのページングが発生する可能性がある。 システムのページングが発生する可能性があります。
InnoDBはバッファや制御構造のために追加のメモリを確保しています。
InnoDBは、バッファと制御構造のために追加のメモリを確保しますので、割り当てられたスペースの合計は 指定されたサイズよりも約10%多く割り当てられます。
アドレス空間は連続していなければなりませんが、これは、Windows DLLが特定のアドレスでロードされるWindowsシステムでは問題となる可能性があります。
バッファプールの初期化にかかる時間は、そのサイズにほぼ比例します。 サイズにほぼ比例します。大規模なシステムでは、この初期化時間は 大規模な設備では、この初期化時間が重要になります。例えば、最新のLinux x86_64サーバーの場合。 10GBのバッファプールの初期化には約6秒かかります。 8.9.1項の"The InnoDB Buffer Pool"を参照してください。
あなたのmy.cnf
に以下のような値があります。
| innodb_io_capacity | 200 |
| innodb_read_io_threads | 4 |
| innodb_thread_concurrency | 4 |
| innodb_write_io_threads | 4 |
これらの数値は、InnoDBが複数のコアにアクセスする際の障害となります。
を設定してください。
[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
この件については、以前にDBA StackExchangeの
May 26, 2011
: https://dba.stackexchange.com/questions/2918/about-single-threaded-versus-multithreaded-databases-performance/2948#2948Sep 12, 2011
: https://dba.stackexchange.com/questions/5666/possible-to-make-mysql-use-more-then-one-core/5670#5670Sep 20, 2011
: https://dba.stackexchange.com/questions/5926/multi-cores-and-mysql-performance/5968#5968私はちょうどServerFaultでこのような質問にもっと簡潔な式を使って回答したところです。
SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
Recommended_InnoDB_Buffer_Pool_Size FROM
(
SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
FROM
(
SELECT SUM(data_length+index_length)*1.1*growth RIBPS
FROM information_schema.tables AAA,
(SELECT 1.25 growth) BBB
WHERE ENGINE='InnoDB'
) AA
) A;
[2]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_join_buffer_size [3]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_sort_buffer_size [4]: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_read_buffer_size
あなたのタイトルはinnodb_buffer_pool_sizeについて尋ねていますが、私はそれが本当の問題ではないと思います。 (Rolando氏は、なぜあなたが十分な大きさに設定したのか、さらには大きすぎるとコメントしています)。
私はmax connections = 800に設定していますが、時々飽和してサーバーが停止してしまいます。
それは不明です。 800人のユーザーが"Sleep"モードに入っていても、システムへの影響はほとんどありません。 800のアクティブなスレッドは災害の原因になります。 いくつのスレッドが"run"されていますか?
スレッドがお互いにブロックしていませんか? デッドロックなどについての手がかりはSHOW ENGINE INNODB STATUSを参照してください。
Slowlogに表示されているクエリはありますか? それらを最適化してみましょう。
お使いのバージョンは? XtraDB(InnoDBの代替品)は、マルチコアの使用に適しています。 5.6.7ではさらに改善されています。
innodb_buffer_pool_instances -- これを 8 に変更してください(20G のバッファプールを想定)。
I/Oに縛られているのか、CPUに縛られているのか? その答えによって、解決策は大きく変わります。
SSD -- すべてのログファイルがSSD以外のドライブにある場合は、より良いかもしれません。
メモリは多いに越したことはありませんが、私の経験ではバッファプールのサイズがデータサイズに合わないことがほとんどです。多くのテーブルはバックアップテーブルのようにほとんどの場合非アクティブなので、innodbのバッファプールサイズはむしろ最終的なデータサイズに合うべきです。
アクティブなページの時間帯を指定するとパフォーマンスに影響しますが、バッファサイズを大きくしてもそれほどパフォーマンスが上がらない最適なポイントがあります。show engine innodb status`で推定/計算/測定することができます。