Cpanel でインストールした MYSQL サーバーで問題が発生しています。Web のトラフィックがそれほど多くない場合でも、サーバーで MySQL の使用率が 100% と表示されます。
私は 4CPU コアの 80GB SSD と 8GB RAM を搭載した CentOS 6.5x64 を実行しています。
my.cnf:
[mysqld]
max_connections=500
key_buffer=128M
myisam_sort_buffer_size=35M
join_buffer_size=1M
read_buffer_size=1M
sort_buffer_size=2M
table_cache=4000
thread_cache_size=286
interactive_timeout=25
wait_timeout=7000
connect_timeout=10
max_allowed_packet=268435456
max_connect_errors=10
query_cache_limit=2M
query_cache_size=256M
query_cache_type=1
tmp_table_size=16M
innodb_buffer_pool_size=134217728
open_files_limit=10000
[mysqld_safe]
[mysqldump]
quick
max_allowed_packet=16M
[myisamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M
MySQLTuner の出力は次のとおりです。
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 164M (Tables: 88)
[--] Data in InnoDB tables: 33M (Tables: 239)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 129
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 15m 2s (83K q [92.549 qps], 13K conn, TX: 44M, RX: 5M)
[--] Reads / Writes: 61% / 39%
[--] Total buffers: 544.0M global + 4.5M per thread (500 max threads)
[OK] Maximum possible memory usage: 2.7G (34% of installed RAM)
[OK] Slow queries: 0% (0/83K)
[OK] Highest usage of available connections: 23% (116/500)
[OK] Key buffer size / total MyISAM indexes: 128.0M/59.2M
[OK] Key buffer hit rate: 97.9% (24K cached / 527 reads)
[OK] Query cache efficiency: 54.3% (18K cached / 33K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (5 temp sorts / 388 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 374 total)
[OK] Thread cache hit rate: 99% (116 created / 13K connections)
[OK] Table cache hit rate: 98% (465 open / 474 opened)
[OK] Open file limit used: 3% (311/10K)
[!!] Table locks acquired immediately: 58%
[OK] InnoDB buffer pool / data size: 128.0M/33.6M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Optimize queries and/or use InnoDB to reduce lock wait
これが私のTOPの出力です:
top - 12:01:06 up 1 day, 1:02, 1 user, load average: 10.37, 9.22, 8.61
Tasks: 399 total, 1 running, 397 sleeping, 0 stopped, 1 zombie
Cpu(s): 84.1%us, 15.1%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.2%si, 0.7%st
Mem: 8191528k total, 6042780k used, 2148748k free, 242064k buffers
Swap: 0k total, 0k used, 0k free, 4785012k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
24515 mysql 20 0 2732m 165m 6264 S 303.8 2.1 42:43.18 mysqld
19471 root 20 0 174m 10m 6148 S 1.0 0.1 0:00.03 php
19523 root 20 0 172m 10m 5864 S 1.0 0.1 0:00.03 php
19533 root 20 0 172m 9m 5804 S 0.7 0.1 0:00.02 php
19535 root 20 0 172m 10m 5864 S 0.7 0.1 0:00.02 php
この問題の原因は何でしょうか?
サーバーがハングしないようにするには、my.cnf に変更を加える必要がありますか?
それとも、16 GB の RAM を搭載した新しいサーバーを購入する必要がありますか?
答え1
お客様の詳細を確認すると、次のような一般的な推奨事項を結論付けることができます。
あなたの読み取り:書き込み比率 (61:39)は、エンジンタイプを に変更する必要があるというヒントを与えます
Innodb。その前に、SHOW ENGINE INNODB STATUS\G; SHOW FULL PROCESSLIST;ピーク時には、クエリが の状態になる可能性が高いです
LOCK。テーブルロックは の大きな制限の 1 つですMyISAM。大量の同時書き込みと選択があり、クエリのパフォーマンスが一貫して高速でなければならない場合、より優れたロックメカニズム ( )Innodbにより が唯一の選択肢となります。row-level断片化されたテーブルの合計数: 129
OPTIMIZE TABLEパフォーマンス向上のため、すべてを実行してデフラグしてくださいあなたのslow_query.logQuery_time、Lock_time、Rows_sent、Rows_examined をチェックします。セットの比率が高いクエリが見つかった場合
Rows Sent / Rows Examined、それらのクエリは に適していますoptimization。slow_query.log 内の一部のクエリが CPU リソースを大量に使用している可能性があります。RAM と使用可能なディスク容量に関するサーバーのステータスを確認します。
free -m , df -h使用可能なメモリがある場合は、innodb_buffer_pool_sizeキャッシュを増やすために を増やします。MySQL設定でこれらのパラメータを調整すると良いでしょう
innodb_fast_shutdown=0 innodb_log_buffer_size innodb_log_file_size innodb_flush_method=O_DIRECT query_cache_size=0
警告：
チェックリスト1: MySQLプロセスのCPU、可能であればCPUを増やす
チェックリスト 2: RAM、データ セットが RAM に収まっているかどうか。
チェックリスト3 :接続関連のパラメータ
ABORTED_CONNECTS、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、、CONNECTIONSMAX_USED_CONNECTIONSOPEN_FILESOPEN_TABLESOPENED_FILESOPENED_TABLESQUERIESSLOW_QUERIESTHREADS_CONNECTEDTHREADS_CREATEDTHREADS_RUNNINGPERFORMANCE_SCHEMAQUERY_CACHE_SIZEWAIT_TIMEOUTLONG_QUERY_TIMEINTERACTIVE_TIMEOUTCONNECT_TIMEOUTMAX_CONNECTIONSLOCK_WAIT_TIMEOUTTX_ISOLATIONMAX_ALLOWED_PACKETGENERAL_LOGTABLE_OPEN_CACHESLOW_QUERY_LOGOPEN_FILES_LIMITINNODB_LOCK_WAIT_TIMEOUTチェックリスト4 :メモリ関連のパラメータ
key_buffer_size、、、、、、、、、、、、query_cache_sizeinnodb_buffer_pool_size,innodb_log_buffer_sizemax_connectionsread_buffer_sizeread_rnd_buffer_sizesort_buffer_sizejoin_buffer_sizebinlog_cache_sizethread_stacktmp_table_sizeチェックリスト 5: その他の重要な InnoDB パラメータ innodb_log_file_size、innodb_file_per_table、innodb_flush_log_at_trx_commit、innodb_flush_method、innodb_fast_shutdown
チェックリスト 6: mysqltuner
インデックスなしで実行された結合: テーブル キャッシュ ヒット率:
-------- MyISAM Metrics -------------------------------------------- [!!] [!!] Write Key buffer hit rate: -------- InnoDB Metrics -------------------------------------------- [!!] InnoDB buffer pool / data size: [!!] Ratio InnoDB log file size / InnoDB Buffer pool size : [!!] InnoDB buffer pool instances:チェックリスト7: プロセスリスト以下のクエリは、長時間「データ送信中」の状態になっています。
チェックリスト 8: 遅いクエリログ - Rows_examined と Rows_sent の比率が非常に高い、
チェックリスト 9: 重複したインデックス (ある場合)
チェックリスト 10: 低速クエリの計画を説明する
チェックリスト 11: where 句の列が適切にインデックスされているかどうかを確認する (インデックスなしのクエリ)
チェックリスト 12: テーブル ロック、メタデータ ロック、デッドロックがあるかどうかを確認します。
チェックリスト 13: メンテナンス ジョブが一定間隔で実行されているか (例: mysqlcheck、mysqldump など)
チェックリスト 14: ハードウェア リソース、低速ディスク、RAID の再構築、ディスク I/O、飽和したネットワーク、スレッド数の増加に伴うスループットに対するネットワーク帯域幅。