SE の雑記

普段は SQL Server を使用しているのですが、OSS の DB についても学習をする必要が出てきました。
私の場合、SQL Server を使用する際には開発ではなく管理側となり「SQL Server の各種情報を取得し分析する」ことが多いです。

その中で「OSS DB では通常、SQL Server で確認しているこの情報はどのように取得するのか？」という調査をすることになるのですが、「取り掛かりとしてどこを見ればよいのか」の勘所がわからなくて四苦八苦しています。

そこで、基本的なメトリクスを取得するためにはどのような情報を取得すればよいのかを少し調べてみました。

さらに踏み込んで情報取得について検討を行う必要があるとは思いますが、私が情報を確認しようとした場合には、これらの情報を足掛かりすると対応ができそうな感じでした。

検証環境の構築

最初に作業をしなくてはいけないのは、実際に情報取得の検証を行うための環境の構築です。
構築した環境には、「ある程度データの入っている環境」があると、操作がしやすいかと思います。
環境をすぐに初期状態に戻せると検証もしやすいのではないでしょうか。

そういう時はサクッと Docker を使うのが楽かと思います。
MySQL / PostgreSQL のサンプルデータが含まれている Docker のイメージが公式ではありませんが、コミュニティイメージとして公開されていますので、最初はこちらを使用すると良いのではないでしょうか。

ベースとなるサンプルデータは次のものが使用されているようです。

• MySQL : Employees Sample Database
  • デフォルトポート : 3306
• PostgreSQL : Sample Databases
  • デフォルトポート : 5432

SQL Server でいう AdventureWorks / Wide World Importes ですね。

MySQL

• genschsa/mysql-employees

PostgreSQL

• aa8y/postgres-dataset

これらの Docker イメージを使用することで、最初からデータが投入されている環境を構築することができます。

OSS DB の特徴としてソースが公開されているというものがあるかと思いますが、各ソースは次の場所になる感じですかね。

• mysql/mysql-server
• postgres/postgres

 

クライアントツールの導入

検証環境の構築が終わったら次は環境に接続するためのツールの導入です。

OSS DB に接続できるツールは様々なものが公開されていますが、「各ソフトのすべての機能を活用する」という観点ですと公式のクライアントを使用するのが手っ取り早いですので、各種ツールを導入します。

MySQL

• MySQL Workbench
• MySQL Command-Line Client
  • Windows で利用する場合、MySQL を ZIP ダウンロードして bin の mysql.exe を使用すれば、サーバーはインストール不要
• MySql.Data.MySqlClient .Net Core Class Library
  • Connector/NET Connections
• LOAD DATA ステートメント
  • LOAD DATA INFILE on remote machine

PostgreSQL

• pgAdmin
• psql
  • PostgreSQL をインストールする際に、Command Line Tools のみをインストールすれば、サーバーはインストール不要
• Npgsql is the open source .NET data provider for PostgreSQL.
  • Getting Started
• COPY
  • Execute COPY command in remote database using local file

PostgreSQL については、Azure Data Studio (ADS) に PostgreSQL 拡張機能をインストール することで接続もできますので、ある程度クエリの実行に慣れてきたら ADS で操作をした方が SQL Server のエンジニアは作業がしやすいのかもしれませんね。

MySQLとPostgreSQLコマンド比較表 も操作をする際にはわかりやすいですね。

ここまでの導入が終わると、SQL Server を触っていたエンジニアであれば、各種 DB の接続をして、簡単なクエリの実行まではできるようになるかと思います。

 

公式ドキュメント

検証を行う際に構文等の確認をするのは公式ドキュメントになりますので、ドキュメントの場所も把握しておく必要があるかと思います。

MySQL

• MySQL 8.0 リファレンスマニュアル
• MySQL 5.6 リファレンスマニュアル

PostgreSQL

• PostgreSQL: Documentation
  • PostgreSQL 13.2 Documentation
• PostgreSQL日本語ドキュメント
  • PostgreSQL 12.4文書

 

SQL Server のエンジニアは、Microsoft のドキュメントになじみが深いケースも多いのではないでしょうか。
Azure でも MySQL / PostgreSQL が提供されていますので、基本的な操作方法は Azure のドキュメントから学習するというもの一つの方法かと思います。

• Azure Database for MySQL のドキュメント
• Azure Database for PostgreSQL のドキュメント

 

各種情報の取得方法

最後の大きな内容となるのが「SQL Server で取得していた各種情報を各 OSS DB でどのように取得するか」です。

SQL Server であれば動的管理ビュー (DMV) を介して情報の取得を行っていましたが、これと等価の情報をいかにして取得するのかがポイントとなってきます。

基本情報

一つの方法として考えられるのが INFOMATION_SCHEMA から情報を取得する方法です。
INFORMATION_SCHEMA は ISO 標準定義のスキーマとなりますので、各種 DB でもある程度つぶしが効くようになっています。

