RDBMS Plugins

pg_stat_statements

MySQLもPostgreSQLもスタンダードな状態で提供されているデータベース・ソフトウエアに対して、プラグインを利用して色々な拡張機能を追加する事が可能です。また、必要なモジュールを自分で開発してプラグインとしてデータベースに追加する事で、運用を楽にする事も出来るので活用してみるのも良いかと思います。

In Case of MySQL

MySQL8.0.27に、DefaultでインストールされているPluginは以下の通りでした。

mysql> show plugins;
+---------------------------------+----------+--------------------+---------+---------+
| Name                            | Status   | Type               | Library | License |
+---------------------------------+----------+--------------------+---------+---------+
| binlog                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| mysql_native_password           | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha256_password                 | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| caching_sha2_password           | ACTIVE   | AUTHENTICATION     | NULL    | GPL     |
| sha2_cache_cleaner              | ACTIVE   | AUDIT              | NULL    | GPL     |
| daemon_keyring_proxy_plugin     | ACTIVE   | DAEMON             | NULL    | GPL     |
| CSV                             | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MEMORY                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| InnoDB                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| INNODB_TRX                      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP                      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_RESET                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMPMEM_RESET             | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX            | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CMP_PER_INDEX_RESET      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_PAGE_LRU          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_BUFFER_POOL_STATS        | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TEMP_TABLE_INFO          | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_METRICS                  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DEFAULT_STOPWORD      | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_DELETED               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_BEING_DELETED         | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_CONFIG                | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_CACHE           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_FT_INDEX_TABLE           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLES                   | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESTATS               | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_INDEXES                  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_TABLESPACES              | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_COLUMNS                  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_VIRTUAL                  | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_CACHED_INDEXES           | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| INNODB_SESSION_TEMP_TABLESPACES | ACTIVE   | INFORMATION SCHEMA | NULL    | GPL     |
| MyISAM                          | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| MRG_MYISAM                      | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| PERFORMANCE_SCHEMA              | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| TempTable                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| ARCHIVE                         | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| BLACKHOLE                       | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |
| FEDERATED                       | DISABLED | STORAGE ENGINE     | NULL    | GPL     |
| ngram                           | ACTIVE   | FTPARSER           | NULL    | GPL     |
| mysqlx_cache_cleaner            | ACTIVE   | AUDIT              | NULL    | GPL     |
| mysqlx                          | ACTIVE   | DAEMON             | NULL    | GPL     |
+---------------------------------+----------+--------------------+---------+---------+
45 rows in set (0.01 sec)

参照:5.6 MySQL Server プラグイン

あまり認知されていませんが、MySQLには以下のようなプラグインも用意されています。

5.6.4 リライタクエリーリライトプラグインMySQL は、サーバーが SQL ステートメントを実行する前に、サーバーが受け取った SQL ステートメントを調査して変更できるクエリーリライトプログインをサポートしています。
5.6.7 クローンプラグインクローンプラグインを使用すると、ローカルまたはリモートの MySQL サーバーインスタンスからデータをクローニングできます。 クローンデータは、スキーマ、テーブル、テーブルスペースおよびデータディクショナリメタデータを含む、InnoDB に格納されているデータの物理スナップショットです。
12.10.9 MeCab フルテキストパーサープラグインMySQL には、日本語をサポートする ngram 全文パーサープラグインも用意されています。
15.20 InnoDB memcached プラグイン「InnoDB memcached プラグインは、MySQL 8.0.22 の時点では非推奨です。将来のバージョンの MySQL ではサポートされなくなる予定です。」との事。
mysql plugins

自作したPluginをインストールする場合は、以下のフォルダーにモジュールを作成してからInstall
plugin_dir

参照:13.7.4 コンポーネント、プラグインおよびユーザー定義関数のステートメント

例) ここでは、mecabプラグインをインストールしてみます

設定ファイルを変更して、libpluginmecab.soが読み込まれる様に設定する。

#:~/git/rdbms-docker/mysql$ cat docker-compose-with-volume-web.yml | grep mysql
        image: mysql:8.0
        container_name: mysql-container
          - ./docker/mysql/data:/var/lib/mysql
          - ./docker/mysql/logs:/var/log/mysql
          - ./docker/mysql/my.cnf:/etc/mysql/conf.d/my.cnf
