職涯認證路線圖

  • AWS Certified Database – Specialty
    Exam Number: DBS-C01
  • MySQL 8.0 Database Administrator (Also available in CHS for Taiwan)
    Exam Number: 1Z0-908
  • Oracle Database Foundations (Also available in CHS for Taiwan)
    Exam Number: 1Z0-006
  • Oracle Database Administration I (Available only in China)
    Exam Number: 1Z0-082-CHN
  • Oracle Database Administration II (Available only in China)
    Exam Number: 1Z0-083-CHN
  • Oracle Database Foundations (Also available in CHS for Taiwan)
    Exam Number: 1Z0-006
  • Oracle Database 23ai Administration Associate
    Exam Number: 1Z0-182
  • Oracle Database 19c: Data Guard Administration
    Exam Number: 1Z0-076
  • Oracle Database 19c: RAC, ASM, and Grid Infrastructure Administration
    Exam Number: 1Z0-078

使用 GitHub Action 快速建立免費的 VPC

會使用到的工具有二
1. Github Action
2. ngrok

先從 ngrok 取得一組 API Token 備用 ; 沒有帳號的同學請使用 Github / Google 帳號連動即可

接著處理 Github Action 的基礎設定

Create new repo: 記得選成 private ; 因為需要放 ngrok API Token

Master Branch 甚至也不用 commit 資訊,因為重點在 Actions 環節

接著準備 main.yml 資訊 ; 記得把 ${API Token} 替換成自己的資訊

# 1682879657
name: CI
on: [push, workflow_dispatch]

jobs:
  build:
    runs-on: ubuntu-latest
    steps:

    - name: package install
      run: sudo apt-get -qq install neofetch speedtest-cli aptitude php-cli

    - name: Download ssh config
      run: wget https://raw.githubusercontent.com/GogoZin/sshd/main/sshd_config -O sshd_config

    - name: replace ssh config
      run: sudo cp sshd_config /etc/ssh/sshd_config

    - name: restart ssh service
      run: sudo service ssh restart

    - name: Remove root password
      run: sudo passwd -d root

    - name: remove runner password

      run: sudo passwd -d runner

    - name: download ngrok package
      run: wget https://bin.equinox.io/c/bNyj1mQVY4c/ngrok-v3-stable-linux-amd64.tgz

    - name: unzip ngrok package
      run: tar zxvf ngrok-v3-stable-linux-amd64.tgz

    - name: setup ngrok API token
      run: ./ngrok config add-authtoken ${API Token}

    - name: Tunel for SSH port
      run: ./ngrok tcp 22

直接 New 一份 workflow

因為 main.yml 已經準備好了,所以就 set up a workflow yourself

把 main.yml 貼完後 commit change

通常第一次 commit 完會自動執行,但如果需要重新執行時,也可以使用 Re-run all jobs 處理

點選 Build 也可以確認 CI/CD 狀態

這個時候我們可以回到 ngrok 確認資源啟動狀態 ; hmmm, endpoint 已經正常啟動了 ; 並且點選可以看到設備的基礎資訊

通常介面資訊就已經可以取得 SSH 接入方式了 ; 但如果使用量大的話 endpoint 變換其實是非常大的

所以寫了個程式擷取資訊

#!/bin/bash

api_endpoint="https://api.ngrok.com/endpoints"
authtoken="${ngrok_api_token}"
ngrok_version="2"

hostport=$(curl -sSL \
           -X GET \
           -H "Authorization: Bearer ${authtoken}" \
           -H "Ngrok-Version: ${ngrok_version}" \
           "${api_endpoint}" | jq -r '.endpoints[].hostport')

host=$(echo "${hostport}" | cut -d':' -f1)
port=$(echo "${hostport}" | cut -d':' -f2)

ssh "root@${host}" -p "${port}"

就連 ssh 接入設備也就是一下子的事

最後一定要備註的事,這 VPC 不是永久設備,過一陣子就會消失了

實際測試時間有長有短,但有帳面紀錄的時間我會說 43 min

 18:15:56 up 43 min,  1 user,  load average: 0.00, 0.00, 0.00
 18:15:57 up 43 min,  1 user,  load average: 0.00, 0.00, 0.00
 18:15:58 up 43 min,  1 user,  load average: 0.00, 0.00, 0.00
