Tag: index

在 Production 上惡搞 PostgreSQL，建立一千萬筆 Index...

在「Running 10 Million PostgreSQL Indexes In Production (And Counting)」這邊看到 Heap 他們在 PostgreSQL 上惡搞的方式。

為了效能，所以在上面建立了大量的 Partial index。像是這樣的條件 (所以其實每個都不算太大)：

CREATE INDEX ON events (time) WHERE type = ‘click’ AND text = ‘login’

要注意的是 MySQL 的 Partial index 其實是 prefix index，用以減少 index 的空間要求，這在 VARCHAR(255) 以及 MD5 後的欄位還蠻有幫助的。而 PostgreSQL 的 Partial index 則是指條件式，兩者不太一樣。

不過他們還是有遇到「建立 index」這件事情的效能問題 (以及 workaround 的方式)。

不太常看到這樣惡搞，看到這篇的時候在電腦前笑的頗開心的 XDDD

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 應該還可以撐不少量起來。

iOS 上 Spotlight Search 如果很慢...

看到「Disabling Slack Indexing Seems to Improve Spotlight Performance on iOS」這篇講 iOS 的 Spotlight Search 很慢的問題，作者後來找到與 Slack 有關？到這邊關掉就正常了：

Settings > General > Spotlight Search > toggle Slack off

我是沒遇到... 不過先寫起來，如果以後遇到時應該會有幫助。

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

在「MySQL 8.0: When to use utf8mb3 over utf8mb4?」這邊提到了 MySQL 對 utf8 以及 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.

MySQL 8.0 將會實作「真正的」Descending Indexes

在「MySQL 8.0 Labs – Descending Indexes in MySQL」這邊看到 MySQL 打算在 8.0 時實作出真正的 Descending Indexes。在 5.7 以及之前的版本，可以從「14.1.14 CREATE INDEX Syntax」看到這個參數是～假～的～XDDD

An index_col_name specification can end with ASC or DESC. These keywords are permitted for future extensions for specifying ascending or descending index value storage. Currently, they are parsed but ignored; index values are always stored in ascending order.

所以當 8.0 建立了 a_desc_b_asc (a DESC, b ASC) 這樣的 index，可以看到對於不同 ORDER BY 時效能的差異：(一千萬筆資料)

有些變快可以理解，但有些結果不太清楚造成的原因...

Anyway，對於變慢的兩個 query，他提了一個不算解法的解法，就是加上對應的 index XDDD：

If user wants to avoid filesorts for Query 5 and Query 6, he/she can alter the table to add a key (a ASC, b ASC) . Further to this, if the user wants to avoid backward index scans too, he/she can add both ( a ASC, b DESC) and (a DESC, b DESC).

這樣就會變快，但寫入的 overhead 會增加啊... XD

但不管怎樣，總算是把這個功能生出來了...

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 找問題會很有幫助，可以在短時間翻出是哪個部份爆炸...

Percona 對 MySQL Index 效用的基本教學

Percona 好像偶而就會整理一篇類似的文章出來...

這次是對 MySQL 上線後，關於 Index 的設計研究：「Basic Housekeeping for MySQL Indexes」。

包括了這幾個主題：

  • Unused indexes
  • Duplicated indexes
  • Potentially missing indexes
  • Multiple column indexes order

這幾點都還蠻基本的，不過這些都做完了之後效能應該不會太差，算是上線後的觀測基本功...

最近討論 Uber 的 MySQL 換 PostgreSQL 後又換回 MySQL 的文章...

先把兩份連結丟出來，一份是 PyPgDay 2013 時由 Uber 的 Evan Klitzke 給的「Migrating Uber from MySQL to PostgreSQL」，原 PDF 連結已經失效 (看起來已經被刪除)，但這個網路年代什麼都可以找到備份... 可以在「Migrating Uber from MySQL to PostgreSQL」取得，但這個網站怪怪的，我另外丟了一份到 Google Docs 上。

另外一份則是同一個人 Evan Klitzke 在 2016 年發表於公司的官方網站上：「Why Uber Engineering Switched from Postgres to MySQL」。

2013 年描述了從 MySQL 換到 PostgreSQL，2016 年同一個人出來則描述了從 PostgreSQL 換到 MySQL 的理由，有種臉腫腫的感覺。

先抓 2013 年的重點，當時分享的目標是要用 PostGIS：

在 2016 年的文章絕口不提 PostGIS，而是提到各種效能問題：花了很長的篇幅講 Non-clustered Index 與 Clustered Index 的設計，以及 Replication 時的頻寬效能差異。

先不管 PostGIS，如果真的是 UPDATE 造成效能問題，那麼不是要朝 sharding 解決嗎，怎麼是換成 MySQL？換到 MySQL 後還是會遇到效能問題啊，你還是要在 application 層上面找出方案啊。

這篇文章看起來更像是內部技術與政治問題掛勾在一起談，因為政治原因而換 MySQL，然後找出技術原因說明換的理由 XDDD

Stack Overflow 做的 Developer Survey 2016

Stack Overflow 對開發者發問卷後把結果整理出來了：「Stack Overflow Developer Survey 2016 Results」，約 56k 個樣本數：

This year, 56,033 coders in 173 countries answered the call.

整個問卷分成五塊區域：Overview、Developer Profile、Technology、Work、Community，其中 Overview 的部份是給時間不多的人看的，整理了一些比較特別或是有趣的重點：

Most developers prefer dogs to cats. (But not developers in Germany.)

(唔？)

要注意的是，問卷只有英文版本，所以這份問卷明顯對於英文非母語的開發者會有比較低的填寫意願，會造成統計偏差問題，所以在讀之前要注意到：

Surveys aren’t perfect. While our large sample size helps offset some biases, it’s still biased against devs who don't speak English, or who don't like taking English-language surveys.

另外是有女性對這份問卷表示不滿：「Stack Overflow’s developer survey analysis hurts women」，尤其是 Stack Overflow 標示了只有 5.8% 的女性，這會導致女性樣本數在答案細分族群時的統計偏差的問題會很嚴重。

另外這篇文章的作者也對 Stack Overflow 裡的結論很不滿意。

回到原來文章，有些東西還蠻有趣的：

其中 Salary 這段應該是很多人都有動力去讀一讀了解的，裡面還包括了各地區與麥當勞的大麥克指數的相對數值分析，讓你有個參考值可以感覺。

MySQL 5.7 的 JSON、Virtual Column 以及 Index

Percona 提到了 MySQL 5.7 的 JSON 與 virtual column，再加上 index 後的效能提昇：「JSON document fast lookup with MySQL 5.7」。

每一家都把這些功能給做出來了，在 MySQL 5.7 提供了 JSON 格式：

CREATE TABLE `test_features` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `feature` json NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

而你可以對 JSON 欄位運算，拉出資料後產生出 virtual column：

ALTER TABLE test_features ADD COLUMN street VARCHAR(30) GENERATED ALWAYS AS (json_unquote(json_extract(`feature`,'$.properties.STREET'))) VIRTUAL;

然後就可以對 virtual column 下 index：

ALTER TABLE test_features ADD KEY `street` (`street`);

接著對 virtual column 查詢的速度就會超快：

SELECT count(*) FROM test_features WHERE street = 'BEACH';

其實不一定要 JSON，光是 virtual column 與 index 就可以解決老問題：

已經有使用者所在的國家，想要快速查詢使用者是住在哪個洲 (亞洲、歐洲、美洲、...)。

以前是另外拆出一個欄位來做，用 trigger 更新確保資料正確性後，再對拆出來的欄位下 index。現在可以用 virtual column 建立出來下 index。