#:~/git/rdbms-docker/mysql$ cat ./docker/mysql/data/mecabrc | grep dicdir
; dicdir =  /path/to/mysql/lib/mecab/lib/mecab/dic/ipadic_euc-jp
dicdir = /var/lib/mecab/dic/ipadic-utf8
#:~/git/rdbms-docker/mysql$ cat ./docker/mysql/my.cnf | grep mecab
loose-mecab-rc-file=/var/lib/mysql/mecabrc

上記の例では、mecabrcをデータディレクトリーに作成してコンテナ内のmecabフォルダーを指定しています。

#:~/git/rdbms-docker/mysql/docker/mysql$ docker exec -it mysql-container /bin/bash
root@e6adf5779967:/# ls -l /var/lib/mysql/mecabrc
-rwxrwxrwx 1 mysql mysql 1425 Apr 10 01:44 /var/lib/mysql/mecabrc
root@e6adf5779967:/# exit

コンテナを再起動して設定をロードします。

#:~/git/rdbms-docker/mysql$ docker-compose restart
Restarting mysql-container ... done
#:~/git/rdbms-docker/mysql$

プラグイン(libpluginmecab.so)をインストールして利用可能になっている事を確認。

mysql> INSTALL PLUGIN mecab SONAME 'libpluginmecab.so';
Query OK, 0 rows affected (0.02 sec)
mysql> select * from mysql.plugin;
+-------+-------------------+
| name  | dl                |
+-------+-------------------+
| mecab | libpluginmecab.so |
+-------+-------------------+
1 row in set (0.01 sec)
mysql> show plugins;
+---------------------------------+----------+--------------------+-------------------+---------+
| Name                            | Status   | Type               | Library           | License |
+---------------------------------+----------+--------------------+-------------------+---------+
| binlog                          | ACTIVE   | STORAGE ENGINE     | NULL              | GPL     |
| mysql_native_password           | ACTIVE   | AUTHENTICATION     | NULL              | GPL     |
<SNIP>
| mysqlx_cache_cleaner            | ACTIVE   | AUDIT              | NULL              | GPL     |
| mysqlx                          | ACTIVE   | DAEMON             | NULL              | GPL     |
| mecab                           | ACTIVE   | FTPARSER           | libpluginmecab.so | GPL     |
+---------------------------------+----------+--------------------+-------------------+---------+
46 rows in set (0.01 sec)

In Case of PostgreSQL

インストール済みの拡張 (Extenstion)の確認

POC=# \dx
                                List of installed extensions
  Name   | Version |   Schema   |                        Description
---------+---------+------------+------------------------------------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis | 3.1.4   | public     | PostGIS geometry and geography spatial types and functions
(2 rows)
POC=# select oid,extname,extowner,extnamespace,extrelocatable,extversion,extconfig,left(extcondition::text,30) from pg_extension;
  oid  | extname | extowner | extnamespace | extrelocatable | extversion | extconfig |       left
-------+---------+----------+--------------+----------------+------------+-----------+------------------
 13431 | plpgsql |       10 |           11 | f              | 1.0        |           |
 16396 | postgis |       10 |         2200 | f              | 3.1.4      | {16703}   | {"WHERE NOT (   +
       |         |          |              |                |            |           | srid BETWEEN 200
(2 rows)
利用可能な拡張は pg_available_extensionsで確認する事が出来ます。
SELECT * FROM pg_available_extensions;
pg_available_extensions

例) Extentionをインストールしてみる (pg_stat_statements)

POC=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

postgresql.confを編集してコンテナを再起動

#~/git/rdbms-docker/postgresql/docker/postgresql/data$ sudo cp -p postgresql.conf postgresql.conf.20220409
[sudo] password for shinya:
#~/git/rdbms-docker/postgresql/docker/postgresql/data$ sudo vim postgresql.conf
#~/git/rdbms-docker/postgresql/docker/postgresql/data$ diff postgresql.conf postgresql.conf.20220409
726,730d725
< shared_preload_libraries = 'pg_stat_statements'
<
< pg_stat_statements.max = 10000
< pg_stat_statements.track = all
<
#~/git/rdbms-docker/postgresql/docker/postgresql/data$
#~/git/rdbms-docker/postgresql$ sudo service docker stop
 * Stopping Docker: docker                         [ OK ]
