MySQL 8.0 的 innodb_dedicated_server

Percona 介紹了 MySQL 8.0 將會推出的 innodb_dedicated_server 參數:「New MySQL 8.0 innodb_dedicated_server Variable Optimizes InnoDB from the Get-Go」,Oracle 官方的文件在「15.6.13 Enabling Automatic Configuration for a Dedicated MySQL Server」這邊可以翻到。

這是針對整台機器完全給 MySQL 用的情況所設計的參數。在這種情況下,可以透過 RAM 的大小以及一些簡單的公式,得到還算堪用的系統參數...

依照說明,可以看到系統會依照記憶體的大小自動計算出 innodb_buffer_pool_sizeinnodb_log_file_size 這兩個參數,並且把 innodb_flush_method 設為 O_DIRECT_NO_FSYNC (如果所在平台有支援這個值)。

不過看了一下公式,依照經驗可以設的更積極一點... 像是 Percona 文章裡提到的,當記憶體夠大時,其實可以考慮從 80% 開始調整大小 (innodb_buffer_pool_size):

For InnoDB buffer pool size (based on this article), consider allocating 80% of physical RAM for starters. You can increase it to as large as needed and possible, as long as the system doesn’t swap on the production workload.

innodb_log_file_size 則應該要分析寫入的 pattern 而不是直接看 RAM 大小。有些機器雖然很大台但幾乎沒有寫入的量,照著公式的值就偏大很多:

For InnoDB log file size, it should be able to handle one hour of writes to allow InnoDB to optimize writing the redo log to disk. You can calculate an estimate by following the steps here, which samples one minute worth of writes to the redo log. You could also get a better estimate from hourly log file usage with Percona Monitoring and Management (PMM) graphs.

不過基本上 tune 出來的值還算堪用,對於剛入手的人頗有幫助。

這次 PKCS #1 1.5 的 ROBOT 攻擊,Cisco 沒打算修...

1998 年就發現的 security issue 因為 workaround 也很複雜,所以不是每一家都修對方法,於是 19 年後又被爆破了。這次叫做 ROBOT:「1998 attack that messes with sites’ secret crypto keys is back in a big way」。

可以看到中獎的表:

這次的攻擊在 client 端無法修正,只能在 server 端修正:

Do I need to update my browser?
No. This is an implementation bug in servers, there is nothing clients can do to prevent it.

如果 server 端無法盡快修正的話,想辦法避開 RSA encryption 可以躲開這個問題,而且因為現代瀏覽器都有非 RSA 的替代方案,這樣做應該都還有退路,可以維持連線的可能性:

Disable RSA encryption!
ROBOT only affects TLS cipher modes that use RSA encryption. Most modern TLS connections use an Elliptic Curve Diffie Hellman key exchange and need RSA only for signatures. We believe RSA encryption modes are so risky that the only safe course of action is to disable them. Apart from being risky these modes also lack forward secrecy.

但使用 Cisco ACE 就哭了,因為 Cisco ACE 只支援 RSA encryption,而 Cisco 官方以產品線已經關閉,不再提供維護而沒有提供更新的計畫,所以就進入一個死胡同...

不過 Cisco 自己也還在用 Cisco ACE 就是了,不在意就不會痛的感覺 XD

I have a Cisco ACE device.
Cisco informed us that the ACE product line was discontinued several years ago and that they won't provide an update. Still, we found plenty of vulnerable hosts that use these devices.

These devices don't support any other cipher suites, therefore disabling RSA is not an option. To our knowledge it is not possible to use these devices for TLS connections in a secure way.

However, if you use these products you're in good company: As far as we can tell Cisco is using them to serve the cisco.com domain.

原來 Oracle 與 Microsoft 裡的條款是這樣來的...

