Yung-Chung Ku ycku

Date	Scale	Size (MB)
2020/01/01	0	7.77
2020/02/01	10	157.00
2020/03/01	50	756.00
2020/04/01	100	1503.00
2020/04/02	100	1507.00
2020/04/03	100	1510.00
2020/04/04	100	1515.00

                                                                     QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=107446.22..266934.22 rows=1 width=60) (actual time=561.213..14329.521 rows=15 loops=1)
   ->  Nested Loop Left Join  (cost=107445.79..139804.60 rows=1 width=24) (actual time=558.920..7446.517 rows=15 loops=1)
         ->  Index Scan using dtapb001_pk on dtapb001 a  (cost=0.56..19.74 rows=1 width=18) (actual time=0.209..1.322 rows=15 loops=1)
               Index Cond: ((emp_id = 'JXXXXXXXXX'::bpchar) AND (vrfy_wkym <= '202009'::numeric) AND (vrfy_wkym >= '201908'::numeric))
               Filter: (substr((emp_org_id)::text, 2, 1) = '3'::text)
               Rows Removed by Filter: 30
         ->  Bitmap Heap Scan on pg060 s  (cost=107445.23..139784.85 rows=1 width=24) (actual time=496.333..496.333 rows=0 loops=15)

                                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop Left Join  (cost=1.43..6360.93 rows=1 width=60) (actual time=19.573..273.880 rows=15 loops=1)
   ->  Nested Loop Left Join  (cost=0.99..2521.89 rows=1 width=24) (actual time=9.920..134.981 rows=15 loops=1)
         ->  Index Scan using dtapb001_pk on dtapb001 a  (cost=0.56..19.74 rows=1 width=18) (actual time=0.025..0.135 rows=15 loops=1)
               Index Cond: ((emp_id = 'JXXXXXXXXX'::bpchar) AND (vrfy_wkym <= '202009'::numeric) AND (vrfy_wkym >= '201908'::numeric))
               Filter: (substr((emp_org_id)::text, 2, 1) = '3'::text)
               Rows Removed by Filter: 30
         ->  Index Scan using pg060_idx2 on pg060 s  (cost=0.43..2502.14 rows=1 width=24) (actual time=8.987..8.987 rows=0 loops=15)

REINDEX 會影響線上作業, 在有限的作業時間內, 儘可能進行更多的 REINDEX

在 DEADLINE 之前能做多少算多少, DEADLINE 之後就放棄
此例以 INDEX SIZE 為最基本的排序, 大的先做
後續可以再加上使用統計的條件, 更精確地 REINDEX 需要重建的索引
scripts.list 每行一個 shell 指令
maintenance.sh 可用於其他排程控制
SQL 語句條件記得要修改

$ psql --dbname=pgbench -t -f reindex_by_size_desc.sql &gt; scripts.list

sudo mokutil --disable-validation

把指定來源的 repository 檔案都抓回來

wget -r -np --no-check-certificate http://fedora.cs.nctu.edu.tw/epel/8/Everything/x86_64/

使用ssh跳版機(Bastion server)連線到 remote host

ssh -J bastion_user@bastion_host remote_user@remote_host

使用ssh跳版機執行 scp

scp -oProxyJump=bastion_user@bastion_host remote_user@remote_host:/tmp/test.txt .

	DECLARE @vmname VARCHAR(20)
	set @vmname='VMXX-001'
	delete FROM [vdi7s_composer].[dbo].[SVI_VM_NAME] where NAME=@vmname
	delete FROM [vdi7s_composer].[dbo].[SVI_COMPUTER_NAME] where NAME=@vmname
	delete FROM [vdi7s_composer].[dbo].[SVI_TASK_STATE] where SIM_CLONE_ID=(select id FROM [vdi7s_composer].[dbo].[SVI_SIM_CLONE] where VM_NAME=@vmname)
	delete
	FROM [vdi7s_composer].[dbo].[SVI_SC_PDISK_INFO] where PARENT_ID=(select id FROM [vdi7s_composer].[dbo].[SVI_SIM_CLONE] where VM_NAME=@vmname)
	delete
	FROM [vdi7s_composer].[dbo].[SVI_SC_BASE_DISK_KEYS] where PARENT_ID=(select id FROM [vdi7s_composer].[dbo].[SVI_SIM_CLONE] where VM_NAME=@vmname)
	delete

	-- password is saved in ~/.pgpass
	-- Connect to your pgbouncer and then get "SHOW CLIENTS"
	-- SELECT * FROM pgbouncer.connect;
	-- SELECT * FROM pgbouncer.clients;

	CREATE EXTENSION IF NOT EXISTS dblink;
	CREATE SCHEMA IF NOT EXISTS pgbouncer;

	DROP VIEW pgbouncer.connect;
	CREATE VIEW pgbouncer.connect AS SELECT dblink_connect('pgbouncer','host=localhost port=5432 user=pgbouncer dbname=pgbouncer');

	$target_dir = "d:\temp"
	# WHERE for safe test
	# Process the whole directories by removing WHERE clause
	$target_list = Get-ChildItem $target_dir \| WHERE { $_.name -eq "00123456" }

	foreach ($userdir in $target_list) {
	$userdir.Fullname
	$ACL = Get-ACL $userdir.Fullname
	# Disable inheritance
	$ACL.SetAccessRuleProtection($True, $False)