MySQL : INFORMATION_SCHEMA

• 第 26 章 INFORMATION_SCHEMA テーブル

PostgreSQL : INFORMATION_SCHEMA

• 第36章 情報スキーマ

他にも関数で取得するというケースもあるようですので、基本的な関数の把握も必要かもしれませんね。

MySQL : 情報関数

• 12.16 情報関数

PostgreSQL: 情報関数

• 9.25. システム情報関数

応用情報

次に考える必要があるのが「INFORMATION_SCHEMA 以外で、各 DB 固有の情報を取得するための方法」です。
INFORMATION_SCHEMA を使用することで基本的な情報が取得できるかもしれませんが、各 DB 固有の情報については取得できていない可能性があります。

MySQL でしたら、InnoDB INFORMATION_SCHEMA や MySQL パフォーマンススキーマ、PostgreSQL であれば、統計情報コレクタ / pg_stat_statements モジュール等があるようです。

以下は、pg_stat_statements を学習する際の参考。

• Aurora PostgreSQL で pg_stat_statements ビューを参照できるようにする
• PostgreSQL pg_stat_statementsで統計情報を見てみよう
• postgrsqlで実行されたSQLの実行時間や回数などを記録する
• チューニング ～ SQLチューニングの概要 ～

SQL Server だけを触っていると「どこから情報を取得すればよいのか？」のイメージがぱっと思いつかないので、情報の取得方法に打ち手は、OSS のメトリクス収集の仕組みの助けを借りてみます。

今回は次のようなプロダクトの情報を参考にさせていただきたいと思います。

• influxdata/telegraf
• mackerelio/mackerel-agent-plugins
• DataDog/integrations-core

MySQL

• telegraf
  • INFORMATION_SCHEMA
    • information_schema.processlist
    • information_schema.user_statistics
    • information_schema.tables
    • information_schema.columns
    • information_schema.schemata
  • InnoDB INFORMATIONSCHEMA
    • information_schema.INNODB_METRICS
  • Performance_Schema
    • performance_schema.table_io_waits_summary_by_table
    • performance_schema.table_io_waits_summary_by_index_usage
    • performance_schema.table_lock_waits_summary_by_table
    • performance_schema.events_statements_summary_by_digest
    • performance_schema.events_waits_summary_global_by_event_name
    • performance_schema.events_statements_summary_by_account_by_event_name
  • SHOW
    • SHOW GLOBAL STATUS
    • SHOW GLOBAL VARIABLES
    • SHOW SLAVE STATUS
    • SHOW ALL SLAVES STATUS
    • SHOW BINARY LOGS
• mackerel
  • MySQLの監視 ~ mackerel-plugin-mysqlを読み解く が参考になります。
  • SHOW
    • SHOW VARIABLES
    • show /*!50002 global */ status
    • SHOW /*!50000 ENGINE*/ INNODB STATUS
    • SHOW SLAVE STATUS
    • SHOW PROCESSLIST
• DataDog
  • INFORMATION_SCHEMA
    • information_schema.tables
    • information_schema.processlist
    • information_schema.ENGINES
    • information_schema.replica_host_status
  • Performance_Schema
    • performance_schema.events_statements_summary_by_digest
    • performance_schema.thread
  • SHOW
    • SHOW VARIABLES
    • SHOW GLOBAL VARIABLES
    • SHOW /*!50002 GLOBAL */ STATUS
    • SHOW BINARY LOGS
    • SHOW MASTER STATUS
    • SHOW /*!50000 ENGINE*/ INNODB STATUS

InnoDB の INFORMATION_SCHEMA テーブル /  MySQL パフォーマンススキーマ / SHOW 構文 (Global) を使用して各種情報を取得するのが一般的な方法となるようですね。

PostgreSQL

• telegraf
  • 統計情報コレクタ
    • pg_stat_database
    • pg_stat_bgwriter
• mackerel
  • PostgreSQLの監視 ~ mackerel-plugin-postgresを読み解く が参考になります。
  • 統計情報コレクタ
    • pg_stat_database
    • pg_stat_activity
  • システム情報関数
    • version
  • システム管理関数
    • pg_is_in_recovery
    • pg_wal_lsn_diff
    • pg_xlog_location_diff
• DataDog
  • 統計情報コレクタ
    • pg_stat_database
    • pg_stat_bgwriter
    • pg_stat_archiver
    • pg_stat_user_tables
    • pg_stat_user_indexes
    • pg_statio_user_tables
  • システム管理関数
    • pg_is_in_recovery
  • システムカタログ
    • pg_locks
    • pg_database
    • pg_class
    • pg_namespace
    • pg_roles
  • pg_stat_statements
    • pg_stat_statements

統計情報コレクタ / システム情報関数 / システム管理関数 / システムカタログ / pg_stat_statements を使用して各種情報を取得するのが一般的な方法となるようですね。