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

データベースの最適化についてインターネットで検索すると、基本的に 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_size16-32M動作条件に応じて調整ログ関連sync_binlog1, 100, 01 最高のセキュリティ監視と接続max_connectionsビジネスに応じて調整条件事前設定可能値の一部を残すファイルの読み取りおよび書き込みパフォーマンスinnodb_flush_log_at_trx_commit2セキュリティとパフォーマンスの低下に関する考慮事項その他wait_timeout、interactive_timeout28800スケジュールされたアプリケーション接続の中断を回避する#POSTGRES

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

パラメータ分類

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

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

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

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

		データ キャッシュ	BUFFER

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

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

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