データベースの最適化を説明する例

データベースの最適化についてインターネットで検索すると、基本的に SQL レベルからの最適化が行われ、データベース自体のインスタンスの最適化について言及されることはほとんどありません。たとえ存在するとしても、それはすべて特定のデータベースのインスタンスの最適化に基づいています。この記事では、現在市場に出ているすべての主流のデータベース (Oralce、MySQL、POSTGRES、Dameng) のインスタンスの最適化について説明します。記事の構成に従って、次のことができます。データベースのパフォーマンスの 80% 以上を使用します。

#データベース最適化手法

この部分は理論的な知識です。興味のない学生は、パラメーター設定の部分に直接ジャンプできます。

データベース最適化の目標

推奨される「mysql ビデオ チュートリアル」

さまざまな役割に応じて、データベースの最適化は次の目標に分かれています:

#ビジネスの観点 (主要ユーザー):

ユーザー ページの応答時間の短縮

データベースの観点 (開発):

データベース SQL 応答時間の短縮

データベース サーバーの観点 (運用)次元):

# データベース サーバーの物理リソースを最大限に活用する

#データベース サーバーの CPU 使用率を削減する

データベース サーバーの IO 使用量の削減

データベース サーバーのメモリ使用量の削減

##インジケーター

1. SQL の平均応答時間が短くなります

a. 最適化前: データベースの平均応答時間は 500ms#b. 最適化の対象: データベースの平均応答時間は 200ms

2. データベース サーバー CPU 使用率が低下します

#a. 最適化前: データベース ピーク時の CPU 使用率は 70%

##b. 最適化目標: データベース ピーク時の CPU 使用率は 50%データベースのピーク期間

3 . データベース サーバーの IO 使用率が低くなります

#a. 最適化前: データベース IO WAIT は 30%

b. 最適化ターゲット: データベース IO WAIT が減少します10% より

データベース最適化に関する誤解

データベースを最適化する際には、次のような誤解がある可能性があります:

1. 最適化する前に、十分な理解が必要です。データベースの内部原理を理解する

最適化には「ルーチン」があり、これらの「ルーチン」に従うことでデータベースの最適化をうまく完了することもできます

2. データベース パラメータを継続的に調整することで、最終的に最適化を達成できます

設計ではどんなに無理なパラメータを調整してもうまくいかないこともある

3.オペレーティングシステムのパラメータを継続的に調整し続けることで最終的に最適化が達成できる

同上

4. データベースのパフォーマンスはアプリケーションとデータベース アーキテクチャによって決まり、アプリケーション開発とはほとんど関係ありません

##どころか、アプリケーション開発とは多くの関係があります

5. 読み書きを分離し、データベースとテーブルに分割する必要がある

データ量一定の割合に達した場合のみ読み書きを分離し、分割する必要があるテーブルを別のデータベースに分割しないと、複雑さが増すだけです。一般的に、Oracle の単一テーブル サイズは 1 億に達する可能性があり、MySQL のテーブル サイズは 1,000 万から 2,000 万に達する可能性があります。

データベース最適化プロセス

完全なデータベース最適化プロセスは次のとおりです。

#まず、最適化問題を可能な限り理解し、問題が発生している間のシステム情報を収集し、アーカイブする必要があります。現在のシステム問題のパフォーマンスに基づいて最適化目標を策定し、顧客とコミュニケーションして目標について合意に達し、一連のツールを使用してシステムの問題を分析し、最適化計画を策定し、計画のレビューが完了した後、各担当者が実行します。最適化目標が達成された場合は、最適化レポートが作成されますが、そうでない場合は、最適化計画を再策定する必要があります。

データベース インスタンスの最適化

データベース インスタンスの最適化は、次の 3 つの原則に従います。

ログは小さくてはならず、キャッシュは十分に大きくなければならず、接続は十分でなければなりません。 。

データベース トランザクションが送信された後、データの耐久性を確保するために、トランザクションによるデータ ページへの変更をディスクにフラッシュ (fsync) する必要があります。このディスク フラッシュはパフォーマンスが低いランダム書き込みであるため、トランザクションが送信されるたびにディスクがフラッシュされると、データベースのパフォーマンスに大きな影響を与えます。データベースは、アーキテクチャ設計で次の 2 つの最適化手法を採用します:

a. まずトランザクションをログ ファイル RedoLog (WAL) に書き込み、ランダム書き込みをシーケンシャル書き込みに最適化します。 1 つ追加します。 レイヤー キャッシュ構造バッファーは、各書き込みを順次書き込みに最適化します。したがって、ログとキャッシュはデータベース インスタンスにとって特に重要です。十分な接続がない場合、データベースは直接例外をスローし、システムにアクセスできなくなります。

データベース パラメーターの最適化主流のデータベース アーキテクチャには次の共通点があります:

データ キャッシュ

SQL 解析領域

メモリのソート

REDOとUNDO

ロック、LATCH、MUTEX

監視と接続

ファイルの読み取りと書き込みのパフォーマンス

次に、データベースの最高のパフォーマンスを達成するために、さまざまなデータベースに応じてパラメーターを調整します。

オラクル

#パラメータ分類 パラメータ名 パラメータ値 備考 ##データ キャッシュ データキャッシュ SQL解析 監視と接続 その他

SGA_TAGET、MEMORY_TARGET	物理メモリ 70 ～ 80%	大きいほど良い
DB_CACHE_SIZE	物理メモリ 70-80%	大きいほど良い
SHARED_POOL_SIZE	4-16G	あまり大きく設定することはお勧めできません
PROCESSES、SESSIONS、 OPEN_CURSORS	ビジネス要件に応じて設定	通常、推定ビジネス接続数の 120%
SESSION_CACHED_CURSORS	は 200 より大きいです	ソフト分析

MYSQL(INNODB)

パラメータ カテゴリ データキャッシュ INNODB_BUFFER_POOL_SIZE 物理メモリ 50-80% 一般的に、大きいほどパフォーマンスが向上します ログ関連 Innodb_log_buffer_size 16-32M 動作条件に応じて調整 ログ関連 sync_binlog 1, 100, 0 1 最高のセキュリティ 監視と接続 max_connections ビジネスに応じて調整条件 事前設定可能値の一部を残す ファイルの読み取りおよび書き込みパフォーマンス innodb_flush_log_at_trx_commit 2 セキュリティとパフォーマンスの低下に関する考慮事項 その他 wait_timeout、interactive_timeout 28800 スケジュールされたアプリケーション接続の中断を回避する #POSTGRES

パラメータ名	パラメータ値	備考

パラメータ分類

パラメータ名 パラメータ値 備考 データキャッシュ SHARED_BUFFERS 物理メモリ 10-25% CACHE_BUFFER_SIZE 物理メモリ 50-60% wal_buffer 8-64M 大きすぎたり小さすぎたりする設定はお勧めしません 監視と接続 max_connections #ビジネス状況に応じて調整 その他 maintenance_work_mem 512M 以上 8-16M 元の構成 1M は小さすぎます Others checkpoint_segments 32 以上

			データ キャッシュ
			ログ関連
通常、推定ビジネス接続数の 120%
		その他	work_mem
			Dameng データベース

パラメータ分類パラメータ名

パラメータ値 備考 データキャッシュ MEMROY_TARGET、MEMROY_POOL 物理メモリ 90% 物理メモリ 60% データ キャッシュ データ キャッシュ MAX_BUFFER 物理メモリ 70% 最大データ キャッシュ 監視と接続 max_sessions ビジネス要件に応じて設定 通常は推定値の 120%ビジネスの接続数 概要 #ディスク アレイの交換やアップグレードなど、データベースを最適化する方法が多すぎます。ハードウェア、インデックスを追加するための SQL スクリプトの書き換え、パフォーマンスを最適化するためのデータベース パラメーターの調整、さらにはデータベース アーキテクチャの調整。この記事では、データベース自体のパラメーターを最適化します。上記の表のパラメーターを調整することで、基本的にデータベースの最高のパフォーマンスの 80% を達成できます。

		データ キャッシュ	BUFFER

この記事は、php 中国語 Web サイトの

mysql チュートリアル列からのものです。学習へようこそ!

以上がデータベースの最適化を説明する例の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。