RDBMS 裡的各種 Lock 與 Isolation Level

來推薦其他人寫的文章 (雖然是在 Medium 上...):「複習資料庫的 Isolation Level 與圖解五個常見的 Race Conditions」、「對於 MySQL Repeatable Read Isolation 常見的三個誤解」,另外再推薦英文維基百科上的「Snapshot isolation」條目。

兩篇文章都是中文 (另外一個是英文維基百科條目),就不重複講了,這邊主要是拉條目的內容記錄起來,然後寫一些感想...

SQL-92 定義 Isolation 的時候,技術還沒有這麼成熟,所以當時在訂的時候其實是以當時的技術背景設計 Isolation,所以當技術發展起來後,發生了一些 SQL-92 的定義沒那麼好用的情況:

Unfortunately, the ANSI SQL-92 standard was written with a lock-based database in mind, and hence is rather vague when applied to MVCC systems. Berenson et al. wrote a paper in 1995 critiquing the SQL standard, and cited snapshot isolation as an example of an isolation level that did not exhibit the standard anomalies described in the ANSI SQL-92 standard, yet still had anomalous behaviour when compared with serializable transactions.

其中一個就是 Snapshot Isolation,近代的資料庫系統都用這個概念實做,但實際上又有不少差別...

另外「Jepsen: MariaDB Galera Cluster」這篇裡出現的這張也很有用,裡面描述了不同層級之間會發生的問題:

這算是當系統有一點規模時 (i.e. 不太可能使用 SERIALIZABLE 避免這類問題),開發者需要了解的資料庫限制...

2018 年矽谷科技公司的薪資

不太意外的,排名起來加州這一區的科技公司的薪資還是最高的 (這邊包括了所有的所得,包括薪資、股票與分紅):「Top Paying Tech Companies of 2018」。

已經先整理出來的前五名分成「Entry-level / 1+ Yrs of Experience」、「Mid-level / 3+ Yrs of Experience」、「Been Around the Block / 5+ Yrs of Experience」三類,可以看到相對於年資的增加,薪資的調整也很快...

不過這邊相同名次的不會佔多個位置,只會佔一名,跟我們平常用的方式不太一樣,所以雖然是前五名但是都有六個公司。

AWS Lambda 也提供 SLA 了

在「AWS Lambda announces service level agreement」這邊看到 AWS Lambda 提供 99.95% 的 SLA :

We have published a service level agreement (SLA) for AWS Lambda. We will use commercially reasonable efforts to make Lambda available with a Monthly Uptime Percentage for each AWS region, during any monthly billing cycle, of at least 99.95% (the “Service Commitment”).

不過這種東西都是宣示意味比較重 (至少表示 AWS 認為產品穩定度夠上 SLA),倒不是希望會用到...

GCP 推出 Cloud HSM (beta)

這算是 Google Cloud Platform 在補產品線,讓那些有強制使用 HSM 的需求的應用 (通常是遇到一定要 FIPS 140-2 的規範) 可以搬上雲端:「Introducing Cloud HSM beta for hardware crypto key security」。

從圖片上可以看到 LiquidSecurity,應該是「LiquidSecurity® General Purpose HSM Adapters and Appliances」這個產品:

如同 AWSCloudHSM 服務,GCP 的 Cloud HSM 也是提供 FIPS 140-2 Level 3:

Cloud HSM allows you to host encryption keys and perform cryptographic operations in FIPS 140-2 Level 3 certified HSMs (shown below).

演算法上,支援 AESRSAECC (NIST 的 P-256 與 P-384):

In addition to symmetric key encryption using AES-256 keys, you can now create various types of asymmetric keys for decryption or signing operations, which means that you can now store your keys used for PKI or code signing in a Google Cloud managed keystore. Specifically, RSA 2048, RSA 3072, RSA 4096, EC P256, and EC P384 keys will be available for signing operations, while RSA 2048, RSA 3072, and RSA 4096 keys will also have the ability to decrypt blocks of data.

目前只支援 us-east1us-west1,另外價錢也比軟體服務版本的 Cloud KMS 貴不少:

Billable itemFor keys with protection level SOFTWAREFor keys with protection level HSM
Active AES-256 and RSA 2048 key versions$0.06 per month$1.00 per month
Active RSA 3072, RSA 4096 or Elliptic Curve key versions$0.06 per month$2.50 per month for the first 2,000
$1.00 per month thereafter
Destroyed key versionsFreeFree
Key operations: Cryptographic$0.03 per 10,000 operations$0.03 per 10,000 operations for AES-256 and RSA 2048 keys
$0.15 per 10,000 operations for RSA 3072, RSA 4096, and Elliptic Curve keys
Key operations: AdminFreeFree

不過一般情況應該不會得用 CloudHSM,先有個印象就好...

Working Set Size (WSS) 的想法

NetflixBrendan Gregg (他比較知名的發明是 Flame Graph) 寫了一篇「How To Measure the Working Set Size on Linux」,他想要量測單位時間內會用到的記憶體區塊大小:

The Working Set Size (WSS) is how much memory an application needs to keep working. Your app may have populated 100 Gbytes of main memory, but only uses 50 Mbytes each second to do its job. That's the working set size. It is used for capacity planning and scalability analysis.