看到「That time Larry Ellison allegedly tried to have a professor fired for benchmarking Oracle」這篇文章的講古,想起很久前就有聽過 Microsoft 有這樣的條款 (禁止未經原廠同意公開 benchmark 結果),原來是 Oracle 在三十幾年前創出來的?而且這種條款還有專有名詞「DeWitt Clauses」,出自當初被搞的教授 David DeWitt...

Microsoft 的條款是這樣:

You may not disclose the results of any benchmark test … without Microsoft’s prior written approval

Oracle 的則是:

You may not disclose results of any Program benchmark tests without Oracle’s prior consent

IBM 的反而在 license 裡面直接允許:

Licensee may disclose the results of any benchmark test of the Program or its subcomponents to any third party provided that Licensee (A) publicly discloses the complete methodology used in the benchmark test (for example, hardware and software setup, installation procedure and configuration files), (B) performs Licensee’s benchmark testing running the Program in its Specified Operating Environment using the latest applicable updates, patches and fixes available for the Program from IBM or third parties that provide IBM products (“Third Parties”), and © follows any and all performance tuning and “best practices” guidance available in the Program’s documentation and on IBM’s support web sites for the Program…

Amazon RDS 支援更大的硬碟空間與更多的 IOPS

Amazon RDS 的升級:「Amazon RDS Now Supports Database Storage Size up to 16TB and Faster Scaling for MySQL, MariaDB, Oracle, and PostgreSQL Engines」。

空間上限從 6TB 變成 16TB,而且可以無痛升。另外 IOPS 上限從 30K 變成 40K:

Starting today, you can create Amazon RDS database instances for MySQL, MariaDB, Oracle, and PostgreSQL database engines with up to 16TB of storage. Existing database instances can also be scaled up to 16TB storage without any downtime.

The new storage limit is an increase from 6TB and is supported for Provisioned IOPS and General Purpose SSD storage types. You can also provision up to 40,000 IOPS for Provisioned IOPS storage volumes, an increase from 30,000 IOPS.

不過隔壁的 Amazon Aurora 還是大很多啊 (64TB),而且實際上不用管劃多大,他會自己長大:

Q: What are the minimum and maximum storage limits of an Amazon Aurora database?

The minimum storage is 10GB. Based on your database usage, your Amazon Aurora storage will automatically grow, up to 64 TB, in 10GB increments with no impact to database performance. There is no need to provision storage in advance.

IEEE P1735 漏洞,又是 Padding Oracle Attack...

在「IEEE P1735 Encryption Is Broken—Flaws Allow Intellectual Property Theft」這邊看到 US-CERT 發表的「IEEE P1735 implementations may have weak cryptographic protections」,裡面提到的主要漏洞:

The methods are flawed and, in the most egregious cases, enable attack vectors that allow recovery of the entire underlying plaintext IP.

主要應該是第一包:

CVE-2017-13091: improperly specified padding in CBC mode allows use of an EDA tool as a decryption oracle.

又是 CBCpadding oracle attack 啊... 看起來是標準沒有強制定義好造成的?

The main vulnerability (CVE-2017-13091) resides in the IEEE P1735 standard's use of AES-CBC mode.

Since the standard makes no recommendation for any specific padding scheme, the developers often choose the wrong scheme, making it possible for attackers to use a well-known classic padding-oracle attack (POA) technique to decrypt the system-on-chip blueprints without knowledge of the key.

去年 Cloudflare 寫的「Padding oracles and the decline of CBC-mode cipher suites」這邊有提到 padding oracle attack 的方式,比較一般性的解法是避開要自己決定 Encrypt-then-MAC (IPsec;也是數學上證明安全性) 或 Encrypt-and-MAC (SSH) 或是 MAC-then-Encrypt (SSL),而是用 AEAD 類的加密元件直接躲開 padding oracle attack 的某些必要條件 (像是 AES-GCM 或是 ChaCha20-Poly1305)。

不過這也是這幾年大家才了解這樣做的重要性,當年在訂規格的時候都比較沒在在意這些...

MySQL 8.0-rc 的效能測試

