InnoDB redo log 大小對效能的影響

在「Benchmark(et)ing with InnoDB redo log size」這邊看到在討論 InnoDB redo log 的大小對效能的影響 (也就是 innodb_log_file_sizeinnodb_log_files_in_group)。

開頭就有先提到重點,在新版 MySQL 裡,幾乎所有的情況比較大的 redo log 有比較好的效能 (平均值):

tl;dr - conclusions specific to my test

  1. A larger redo log improves throughput
  2. A larger redo log helps more with slower storage than with faster storage because page writeback is more of a bottleneck with slower storage and a larger redo log reduces writeback.
  3. A larger redo log can help more when the working set is cached because there are no stalls from storage reads and storage writes are more likely to be a bottleneck.
  4. InnoDB in MySQL 5.7.17 is much faster than 5.6.35 in all cases except IO-bound + fast SSD

可以看出來平均效能的提昇很顯著,不管是增加 redo log 大小還是升級到 5.7:

但作者也遇到了奇怪的效能問題。雖然平均效能提昇得很顯著,但隨著加入資料的增加,效能的 degradation 其實很嚴重,在原來的網頁上可以看到這些資訊。

The results above show average throughput and that hides a lot of interesting behavior. We expect throughput over time to not suffer from variance -- for both InnoDB and for MyRocks. For many of the results below there is a lot of variance (jitter).

所以也許現階段先加大就好 (至少寫入的效能會提昇),不需要把這個特性當作升級 MySQL 的理由。

玩 CloudWatch Logs

看到「Study Notes - CloudWatch」這篇後想到可以把 CloudWatch Logs 寫下來...

目前的玩法是參考「Quick Start: Install and Configure the CloudWatch Logs Agent on a Running EC2 Instance」這篇設定 IAM Role 的權限,然後安裝 agent,最後設定要丟什麼上去,其實這塊還蠻簡單的...

然後用 kennu/cwtail 這隻程式負責幫你跑出像 tail -f 的效果。像是 cwtail --profile=myaccount -e -f -s -t /var/log/syslog 這樣的用法 (參數的意義可以直接跑 cwtail 看到)。

不過他的 -n 好像不會動,跑下去都會從頭拉 XDDD

Amazon Aurora 支援 Auditing

AWS 的人把 auditing plugin 移植到 Amazon AuroraMySQL 環境上了:「Auditing an Amazon Aurora Cluster」。

官方宣稱的效能很好,打開後不會掉太多:

主要原因是把寫 auditing log 這塊改寫掉:

這樣看起來頗不錯,平常其實可以開起來讓他記錄?

CloudWatch 的降價

更早之前就公告了,但剛剛才翻到:「AWS Price Reduction – CloudWatch Metrics」。

CloudWatch 從 2011 年這次的降幅算比較大的,最低的降幅都有 40%,而超過一萬個 metrics 的部分則是 80%,然後不同級距有不同降幅,最高到 96% (也就是原來的 4% 價錢)...

Amazon EC2 的 CloudWatch 用了七個 metrics,所以如果有開 CloudWatch 進階版本的情況下,價錢從 $3.50 降到 $2.10:

If you have EC2 Detailed Monitoring enabled you will also see a price reduction with per-month charges reduced from $3.50 per instance per month to $2.10 or lower based on the volume tier.

在服務金額裡面的量通常不會太大,不過這次降價不無小補...

Mozilla 也在考慮對 Certificate Transparency 的掌握度

由於 Firefox 要支援 Certificate Transparency 的緣故,在「Mozilla CT Policy」這邊 Mozilla 在討論要建立自己的 CT policy 以及自己的架構:

CT is coming to Firefox. As part of that, Mozilla needs to have a set of CT policies surrounding how that will work. Like our root inclusion program, we intend to run our CT log inclusion program in an open and transparent fashion, such that the Internet community can see how it works and how decisions are made.

這樣就有個開頭了...

Facebook 備份 MySQL 資料並且確認正確性的方法

Facebook 再多花了一些篇幅數對於 MySQL 資料備份以及確認正確性的方法:「Continuous MySQL backup validation: Restoring backups」。

首先是 Continuous Restore Tier (CRT) 這塊,可以看到他們在這塊很仰賴 HDFS 當作備份的第一層基地,包括了 Full logical backups (用 mysqldump)、Differential (diff) backups 以及 Binary log (binlog) backups (stream 進 HDFS)。

另外上了 GTID,對於後續的處理會比較方便:

All of our database servers also use global transaction IDs (GTIDs), which gives us another layer of control when replaying transactions from binlog backups.

在 CRT 這塊可以看到其實是拿現成的工具堆起來,不同單位會因為規模而有不同的作法。真正的重點反而在 ORC Restore Coordinator (ORC) 這塊,可以看到 Facebook 開發了大量的程式將回復這件事情自動化處理:

在收到回復的需求後,可以看到 Peon 會從 HDFS 拉資料出來,並且用 binlog replay 回去:

Peons contain all relevant logic for retrieving backups from HDFS, loading them into their local MySQL instance, and rolling them forward to a certain point in time by replaying binlogs. Each restore job a peon works on goes through these five stages[.]

也是因為 Facebook 對 MySQL 的用量大到需要自動化這些事情,才有這些東西...

Percona 宣佈支援 MyRocks (MySQL 上的 RocksDB engine)

RocksDBFacebookGoogle 放出的 LevelDB 改出來,然後被更多人接受並且投注資源的 library... (看兩邊的 GitHub 應該就會有感覺了)

