mysql 最適化のアイデアの紹介-mysql チュートリアル-php.cn

この記事の内容は、mysql 最適化のアイデアの紹介です。一定の参考価値があります。困っている友人は参考にしてください。お役に立てれば幸いです。

データベースレベルの問題解決のアイデア

一般的な緊急時チューニングのアイデア:
突然の業務処理の遅れが発生すると、通常の業務処理ができなくなります。すぐに解決する必要があるシナリオです。

1、show processlist

2、explain select id ,name from stu where name='clsn'; # ALL id name age sex
select id,name from stu where id=2-1 函数 结果集>30;
show index from table;

3、通过执行计划判断，索引问题(有没有、合不合理)或者语句本身问题

4、show status like '%lock%'; # 查询锁状态
kill SESSION_ID; # 杀掉有问题的session

ログイン後にコピー

一般的なチューニングのアイデア:
たとえば、定期的なビジネスラグの場合、ビジネスは毎日 10 ～ 11 時に非常に遅くなりますが、それでも使用でき、その後は問題なく動作します。この期間。

1. スローログを確認し、スローログを分析し、遅いクエリステートメントを分析します。

2. 特定の優先順位に従って、すべての遅いステートメントを 1 つずつチェックします。

3. トップ SQL を分析し、Explain デバッグを実行して、ステートメントの実行時間を確認します。

4. インデックスまたはステートメント自体を調整します。

システムレベル

cpu アスペクト:
vmstat、sar top、htop、nmon、mpstat

メモリ:
free、ps -aux、

IO デバイス (ディスク、ネットワーク):
iostat、ss、netstat、iptraf、iftop、lsof、

vmstat コマンドの説明:
Procs: r は方法を表示します。多くのプロセスが CPU 時間を待機しています。 b 無中断スリープ中のプロセスの数を表示します。 I/O

Memory を待機中: swpd は、ディスクにスワップされたデータブロックの数を表示します。未使用のデータブロック、ユーザーバッファデータブロック、およびオペレーティングシステムによって使用されるデータブロックの数

スワップ: オペレーティングシステムが 1 秒あたりにディスクからメモリへ、およびメモリからディスクへスワップするデータブロックの数。 s1 と s0 は 0

Io: デバイス b0 に書き込まれ、デバイス b1 から読み取られた 1 秒あたりのデータブロックの数。ディスク I/O を反映します。

システム: 1 秒あたりに発生する割り込み (in) およびコンテキストスイッチ (cs) の数を表示します。

Cpu: ユーザーコード、システムコードの実行に使用される数を表示します。、アイドル、I/O を待機している CPU 時間

iostat コマンドの説明
コマンド例: iostat -dk 1 5
iostat -d -k -x 5 (デバイスの使用状況 (%util) を表示し、応答時間 (待機))

tps: デバイスの 1 秒あたりの送信数。「転送」とは「I/O リクエスト」を意味します。複数の論理リクエストを「単一の I/O リクエスト」に組み合わせることができます。

iops: ハードウェアが工場から出荷されるとき、製造元は 1 秒あたりの最大 IO 数を定義します。「1 回の転送」リクエストのサイズは不明です。

kB_read/s: 1 秒あたりにデバイスから読み取られたデータ量 (ドライブで表現)、

KB_wrtn/s: 1 秒あたりにデバイスに書き込まれたデータ量 (ドライブで表現)。

kB_read: 読み取られたデータの総量;

kB_wrtn: 書き込まれたデータの総量; これらの単位はキロバイトです。

システムレベルの問題の解決策

負荷は高い方が良いと思いますか、それとも低い方が良いと思いますか?
実際の運用においては、CPU 使用率が 90% を超えない限りは問題ないと考えられています。

もちろん、次の特殊な状況は除外されません:
問題 1: CPU 負荷が高く、IO 負荷が低い
メモリ不足

ディスクパフォーマンスの低下

SQL の問題- ----->データベース層に移動して SQL 問題をさらにトラブルシューティングしてください

IO に問題があります (ディスクが重要、RAID 設計が良くない、RAID が劣化しています) 、ロックされており、単位時間あたりの tps が高すぎます)

tps が高すぎます: 多数の小規模なデータ IO、多数のフルテーブルスキャン

問題 2: 高 IO負荷、低 CPU 負荷
多数の小規模な IO 書き込み操作:

自動コミット。多数の小規模な IO を生成します。

IO/PS はディスクの固定値です。ハードウェアが工場から出荷されるとき、製造元は 1 秒あたりの最大 IO 数を定義します。

大量の大規模な IO 書き込み操作

SQL の問題が発生する可能性が比較的高い

問題 3: IO と CPU の負荷が非常に高い
ハードウェアは不十分であるか、SQL に問題があります

5. 基本的な最適化

最適化のアイデア

