postgreSQL その19 VACUUM、ANALYZE

postgreSQL その18 データベースユーザの変更　つづきのつづき

postgreSQL その8 課題リストは2018/4/30時点でのオイラの所見

■VACUUM、ANALYZE

[P.177]

・VACUUM

PostgreSQLでは、複数のユーザからのトランザクションを同時に実行するため、MVCC（Multi-Version Concurrency Control）を実装している。

MVCCは、SELECTを発行した時点のデータが、コミット済みであることを補償するような場合でも、競合しないで同時実行する仕組み。

PostgreSQLのMVCCは追記型を採用しており不要領域の回収が必要になる。

不要領域回収のための処理がVACUUM。

VACUUMを手動実行する場合の構文は下記の通り。

[構文]

VACUUM 「FULL」「テーブル名」;

※VACUUMにテーブルを指定しなかった場合は、データベース内のすべてのテーブルに対してVACUUMが実行される。

手動実行するVACUUMには、FULLオプションを指定しない「標準VACUUM」とFULLオプションを指定した「VACUUM FULL」の2種類がある。

・通常VACUUM

テーブルに発生した不要領域を回収し、データ領域として再利用できるようにする。不要領域はテーブルだけでなくインデックスにも発生するため、同時に改修が必要となる。

VACUUMでは、指定したテーブルのインデックスも含めて不要領域を回収する。不要領域の回収は、物理的にディスクからデータを削除するわけではなく、不要領域に再利用可能であることをマークする。

VACUUMの実行中でも、SELECT、INSERT、UPDATE、DELETEなど、ほとんどのSQLを通常通り実行することができる。

・VACUUM FULL

VACUUM FULLを実行すると、テーブルの内容を新しいファイルに書き替えて、不要領域を物理的にディスクから削除する。より多くの領域を回収できるが、実行中は対象テーブルを排他ロックするため、他のクライアントからの処理はできない。VACUUM FULLは、大量のデータ削除によりディスク領域の大半が不要領域となっている場合に使用し、定期的に実行するものではない。

【検証】

postgres=> select user;
user
------
gad2
(1 row)
postgres=> vacuum tab1;
WARNING: skipping "tab1" --- only table or database owner can vacuum it
VACUUM

postgres=# select user;
user
----------
postgres
(1 行)
postgres=# vacuum tab1;
VACUUM

・ANALYZE

データベースへのSQLの実行は、テーブルに実際にどのようにデータが格納されているかという統計情報に基づいて最適化されて行われる。　統計情報が更新されず古いkママになっていると、テーブルの現在の状況を正しく把握できないために最適化されていないSQLが実行されてしまう可能性がある。　この統計情報を更新するのがANALYZEコマンド。

大量の挿入や更新などが行われたテーブルにはANALYZEを行っておく必要がある。ANALYZEを手動で実行する場合は下記の通り

[構文]

ANALYZE 「テーブル名」;

※テーブル名を省略した場合は、すべてのテーブルが対象となる。

【検証】

postgres=> select user;
user
------
gad2
(1 row)
postgres=> analyze tab1;
WARNING: skipping "tab1" --- only table or database owner can analyze it
ANALYZE

postgres=# select user;
user
----------
postgres
(1 行)
postgres=# analyze tab1;
ANALYZE

・VACUUM ANALYZE

不要領域を回収するVACUUMと、統計情報を更新するANALYZEをまとめて実行することも可能。テーブル名を省略した場合はすべてのテーブルが対象となる。

[構文]

VACUUM ANALYZE 「テーブル名」;

【検証】

postgres=# select user;
user
----------
postgres
(1 行)
postgres=# vacuum analyze;
VACUUM

■自動VACUUM

VACUUMとANALYZEは手動で実行することも可能だが、自動で実行する自動バキュームという機能がある。　自動バキュームでは、VACUUMとANALYZEを自動的に実行するが、単に定期的に実行するわけではなく、データベース内の不要領域の割合が多くなったテーブルに対し、必要に応じてVACUUMとANALYZEを適宜実行する。