#~/git/rdbms-docker/postgresql$ sudo service docker start
 * Starting Docker: docker                         [ OK ]
#~/git/rdbms-docker/postgresql$ psql -h 127.0.0.1 -U postgres -d POC
Password for user postgres:
psql (13.6 (Ubuntu 13.6-1.pgdg20.04+1+b1), server 13.4 (Debian 13.4-4.pgdg110+1))
Type "help" for help.
POC=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+-------------------------------------------------------------
 pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL stat..
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgis            | 3.1.4   | public     | PostGIS geometry and geography spatial types and functions
(3 rows)

もしくは、postgresql.confがcontainer内にある場合は, ALTER SYSTEM SETで有効にする事も可能です。

以下の、ALTER処理によりpostgresql.auto.confが更新されます。(postgresql.confの変更は不要)

POC=# ALTER SYSTEM SET shared_preload_libraries = 'pg_stat_statements';
ALTER SYSTEM

Containerを再起動すると、Libraryがロードされpg_stat_statementsが利用できる様になります。

#~/git/rdbms-docker/postgresql$ docker-compose restart
Restarting postgresql_postgres_1 ... done
#~/git/rdbms-docker/postgresql$ psql -h 127.0.0.1 -U postgres -d POC
Password for user postgres:
psql (13.6 (Ubuntu 13.6-1.pgdg20.04+1+b1), server 13.4 (Debian 13.4-4.pgdg110+1))
Type "help" for help.
POC=# select p12_003,p12_004,ST_X(geom) AS "lat",ST_Y(geom) AS "lng" FROM "p12a-14_13" limit 10;
 p12_003 | p12_004 |        lat         |        lng
---------+---------+--------------------+--------------------
 13      | 13361   | 139.38170530629864 |  34.73785569987695
 13      | 13421   | 142.18552523057625 | 27.101061872628367
 13      | 13382   |  139.5944977029823 | 33.898130001041196
 13      | 13101   | 139.75022040138393 |  35.69244572970171
 13      | 13113   | 139.69942368845832 |  35.67597948569273
 13      | 13106   | 139.79674819901808 |  35.71466140280205
 13      | 13101   | 139.75362044896752 |  35.68485456167983
 13      | 13113   | 139.70696320112447 |   35.6677931758621
 13      | 13102   |  139.7651040986888 |  35.67122046070042
 13      | 13102   |  139.7706329993915 |  35.66545800146108
(10 rows)
POC=# \x
Expanded display is on.
POC=# select * from pg_stat_statements;
-[ RECORD 1 ]-------+----------------------------------------------------------------------------------
userid              | 10
dbid                | 16384
queryid             | 2944445560533253846
query               | select p12_003,p12_004,ST_X(geom) AS "lat",ST_Y(geom) AS "lng" FROM "p12a-14_13" limit $1
plans               | 0
total_plan_time     | 0
min_plan_time       | 0
max_plan_time       | 0
mean_plan_time      | 0
stddev_plan_time    | 0
calls               | 1
total_exec_time     | 0.07669999999999999
min_exec_time       | 0.07669999999999999
max_exec_time       | 0.07669999999999999
mean_exec_time      | 0.07669999999999999
stddev_exec_time    | 0
rows                | 10
shared_blks_hit     | 0
shared_blks_read    | 1
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
wal_records         | 0
wal_fpi             | 0
wal_bytes           | 0

pg_stat_statements_resetでリセットし、以下のSQLでHit Ratioを確認する事が出来ます。

select query, calls, total_exec_time, rows,
100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
from pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;

例)同じ処理を3回実行しHIT率が100%である事が確認出来ました。

POC=# SELECT pg_stat_statements_reset();
-[ RECORD 1 ]------------+-
pg_stat_statements_reset |
POC=# select * from pg_stat_statements;
-[ RECORD 1 ]-------+----------------------------------
userid              | 10
dbid                | 16384
queryid             | -7139447824172960405
query               | SELECT pg_stat_statements_reset()
plans               | 0
total_plan_time     | 0
min_plan_time       | 0
max_plan_time       | 0
mean_plan_time      | 0
stddev_plan_time    | 0
calls               | 1
total_exec_time     | 0.1827
min_exec_time       | 0.1827
max_exec_time       | 0.1827
mean_exec_time      | 0.1827
stddev_exec_time    | 0
rows                | 1
shared_blks_hit     | 0
shared_blks_read    | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0
temp_blks_read      | 0
temp_blks_written   | 0
blk_read_time       | 0
blk_write_time      | 0
wal_records         | 0
wal_fpi             | 0
wal_bytes           | 0
POC=# \x
Expanded display is off.
POC=# select p12_003,p12_004,ST_X(geom) AS "lat",ST_Y(geom) AS "lng" FROM "p12a-14_13" limit 10;
 p12_003 | p12_004 |        lat         |        lng
