★大体の流れ。
・問題の場所にハードウェアを投入する
＝＝＞だから、リソースがたりないよ。。。。増やしてください。
＝＝＝＞はい、CPUを２倍に、MEMを４倍に、SSDにした。
・MySQL プロセスの設定を調整する
＝＝＞だから、MySQLの設定の問題だよ。。。適切に設定されていないでしょうか？
＝＝＝＞既に調整しました、今のこんなレポートがあり、見てください。
＝＝＝＝＝＞あ。。ちょっとプログラムを調査します。
・クエリーを最適化する
プロセスを調整するということは、メモリーを適切な場所に割り当て、そしてどんな種類の負荷を想定するかを mysqld に伝えるということです。ディスクを高速にするよりも、必要なディスク・アクセス数を減らした方が効果的です。同様に、MySQL プロセスが適切に動作している状態を確実にするということは、MySQL プロセスが、クエリーの処理にたくさんの時間を費やすことができ、一時ディスク・テーブルを扱うようなバックグラウンド・タスクの処理や、ファイルを開いたり閉じたりといった処理には、あまり時間を使わずに済むということです。
★★スロー・クエリーのログを取る
SQL サーバーでは、データ・テーブルはディスク上にあります。サーバーは索引によって、テーブル全体を検索することなく、テーブル内の特定のデータ行を見つけることができます。テーブル全体の検索が必要な場合、その検索はテーブル・スキャンと呼ばれます。ほとんどの場合、必要なものはテーブルの中のデータの、ごく小さなサブセットのみであるため、完全なテーブル・スキャンは大量のディスク I/O を浪費し、従って時間を浪費します。この問題は、データの連結が必要な場合にはさらに悪化します。連結されるデータ同士の間で、さらに多くの行を比較しなければならないからです。
slow_query_log スロークエリログの有効/無効を設定 0(または OFF)で無効、1(または ON)が有効
log-output 出力対象を設定 ファイル(FILE)、テーブル(TABLE)または両方を指定可能
slow_query_log_file ログ出力先ファイル名(絶対パス/想定パス指定可)
long_query_tim 指定した時間(sec)以上かかったクエリを記録(デフォルト10秒)
----->
The time to acquire the initial locks is not counted as execution
time.
log_queries_not_using_indexes インデックスを使用しないクエリを記録＝＝＝＝＝＞これは良さそう。。。
min_examined_row_limit 指定数以上のレコードを読み込んだ場合にクエリを記録
★★クエリーをキャッシュする
多くの LAMP アプリケーションはデータベースに大きく依存していますが、同じクエリーを何度も繰り返し行います。クエリーが実行されるたびに、データベースは同じ作業を行わなければなりません (つまりクエリーを解析し、その実行方法を決定し、ディスクから情報をロードし、その情報をクライアントに返します)。MySQL にはクエリー・キャッシュと呼ばれる機能があり、クエリーの結果が再度必要な時のために、その結果をメモリーに保存します。多くの場合、これによってパフォーマンスを劇的に向上させることができます。ただし注意する点として、クエリー・キャッシュはデフォルトで無効になっています。
mysql> SHOW STATUS LIKE 'qcache%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| Qcache_free_blocks | 5216 |
| Qcache_free_memory | 14640664 |
| Qcache_hits | 2581646882 |
| Qcache_inserts | 360210964 |
| Qcache_lowmem_prunes | 281680433 |
| Qcache_not_cached | 79740667 |
| Qcache_queries_in_cache | 16927 |
| Qcache_total_blocks | 47042 |
+-------------------------+------------+
★★制限を設定する
リスト 3. MySQL のリソース設定
set-variable=max_connections=500
set-variable=wait_timeout=10
max_connect_errors = 100
1 行目では最大接続数が指定されています。Apache の MaxClients の場合と同様、最大接続数の考え方は、処理することが可能な接続数の範囲でのみ接続を許可することです。これまでサーバーで観察された最大接続数の情報を得るためには、SHOW STATUS LIKE 'max_used_connections' を実行します。
Connections--->起動してから、成否かかわず接続試行の回数
max_used_connections-->同時に接続の最大数
show variables like '%timeout%'
-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| connect_timeout | 10 |
| delayed_insert_timeout | 300 |
| innodb_flush_log_at_timeout | 1 |
| innodb_lock_wait_timeout | 50 |
| innodb_rollback_on_timeout | OFF |
| interactive_timeout | 28800 |
| lock_wait_timeout | 31536000 |
| net_read_timeout | 30 |
| net_write_timeout | 60 |
| rpl_stop_slave_timeout | 31536000 |
| slave_net_timeout | 3600 |
| wait_timeout | 28800 |->単位は秒、８時間である
+-----------------------------+----------+
A large or growing Table_locks_waited value is the sign of a serious concurrency bottleneck.
Any long running select will block an insert or update from
happening. That will in turn prevent any further selects from occurring
until after the queued insert or update is complete.
Your computer —
with all its wonderful parallelism — will suddenly begin working in
serial, at least with respect to MySQL. It doesn't matter how many CPUs
you throw at it, only one will be used. A performance nightmare.
2 行目は、10 秒以上アイドル状態であったすべての接続を終了するように mysqld に指示されます。通常、LAMP アプリケーションでのデータベースへの接続は、Web サーバーがリクエストを処理するために必要な期間にのみ存在します。場合によると、負荷のある状態で接続が継続し、接続テーブルのスペースを占有してしまうことがあります。対話型のユーザーが多い場合、あるいはデータベースに対して永続的な接続を使用する場合には、この値を低く設定することは賢明ではありません。
最後の行は安全策です。もしホストとサーバーとの接続に問題があり、ホストがリクエストの処理をアボートする回数が多すぎる場合には、FLUSH HOSTS が実行されるまでホストはロックされます。デフォルトでは、10 回も失敗すればブロックされる条件としては十分です。この値を 100 に変更すると、サーバーにはどのような問題からも回復できるだけの十分な時間が与えられます。この値を大きくしても、あまり効果はありません。もしサーバーが 100 回試行しても 1 度も接続できないのであれば、まったく接続できない可能性が高いからです。
★★バッファーとキャッシュ
MySQL には、100 項目をはるかに超える調整可能な設定があります。しかし幸いなことに、そのごく一部をマスターすれば、必要なことのほとんどに対応することができます。こうした設定の適切な値を見つけるためには、SHOW STATUS コマンドを使ってステータス変数を調べ、その結果を基に、mysqld が期待どおり動作しているかどうかを判断します。
mysql> SHOW STATUS LIKE 'open%tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 5000 |
| Opened_tables | 195 |
+---------------+-------+
2 rows in set (0.00 sec)
-----症状例ーーーーー
Open_tables 512--->table_open_cacheに合う。
Opend_tablesーー＞開けたテーブル！
The number of tables that have been opened. If Opened_tables is big, your table_open_cache value is probably too small.
MySQL is MySQL はマルチスレッド化されているため、多数のクライアントが同時に同じものに対してクエリを使用することがあります。2 つのクライアントスレッドで 1 つのファイルに異なるステータスが発生する問題を最小にするため、同時に実行しているスレッドがそれぞれで無関係にテーブルを開きます。これはメモリの消費を増やしますが、一般にパフォーマンスは向上します。
table_open_cache is related
to
max_connections. For
example, for 200 concurrent running connections, specify a
table cache size of at least
200 *
N, where
N is the maximum number of tables
per join in any of the queries which you execute. You must
also reserve some extra file descriptors for temporary tables
and files
リスト 5. は、十分な数のスレッドがキャッシュされているかどうかを調べる方法を示しています。
リスト 5. スレッド使用状況の統計を示す
・mysql> SHOW STATUS LIKE 'threads%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Threads_cached | 27 |
| Threads_connected | 15 |
| Threads_created | 838610 |
| Threads_running | 3 |
+-------------------+--------+
4 rows in set (0.00 sec)
ここで重要な値は Threads_created です。この値は、mysqld が新しいスレッドを作成する必要が生じるたびにインクリメントされます。もし SHOW STATUS コマンドを継続して実行している間にこの数字が急激に増加するようであれば、スレッド・キャッシュを増加することを検討する必要があります。そのためには、my.cnf の中で、例えば thread_cache = 40 のようにします。
・
リスト 6. キーの効率を調べる
mysql> show status like '%key_read%';
+-------------------+-----------+
| Variable_name | Value |
+-------------------+-----------+
| Key_read_requests | 163554268 |
| Key_reads | 98247 |
+-------------------+-----------+
show status like '%key%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+--------+
| Com_assign_to_keycache | 0 |
| Com_preload_keys | 0 |
| Com_show_keys | 0 |
| Handler_read_key | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 319666 |ーー＞数であり、一個は1K
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
+------------------------+--------+
Key_reads はディスクへのアクセスが行われた要求の数を表し、Key_read_requests は要求の合計数を表します。Key_reads を Key_read_requests で割ると、ミス・レートが得られます。この場合では 1,000 回の要求に対して 0.6 回のミスです。もし 1,000 回の要求に対して 1 回を超えるミスが起きている場合には、キー・バッファーを増加することを検討する必要があります。例えば key_buffer = 384M とするとバッファーは 384MB に設定されます。
The Key_reads/Key_read_requests ratio should normally be less than 0.01. The Key_writes/Key_write_requests ratio is usually near 1 if you are using mostly updates and deletes, but might be much smaller if you tend to do updates that affect many rows at the same time or if you are using the DELAY_KEY_WRITE table option.
information_shema,mysql下のtableはMｙISAMを使っている。
・
一時テーブルは高度なクエリーに使われます (例えば GROUP BY 句の場合のように、さらに処理を行う前に一時的にデータを保存しなければならない場合など)。理想的には、そうしたテーブルをメモリー内に作成しますが、一時テーブルは大きくなりすぎすぎるとディスクに書き込まれます。リスト 7 は一時テーブルの作成に関連する統計を示しています。
リスト 7. 一時テーブルの使用状況を調べる
mysql> SHOW STATUS LIKE 'created_tmp%';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 30660 |
| Created_tmp_files | 2 |
| Created_tmp_tables | 32912 |
+-------------------------+-------+
3 rows in set (0.00 sec)
一時テーブルが使われるごとに Created_tmp_tables が増加します。またディスク・ベースのテーブルが使われると Created_tmp_disk_tables がインクリメントされます。この比率はどのようなクエリーが行われるかに依存するため、この比率に関する確かなルールはありません。時間をかけて Created_tmp_disk_tables を観察すると、作成されたディスク・テーブルの割合を知ることができ、そこから設定の効果を判断することができます。tmp_table_size と max_heap_table_size はどちらも一時テーブルの最大サイズを制御します。そのため、この両方を my.cnf の中で設定する必要があります。
・lock
ここが行列になっているみたい。
show status like '%_row_lock_%';
+-------------------------------+----------+
| Variable_name | Value |
+-------------------------------+----------+
| Innodb_row_lock_current_waits | 1 |
| Innodb_row_lock_time | 28066933 |単位はmilisecond-->7.796時間、昨日再起動したばかりので、1/3の時間ロック待ち
| Innodb_row_lock_time_avg | 1568 |待ち時間平均で1.5秒
| Innodb_row_lock_time_max | 51960 |51秒のロック、すごい
| Innodb_row_lock_waits | 17894 |ロック待ちのクエリ総数
+-------------------------------+----------+
・極端で理想の形が、slaveから読んで直接Masterを更新してどうかな？危険
・いろいろしてコミットしないの間もずっとロック持ちだ。。。。トランザクション
その意味で操作を細かくして、早いうちにコミットする