MySQL最適化の経験と方法の概要

会社のウェブサイトのアクセス量はますます大きくなって、MySQLは自然にボトルネックになって、そのため最近私はずっとMySQLの最適化を研究して、第1歩は自然にMySQLシステムのパラメータの最適化を思いついて、1つのアクセス量の大きいウェブサイト(日20万人以上)のデータベースシステムとして、MySQLのデフォルトのシステムのパラメータがMySQLを運行することができることを期待することができません.
Apache、PHP、MySQLのアーキテクチャでは、MySQLがパフォーマンスに与える影響が最も大きく、重要なコア部分でもあります.Discuzに対して!フォーラムプログラムもそうですが、MySQLの設定が合理的に最適化されているかどうかは、フォーラムの速度と負荷量に直接影響します.また、MySQLは最適化の最も難しい部分でもあり、MySQLの専門知識を理解するだけでなく、長時間の観察統計と経験に基づいて判断し、合理的なパラメータを設定する必要があります.以下に私達はMySQLの最適化のいくつかの基礎を理解して、MySQLの最適化私は2つの部分に分けて、1つはサーバーの物理のハードウェアの最適化で、2つはMySQL自身(my.cnf)の最適化です.
一、サーバーハードウェアがMySQL性能に与える影響
①ディスクシーク能力(ディスクI/O)現在の高回転SCSIハードディスク(7200回転/秒)を例にとると、このハードディスクは理論的には毎秒7200回シークされているが、これは物理的特性によって決定され、変更することができない.MySQLでは毎秒、大量で複雑なクエリー操作が行われており、ディスクの読み書き量が予想されます.そのため、ディスクI/OはMySQLの性能を制約する最大の要因の一つとされ、1日平均アクセス数が100万PV以上のDiscuzに対して!フォーラム、ディスクI/Oの制約のため、MySQLの性能はとても低いことができます!この制約要因を解決するには、RAID-0+1ディスクアレイを使用し、RAID-5を使用しないように注意してください.MySQLのRAID-5ディスクアレイでの効率は期待通りに速くありません.
②CPUはMySQLアプリケーションに対して、S.M.P.アーキテクチャの多重対称CPUを推奨します.例えば、Intel Xeon 3.6 GHzのCPUを2つ使用することができます.今、mysqlだけでなく、4 Uのサーバでデータベースサーバを専門にすることをお勧めします.
③物理メモリはMySQLを使用するDatabase Server 1台にとって、サーバーメモリは2 GB以下ではなく、4 GB以上の物理メモリを使用することをお勧めしますが、メモリは現在のサーバーにとって無視できる問題と言え、仕事中にハイエンドサーバーに遭遇した場合、基本的にメモリは16 Gを超えています.
二、MySQL自身の要因
上記のサーバハードウェア制約要因を解決した後、MySQL自身の最適化がどのように動作しているかを見てみましょう.MySQL自体の最適化は主にプロファイルmyです.cnfの各パラメータを最適化調整します.パフォーマンスに大きな影響を及ぼすパラメータについて説明します.なぜならcnfファイルの最適化設定は、サーバハードウェア構成と密接に関係しているため、仮想的なサーバハードウェア環境を指定します.

CPU:2 Intel Xeon 2.4 GHz

メモリ:4 GB DDR

HDD:SCSI 73 GB(一般的な2 Uサーバ).

次に、上記のハードウェア構成に基づいて、最適化されたmyを結合する.cnfで説明する:

#vim/etc/my.cnf以下はmyのみをリストする.cnfファイルの[mysqld]段落の内容、その他の段落の内容はMySQLの実行性能にわずかな影響を及ぼすため、一応無視します. 

[mysqld]  

port = 3306

serverid = 1

socket =/tmp/mysql.sock  

skip-locking  

#MySQLの外部ロックを回避し、エラーの発生率を低減し、安定性を向上させる. 

skip-name-resolve  

#MySQLは外部接続のDNS解析を禁止します.このオプションを使用すると、MySQLがDNS解析を行う時間を排除できます.ただし、このオプションをオンにすると、すべてのリモートホスト接続許可はIPアドレス方式を使用しなければなりません.そうしないと、MySQLは接続要求を正常に処理できません. 

back_log = 384

