PostgreSQL 纏め
仕事でPostgreSQL 9.6 を使っており、インフラを担当している。
PostgreSQLについて、ポイントを纏めてみた。
■fillfactor（空き領域）
空き領域のデフォルト値は、以下の通り。
ORACLE PostgreSQL
テーブル 10 0
INDEX 10 10
※PostgreSQLのテーブルが0であることに注意！
設定値は 以下で確認できる。
pg_class.reloptions（デフォルト値の場合はNULL）
PostgreSQLは追記型アーキテクチャーのため、これを確認するのが重要！
■トランザクション：デフォルトがCOMMIT
ORACLEはROLLBACK。
COMMITだと、以下の場合に注意が必要。
・異常が発生し、再処理が走った場合
・クラスター環境で、切り替わった場合
■スキーマ：ユーザ作成時、作成されない
ORACLEでは作成されるが、PostgreSQLでは作成されず、以下の場合に注意。
・インスタンス管理者でオブジェクトを作成する場合
特に関数を実行して、裏でオブジェクトを作成している場合。
■システムカタログ：確認すべきポイント
・pg_class.reloptions
fillfactorが正しく設定されているか
・pg_stat_user_tablesのn_tup_updとn_tup_hot_upd、
更に、autovacuum_count（HOTが効いているか）
・pg_stat_user_tablesのseq_scan、idx_scan
・pg_stat_user_indexes.idx_scan
Primary Key以外で0のINDEXは、未使用のため 削除を検討
※INDEXが存在することにより、処理が遅くなっている
PostgreSQLの場合、pg_stat_database.stats_reset以降の累計値が格納されている
・pg_stat_activity.state
activeとidleの数
■オブジェクトのID
ORACLEはオブジェクトのIDを保持していないが、
PostgreSQLはオブジェクト毎にIDを保持している。
そのため、以下の双方実施は不可能になる。
・統計情報の固定化
・INDEX再構築を「create index concurrently」で実施
■postgresql.confのポイント
・shared_buffers
以下で決める。
・キャッシュ：オブジェクトをどれだけメモリに載せたいか
・work_mem
アプリチームに聞くと いつも大きめに言ってくるが、
それ程大きくなくても、大問題には ならない。
キャッシュヒット率で確認できる。
postgresql.confの内容は、pg_settingsで設定内容を確認できるが、
shared_buffersは 8KB単位になっている。（pg_settings.unitが8KB）
テーブルやINDEXは、8KB単位でキャッシュに載るため、以下の場合に注意。
・fillfactorで空きを取り過ぎているテーブル
・断片化が進んでいるINDEX
PostgreSQL専用のサーバで、全インスタンスのshared_buffersの合計が、
実メモリの40%までになるようにしている。
・work_mem
connection毎に必要なため、max_connectionsを大きくすると注意が必要だが、
activeなconnectionで考慮すべき。
pg_stat_database.temp_filesとtemp_bytesを確認すれば、
work_memが足りているか 確認できる。
今 担当しているシステムでは、アプリがidleセッションで繋ぎっぱなし
にしているため注意。
※繋ぎっぱなしにする必要はない。
アプリTは性能のために行っているようだが、他に考慮すべき点が山程あり、
そちらを対処すれば、繋ぎっぱなしにする必要はない。
・checkpoint_timeout
デフォルトは5分で、サイトでよく見かけるが、
デフォルト値のままだと負荷が高くなるため、30分に変更している。
・checkpoint_completion_target
デフォルトは0.5だが、負荷が高くなるため、0.9に変更している。
0.5だと、急いでディスクに書き込もうとする。
・wal_log_hints
クラスタ環境で、pg_rewind（差分コピー）を使用するためには、
このパラメータをONに設定する必要がある。
が、これが曲者で、マニュアルに以下と書かれている。
-----
チェックポイント後にはじめてページを変更する際に、
ディスクページの全内容をWALに書き出します。
-----
クラスタ構成の場合、切り替わった直後が問題となる。
旧Standbyの時、アクセスしていなければ、初回のSELECTは
「チェックポイント後にはじめてページを変更する」に該当し、
アクセスした分、ログが出力され、ログ領域がパンクし易い！
■INDEXの状態
INDEXは 本の索引のようなもの。
検索時を考慮し、INDEXのキー項目順で並べている。
そのため、INSERTしているだけでも断片化していき、（虫食い状態になる）
定期的にINDEXを再構築しないと、検索が遅くなる。
ORACLEは、以下の場合に再構築した方が良い、という指針を出している。
・HEIGHTが4以上で、DEL_LF_ROW/LF_ROWSが0.2を超える場合
が、PostgreSQLには 指針が出ていない。
pgstattupleを使用すると、INDEXの状態を把握できるが、
そこに「leaf_fragmentation（リーフページの断片化）」という項目がある。
が、この値は 間違えている。
断片化していないのに 断片化していると出たり、
断片化しているのに 断片化していないと出る。
※断片化は サイズで判断できる
今のシステムで、「avg_leaf_density（リーフページの平均密度）」
を 断片化の判断で利用している。この値は 正しい。
以下の条件で 再構築すると良い。
・サイズ ：50～60MB超
・平均密度：60～70未満
※平均密度=50 というのは、半分が虫食い状態で、
サイズが2倍に膨れ上がっていることを指す。
断片化が進むと、範囲検索への影響が大きく出る。
サイズを指定しているのは、PostgreSQLは オブジェクト毎にOS上の
ファイルが生成され、8KB単位だから。
データを1件登録すると、OS上のファイルが8KBになり、
サイズが小さいほど、平均密度が小さくなりやすい。
PISOについての纏め
今 担当しているシステムで、PISOを導入しています。
PISOは、データベースを監査するソフト。
私はデータベースを担当しており、PISOは他社が担当しているため
詳しくありませんが、ポイントを纏めてみました。
現在 担当しているシステムでは、以下のように使用しています。
・SQLとセッションの監視ルールを設定し、ルール違反があればシスログへ出力
・監視ソフトがシスログを監視し、キーワードに引っ掛かるとメールを送付
■------------------
メモリ
■------------------
マニュアルには、以下としか書かれていない。
・メモリ ：8GB以上
・SWAP領域：RAMのサイズの0.75倍
■------------------
1台ごとの稼働上限値
■------------------
マニュアルに以下と書かれているが、何らかで計測しないと難しい。
※以下を超える場合、2台以上必要
・1時間当たりの件数：最大40万件／１インスタンス
・1日の合計件数 ： 900万件／全インスタンス
1インスタンス追加する毎に、プロセスが2つずつ増えるため、
インスタンスを追加していくと、使用メモリ量が少しずつ増えていく。
※ジョブでサービスを再起動しているため、このプロセスのメモリは 大きくならない。
上記以外にもプロセスが40個以上あり、ps参照時のRSSは
サイズは殆どが変わらないが、2つのみ、徐々に増えていくプロセスがある。
※こちらのプロセスはPIDが変わらず、起動しっぱなし。
PISOは、用途が2つあるため、サーバが2つある。
・Ａ用：20超インスタンス
・Ｂ用： 数インスタンス
PISOは、ORACLEのSGA_TARGETを6GBで設定しており、
多くのプロセスのVSZが6GBで、RSSが増減する。
2つのプロセスのみ RSS値が徐々に増えていき、各3.2GBと3.4GBまで増えた。
本番はインスタンス（プロセス）が多く、恐らくメモリが足りないため、
3.2GBと3.4GBまで増えていたが、災対を確認したら、
1つのプロセスのRSS値が4GBを超えていた。
現在 担当しているシステムでは、12インスタンスまで問題なかったが、以降、
SWAPの空き容量が少しずつ減っていき、Freezeした。（接続できなくなった）
※PISOのみ使用している専用サーバで、Frreze後に初めて気付いた。
メモリ／SWAPを、以下の 上から下へ 変更した。
・メモリ ：10GB
・SWAP領域： 5GB（導入時マニュアルを読んでおらず、メモリの半分に設定）
↓
・メモリ ：16GB
・SWAP領域：12GB
■------------------
SQL監視
■------------------
監視したいテーブルを設定すると、以下に対するSQLが記録される。
1.設定したテーブル
2.上記1.の後で追加したテーブル
2.があり、1.に戻したい場合、ターゲットリロードを実施する。
※2.が蓄積されていることに気付かない場合があるので注意
SQLは、更新やWHERE句に指定した値は記録されず、「$1」等と記録される。
取得元が不明だが、「Elapsed Time」（処理時間）も記録される。
■------------------
除外設定
■------------------
クラスタ環境の場合、ハートビートが頻繁に行われるため、
ハートビートの記録を除外する事が可能。
OSUSER、MACHINE、PROGRAM等を設定可能で、合致すれば記録されない。
設定すると、以下にファイルが作成される。
$IST_HOME/ホスト名/インスタンス名/etc/exclusion_session.lst（セッション除外の場合）
※OSUSERについて
Linuxの場合、/proc/net/tcpのuidカラムから取得しており、
接続が短いと取得できない、とサポートから回答されたため、注意。
現在 担当しているシステムの場合、ハートビートの接続が
1インスタンスにつき、10件／日ほどOSUSERがNULLになっており、
除外設定に合致せず、アラートが挙がっていた。
Linuxの場合、ローカル接続の場合は取得できるが、
他端末からの接続の場合、取得できない。
■------------------
ジョブ
■------------------
スケジューリング機能を標準搭載し、以下にcrontabの形式で書かれる。
$IST_HOME/etc/jobtbl
バックアップ系は、以下が設定されている。
17 * * * * Backup_Controlfile.sub
5 1 * * * usrlog_backup.sub
0 1 * * 0 ConsoleBackup.sub
30 1 * * * LogDataBackup.sub
0 3 * * 1 OracleBackup.sub
追加設定することが可能。
上記で実行されるモジュールは、以下のディレクトリに格納されており、
ソースに 最大実行時間が書かれている。
$IST_HOME/stage/文字コード/admin
LogDataBackup.subで サービスを再起動しており、
OracleBackup.sub内でも、LogDataBackup.subを起動している。
以下の仕組みがあるため、サービスが停止しても 問題ない。
Targetで取得した監査ログデータをネットワーク障害などの理由でISMへ
送信できなかった場合、そのデータを一時的にPISO用SWAP領域に格納する。
格納されたデータは、通信が正常に確立された時点で自動的に送信する。
チェック系では、以下が設定されている。
*/5 * * * * ISMService_Alive.sub
0 6,18 * * * Disk_Free.sub
*/10 * * * * Memory_Swap_Free.sub
7 * * * * CheckDatafile.sub
Memory_Swap_Freeは、SWAPの空き容量を10分毎にチェックしており、
以下の閾値を下回ったら、SWAPの空き容量含め、シスログに出力される。
・Windows
・警告レベル１： 50MB
・警告レベル２：100MB
・警告レベル３：200MB
・UNIX
・警告レベル１：512MB
・警告レベル２： 1GB
・警告レベル３：1.5GB
■------------------
最大実行時間
■------------------
ジョブ毎に最大実行時間が設定されており、超過したら 異常終了する。
以下に、ジョブの実行／終了／エラー等が出力されるため、
監視すれば、ジョブの異常を即時に発見できる。
$IST_HOME/tmp/console.log
バックアップ系の最大実行時間は、以下に設定されている。（単位：分）
・Backup_Controlfile：MAXWTM=10
・usrlog_backup ：MAXWTM=10
・ConsoleBackup ：MAXWTM=6
・LogDataBackup ：MAXWTM=180
・OracleBackup ：MAXWTM=180
月曜日 3:00起動の「OracleBackup」は30分以上かかることがあり、
毎時 17分起動の「Backup_Controlfile」と重なると、
「Backup_Controlfile」の処理時間が長くなり、最大実行時間を越え易い。
■------------------
ログ
■------------------
・ジョブのログは以下
$IST_HOME/tmp/console.log
$IST_HOME/log/console.bkup（logディレクトリ配下だが、こちらの方が古い）
■------------------
メッセージ
■------------------
マニュアルに以下と記載されている。
-----
当製品はUTF-8 の入出力をサポートしておりません。
なお、英語版でインストールした場合には、
画面表示の文字コードとしてUTF-8 を採用しています
-----
メッセージは、メッセージファイルを持っており、
メッセージファイルから出力していると思われる。
メッセージファイルは、以下の通り 文字コード毎にファイルを持っている。
・@@@@_C.msg
・@@@@_EUC_JP.msg
・@@@@_SJIS.msg
UTF-8でメッセージを参照すると文字化けする場合があるが、
メッセージファイルで 内容を確認できる。
※ツールを使用してメッセージ監視している場合に便利
■------------------
マイニングサーチ
■------------------
マイニングサーチという機能があり、ログを条件指定して検索でき、
スケジューリングしてファイルへ出力することが可能。
設定すると、以下に設定ファイルが作成される。
$IST_HOME/admin/<設定名称>.msr
CSVファイルへ出力しており、ヘッダー行を出力しているが、
明細行の有／無を判定せず、必ずヘッダー行を出力している。
ログは以下に出力され、出力件数が書かれるため、
ログを見れば 件数を把握できる。
$IST_HOME/log/MS_<設定名称>_YYYYMMDD_HHMI24SS_CSV.log
■------------------
警告ルール
■------------------
rulectl export
コマンドを実行すると、現在設定している警告ルールが以下に出力され、
全ルールを確認するのに便利。
※条件を指定できるが、指定しないと全てのルールが出力される。
$IST_HOME/log/RULE_YYYYMMDDHHMI24.log
警告ルールに合致してアラートが挙がる場合、
警告ルール名が出力されるため、分かり易い名前にしておかないと、
何が起きているのか 判断できない。
■------------------
インスタンス追加時の注意点
■------------------
$IST_HOME/install/install.sh
インスタンス追加時、上記を実行するが、中で呼んでいるシェルが
「$IST_HOME/stage/sh」から「$IST_HOME/bin」へコピーして
/bin 配下を実行しているため、セキュリティ対策ソフトを導入
していると、エラーでインストーラーが動かないため、注意。
PostgreSQL環境設定内容の確認方法
PostgreSQLは、postgresql.confというファイルで 環境設定を行う。
設定された内容は、pg_settingsというビューで確認でき、
pg_settingsと設計書を比較して内容を確認していたが、
容易にいかないため、断念していた。
理由は、設定した内容が そのまま格納されている訳ではないため。
例えば、shared_buffersに100MBと設定しても、
100、もしくは 100MBと格納される訳ではない。
pg_settingsには、unitという項目があり、単位がある。
shared_buffersは、8kB単位となるため、
shared_buffersに100MBと設定すると、
pg_settingsは以下と格納される。
・setting：12800
・unit ：8kB
テーブルやインデックスは shared_buffersに乗っかるが、
8KB単位になるからだ。
pg_settingsのunitは、以下の5種類ある。
・16MB
・8kB
・kB
・min
・ms
・s
例えば、16MBなのは 以下。
・max_wal_size
・min_wal_size
これは、WALが1ファイル16MBだから。
そこで、アドオン・テーブルを作成し、CSVファイルへ出力して
設計書と内容を確認する方法を紹介する。
概要は以下。
・pg_settingsからアドオン・テーブルへINSERTし、その後単位毎にUPDATEし、
単位を揃える
・pg_settingsからデータを取得する際、IPアドレスを指定して接続する。
ローカル接続すると、以下の値が0になってしまうため。
・tcp_keepalives_idle
・tcp_keepalives_interval
・tcp_keepalives_count
・pg_settingsからデータを出力する際、区切り文字を「|」等に指定する。
「,」だと、データ上に「,」があると、 Excel上 セルが分かれてしまうため。
・ExcelでCSVファイルを開き、区切り位置に「|」を指定し、列を分割する。
・アドオン・テーブルは以下をPrimary Keyにし、環境が複数あった場合、
EXCEL内で比較／確認できるように設計している。
・server_name
・instance
・name（pg_settingsのname）
-- テーブル作成
create table infra_settings (
server_name varchar(030), -- サーバ名
instance varchar(030), -- インスタンス名
name varchar(064),
setting varchar(256),
setting_kb varchar(030), -- 容量の場合、kbを格納
setting_mb varchar(030), -- 容量の場合、mbを格納
setting_gb varchar(030), -- 容量の場合、gbを格納
setting_ms varchar(030), -- 時間の場合、msを格納
setting_s varchar(030), -- 時間の場合、sを格納
setting_min varchar(030), -- 時間の場合、minを格納
setting_h varchar(030), -- 時間の場合、hを格納
unit varchar(030),
category varchar(256),
vartype varchar(030),
source varchar(064),
min_val varchar(030),
max_val varchar(030),
boot_val varchar(256),
sourcefile varchar(256),
pending_restart varchar(030),
CONSTRAINT infra_settings_pk
PRIMARY KEY (server_name,instance,name)
USING INDEX TABLESPACE index_tablespace
)
WITH (FILLFACTOR=95)
TABLESPACE <テーブルスペース名>;
-- INSERT（hostとistcは置換すること）
insert into infra_settings (
server_name
,instance
,name
,setting
,unit
,category
,vartype
,source
,min_val
,max_val
,boot_val
,sourcefile
,pending_restart )
select
:host
,:istc
,name
,setting
,unit
,category
,vartype
,source
,min_val
,max_val
,boot_val
,sourcefile
,pending_restart
from pg_settings;
--
update infra_settings
set setting_mb = (to_number(setting,'999999999')*16)
, setting_gb = (to_number(setting,'999999999')*16/1024)
where unit = '16MB';
--
update infra_settings
set setting_kb = (to_number(setting,'999999999')*8)
, setting_mb = (to_number(setting,'999999999')*8/1024)
, setting_gb = (to_number(setting,'999999999')*8/1024/1024)
where unit = '8kB';
--
update infra_settings
set setting_kb = to_number(setting,'999999999')
, setting_mb = (to_number(setting,'999999999')/1024)
where unit = 'kB';
--
update infra_settings
set setting_min = to_number(setting,'999999999')
, setting_h = (to_number(setting,'999999999')/60)
where unit = 'min';
--
update infra_settings
set setting_ms = to_number(setting,'999999999')
, setting_s = (to_number(setting,'999999999')/1000)
, setting_min = (to_number(setting,'999999999')/1000/60)
where unit = 'ms';
--
update infra_settings
set setting_s = to_number(setting,'999999999')
, setting_min = (to_number(setting,'999999999')/60)
where unit = 's';
ネットで王祿等の日本酒を購入
他の銘柄の日本酒を飲んでみたくて、
新たに「山中酒の店」という店で購入。
https://www.yamanaka-sake.jp/store/
今まで見たことが無かった
王祿、磯自慢、会津娘、東一、田中六五
等の、以下を購入。
王祿『八○(はちまる)』生原酒<春季限定>
磯自慢 特別本醸造 秘蔵寒造り
会津娘 純米酒
東一 純米山田錦64%
田中六五 純米<火入れ>
紀土-KID- 純米大吟醸
金曜の夜に購入し、翌土曜の11:00に決済の連絡が来、
土曜の17:10に発送したメールが届いた。
商品は 翌日曜の10:00に佐川急便で到着。
王祿と磯自慢は、「クール便のみ発送可」と書かれている。
王祿は島根の酒で、かなり拘りが強いようで、
扱っている特約店が少ない。
ホームページに 以下と書かれていた。
http://www.ouroku.com/
・王祿の酒は全品無濾過
・酒は生、あるいは生詰
通常販売されている日本酒は、二度もしくはそれ以上の回数火入れ殺菌をしますが、
火入れをすることによって酒の成長にとって必要な酵素も失活してしまいます。
その為、できる限り火入れ殺菌をしておりません。
瓶内で酒は生きています。
王祿『八○(はちまる)』は、精米歩合80%。
精米歩合は 通常60%くらいで、それより大きいと
雑味がある 等と感じられる。
米作りから取り組んだようで、ホームページに以下と書かれている。
飲むのが楽しみ！
王祿『八○(はちまる)』
高精米であればあるほど良い酒が出来るという常識を打ち破った酒。
納得いく米作りに７年の歳月を費やしている。
気に入ったら、また購入しよう！
ポケモン ソード 色違いが出ない...
色違いを狙っているが、なかなか出ない...
倒した数が850を超えたところで1度現れたが、
「ボックスが一杯です」と言われ、捕まえれなかった...
ポケモン ソード 色違いが出る確率
について、以下で書いたが、
https://chakugan.hatenablog.com/entry/2021/02/07/143145
確率で言うと、初めから光るお守り所持の場合、
1,168匹倒したところで、2体目の色違いが表れる筈。
カウンターが999までしかないが、
恐らく2,000を超えていると思うが、
2体目が全く出ない...
ポケモン ソード 色違いが出る確率
ひかるおまもりを持ち、倒した数が700を超えたが、
狙ったポケモンの色違いが出ない..
そこで、色違いが出る確率を、確率的に計算してみた。
※野生で出る確率
┏━━━━┳━━━━━━━┳━━━━━━━┓
┃倒した数┃光るお守り無 ┃光るお守り有 ┃
┃ 0～ 49┃0.0244140625% ┃0.0732421875% ┃
┃ 50～ 99┃0.048828125% ┃0.09765625% ┃
┃100～199┃0.0732421875% ┃0.1220703125% ┃
┃200～299┃0.09765625% ┃0.146484375% ┃
┃300～499┃0.1220703125% ┃0.1708984375% ┃
┃500～ ┃0.146484375% ┃0.1953125% ┃
┗━━━━┻━━━━━━━┻━━━━━━━┛
倒した数の時の累計確率は、以下の通り
┏━━┳━━━━━━━┳━━━━━━━┓
┃倒数┃光るお守り無 ┃光るお守り有 ┃
┃ 49┃ 1.1962890625%┃ 3.5888671875%┃
┃ 99┃ 3.6376953125%┃ 8.4716796875%┃
┃ 199┃10.9619140625%┃20.6787109375%┃
┃ 299┃20.7275390625%┃35.3271484375%┃
┃ 499┃45.1416015625%┃69.5068359375%┃
┗━━┻━━━━━━━┻━━━━━━━┛
以下の確率を超える時の倒す数は、以下の通り
┏━━┳━━━━┳━━━━┓
┃確率┃お守り無┃お守り有┃
┃ 70%┃ 669┃ 502┃
┃ 80%┃ 737┃ 553┃
┃ 90%┃ 806┃ 604┃
┃100%┃ 874┃ 656┃
┗━━┻━━━━┻━━━━┛
上記の「お守り有」は、倒した数が０の時にお守りをGetした場合。
ありがちなところで計算してみると、
光るお守りをGetした時、倒した数が５だった場合、
657匹引き倒すと、確率が100%を超え、100.1220703125%になる。
