MySQL 總算要拔掉 mysql_query_cache 了

半官方的 MySQL blog 上宣佈了拔掉 mysql_query_cache 的計畫:「MySQL 8.0: Retiring Support for the Query Cache」。

作者開頭引用了 ProxySQL 的人對 MySQL Query Cache 的說明:

Although MySQL Query Cache was meant to improve performance, it has serious scalability issues and it can easily become a severe bottleneck.

主要問題在於 MySQL Query Cache 在多 CPU 環境下很難 scale,很容易造成一堆 thread 在搶 lock。而且作者也同意 ProxySQL 的說法,將 cache 放到 client 的效能比較好:

We also agree with Rene’s conclusion, that caching provides the greatest benefit when it is moved closer to the client:

可以看到 Query Cache 在複雜的環境下對效能極傷。而之前也提到過類似的事情了:「Percona 對 mysql_query_cache 的測試 (以 Magento 為例)」、「關閉 MySQL 的 Query Cache」。

一般如果要 cache 的話,透過 InnoDB 裡良好的 index 應該還可以撐不少量起來。

透過 DNS TXT 傳遞指令的惡意程式

看到「New Fileless Malware Uses DNS Queries To Receive PowerShell Commands」這篇,所以是有人開始這樣惡搞了...

Distributed through an email phishing campaign, the DNSMessenger attack is completely Fileless, as it does not involve writing files to the targeted system; instead, it uses DNS TXT messaging capabilities to fetch malicious PowerShell commands stored remotely as DNS TXT records.

利用 DNS 的穿透力來取得外部資訊...

Swap 對 InnoDB 的影響

Percona 的老大拿 5.7 版做實驗,確認 swap 對 InnoDB 的影響:「The Impact of Swapping on MySQL Performance」。

測試的機器是 32GB RAM,作業系統 (以及 swap) 裝在已經有點年紀的 Intel 520 SSD 上,而 MySQL 則是裝在 Intel 750 NVMe 上。透過對 innodb_buffer_pool 的調整來看情況。

可以看到設為 24GB (記憶體 75% 的量) 時很穩定的在 44K QPS 與 3.5ms (95%):

This gives us about 44K QPS. The 95% query response time (reported by sysbench) is about 3.5ms.

而當設成 32GB 的時候開始可以觀察到 swap i/o,掉到 20K QPS 與 9ms (95%):

We can see that performance stabilizes after a bit at around 20K QPS, with some 380MB/sec disk IO and 125MB/sec swap IO. The 95% query response time has grown to around 9ms.

當拉到 48GB 的時候就更掉更多,6K QPS 與 35ms (95%):

Now we have around 6K QPS. Disk IO has dropped to 250MB/sec, and swap IO is up to 190MB/sec. The 95% query response time is around 35ms.

作者發現掉的比率沒有想像中大:

When I started, I expected severe performance drop even with very minor swapping. I surprised myself by getting swap activity to more than 100MB/sec, with performance “only” halved.

這邊測試用的是 SSD,如果是傳統用磁頭的硬碟,對 random access 應該會很敏感而掉更多:

This assumes your swap space is on an SSD, of course! SSDs handle random IO (which is what paging activity usually is) much better than HDDs.

基本上還是要避免碰到 swap 啦,另外 comment 的地方剛好有提到前陣子在猜測的 best practice,測試時的 vm.swappiness 是設成 1,這應該是作者的 best practice:

Swappiness was set to 1 in this case. I was not expecting this to cause significant impact as swapping is caused by genuine (intended) missconfiguration with more memory required than available.

Amazon Aurora 支援 Auditing

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

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

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

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

Amazon Athena:直接在 S3 上進行分析

Amazon Athena 提供另外一種選擇,讓分析的便利性增加了許多:「Amazon Athena – Interactive SQL Queries for Data in Amazon S3」。

以往都需要開 server 起來分析,這個新的服務直接使用就好:

Athena is based on the Presto distributed SQL engine and can query data in many different formats including JSON, CSV, log files, text with custom delimiters, Apache Parquet, and Apache ORC.

果然是用 Presto 改出來的... XDDD

指定好各種資料來源之後直接下 SQL query 分析,然後依照分析的量來算錢... 而 FAQ 的地方也有提到可以透過 JDBC 接上去,這樣看起來跑報表的場合直接丟給他處理了:

Amazon Athena can be accessed via the AWS management console and a JDBC driver. You can programmatically run queries, add tables or partitions using the JDBC driver.

隔壁 Amazon Redshift 的立場變得很尷尬啊,Amazon Athena 不需要養機器而且又可以直接從 Amazon S3 拉資料,如果之後把 Presto 對 RDBMS 的部分再補上來的話就更棒了... (應該是下一階段的任務,把 RDS 補上)

關閉 MySQL 的 Query Cache

MySQL 的 Query Cache 是目前已知效能不好的主要因素之一 (global mutex lock 的緣故),在正式環境裡的 best practice 一般都是關閉,之前測過也是一開下去效能就會狂掉...

