mysql 最適化のアイデアの紹介

この記事の内容は、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. インデックスまたはステートメント自体を調整します。

1. システム レベル

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: 書き込まれたデータの総量; これらの単位はキロバイトです。

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

負荷は高い方が良いと思いますか、それとも低い方が良いと思いますか?
実際の運用においては、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. 基本的な最適化

1. 最適化のアイデア

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

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

1. ハードウェアの最適化

##ホストの側面:

データベースの種類、ホストの 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. サーバー ハードウェアの最適化

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

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

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

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

1. システム最適化

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 スケジューリング戦略:

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

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

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

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

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

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

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

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

1. データベース パラメータの最適化

調整:
インスタンス全体 (高度な最適化、拡張)

接続層 (基本的な最適化)
適切な接続クライアントと接続方法を設定する

この記事はここで終了です。その他の魅力的なコンテンツについては、PHP 中国語 Web サイトの MySQL チュートリアル ビデオ 列に注目してください。 ！

以上がmysql 最適化のアイデアの紹介の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。