問題点の特定:
ハードウェア--> ; システム --> アプリケーション -- > データベース --> アーキテクチャ (高可用性、読み取り/書き込み分離、サブデータベースおよびサブテーブル)

処理方向:
明確な最適化目標、パフォーマンスとセキュリティの間で妥協し、問題が発生する前に防止します

ハードウェアの最適化

##ホストの側面:

データベースの種類、ホストの CPU の選択、メモリ容量の選択に応じて、ディスクの選択

メモリとディスクリソースのバランスをとる

ランダム I/O およびシーケンシャル I/O

ホスト RAID カードの BBU (バッテリーバックアップユニット) がオフになっています

CPU の選択:

CPU の 2 つの重要な要素: コアの数とメイン周波数

さまざまなビジネスタイプに応じて選択:

CPU 集中型: より多くの計算、高いメイン周波数とより多くのコアを備えた OLTP CPU

IO 集中型: クエリの比較では、OLAP コアの数が多く、メイン周波数は必ずしも高いとは限りません。

メモリの選択:

OLAP タイプのデータベースはより多くのメモリを必要とし、これはデータ取得のレベルに関係します。

OLTP タイプのデータには通常、メモリ内の CPU コア数の 2 ～ 4 倍が必要であり、ベストプラクティスはありません。

ストレージ:

保存されているデータの種類に応じて異なるストレージデバイスを選択します

適切な RAID レベルを構成します (RAID 5、RAID 10、ホットスペアディスク)

# OS については、あまり特別な選択は必要なく、冗長化するのがベストです (raid1) (ssd、sas、sata)

raid カード: ホスト RAID カードの選択:

実装動作システムディスクの冗長性 (raid1)

メモリとディスクリソースのバランスをとる

ランダム I/O およびシーケンシャル I/O

ホスト RAID カードバックアップユニットの BBU (バッテリー)閉じる

ネットワーク機器:
より高いトラフィックをサポートするネットワーク機器を使用してください (スイッチ、ルーター、ネットワークケーブル、ネットワークカード、HBA カード)

注: システムを最初に設計する際には、上記の計画を考慮する必要があります。。

サーバーハードウェアの最適化

1. 物理ステータスライト:

2. 付属の管理デバイス: リモートコントロールカード (FENCE デバイス: ipmi ilo idarc) )、電源オン/オフ、ハードウェア監視。

3. サードパーティの監視ソフトウェアおよび機器 (SNMP、エージェント) は物理設備を監視します

4. ストレージ機器: 内蔵監視プラットフォーム。 EMC2 (HP が買収)、日立 (HDS)、IBM ローエンド OEM HDS、ハイエンドストレージは独自のテクノロジー、Huawei ストレージ

システム最適化

Cpu:
基本的に調整は必要ありません。ハードウェアの選択に集中してください。

メモリ:
基本的に調整は必要ありません。ハードウェアの選択に集中してください。

SWAP:
MySQL はスワップの使用を避けようとします。 Alibaba Cloud サーバーのデフォルトのスワップは 0

IO:
raid、lvm なし、ext4 または xfs、ssd、IO スケジューリング戦略

スワップ調整 (スワップパーティションを使用しない)

このパラメータは、Linux がスワップの使用を優先するか、ファイルシステムキャッシュの解放を優先するかを決定します。メモリが不足している状況では、値を低くするとファイルシステムキャッシュが解放される傾向があります。もちろん、このパラメーターはスワップを使用する可能性を減らすだけであり、Linux によるスワップの使用を防ぐことはできません。

MySQL 設定パラメータ innodb_flush_method を変更し、O_DIRECT モードを有効にします。この場合、InnoDB のバッファプールはファイルシステムキャッシュを直接バイパスしてディスクにアクセスしますが、REDO ログは引き続きファイルシステムキャッシュを使用します。 Redo ログは上書きモードであることに注意してください。ファイルシステムキャッシュが使用されていても、あまり多くの量を消費することはありません

IO スケジューリング戦略:

システムパラメーターの調整

Linux システムカーネルパラメータの最適化:

ユーザー制限パラメータ (MySQL は次の構成を設定する必要はありません):

アプリケーションの最適化

ビジネスアプリケーションとデータベースアプリケーションは独立しています。ファイアウォール: iptables、selinux およびその他の役に立たないサービス (終了):

グラフィカルインターフェイスを備えたサーバーをインストールする場合は、グラフィカルインターフェイスランレベル 3 を起動しないでください。さらに、将来的に私たちのビジネスが現実になるかどうか、MySQL が必要か、それとも別の種類のデータベースを使用するかを考えてください。データベースを使用する最高の状態は、データベースを使用しないことです。

6. データベースの最適化
SQL 最適化の方向:
実行計画、インデックス、SQL 書き換え

アーキテクチャ最適化の方向:
高可用性アーキテクチャ、高パフォーマンスアーキテクチャ、サブデータベースサブテーブル