自動バキュームを有効にする設定は、postgresql.confの「autovacuum = on」で行う。

せ7> grep autovacuum postgresql.conf
#autovacuum_work_mem = -1 # min 1MB, or -1 to use maintenance_work_mem
#autovacuum = on # Enable autovacuum subprocess? 'on'
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
#autovacuum_analyze_threshold = 50 # min number of row updates before
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
#autovacuum_multixact_freeze_max_age = 400000000 # maximum multixact age
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -1 means use

【課題】

PITS、VACUUM、ANALYZE、autovacuum、トランザクションに関しては、「OSS-DB silver」の教科書の内容は非常にあっさり流してる。本来これらの技術は深遠なはずなので、「OSS-DB gold」でより深く調べる必要がある。場合によってはこれらの技術だけ掘り下げて調査や検証する必要がある。

■システム情報取得関数

・version()関数

サーバで稼働しているpostgresqlのバージョンを確認する。

※psqlコマンドを実行したときに表示されるのは、クライアント(psqlコマンド)のバージョン

- ローカルクライアントから実行した場合

postgres=> select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 row)

- リモートクライアントから実行した場合

postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 10.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-18), 64-bit
(1 行)

・current_database()関数

現在接続しているデータベースを確認する。

・current_userとuser

current_userとuserで現在のユーザを確認する。

current_user()　あるいは、user() のように小括弧「()」をつけない。つけるとエラーになる。

■情報スキーマとシステムカタログ

[P.182]

データベースクラスタ全体にかかわる各種の情報は、SQLの標準規格で規定されている「情報スキーマ」と、データベース管理ソフトウェア固有の「システムカタログ」に格納されている。

■情報スキーマ

[P.182]

情報スキーマは、information_schema という名前のスキーマ。

information_schema には、データベース内のテーブルや列、ユーザなどの様々なものについての定義情報が格納されている。これらの情報を取得する場合には、移植性を考慮し、データベース管理ソフトウェアのシステムカタログではなく情報スキーマを使う方がよい。

information_schema にはPostgreSQL固有の情報は一切含まれていない。

より詳細な情報を得るにはシステムカタログを見る必要がある。

【検証】

・一般ユーザで実行

postgres=> select user;
user
------
gad2
(1 row)

postgres=> select * from information_schema.enabled_roles;
role_name
-----------
gad2
(1 row)

→一般ユーザで実行すると自分しか見えない？

→スキーマpublicでユーザが作成したテーブルが見れない

・スーパーユーザで実行

postgres=# select user;
user
----------
postgres
(1 行)
postgres=# select * from information_schema.enabled_roles;
role_name
----------------------
pg_monitor
pg_read_all_settings
pg_read_all_stats
pg_stat_scan_tables
pg_signal_backend
postgres
punisuke
gad2
gad4
(9 行)

→スーパーユーザで実行するとすべてのユーザが見える。

| | | YES | NO |
postgres | pg_catalog | pg_policy | BASE TABLE | | |
| | | YES | NO |
postgres | pg_catalog | pg_authid | BASE TABLE | | |
| | | YES | NO |

・・・中略・・・

→スキーマpublicでユーザが作成したテーブルを含めて一般ユーザのときより10個も余計にテーブルが見えた。

■システムカタログ

システムカタログは、データベースに関する内部情報を格納したテーブル。　PostgreSQLのシステムカタログは、pg_catalogという名前のスキーマで定義されており、様々な内部情報に直接アクセスできる。

PostgreSQLのシステムカタログには、通常、先頭に「pg_」が付く。

■pg_rolesビュー、pg_authidビュー

psqlコマンドの\duで表示されるようなユーザに関する情報は、pg_rolesビューを参照することで確認できる。 rolsuperが「t」のユーザはスーパーユーザ、「f」のユーザは一般ユーザを表している。

pg_rolesビューは、pg_authidカタログのビュー。pg_authidカタログは、MD5でハッシュ化されたパスワードをrolpasswordれつに含んでおり、pg_rolesビューでは見えないようになっている。

[構文]

・pg_rolesビューの出力