Connection to 0.tcp.ngrok.io closed by remote host.
Connection to 0.tcp.ngrok.io closed.

Tips and Tricks for Testing Your Environment with bench.sh Script

What is bench.sh?

Just curl https://bench.sh/

Installing and Setting up bench.sh

不用安裝,直接存取原始檔案執行即可

Executive Command Suggestions

Interpreting the Results

root@fv-az422-569:~# wget -qO- bench.sh | bash
 OS                 : Ubuntu 22.04.2 LTS
-------------------- A Bench.sh Script By Teddysun -------------------
 Version            : v2022-06-01
 Usage              : wget -qO- bench.sh | bash
----------------------------------------------------------------------
 CPU Model          : Intel(R) Xeon(R) Platinum 8171M CPU @ 2.60GHz
 CPU Cores          : 2 @ 2095.173 MHz
 CPU Cache          : 36608 KB
 AES-NI             : Enabled
 VM-x/AMD-V         : Disabled
 Total Disk         : 96.9 GB (56.8 GB Used)
 Total Mem          : 6.8 GB (599.8 MB Used)
 Total Swap         : 4.0 GB (0 Used)
 System uptime      : 0 days, 0 hour 6 min
 Load average       : 0.04, 0.25, 0.16
 OS                 : Ubuntu 22.04.2 LTS
 Arch               : x86_64 (64 Bit)
 Kernel             : 5.15.0-1036-azure
 TCP CC             : cubic
 Virtualization     : Hyper-V
 Organization       : AS8075 Microsoft Corporation
 Location           : San Jose / US
 Region             : California
----------------------------------------------------------------------
 I/O Speed(1st run) : 200 MB/s
 I/O Speed(2nd run) : 202 MB/s
 I/O Speed(3rd run) : 202 MB/s
 I/O Speed(average) : 201.3 MB/s
----------------------------------------------------------------------
 Node Name        Upload Speed      Download Speed      Latency
 Speedtest.net    1426.17 Mbps      9096.61 Mbps        1.12 ms
 Los Angeles, US  1424.66 Mbps      14050.23 Mbps       10.36 ms
 Dallas, US       1425.80 Mbps      8363.59 Mbps        39.13 ms
 Montreal, CA     191.80 Mbps       901.43 Mbps         68.53 ms
 Paris, FR        749.30 Mbps       4829.11 Mbps        149.39 ms
 Amsterdam, NL    606.65 Mbps       4816.54 Mbps        147.69 ms
 Guangzhou, CN    18.88 Mbps        17.38 Mbps          388.49 ms
 Hongkong, CN     6.11 Mbps         0.89 Mbps           151.28 ms
 Singapore, SG    508.98 Mbps       88.97 Mbps          168.32 ms
 Tokyo, JP        727.35 Mbps       6674.47 Mbps        104.88 ms
----------------------------------------------------------------------
 Finished in        : 5 min 21 sec
 Timestamp          : 2023-04-27 16:54:21 UTC
----------------------------------------------------------------------

網路測通也在幾個重點 Region 跑過一輪

不同虛擬平台,如果想增加些掌握度的話,資訊的確夠用了我覺得

使用 MAC 快速測試網路頻寬

Environment

Host: 2023 MAC Mini M2

user@userdeMac-mini ~ % uname -a
Darwin userdeMac-mini.local 22.4.0 Darwin Kernel Version 22.4.0: Mon Mar 6 21:01:02 PST 2023; root:xnu-8796.101.5~3/RELEASE_ARM64_T8112 arm64

也假設 Homebrew 已經安裝完成了 ; 其實預設也有 speedtest-cli 可以用,但如果有官方的原創,好像也不賴,重點其實也就測試數據罷了

Install

先清掉舊的

brew uninstall speedtest-cli --force\n

再來裝新的

brew tap teamookla/speedtest\n
brew update
brew install speedtest --force

How to use it?

先看 help

