別のMySQL DBA: MySQLサーバをチューニングする方法

Original post: http://anothermysqldba.blogspot.com/2013/05/how-to-tune-mysql-server.html

このブログの記事、ブログのシリーズの一部です

MySQLサーバチューニング質問

MySQLのスロークエリと関連の懸念を識別する方法

MySQLサーバをチューニングする方法

サーバー自体で今継続するには：

まあただ言ってほかにピーターが言う何をすべきか、彼らはこの自体を解決することができる方法の見直し例をすることができます。

まず第一に、あなたはあなたのQ＆A Percona経由で無料でご利用いただけますバージョンに対してmy.cnfのファイルを比較することができます。これは理想的な解決法は？いいえ、それはあなたが彼らのすべての質問に答えた後、あなたはそれらの構成ウィザードを介して、あなたの設定ファイルで新鮮な表情を取ることができます。

https://tools.percona.com/wizard

innodb_buffer_pool_size -

select @@innodb_buffer_pool_size;

innodb_buffer_pool_sizeを設定することでこれまでのところ、このトピック上でMySQLのInnoDB database.Some良い読書のための最も重要な設定の一つであることは以下の通りです：

あなたは、innodb_buffer_pool_sizeを設定するための "ワンサイズですべてのクエリに適合する"がないことに留意する必要があります。我々は長年にわたって表面化している、いくつかの一般的なガイドラインや提案を持っている（以下のように）、我々はまた、最高のオプションのいくつかの異なった理論を持っている。

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;


SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,
(SELECT 3 PowerOf1024) B;

それはあなたのinnodb_buffer_pool_size変数としてデフォルト8Mセットがある場合、これはあなたが調整する最初の設定のいずれかでなければなりませんと言うことは安全です。

基本的に、これはあなたがそれを与えるために余裕がある使用可能なすべてのメモリを持つことができます。他に何がサーバー上のメモリを割り当てる必要があるように、もちろん、これは考慮いくつかの要因を取る？ディスクI / Oパフォーマンスと、この変数は、I / Oは、テーブルへのアクセスを要求されるべきより少ないディスクを持つことができるよう、より多くのメモリが重要であることに留意してください。

私たちはまた、このことを考えてみましょう....
一般的な推奨事項を設定することですのでthe innodb_log_file_sizeでバッファー·プール·サイズの25％我々は我々が持っているものを評価しなければならないinnodb_log_file_size per 男爵のコンセプトその後innodb_buffer_pool_sizeどのように関連するかを見て見て。

たとえば、論理回路からの後男爵の記事：

> show engine innodb status\G select sleep(60); show engine innodb status\G
Log sequence number 3982683217
1 row in set (0.01 sec)

1 row in set (59.99 sec)

Log sequence number 3991367755
1 row in set (0.01 sec)

> SET @sequence1=3982683217;
Query OK, 0 rows affected (0.00 sec)
> SET @sequence2=3991367755;
Query OK, 0 rows affected (0.00 sec)
> select (@sequence2 - @sequence1) / 1024 / 1024 as MB_per_min;
+------------+
| MB_per_min |
+------------+
| 8.28222084 |
+------------+
1 row in set (0.00 sec)
> select ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 as MB_per_hour ;
+--------------+
| MB_per_hour |
+--------------+
| 496.93325043 |
+--------------+
1 row in set (0.00 sec)
> select ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 as estimated_buffer_pool ;
+-----------------------+
| estimated_buffer_pool |
+-----------------------+
| 1987.73300171 |
+-----------------------+
1 row in set (0.00 sec)
> select ( ( ( (@sequence2 - @sequence1) / 1024 / 1024 )* 60 ) * 4 ) / 1024 as estimated_buffer_pool_GB ;
+--------------------------+
| estimated_buffer_pool_GB |
+--------------------------+
| 1.941145509481 |
+--------------------------+
1 row in set (0.00 sec)

今、あなたは "ガイドライン"にこれらの結果を比較する