而 Facebook 的人在改進後又花了不少力氣 porting 到 MySQL 上...

之前 Twitter 上就有看到不少消息,這次算是在 Percona 官方的 blog 上正式公佈要支援 MyRocks 的消息:「Announcing MyRocks in Percona Server for MySQL」。

依照目前的計畫次在明年 2017 的 Q1 放出 experimental build,依照 Percona 的品質慣例,應該是可以拿來在測試環境下跑的順順的 (在還沒有 heavy loading 的前提下):

We will provide the experimental builds of MyRocks in Percona Server in Q1 2017, and we encourage you to start testing and experimenting so we can quickly release a solid GA version.

文章下面的 comment 剛好有人提到 Percona 另外一個產品線 TokuDB,這兩個產品線重複的問題:

MyRocks seems pretty similar to TokuDB. They are both write-optimized. MyRocks uses LSM tree while TokuDB uses fractal tree.

How do the 2 compare? Which one would you recommend using?

之前被 Percona 買下的 TokuDB 跟 Facebook 所發展出來的 MyRocks 的產品重複性頗高 (都是為了寫入的部分最佳化)。應該還是因為 fractal treeLSM tree 成熟度造成的效能差異還是太明顯吧 (當然另外也跟後面公司投入的資源有關),讓 Percona 決定還是要支援 MyRocks,而不是全力推動自家買下的 TokuDB... (唔,變成阿斗了?)

不知道成熟後有沒有機會變成 InnoDB replacement...

iOS 阻擋 WoSign 憑證

mozilla.dev.security.policy 上看到有人行動了:「Apple's response to the WoSign incidents」。這使得 Apple 成為 WoSign 事件中第一個行動的單位。

Apple 這次先把 WoSign 放入 iOS 憑證清單的黑名單公告在這邊:「Blocking Trust for WoSign CA Free SSL Certificate G2」。

WoSign 在 iOS 產品線中是靠 StartComComodo 的交叉簽章,所以如果 Apple 只想擋 WoSign 憑證的話,必須以阻擋 Intermediate CA 的方式避開:

Although no WoSign root is in the list of Apple trusted roots, this intermediate CA used cross-signed certificate relationships with StartCom and Comodo to establish trust on Apple products.

不過為了降低對 user 的影響,這次的阻擋會有例外。當 CT log server 在 2016-09-19 前收到的 SSL certificate 還是會信任 (要注意的重點是,這邊的日期不是簽發,是送到 CT log server 上):

To avoid disruption to existing WoSign certificate holders and to allow their transition to trusted roots, Apple products will trust individual existing certificates issued from this intermediate CA and published to public Certificate Transparency log servers by 2016-09-19.

接下來會開始更深入的調查 WoSign 與 StartCom:

As the investigation progresses, we will take further action on WoSign/StartCom trust anchors in Apple products as needed to protect users.

另外本來的棚子裡,Qihoo 360 與 StartCom 正式提出要求在 2016/10/04 與 Mozilla 的人面對面討論 (在英國):「WoSign and StartCom: next steps」:

Following the publication of the recent investigative report, representatives of Qihoo 360 and StartCom have requested a face-to-face meeting with Mozilla. We have accepted, and that meeting will take place next Tuesday in London.

繼續來看進度... 下個禮拜應該會有更多的資料出來。

Google Allo 減弱本來的安全設計

Google Allo 減弱了本來的安全設計:「Google backs off on previously announced Allo privacy feature」。

藉由修改預設行為減弱:

The version of Allo rolling out today will store all non-incognito messages by default — a clear change from Google’s earlier statements that the app would only store messages transiently and in non-identifiable form.

本來的預設值不會記錄身份,現在會了。而 The Verge 的猜設是這樣可以減少其他類似的情況,藉以討好政府:

That leaves Google with much less danger of the kind of legal showdown Apple faced in San Bernardino and WhatsApp currently faces in Brazil.

MySQL 全系列的安全性漏洞

包含 MySQL 本家與所有從 MySQL 改出去的分支都中了,引用 Percona 的通報:「Percona Server Critical Update CVE-2016-6662」。

This is a CRITICAL update, and the fix mitigates the potential for remote root code execution.

原始的 security advisory 在「CVE-2016-6662 - MySQL Remote Root Code Execution / Privilege Escalation ( 0day )」這邊,雖然是標 0day,但發現的人在七月時就有先通報給 vendor 們讓他們有時間修正:

The vulnerability was reported to Oracle on 29th of July 2016 and triaged by the security team. It was also reported to the other affected vendors including PerconaDB and MariaDB.

Oracle 還沒修正,也就是 upstream 目前仍然是有問題的,目前得靠其他 vendor 修正:

Official patches for the vulnerability are not available at this time for Oracle MySQL server.

其中 Percona 與 MariaDB 都已經先推出修正版本了:

The vulnerabilities were patched by PerconaDB and MariaDB vendors by the end of 30th of August.

然後看了一下這個漏洞,從 SQL 指令可以做檔案操作一路打出來... 可以看到範例:

mysql> set global general_log_file = '/etc/my.cnf';
mysql> set global general_log = on;
mysql> select '
    '> 
    '> ; injected config entry
    '> 
    '> [mysqld]
    '> malloc_lib=/tmp/mysql_exploit_lib.so
    '> 
    '> [separator]
    '> 
    '> ';
1 row in set (0.00 sec)
mysql> set global general_log = off;

這下苦了...