Percona 的人在討論要怎麼樣才能完全關閉 MySQL 的 Query Cache:「Is Your Query Cache Really Disabled?」,而他們發現只有在 query_cache_typequery_cache_size 都設為 0,而且重開 MySQL 才能完全避免 global mutex lock:

[W]e can see it is not possible to fully disable the query cache on the fly by changing query_cache_type or/and query_cache_size to 0. Based on the code and the tests, if you want to make sure the query cache is fully disabled, change query_cache_size and query_cache_type to 0 and restart MySQL.

應該是要再修正 my.cnf 的 template 了...

Route53 也支援 IPv6 了...

Amazon Route 53 也宣佈支援 IPv6 了:「Amazon Route 53 Now Supports DNS Queries over IPv6 Networks」。

依照說明應該是無痛切換過去:

The change is seamless and requires no action from you; your end users and clients can begin making DNS queries over IPv6 immediately.

不過測了 heroku.com 的 NS RR (拿 ns-405.awsdns-50.com 測試),還是只有 A record 啊?另外測了其他幾個也是 (反而沒找到已經切過去的?),不知道是不是分批切換...

MySQL 8.0 的 performance_schema 加上 index 了...

MySQL 8.0 是 MySQL 5.7 的後續版本,中間的 6.0 與 7.0 都有一些故事,就被跳過去了,跟 PHP 的情況有點像。

在 8.0 版將會把 performance_schamea 加上 index,讓查詢的速度變快:「MySQL 8.0: Performance Schema, now with indexes!」:

In MySQL 8.0, performance_schema tables are now indexed to speed up data retrieval.

A total of 115 indexes have been added in the performance schema in MySQL 8.0.0, to support better data access patterns in general.

有用過 performance_schema 的人都會有種「這好慢啊」的感覺,總算要改善了... 而且這幾乎是沒什麼成本的改善:

Question: How much overhead was just added by this new feature?
Answer: Absolutely zero

並不是用 index 加快速度,而是加了一些資訊,修正 optimizer 的行為:

It does — not — maintain a physical index internally, be it on file or memory.
It does, however, — pretend — to the optimizer that it has indexes, so that the optimizer is coerced into using the most efficient access pattern.

在有些情況下可以看到會快非常的多:

The performance improvements from indexes can be very easily seen in many of the sys schema queries. With 1000 idle threads, the query SELECT * FROM sys.session drops from 34.70 seconds down to 1.01 seconds (a 30x improvement!):

不知道 Percona 會不會 backport 回來,這看起來對於爆炸中的 server 找問題會很有幫助,可以在短時間翻出是哪個部份爆炸...

CloudFront 支援將 Query String 內的特定 Key/Value 當作 Cache Key 的一部分

Amazon CloudFront 可以指定 query string 中的某個特定的 key/value 當做 cache key 的一部分了:「Announcing Query String Whitelisting for Amazon CloudFront」,對應的文件在「Configuring CloudFront to Cache Based on Query String Parameters」這邊可以查到。

先前只能針對選擇忽略掉整個 query string,或是把整個 query string 當作 cache key 的一部分,現在可以細部調整了。

最簡單的應用可以用在 css/js 的 asset 上,針對 v=\d+ 當作 cache key 的一部分,而其他的參數可以忽略,不過這好像沒什麼特別的意義。

目前想到比較有意義的應用是針對 dynamic content 多了一些籌碼可以用,像是 Slack 把整個網站放上 CloudFront 後,應該會有很多 API 是透過 query string 傳遞參數,而這次的改變讓 CloudFront 可以細部調整。

MySQL 5.7 的 Rewrite Query Plugin

在「What to do with optimizer hints after an upgrade?」這邊介紹了 MySQL 5.7 引入的 Rewrite Query Plugin,看起來有很多可以拿來變化的?

作者提到的用法是當 minor version 升級後 (譬如 5.6 升到 5.7),由於 optimizer 愈來愈聰明,hint 應該都要重新確認是否還需要指定 (像是 USING INDEX),避免效能反而變差。

但這個前提是你能夠改到程式碼,如果你改不到程式碼就只能祈禱效能不會變差。

而 MySQL 5.7 提供的 Rewrite Query Plugin 則可以改寫 SQL query,像是 Oracle 官方文件裡給的範例:

mysql> SELECT * FROM query_rewrite.rewrite_rules\G
*************************** 1. row ***************************
                id: 1
           pattern: SELECT ?
  pattern_database: NULL
       replacement: SELECT ? + 1
           enabled: YES
           message: NULL
    pattern_digest: 46b876e64cd5c41009d91c754921f1d4
normalized_pattern: select ?

就會把 SELECT 1 變成 SELECT 1 + 1。實際測試會發現檢查的很嚴格,用 PI() 不會變:

mysql> SELECT PI();
+----------+
| PI()     |
+----------+
| 3.141593 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT 10;
+--------+
| 10 + 1 |
+--------+
|     11 |
+--------+
1 row in set, 1 warning (0.00 sec)

目前好像只想的到 hint 可以這樣做,反正還一堆都跑 5.6 (這兩天 Percona 才出 5.7 的 GA),可以邊規劃升級,邊想看看有什麼情境可以用的...