這可以拿來分析這些應用程式是否能夠利用 L1/L2/L3 cache 大幅增加執行速度,於是就可以做成圖,像是這樣:

在 Netflix 這樣人數的公司,需要設計一些有用的指標,另外發展出對應的工具,讓其他人更容易迅速掌握狀況,畢竟不是每個人都有上天下海的能力,遇到狀況可以馬上有頭緒進行 trouble shooting...

Avast 放出他們的 Decompiler,RetDec

AvastMIT License 放出他們的 Decompiler,叫做 RetDec:「Avast open-sources its machine-code decompiler」,專案在 GitHub 上的 avast-tl/retdec 這邊。

Decompiler,也就是直接把 machine code 試著轉回高階語言的程式碼:

這對於分析工作來說簡化很多,尤其是在資安產業的人... 以往比較常見是轉成 assembly 再用人工分析,現在這樣有機會讓大腦輕鬆一些。

雖然目前有些限制 (像是 32 bits only),不過 open source 出來後,可以預料會有不少人開始加功能進去:

  • Supported file formats: ELF, PE, Mach-O, COFF, AR (archive), Intel HEX, and raw machine code.
  • Supported architectures (32b only): Intel x86, ARM, MIPS, PIC32, and PowerPC.

Amazon ECS 與 AWS Fargate 都納入 Amazon Compute SLA 計算

AWS 宣佈的這兩個服務 (Amazon ECSAWS Fargate) 都納入 99.99% 的 SLA 合約範圍:「Amazon Compute Service Level Agreement Extended to Amazon ECS and AWS Fargate」。

Amazon Elastic Container Service (Amazon ECS) and AWS Fargate are now included in the Amazon Compute Service Level Agreement (SLA) for 99.99% uptime and availability.

ECS 已經跑一陣子了可以理解,但 Fargate 的概念算比較新,剛出來沒多久就決定放進去比較意外...

AWS 主動提高 Amazon EC2 與 Amazon EBS 的 SLA

AWS 主動提高 Amazon EC2Amazon EBSSLA:「Announcing an increased monthly service commitment for Amazon EC2」。

Amazon EC2 is announcing an increase to the monthly service commitment in the EC2 Service Level Agreement (“SLA”), for both EC2 and EBS, to 99.99%. This increased commitment is the result of continuous investment in our infrastructure and quality of service. This change is effective immediately in all regions, and is available to all EC2 customers.

之前是 99.95% monthly (參考前幾天的頁面:「Amazon EC2 SLA」),現在拉到 99.99% 了。第一階的賠償條件也從 99.95%~99% 改成 99.99%~99% 了 (賠 10%)。

AWS CloudHSM 支援 FIPS 140-2 Level 3 了

AWS CloudHSM 推出了一些新功能:「AWS CloudHSM Update – Cost Effective Hardware Key Management at Cloud Scale for Sensitive & Regulated Workloads」。

其中比較特別的是從以前只支援 Level 2 變成支援 Level 3 了:

More Secure – CloudHSM Classic (the original model) supports the generation and use of keys that comply with FIPS 140-2 Level 2. We’re stepping that up a notch today with support for FIPS 140-2 Level 3, with security mechanisms that are designed to detect and respond to physical attempts to access or modify the HSM.

在維基百科裡面有提到 Level 2 與 Level 3 的要求:

Security Level 2 improves upon the physical security mechanisms of a Security Level 1 cryptographic module by requiring features that show evidence of tampering, including tamper-evident coatings or seals that must be broken to attain physical access to the plaintext cryptographic keys and critical security parameters (CSPs) within the module, or pick-resistant locks on covers or doors to protect against unauthorized physical access.

In addition to the tamper-evident physical security mechanisms required at Security Level 2, Security Level 3 attempts to prevent the intruder from gaining access to CSPs held within the cryptographic module. Physical security mechanisms required at Security Level 3 are intended to have a high probability of detecting and responding to attempts at physical access, use or modification of the cryptographic module. The physical security mechanisms may include the use of strong enclosures and tamper-detection/response circuitry that zeroes all plaintext CSPs when the removable covers/doors of the cryptographic module are opened.

主動式偵測以及銷毀算是 Level 3 比 Level 2 安全的地方。

另外就是計價方式的修正,先前有一筆固定的費用,現在變成完全照小時計費了:

Pay As You Go – CloudHSM is now offered under a pay-as-you-go model that is simpler and more cost-effective, with no up-front fees.

MySQL 上不同 Isolation Level 對效能的影響

目前看到的結論都是:MySQL (InnoDB) 上因為高度對 RR (REPEATABLE-READ) 最佳化,使得 RR 的效能反而比 RC (READ-COMMITTED) 以及 RU (READ-UNCOMMITTED) 都好。

不清楚 RR/RC/RU 差異的可以參考維基百科上「Isolation (database systems)」的解釋...

從 2010 年在測 5.0 的「Repeatable read versus read committed for InnoDB」到 2015 年測 5.7 的「MySQL Performance : Impact of InnoDB Transaction Isolation Modes in MySQL 5.7」都測出 RR 的效能比 RC/RU 好... 三段分別是 RR/RC/RU:

所以在 MySQL 上沒有使用 RC/RU 的必要... (抱頭)