#back_logパラメータの値は、MySQLが新しいリクエストへの応答を一時的に停止するまでの短時間でスタックに存在できるリクエストの数を示します.システムが短時間で多くの接続を持っている場合、そのパラメータの値を大きくする必要があります.このパラメータの値は、到来するTCP/IP接続のリスニングキューのサイズを指定します.異なるオペレーティングシステムは、このキューサイズに独自の制限があります.back_を設定しようとするlogがあなたのオペレーティングシステムより高い制限は無効です.既定値は50です.Linuxシステムでは512未満の整数を推奨します. 

key_buffer_size = 256M

#key_buffer_sizeインデックスに使用するバッファサイズを指定し、インデックス処理のパフォーマンスを向上させます.このパラメータは、4 GB程度のサーバに対して256 Mまたは384 Mとすることができる.注意:このパラメータ値の設定が大きすぎると、サーバ全体の効率が低下します. 

max_allowed_packet = 4M

thread_stack = 256K

table_cache = 128K

sort_buffer_size = 6M

#クエリー・ソートで使用できるバッファ・サイズ.注意:このパラメータに対応する割り当てメモリは、接続ごとに排他的であり、100個の接続がある場合、実際に割り当てられた合計ソートバッファサイズは100です.× 6 ＝ 600MB.したがって,4 GB程度のサーバについては6−8 Mを推奨する. 

read_buffer_size = 4M

#読み込みクエリー操作で使用できるバッファサイズ.とsort_buffer_sizeと同様に、このパラメータに対応する割り当てメモリも接続ごとに使用されます. 

join_buffer_size = 8M

#クエリー操作で使用できるバッファサイズとsort_buffer_sizeと同様に、このパラメータに対応する割り当てメモリも接続ごとに使用されます. 

myisam_sort_buffer_size = 64M

table_cache = 512

thread_cache_size = 64

query_cache_size = 64M

#MySQLクエリーバッファのサイズを指定します.Qcache_lowmem_prunesの値は非常に大きく、バッファが足りないことが多いことを示しています.Qcache_hitsの値が非常に大きい場合は、クエリー・バッファが頻繁に使用されていることを示します.この値が小さいと、逆に効率に影響を与える場合は、クエリー・バッファを使用しないことが考えられます.Qcache_free_blocksは、この値が非常に大きい場合、バッファに破片が多いことを示します. 

tmp_table_size = 256M

max_connections = 768

#MySQLが許可する最大接続プロセス数を指定します.フォーラムにアクセスするときにToo Many Connectionsのエラーメッセージが頻繁に表示される場合は、このパラメータ値を大きくする必要があります. 

max_connect_errors = 10000000

wait_timeout = 10

#1つのリクエストの最大接続時間を指定し、4 GB程度のメモリのサーバに対して5-10に設定できます. 

thread_concurrency = 8

#このパラメータの値はサーバ論理CPU数*2であり、この例ではサーバに物理CPUが2個あり、各物理CPUはH.Tハイパースレッドをサポートするので、実際の値は4*2=8

である.

skip-networking  

#このオプションをオンにするとMySQLのTCP/IP接続方式を完全にオフにすることができます.WEBサーバがリモート接続でMySQLデータベースサーバにアクセスしている場合は、このオプションをオンにしないでください.正常に接続できません. 

table_cache=1024

#物理メモリが大きいほど、設定が大きくなる.デフォルトは2402で、512-1024に最適な

に設定されています.

innodb_additional_mem_pool_size=4M

#デフォルトは2 M

innodb_flush_log_at_trx_commit=1

#0に設定するとinnodb_まで待ちますlog_buffer_size列がいっぱいになったら統一的に保存し、デフォルトは1

です.

innodb_log_buffer_size=2M

#デフォルトは1 M

innodb_thread_concurrency=8

#あなたのサーバーのCPUはいくつかあっていくつに設定して、デフォルトで普通は8

を使うことを提案します

key_buffer_size=256M

#デフォルトは218で、128最適

に調整されます.

tmp_table_size=64M

#デフォルトは16 Mで、64-256最掛

に変更されました.

read_buffer_size=4M

#デフォルトは64 K

read_rnd_buffer_size=16M

#デフォルトは256 K

sort_buffer_size=32M

#デフォルトは256 K

thread_cache_size=120

#デフォルトは60

query_cache_size=32M

※注意すべきことは、具体的な状況を具体的に分析する必要がある場合が多いことです.
もしKey_readsが大きすぎるならmy.cnfでKey_buffer_sizeが大きくなり、Key_をキープreads/Key_read_requestsは少なくとも1/100以上で、小さいほどいいです.