データベースのパフォーマンスは、アプリケーションのすべての層に影響を与え、エンドユーザー・エクスペリエンス（期待値）からクラウドのコストに至るまで、あらゆるものに影響を与える可能性があります。しかし、管理者、開発者、SRE(サイト信頼性エンジニアリング) にとって、データベースで何が起こっているかを理解することは困難です。MySQL、PostgreSQL、またはAzure SQL DatabaseやAmazon Auroraなどのクラウドデータベースを使用している場合でも、データ層のパフォーマンスを理解することは、アプリケーションのパフォーマンスを向上させるための鍵となり得ます。
アプリケーションにおけるデータ層の役割を理解することは、非常に重要です。アプリケーションサーバやウェブサーバは、通常、アプリケーションの変更が展開されたときにのみ更新されるファイルです。これらのサーバはステートレスとして知られています。つまり、アプリケーションの規模に合わせてインスタンスを簡単に追加することができるのです。しかし、アプリケーションの状態データを保持するデータ層では、データベースを慎重に設計しなければ、ボトルネックを解決するための最も一般的な選択肢はサーバで利用可能なハードウェアリソースを増加させることです。ハードウェアのアップグレードは、他のすべての最適化を試みた場合に、短期的な利益を得るためのオプションとなり得ますが、通常このオプションには、物理的にも金銭的にも限界があります。
アプリケーションは、画面のペイント、銀行残高の表示、利用可能な商品の表示、ファンタジなスポーツのスコア表示など、データ層に依存した処理を行います。データ層がうまく機能していないと、エンドユーザー・エクスペリエンスに劇的な影響を与える可能性があります。データ層を拡張するのは難しいため、データベースの性能はアプリケーションスタックの中で最も重要な部分です。データベースエンジンは、エンジンと個々のクエリの両方のパフォーマンスに関するメタデータを収集するために、厳重に実装されています。これにより、訓練を受けたデータベース管理者、システム管理者、または開発者は、頻繁に実行されるクエリのパフォーマンスを劇的に向上させることができる情報を取得することができます。ヒントを追加してクエリの実行計画を変更したり、頻繁にクエリを実行するテーブルによりよいインデックスを追加したりすると、データベースサーバのCPU使用率を90%台後半から10%以下にまで低下させることができます。
このような性能向上は、実現可能なものですが、それを見極めるのは困難です。このことをよりよく理解するために、テーブルと基本的なクエリを検証し、データベースエンジンがこのデータをどのように処理するかを説明することが役に立ちます。
以下の2つのテーブルがあり、それぞれEmployeeとSalaryと呼ばれる場合:
| EMPLOYEE ID | BIRTHDATE | FIRST NAME | LAST NAME | HIRE DATE |
|
1 |
02/01/1972 |
Ted |
Codd |
03/03/2018 |
|
2 |
04/23/1993 |
Jane |
Smith |
04/01/2019 |
| EMPLOYEEID | SALARY | FROM DATE | TO DATE |
|
1 |
124000 |
03/03/2018 |
03/03/2021 |
|
1 |
150000 |
03/03/2021 |
NULL |
|
2 |
225000 |
04/01/2019 |
NULL |
そして、次のようなクエリを書きます。
SELECT e.FirstName, e.LastName, s.Salary
FROM Employees E
INNER JOIN Salary S on s.EmployeeID=e.EmployeeID W
HERE ToDate IS NULL
まず、データベースエンジンはクエリを解析します。つまり、SQL構文が有効であること、EmployeesテーブルとSalaryテーブルが存在すること、そして最後にクエリで参照されているすべてのカラムが存在することを評価します。この処理は非常に高速で、一般にパフォーマンスへの影響はありません。この処理の次の部分が、クエリの最適化という興味深いものになります。エンジンはテーブル、インデックス、そしてテーブルのデータについてエンジンが保存している統計的メタデータを調べ、クエリの結果を返すための実行（または説明）計画を生成します。この計画を生成するプロセスはCPUに負荷がかかるため、データベースエンジンによっては、このプロセスのために特別に再利用されるキャッシュを備えているものもあります。
上記のクエリをさらに見て、インデックスが全くないと最初に仮定した場合、エンジンが実行する必要がある論理的なステップを考えてみてください。最初のステップは、Salary テーブルの ToDate が NULL 値である値を識別することです。インデックスがないため、データベースエンジンは、テーブル全体をスキャンする必要があります。さらに、Employees テーブルをスキャンして、Salary テーブルに一致する EmployeeID、および姓と名を取得する必要があります。
この例では数行しか紹介していませんが、テーブルの行数が数千から数百万になると、スキャンにかかるコストは、ストレージ使用率（エンジンはディスクからすべてのデータを読み取る必要がある）とCPU使用率（一致する行を見つけるためにエンジンはテーブル内のすべての行を処理しなければならない）を劇的に増加させる可能性があります。しかし、いくつかの簡単な追加を行うことで、実行を劇的に変化させることができます。
以下のインデックスを作成すると
CREATE INDEX IX_Employee_EmpID on Employees (EmployeeID) INCLUDE
(FirstName, LastName)
CREATE INDEX IX_Salary_EmpID on Salary (EmployeeID)
CREATE INDEX IX_Salary_EndDate on Salary (EndDate)
新しいクエリプランは、突然、より効率的になります。EndDate列にインデックスがあるため、エンジンはその列を読み取り、NULLレコードのみを取得することができます（場合によっては、1つのデータページを読み取るだけで済むかもしれません）。また、EmployeeID 列の両方にインデックスがあるため、エンジンが結合操作を処理する際に非常に効率的です。これはインデックスがクエリ実行にどのように役立つかを示す拡張例でしたが、多忙で複雑なアプリケーションでは、この種の最適化の可能性が見え隠れしているものです。
コードレビューでこのような問題に対処できればよいのですが、データベース管理者やシステム管理者はこのプロセスから取り残され、実運用で性能問題が発生し始めたときに初めて呼ばれることがあります。つまり、DBAは自分自身で問題のあるクエリやリソースの制約を監視し、特定しなければならないのです。もう一つの性能問題は、データがシフトし、実行計画が突然最適でなくなったときに発生します。DBAはこれを「なぜか動作が遅くなる」問題と呼んでいます。個々のクエリの性能を調べるスクリプトを書いたり、サーバ上で実行されているある時点のスナップショットを取得することは簡単ですが、データベースシステムの性能監視の鍵は、すべてのメタデータを永続化し、ベースライン化することです。このデータを永続化するためには、パフォーマンス・データ・ウェアハウスを構築する必要がありますが、これは複雑なプロジェクトで、多くの独自コードを保守する必要があります。コードを展開するだけでなく、監視システムの可用性と安定性を確保する必要があるため、本番システムのサポートに時間を取られます。
データベースエンジンの内部動作を深く理解できるツールがあれば、ボトルネックを迅速に特定し、データベースパフォーマンスの改善に関する推奨事項を提供することができます。また、時間の経過とともに発生する可能性のあるパフォーマンスの異常を迅速に特定するための基準値を提供することができます。
関連したトピックス
- なぜデータベースにインデックスが必要なのか？ [Database Performance Analyzer :DPA]
- Database Performance Analyzer (旧Ignite）の情報からSQLチューニング実践：索引編
- PostgreSQLとMySQLのデータベースとしての機能の違い
- Oracle View(ビュー）のパフォーマンスを高速化する – Database Performance Analyzer (DPA)によるチューニング
- DBMotoレプリケーションに必要なMySQLユーザ権限
- DBMotoレプリケーションに必要なOracleユーザ権限
- GlueSyncでNoSQL活用を加速：データモデリング編
- レプリケーション検証機能(Validate)によるトランザクション情報取得チェック
- ジュリアンデートのレプリケーション【リアルタイムレプリケーションツールDBMoto】
- Syniti(旧DBMoto)レプリケーションに必要なOracleユーザ権限 Trigger使用時
RSSフィードを取得する