SELECT CONCAT(ROUND(KBS/POWER(1024,
-> IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999),
-> SUBSTR(' KMG',IF(PowerOf1024<0,0,
-> IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size
-> FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
-> WHERE engine='InnoDB') A,
-> (SELECT 3 PowerOf1024) B;
+-------------------------------------+
| recommended_innodb_buffer_pool_size |
+-------------------------------------+
| 1G |
+-------------------------------------+

あなたは、異なる結果を得る。男爵のチェックが（あなたはトラフィックのピーク時にこれを確認する必要がある理由です）、それが内で実行されている時間枠に依存しているものの、それは、あなたのトラフィックと利用に関するより現実的なビューを与えることができます。私はこの単純な例ではありません2G 1Gにinnodb_buffer_pool_sizeの設定になります。

@シーケンス1セット= 3982683217;
@シーケンス2セット= 3991367755;
選択 - MB_per_minとして（@ @シーケンス2シーケンス1）/ 1024/1024
選択（（@シーケンス2 - @シーケンス1）/ 1024分の1024）MB_per_hourとして* 60;
選択して（（（@シーケンス2 - @シーケンス1）/ 1024分の1024）* 60）* 4 estimated_buffer_poolとして;
選択して（（（（@シーケンス2 - @シーケンス1）/ 1024分の1024）* 60）* 4）estimated_buffer_pool_GBとして/ 1024;

innodb_log_file_size -

select @@innodb_log_file_size;

私は楽しむことを発見した男爵の概念を innodb_log_file_sizeを設定する方法について。真の測定値を取得するにはピーク時に彼の提案を実行することを忘れないでください。大きいこれらのファイルのサイズが大規模なデータセットとのより良いパフォーマンスが、何が自由のために来るには、回復時間が増加します。それはあなたが待っていると永遠にプロセスを見ていることを収入に依存するデータベースになるまで増加し、回復時間は、いくつかの主要な関心事のように聞こえないかもしれません。一般的な推奨事項は、バッファー·プール·サイズの25％でそれを設定することです。

select ( @@innodb_buffer_pool_size * .25 )

innodb_log_buffer_size -

select @@innodb_log_buffer_size;

私はかつて= 128Mをinnodb_log_buffer_sizeするサーバー上でこのセットを持っていたリコール。それは良い選択だったのですか？いいえ、それは私がそうする必要はありませんでしたときに私は記憶を望ん意味しません。それは非常に重いデータベースの書き込みだったが、この設定は、高に非常に可能性があった。最初のデフォルトに焦点を当て、それ（8MB - 16MB）ダブルマックス。

innodb_additional_mem_pool_size -

select @@innodb_additional_mem_pool_size;

ピーターは彼には、この設定に対応してブログの記事。あなたはそれに対処し、他の設定は、私の考えで対処された後にオプションを評価することができます。

innodb_flush_log_at_trx_commit -

select @@innodb_flush_log_at_trx_commit;

> SET GLOBAL innodb_flush_log_at_trx_commit = 2;
ピーターは彼には、この設定に対応してブログの記事だけでなく、。 2 - これは0から調整することによって、一般的に見落とさ変数と価値のテストパフォーマンスの向上です。リスクを念頭に置いてください。

thread_cache -

select @@thread_cache_size;
+---------------------+
| @@thread_cache_size |
+---------------------+
| 50 |
+---------------------+

http://dev.mysql.com/doc/refman/5.5/en/connection-threads.html

>show status like 'threads_created';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Threads_created | 4 |
+-----------------+-------+
1 row in set (0.00 sec)

> show status like 'connections';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Connections | 962 |
+---------------+-------+
1 row in set (0.00 sec)

> SELECT 100 - ((4 / 962) * 100);
+-------------------------+
| 100 - ((4 / 962) * 100) |
+-------------------------+
| 99.5842 |
+-------------------------+

このステータスに目が離せない： 'threads_created'のような状態を示し;
それが登りなら、あなたはそれがあまりにも低く設定されて、それが提起する必要があります。

query_cache_sizeの -

select ((@@query_cache_size / 1024) / 1024);

許容値は、1024の倍数である。

http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html＃sysvar_query_cache_size

この設定に注意を払うには、より多くのあなたがアプリケーションになり依存読み。私はそれがkey_buffer = 16Mに設定されていて、私は、おそらくことを倍増している必要があります可能性を使用して実行するために使用。

key_buffer_sizeは -

select @@key_buffer_size;

MySQLはデフォルトのストレージエンジンとしてInnoDBテーブルへの切り替えで、それはおそらく我々はMyISAMテーブルとして作成少なくテーブルを見ることができ、いくつかある。それはちょうど意見事実ではありません。あなたはMyISAMテーブルを利用しない場合は、key_buffer_sizeはあなたにとって非常に重要でしょう。

レビューShlomi Noachのポストは、あなたがどの簡単にどのテーブルれるエンジンの種類を見つけるのに役立ちます。
"（INFORMATION_SCHEMAに提示ほぼ正確に）テーブルサイズごとに参照してください" -Shlomi Noach。

> SHOW VARIABLES LIKE 'key_buffer_size';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| key_buffer_size | 15728640 |
+-----------------+----------+

table_cacheの -

show variables like '%table%cache%';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| table_definition_cache | 4096 |
| table_open_cache | 4096 |
| table_open_cache_instances | 1 |
+----------------------------+-------+

マニュアルによると、これが判断するための良い式ですtable_definition_cacheサイズを。

SELECT 400 + (@@table_open_cache / 2);

> SHOW status like '%Opened_tables%';

table_open_cache_instances
8または16の値を定期的に16以上のコアを使用するシステムでは推奨され、デフォルトは1です。

うまくいけば、これは、あなたのMySQLデータベースから最高を得るためにレビューする必要がある変数と設定を公開します。

別のMySQL DBA

Pages

2013年5月7日火曜日

MySQLサーバをチューニングする方法