select * from pg_roles;

または、

select * from pg_catalog.pg_roles;

・pg_authidビューの出力

select * from pg_authid;

または、

select * from pg_catalog.pg_authid;

■その他のシステムカタログ

[P.185]

・pg_settingsビュー

SHOW文で扱うようなパラメータ設定を参照できる

postgres=# \x
Expanded display is on.
postgres=# select * from pg_settings;
-[ RECORD 1 ]---+---------------------------------------------------------------------------------------------
name | allow_system_table_mods
setting | off
unit |
category | Developer Options
short_desc | Allows modifications of the structure of system tables.
extra_desc |
context | postmaster
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f
-[ RECORD 2 ]---+---------------------------------------------------------------------------------------------
name | application_name
setting | psql.bin
unit |
category | Reporting and Logging / What to Log
short_desc | Sets the application name to be reported in statistics and logs.
extra_desc |
context | user
vartype | string
source | client
min_val |
max_val |
enumvals |
boot_val |
reset_val | psql.bin
sourcefile |
sourceline |
pending_restart | f
-[ RECORD 3 ]---+---------------------------------------------------------------------------------------------
name | archive_command
setting | cp %p /var/lib/pgarchive/%f
unit |
category | Write-Ahead Log / Archiving
short_desc | Sets the shell command that will be called to archive a WAL file.
extra_desc |
context | sighup
vartype | string
source | configuration file
min_val |
max_val |
enumvals |
boot_val |
reset_val | cp %p /var/lib/pgarchive/%f
sourcefile | /var/lib/pgdata/postgresql.conf
sourceline | 222
pending_restart | f
-[ RECORD 4 ]---+---------------------------------------------------------------------------------------------
name | archive_mode
setting | on
unit |
category | Write-Ahead Log / Archiving
short_desc | Allows archiving of WAL files using archive_command.
extra_desc |
context | postmaster
vartype | enum
source | configuration file
min_val |
max_val |
enumvals | {always,on,off}
boot_val | off
reset_val | on
sourcefile | /var/lib/pgdata/postgresql.conf
sourceline | 219
pending_restart | f
・・・中略・・・

-[ RECORD 268 ]-+---------------------------------------------------------------------------------------------
name | xmloption
setting | content
unit |
category | Client Connection Defaults / Statement Behavior
short_desc | Sets whether XML data in implicit parsing and serialization operations is to be considered as documents or content fragments.
extra_desc |
context | user
vartype | enum
source | default
min_val |
max_val |
enumvals | {content,document}
boot_val | content
reset_val | content
sourcefile |
sourceline |
pending_restart | f
-[ RECORD 269 ]-+---------------------------------------------------------------------------------------------
name | zero_damaged_pages
setting | off
unit |
category | Developer Options
short_desc | Continues processing past damaged page headers.
extra_desc | Detection of a damaged page header normally causes PostgreSQL to report an error, aborting the current transaction. Setting zero_damaged_pa
ges to true causes the system to instead report a warning, zero out the damaged page, and continue processing. This behavior will destroy data, namely all th
e rows on the damaged page.
context | superuser
vartype | bool
source | default
min_val |
max_val |
enumvals |
boot_val | off
reset_val | off
sourcefile |
sourceline |
pending_restart | f

・pg_languageカタログには、登録されている手続き言語が格納されている。

・psql -lや、psqlのサブコマンドの\lで表示されるデータベース一覧はpg_databaseカタログに格納されている。

※pg_databaseカタログはデータベースごとでなく、各データベースクラスタに1つ存在する。

・pg_tableビューは、データベース内のテーブルに関する情報を参照することができる。スキーマ名、テーブル名、テーブル所有者、インデックスの有無などが確認できる。

※shcemanameがpublicと一致する行をpg_tablesビューを使って検索すると、ユーザが作成したテーブルを調べられる。

postgres=# select * from pg_tables where schemaname = 'public';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
public | tab1 | postgres | | f | f | f | f
public | tab2 | postgres | | f | f | f | f
public | tab3 | postgres | | f | f | f | f
(3 rows)