user@userdeMac-mini ~ % networkQuality --help
networkQuality: illegal option -- -
USAGE: networkQuality [-C ] [-c] [-h] [-I ] [-k] [-p] [-r host] [-s] [-v]
-C: override Configuration URL or path (with scheme file://)
-c: Produce computer-readable output
-h: Show help (this message)
-I: Bind test to interface (e.g., en0, pdp_ip0,…)
-k: Disable certificate validation
-p: Use Private Relay
-r: Connect to host or IP, overriding DNS for initial config request
-s: Run tests sequentially instead of parallel upload/download
-v: Verbose output

啟動執行的話就是加個 -v 即可 ; 建議 sequentially 執行畢竟也是個非對稱線路,數據不好看

user@userdeMac-mini ~ % networkQuality -v -s
Downlink: capacity 396.405 Mbps, responsiveness 684 RPM (15.609 MB transferred) - Uplink: capacity 0.000 Mbps, responsiveness 0 RPM (0 B transferred
skip
Downlink: capacity 436.744 Mbps, responsiveness 267 RPM (472.174 MB transferred) - Uplink: capacity 218.527 Mbps, responsiveness 1865 RPM (457.728 M
==== SUMMARY ====
Uplink capacity: 224.981 Mbps (Accuracy: High)
Downlink capacity: 436.744 Mbps (Accuracy: High)
Uplink Responsiveness: High (1865 RPM) (Accuracy: High)
Downlink Responsiveness: Medium (267 RPM) (Accuracy: High)
Idle Latency: 12.958 milliseconds (Accuracy: High)
Interface: en1
Uplink bytes transferred: 461.166 MB
Downlink bytes transferred: 472.174 MB
Uplink Flow count: 20
Downlink Flow count: 12
Start: 2023/4/25 上午12:00:04
End: 2023/4/25 上午12:00:31
OS Version: Version 13.3.1 (Build 22E261)

以光世代 500M/250M 802.11ax (其實雜訊不低) 測試這樣

應該也夠 WFH 用了

The tricks for purge hydra useless data.

Intro

Ory Hydra is a hardened, OpenID Certified OAuth 2.0 Server and OpenID Connect Provider.

使用苦主在這兒也簡介了下 Hydra

問題是什麼

在 Hydra v1.3.* 的時期 Schema 依賴 foreign key 建立關聯來節省些事情

但也因為服務特性,只要被摸就會留紀錄的特性目前無法避免無效資料量的增長,所以就只能手動刪除了

好消息是 v2.0.* 後就完全找不到 foreign key 依賴的 Schema 了

這樣資料作業也會方便些。

Background

DB: MariaDB 10.4.15
Hydra v1.3.*

table space size

# ls -lhS | head
total 318G
-rw-rw---- 1 mysql mysql  63G Dec 21 19:53 hydra_oauth2_authentication_request.ibd
-rw-rw---- 1 mysql mysql  55G Dec 21 19:53 hydra_oauth2_oidc.ibd
-rw-rw---- 1 mysql mysql  50G Dec 21 19:53 hydra_oauth2_access.ibd
-rw-rw---- 1 mysql mysql  49G Dec 21 19:53 hydra_oauth2_code.ibd
-rw-rw---- 1 mysql mysql  42G Dec 21 19:53 hydra_oauth2_consent_request.ibd
-rw-rw---- 1 mysql mysql  35G Dec 21 19:53 hydra_oauth2_logout_request.ibd
-rw-rw---- 1 mysql mysql 9.4G Dec 21 19:53 hydra_oauth2_authentication_session.ibd
-rw-rw---- 1 mysql mysql 7.9G Dec 21 19:53 hydra_oauth2_consent_request_handled.ibd
-rw-rw---- 1 mysql mysql 6.6G Dec 21 19:53 hydra_oauth2_authentication_request_handled.ibd

table hydra_oauth2_authentication_request status

$ mysql -e "show table status like 'hydra_oauth2_authentication_request'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name                                | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment | Max_index_length | Temporary |
+-------------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| hydra_oauth2_authentication_request | InnoDB |      10 | Dynamic    | 38617002 |           1085 | 41912156160 |               0 |  24018206720 | 250609664 |           NULL | 2022-04-30 23:53:04 | 2022-12-21 19:55:03 | NULL       | utf8_general_ci |     NULL |                |         |                0 | N         |
+-------------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+

問題

產品清資料的語法大概長這樣

mysql> delete from `hydra_oauth2_consent_request` where `client_id` = 'vip' limit 10;

交代一下 Schema 的 FK 關聯多麻煩

$ mysql -e "show create table hydra_oauth2_consent_request\G"
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: hydra_oauth2_consent_request
Create Table: CREATE TABLE `hydra_oauth2_consent_request` (
  `challenge` varchar(40) NOT NULL,
  `verifier` varchar(40) NOT NULL,
  `client_id` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `request_url` text NOT NULL,
  `skip` tinyint(1) NOT NULL,
  `requested_scope` text NOT NULL,
  `csrf` varchar(40) NOT NULL,
  `authenticated_at` timestamp NULL DEFAULT NULL,
  `requested_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `oidc_context` text NOT NULL,
  `forced_subject_identifier` varchar(255) DEFAULT '',
  `login_session_id` varchar(40) DEFAULT NULL,
  `login_challenge` varchar(40) DEFAULT NULL,
  `requested_at_audience` text NOT NULL,
  `acr` text NOT NULL,
  `context` text NOT NULL,
  PRIMARY KEY (`challenge`),
  UNIQUE KEY `hydra_oauth2_consent_request_veri_idx` (`verifier`),
  KEY `hydra_oauth2_consent_request_cid_idx` (`client_id`),
  KEY `hydra_oauth2_consent_request_sub_idx` (`subject`),
  KEY `hydra_oauth2_consent_request_idx_01` (`requested_at`),
  KEY `hydra_oauth2_consent_request_login_session_id_idx` (`login_session_id`),
  KEY `hydra_oauth2_consent_request_login_challenge_idx` (`login_challenge`),
  CONSTRAINT `hydra_oauth2_consent_request_client_id_fk` FOREIGN KEY (`client_id`) REFERENCES `hydra_client` (`id`) ON DELETE CASCADE,
  CONSTRAINT `hydra_oauth2_consent_request_login_challenge_fk` FOREIGN KEY (`login_challenge`) REFERENCES `hydra_oauth2_authentication_request` (`challenge`) ON DELETE SET NULL,
  CONSTRAINT `hydra_oauth2_consent_request_login_session_id_fk` FOREIGN KEY (`login_session_id`) REFERENCES `hydra_oauth2_authentication_session` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

當然這張表也不小

$ mysql -e "show table status like 'hydra_oauth2_consent_request'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| Name                         | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment | Max_index_length | Temporary |
+------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+
| hydra_oauth2_consent_request | InnoDB |      10 | Dynamic    | 21986247 |           1362 | 29961666560 |               0 |  14324645888 | 108003328 |           NULL | 2022-04-30 23:53:04 | 2022-12-21 20:01:01 | NULL       | utf8_general_ci |     NULL |                |         |                0 | N         |
+------------------------------+--------+---------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------+------------------+-----------+

先從 explain 來看的話會發生什麼事情

$ mysql -e "explain delete from hydra_oauth2_consent_request where client_id = 'vip' limit 10;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------------+------------------------------+-------+--------------------------------------+--------------------------------------+---------+------+---------+-------------+
| id   | select_type | table                        | type  | possible_keys                        | key                                  | key_len | ref  | rows    | Extra       |
+------+-------------+------------------------------+-------+--------------------------------------+--------------------------------------+---------+------+---------+-------------+
|    1 | SIMPLE      | hydra_oauth2_consent_request | range | hydra_oauth2_consent_request_cid_idx | hydra_oauth2_consent_request_cid_idx | 767     | NULL | 9292132 | Using where |
+------+-------------+------------------------------+-------+--------------------------------------+--------------------------------------+---------+------+---------+-------------+

接下來作業的時候發生了什麼狀況,很明顯的一根 replication delay

雖然不至於產生 HA 切換,但麻煩的是商務邏輯在讀寫分離的情況下產生的功能異常影響就大條了

區分 delete / select 在 explain 上的差異 ; 光 Using index condition / Using where 的差異就明顯解釋了效能差異

[wnlin@dba-desktop-1 ~]$ mysqlq dbm-m028 m028006 -e "explain select * from hydra_oauth2_consent_request where client_id = 'vip' limit 10;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+-------------+------------------------------+------+--------------------------------------+--------------------------------------+---------+-------+---------+-----------------------+
| id   | select_type | table                        | type | possible_keys                        | key                                  | key_len | ref   | rows    | Extra                 |
+------+-------------+------------------------------+------+--------------------------------------+--------------------------------------+---------+-------+---------+-----------------------+
|    1 | SIMPLE      | hydra_oauth2_consent_request | ref  | hydra_oauth2_consent_request_cid_idx | hydra_oauth2_consent_request_cid_idx | 767     | const | 9292105 | Using index condition |
+------+-------------+------------------------------+------+--------------------------------------+--------------------------------------+---------+-------+---------+-----------------------+

這個時候的 limit offset 對解決問題的幫助不大

所以解決問題的思維還是要從 PK 下手

但怎麼找出隨機的 PK 又符合 client_id 條件

order by random() 就不再提了

但如果在同一張表上 JOIN 處理過的 Data Set 就好辦了

STMT 大概會長這樣 ; 有沒有需要 ORDER 見仁見智,畢竟原生需求只要清資料

explain SELECT r1.challenge, r1.client_id
  FROM hydra_oauth2_consent_request AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(challenge)
                        FROM hydra_oauth2_consent_request)) as id)
        AS r2
 WHERE r1.challenge >= r2.id
 AND r1.client_id = 'vip'
 -- ORDER BY r1.challenge ASC
 LIMIT 10;

再確認一下 explain 已經可以正常使用 index 了

mysql> explain SELECT r1.challenge, r1.client_id
    ->   FROM hydra_oauth2_consent_request AS r1 JOIN
    ->        (SELECT CEIL(RAND() *
    ->                      (SELECT MAX(challenge)
    ->                         FROM hydra_oauth2_consent_request)) as id)
    ->         AS r2
    ->  WHERE r1.challenge >= r2.id
    ->  AND r1.client_id = 'vip'
    ->  -- ORDER BY r1.challenge ASC
    ->  LIMIT 10;
+------+-------------+------------+--------+----------------------------------------------+--------------------------------------+---------+-------+---------+------------------------------+
| id   | select_type | table      | type   | possible_keys                                | key                                  | key_len | ref   | rows    | Extra                        |
+------+-------------+------------+--------+----------------------------------------------+--------------------------------------+---------+-------+---------+------------------------------+
|    1 | PRIMARY     | <derived2> | system | NULL                                         | NULL                                 | NULL    | NULL  | 1       |                              |
|    1 | PRIMARY     | r1         | ref    | PRIMARY,hydra_oauth2_consent_request_cid_idx | hydra_oauth2_consent_request_cid_idx | 767     | const | 9290919 | Using where; Using index     |
|    2 | DERIVED     | NULL       | NULL   | NULL                                         | NULL                                 | NULL    | NULL  | NULL    | No tables used               |
|    3 | SUBQUERY    | NULL       | NULL   | NULL                                         | NULL                                 | NULL    | NULL  | NULL    | Select tables optimized away |
+------+-------------+------------+--------+----------------------------------------------+--------------------------------------+---------+-------+---------+------------------------------+
4 rows in set, 1 warning (0.00 sec)

再看看這個時候取資料的效率 ; 各 offset limit 反應時間都可以維持在 0.5s 內

10 rows in set, 11 warnings (0.01 sec)
1000 rows in set, 1001 warnings (0.01 sec)
10000 rows in set, 10001 warnings (0.04 sec)
50000 rows in set, 50001 warnings (0.11 sec)

我不建議再把語法改寫成 DELETE INNER JOIN 作業

也因為取得的 dataset 內已含 challenge 這 PK 的話,那就是 loop 把 dataset 一個一個走完,原則上也就不擔心 DELETE STMT 產生的 replication delay 了.