---------+---------+--------------------+--------------------
 13      | 13361   | 139.38170530629864 |  34.73785569987695
 13      | 13421   | 142.18552523057625 | 27.101061872628367
 13      | 13382   |  139.5944977029823 | 33.898130001041196
 13      | 13101   | 139.75022040138393 |  35.69244572970171
 13      | 13113   | 139.69942368845832 |  35.67597948569273
 13      | 13106   | 139.79674819901808 |  35.71466140280205
 13      | 13101   | 139.75362044896752 |  35.68485456167983
 13      | 13113   | 139.70696320112447 |   35.6677931758621
 13      | 13102   |  139.7651040986888 |  35.67122046070042
 13      | 13102   |  139.7706329993915 |  35.66545800146108
(10 rows)
POC=# select p12_003,p12_004,ST_X(geom) AS "lat",ST_Y(geom) AS "lng" FROM "p12a-14_13" limit 10;
 p12_003 | p12_004 |        lat         |        lng
---------+---------+--------------------+--------------------
 13      | 13361   | 139.38170530629864 |  34.73785569987695
 13      | 13421   | 142.18552523057625 | 27.101061872628367
 13      | 13382   |  139.5944977029823 | 33.898130001041196
 13      | 13101   | 139.75022040138393 |  35.69244572970171
 13      | 13113   | 139.69942368845832 |  35.67597948569273
 13      | 13106   | 139.79674819901808 |  35.71466140280205
 13      | 13101   | 139.75362044896752 |  35.68485456167983
 13      | 13113   | 139.70696320112447 |   35.6677931758621
 13      | 13102   |  139.7651040986888 |  35.67122046070042
 13      | 13102   |  139.7706329993915 |  35.66545800146108
(10 rows)
POC=# select p12_003,p12_004,ST_X(geom) AS "lat",ST_Y(geom) AS "lng" FROM "p12a-14_13" limit 10;
 p12_003 | p12_004 |        lat         |        lng
---------+---------+--------------------+--------------------
 13      | 13361   | 139.38170530629864 |  34.73785569987695
 13      | 13421   | 142.18552523057625 | 27.101061872628367
 13      | 13382   |  139.5944977029823 | 33.898130001041196
 13      | 13101   | 139.75022040138393 |  35.69244572970171
 13      | 13113   | 139.69942368845832 |  35.67597948569273
 13      | 13106   | 139.79674819901808 |  35.71466140280205
 13      | 13101   | 139.75362044896752 |  35.68485456167983
 13      | 13113   | 139.70696320112447 |   35.6677931758621
 13      | 13102   |  139.7651040986888 |  35.67122046070042
 13      | 13102   |  139.7706329993915 |  35.66545800146108
(10 rows)
POC=# \x
Expanded display is on.
POC=# select query, calls, total_exec_time, rows,
100.0 * shared_blks_hit /nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
from pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+------------------------------------------------------------------------------------------
query           | SELECT pg_stat_statements_reset()
calls           | 1
total_exec_time | 0.1827
rows            | 1
hit_percent     |
-[ RECORD 2 ]---+------------------------------------------------------------------------------------------
query           | select * from pg_stat_statements
calls           | 1
total_exec_time | 0.13779999999999998
rows            | 1
hit_percent     |
-[ RECORD 3 ]---+------------------------------------------------------------------------------------------
query           | select p12_003,p12_004,ST_X(geom) AS "lat",ST_Y(geom) AS "lng" FROM "p12a-14_13" limit $1
calls           | 3
total_exec_time | 0.1144
rows            | 30
hit_percent     | 100.0000000000000000
POC=#

参照:F.30. pg_stat_statements

カテゴリー:

最近のコメント

表示できるコメントはありません。