Oracle 的 Dimitri KRAVTCHUK (dim) 做了測試,整理出 MySQL 8.0-rc 與其他版本效能的比較:「MySQL Performance : 2.1M QPS on 8.0-rc」。

不過先不管 MySQL 8.0-rc,這個測試其實也在說 MySQL 在 5.6 到 5.7 的過程中,對於高階伺服器效能改善非常的多 (有非常多 CPU core 的機器):

就更不用說 5.5 版 (甚至已經沒支援的 5.0 & 5.1),差距就更大了...

Heimdall Data:自動 Cache RDBMS 資料增加效能

看到 AWS 的「Automating SQL Caching for Amazon ElastiCache and Amazon RDS」這篇裡面介紹了 Heimdall Data – SQL caching and performance optimization 這個產品。

從官網的介紹也可以看出來是另外疊一層 proxy,但自動幫你處理 cache invalidation 的問題:

But what makes Heimdall Data unique in industry is its auto-cache AND auto-invalidation capability. Our machine learning algorithms determine what queries to cache while invalidating to ensure maximum performance and data integrity.

看起來支援了四個蠻常見的 RDBMS:

Heimdall Data supports most all relational database (e.g. MySQL, Postgres, Amazon RDS, Oracle, SQL Server, MariaDB).

看起來是一個花錢直接買效能的方案... 不過 cache invalidation 的部分不知道要怎麼跨機器做,在 FAQ 沒看到 cluster 情況下會怎麼解決。

MySQL 8.0 對 4 bytes UTF-8 的效能改善

在「MySQL 8.0: When to use utf8mb3 over utf8mb4?」這邊提到了 MySQLutf8 以及 utf8mb4 的故事,以及在 MySQL 8.0 預期的效能提昇:

可以看到 Oracle 的團隊花了不少力氣提昇 utf8mb4 的效能。另外提到了在 5.7 的時候將 row format 的預設值轉成 DYNAMIC

MySQL 5.7 (2015) added some optimizations such as a variable length sort buffer, and also changed InnoDB’s default row format to DYNAMIC. This allows for indexes on VARCHAR(255) with utf8mb4; something that made migrations more difficult prior.

依照「14.11.3 DYNAMIC and COMPRESSED Row Formats」這邊的敘述,看起來 COMPRESSED 也應該支援一樣的特性,不過不確定... (因為通常不會完整 index 整個 VARCHAR(255),只會 index 某個 prefix length):

The COMPRESSED row format uses similar internal details for off-page storage as the DYNAMIC row format, with additional storage and performance considerations from the table and index data being compressed and using smaller page sizes.

Oracle 官方的 InnoDB Cluster 出 GA 了...

Oracle 推出的 InnoDB Cluster 進入 GA 了,不過先觀望看看就好:「MySQL InnoDB Cluster GA is Available Now!」。

The GA release of InnoDB Cluster builds upon the great work that the MySQL Development Team has done on Group Replication, filling out the rest of the stack for setup, management, orchestration, and client routing.

算是 Galara Cluster 的競爭對手 (被 PerconaMariaDB 採用),產品成熟度還得再看如何...

eBay 把 MongoDB 當 cache layer 的用法...

在「How eBay’s Shopping Cart used compression techniques to solve network I/O bottlenecks」這邊 eBay 描述了他們怎麼解決在 MongoDB 上遇到的問題,不過我看的是他們怎麼用 MongoDB,而不是這次解決的問題:

It’s easier to think of the MongoDB layer as a “cache” and the Oracle store as the persistent copy. If there’s a cache miss (that is, missing data in MongoDB), the services fall back to recover the data from Oracle and make further downstream calls to recompute the cart.

把 MongoDB 當作 cache layer,當 cache miss 的時候還是會回去底層的 Oracle 撈資料計算,這用法頗有趣的...

不拿 memcached 出來用的原因不知道是為什麼,是要找個有 HA 方案的 cache layer 嗎?還是有針對 JSON document 做判斷操作?