Index 下的不好會對 MySQL 的 Index Merge 產生負面效果...

前陣子在看資料庫寫入會卡住時找到的問題,主要是 SELECT SQL query 跑太久造成 InnoDB lock issue。

這個問題可以朝幾個不同方向解決,其中一個方向是升級到 MySQL 5.6,對 read-only transaction 與 index merge 的判斷與效能都有改善。

另外一個方向是改善 SQL query 本身效能,讓他不要跑太久就不會有 InnoDB lock issue。這篇要討論的就是這個方法...

MySQL 在 5.0 開始就支援 Index Merge Optimization,也就是利用多個 index 的結果運算:「Index Merge Optimization」。

有三種 index merge optimization,可以在 EXPLAIN 時看到:

  • Using intersect(...)
  • Using union(...)
  • Using sort_union(...)

其中 intersection (交集) 的部份,Percona 在兩年前有寫一篇關於 Index Merge Intersection 反而會使得效能變差的文章:「The Optimization That (Often) Isn't: Index Merge Intersection」。

以歌曲的例子來說,假設有一個表格叫做 song,裡面有千萬首歌曲的資料:

+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| song_id    | int(10)    | NO   | PRI | NULL    | auto_increment |
| album_id   | int(10)    | NO   | MUL | 0       |                |
| artist_id  | int(10)    | NO   | MUL | 0       |                |
| song_valid | tinyint(1) | NO   | MUL | 0       |                |
+------------+------------+------+-----+---------+----------------+

其中 album_id 自己一個 index,artist_id 自己也一個 index,song_valid 自己也一個 index。當下這樣的 query 時就有可能會出現 index merge:

SELECT * FROM song WHERE artist_id = 10000 AND song_valid = 1;

其中 song_valid = 1 可能會佔 song 表格裡 90% 的資料,也就是 900 萬筆...

如果發生 index merge,MySQL 會先抓出 artist_id = 10000 的 song_id,以及 song_valid = 1 的 song_id 取交集,會造成大量的 seq i/o read。即使這些資料都在記憶體 cache 內,還是需要 CPU 大量運算。用 force index 的方式強迫 MySQL 直接用 artist_id 的 index 抓出來掃會快很多。

不過仔細去想,會發現有幾個解法:

  • 應該對 (artist_id, song_valid) 建立 index,而非對 artist_id 單一欄位建立。這樣上面的 query 就會直接用到這個 index。
  • 或是,由於 90% 的資料都是 song_valid = 1,而我們大多數也都是查 song_valid = 1,就直接把 song_valid 的 index 拔掉。

我是選擇後面這條,這是 index 愈多反而變得愈慢的 case 之一。

幾個程式語言對引號以及 backslash 解讀...

三個 P 開頭的語言...

PHP

#!/usr/bin/env php
<?php
 
echo "\\\"\'", "\n";
echo '\\\"\'', "\n";

Perl

#!/usr/bin/env perl
 
use 5.010;
use strict;
use warnings;
 
INIT {
    $a = "\\\"\'";
    $b = '\\\"\'';
 
    say $a;
    say $b;
}
 
__END__

Python

#!/usr/bin/env python
 
print "\\\"\'"
print '\\\"\''

結果是:

$ ./quote.php
\"\'
\\"'

$ ./quote.pl
\"'
\\"'

$ ./quote.py
\"'
\"'

這...

用 nginx + FastCGI 接 HHVM...

看到「HHVM, Nginx and Laravel」這篇文章,加上 Rackspace 香港的機器用起來很愉快,就開一台起來測試...

文章內的方法都 okay,只是在裝 HHVM 前少了一步把 key 加到 apt 內的指令:

apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 5A16E7281BE7A449

補上去以後 apt-get update 才會動 :p

速度上當然是沒話說,而且使用的習慣很接近 drop-in replacement 了... (更新檔案後不需要自己另外 purge cache,HHVM 會自己偵測到)

晚點來測試看看 ApacheFastCGI 接 HHVM,如果可行的話就更好了?(.htaccess 啊...)

Rackspace 的雲端服務...

很久前有註冊 Rackspace 帳號,剛剛無意間登入進去發現其實還蠻有競爭力?

有香港伺服器,開了一台起來測試發現從台灣過去的速度都不錯... 而且反應速度比 AWSWeb Console 好太多。

一開起來有 IPv4 與 IPv6 對外,可以另外自己建立 private network (類似 AWS 的 VPC),一樣有 block stoage (類似 AWS 的 EBS)。

不過透過 Web Console 開機器是給隨機的 root password,而不是 ssh key 登入,這就有點不太一樣了。

雖然是第二大,但整個順暢度很好啊... 還是頗值得玩看看到底如何 :o

Netflix 對 S3 的 Eventually Consistency 的補強...

眾所皆知的,Netflix 幾乎將所有服務都放在 AWS 上,這當然也包括了 Amazon S3

在 Amazon S3 上會有 Eventually Consistency 的問題:寫入後可能會讀到舊的資料,於是就算錯資料了...

Netflix 的人討論了幾種方案,後來開發 s3mper 用來解決 Amazon S3 的 Eventually Consistency 問題:「S3mper: Consistency in the Cloud」。

s3mper 透過 AWS DynamoDB 儲存檔案的 metadata,藉以得知是否 consistency。而 Amazon DynamoDB 本身雖然也是 Eventually Consistency,但多了 API 可以得知是否 Consistency。

Supported Operations in DynamoDB 可以看到 Data Read and Consistency Considerations 這段提供了兩種 read mode:

  • Eventually Consistent Reads
  • Strongly Consistent Reads

在 Strongly Consistent Reads 中,可以確認讀到的是不是最新的資料。只有當 DynamoDB 與 S3 的資料都正確時才繼續往下跑...

這個解法相當於在 Amazon S3 上面架了一層防護網,算是 workaround 吧 :p 如果 Amazon S3 可以提供 consistency 資訊的話,也就不用這樣搞了...

虛擬環境下檔案系統的讀寫速度...

有人對 VMware 以及 VirtualBox 測試效能:「Comparing Filesystem Performance in Virtual Machines」,有四張圖:

不過數字怪怪的,NFS 那邊是怎樣 XDDD

看看就好,如果有用到再自己測試看看吧?