盡量避免使用 BLOB 和 TEXT

剛好看到朋友在討論, 順便記一下舊心得。

初學 mysql 時容易犯的一個錯誤就是亂用 data type, 明明沒有需要很大的空間, 只是方便就選最大的那個。一但選用 BLOB 或 TEXT 後, mysql 許多執行 SQL 的策略會不同:

• mysql 不會選用 modified filesort, 速度有可能會慢很多。注意: group by 也會用到 sort。
• mysql 不會做 query cache。

別小看 query cache, 它是 mysql 有高效能表現的原因之一。想像完全不懂 database 也沒建 index 的開發者, 若網站大部份需求是讀資料, 只要開夠大的 query cache, 事先用程式掃一掃網站 warm up 一下, 之後 99% 使用者連到網站時, 網頁所用的 SQL 都會從 query cache 裡拿, 連 SQL parsing 都不用, 等同於將 mysql 當作 in-memory key/value store (key = sql), 亂用都還有不錯的效能。

MySQL vs. PostgreSQL

去年的隨手心得, 記在這備忘。主要是參考《MySQL vs PostgreSQL - WikiVS》 2010-09 的版本, 後來情況應該也有些變化。另外, 很多東西還是要自己測過才準, 後來和一些很熟 DB 的前輩聊, 發覺實際情況還是和看到的有些出入。需求不同, 又有太多細節可以調整了。

後來覺得選用 MySQL 的好處是:

• 資源最豐富, 有書有文件, 也有一堆顧問公司 (如 Percona)
• 有許多大公司實際執行到海量的實例, 不用擔心會流量會卡住 (雖說大部份時候應該要先擔心服務不夠好, 流量太小)
• 有些 syntax sugar 或特制功能很好用, 像是 insert ... on duplicate update , 或 insert ignore
• 有趣的是, 該文認為 MySQL 比 PostgreSQL 流行的原因之一, 是因為 PostgreSQL 沒法限制各個 database 的大小, 對於商用服務來說, 這點很重要 (商用服務會在意限制資源、管理權限等, open source 專案先以個人需求為主, 一開始大概都不會重視這些吧)

備忘

fcamel 2010-09-01 19:54:43
MySQL vs. PostgreSQL, 有這種對照文真不錯, 書上有看過的相關觀念可以重新消化一次, 之前沒看過的....在這也有看沒感覺
MySQL vs PostgreSQL - WikiVS
MySQL vs PostgreSQLFrom WikiVS, the open comparison websiteJump to: navigation, search MySQL Postgr
發表回應
1

fcamel 2010-09-01 19:55:43
PostgreSQL 只有一個 engine, 一般來說在大量同時寫入下會比 MySQL 搭各種 engine 快
2

fcamel 2010-09-01 19:56:36
PostgreSQL 比較穩, RDBMS 功能較齊全, 這點在 stackoverflow 上可找出一卡車支持者。也可找出一卡車 MySQL 使用者說他們遇到 DB 炸了多少次
3

fcamel 2010-09-01 19:57:24
MySQL + MyISAM 有無敵快的 count, 因為它沒 MVCC, 可以視情況直接 count index 而不用取 data rows。又因為沒 MVCC, 可以安心地 cache count
4

fcamel 2010-09-01 19:58:58
8.x 的 PostgreSQL 沒有 Insert Ignore / Replace
5

fcamel 2010-09-01 20:00:31
MySQL + MyISAM 在 read-only 或低頻率寫入的情況下是超級快, 因為它沒 constraint, 沒 MVCC。無賴戰法: 我就什麼功能都沒有, 但是只讀不寫的話, 我超快的哦!!
6

fcamel 2010-09-01 20:00:49
剛好我們現在正好只需要這種無賴戰法 XD
7

fcamel 2010-09-01 20:06:12
mysql 內建 replication 機制, 效率比 PostgreSQL 好 (?)
8

fcamel 2010-09-01 20:07:41
Ubuntu 8.04 的 mysql 版本有點舊, 只到 5.0.51a, 之後有幾版大幅提昇速度, 5.1 又有多 sharding 的樣子。
9

fcamel 2010-09-01 20:10:56
PostgrelSQL 有 Bitmap Indexes, 可以合併兩個以上的 indexes, 之後來看看這是啥。一直很好奇怎麼做才能同時用兩個 indexes
10

fcamel 2010-09-01 20:13:45
MySQL 有 covering index, 而 PostgrelSQL 沒有, 令我滿意外的, 原以為 index 那區 MySQL 會兵敗如山倒
11

fcamel 2010-09-01 20:15:14
PostgreSQL 支援 expression indexes, 意思是也可以查 "%keyword%" 嗎? 太神奇了! 之後來查查這是啥
12

fcamel 2010-09-01 20:19:32
這個有意思: "In PostgreSQL there is no built-in mechanism for limiting database size. This is the main reason why the most of the web hosting companies are using MySQL. Also, PostgreSQL doesn't have scheduled procedures. "
13

fcamel 2010-09-01 20:20:32
結果 PostgreSQL 無法流行的主因是沒做這種看似不重要的功能嗎? XD
14

fcamel 2010-09-01 20:23:40
天啊...., 難怪有人提到他比較喜歡 PostgreSQL 的 license, MySQL client library 是 GPL 非 LGPL!! 若沒和 Sun (現在是 Oracle?) 買授權, 就得 open source, 太可怕了...
15

fcamel 2010-09-01 20:30:10
看完兩者的討論後還真想用 PostgreSQL 啊, 考量現況還是繼續用 MySQL 比較務實 Orz
16

fcamel 2010-09-01 20:41:29
stackoverflow 有人提到用 PostgreSQL 加 column 似乎不用鎖住 table, 他們之前用 mysql + replication 的結果, 就不敢加 column, 只好另開 table 用 join 取資料
17

榮尼王 2010-09-01 20:53:54
mysql 加上 master-master 架構的話，alter table 就隨便了啊
18

fcamel 2010-09-01 21:04:56
soga, 還沒看深, 果然各種問題都有一定的配套解法, 謝啦。
 回應

讓 mysql 使用 utf-8 的相關設定

這對於新手來說應該很有幫助吧

在 [mysqld] 下的相關設定:

character-set-server=utf8
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'

MySQL 的設定檔位置不太好找, 細節參考《fcamel 技術隨手記: 更改 mysql conf 的注意事項》。

warm up server 的一般作法

一開始用 MySQL 的 MyISAM 時, 發覺有語法可以事先載入整個 index, 就以為大部份 server 應該有提供這功能。不過後來發覺 InnoDB 沒有, 所以要自己下 SELECT 掃一次 table, 讓 MySQL 自動觸發 cache 機制。後來用 Solr, 查了一陣子, 似乎也沒有這種機制。大概設計 server 的人覺得另外設計語法會增加維護成本, 不如讓大家自己查詢全部要用的資料, 讓 server 自己管好 cache 比較實際吧。

mysql flush key cache

MyISAM 的 index 會暫存到 key cache 裡, 若想預先載入整個 table 的 indexes 到 key cache 裡, 可以用 LOAD INDEX INTO CACHE。但若要 flush key cache 的話, 並沒有像 flush query cache 那樣有 RESET QUERY CACHE 的語法。替代作法是, 改變 key cache 的大小或 block size, MySQL 就會重設 key cache。

小技巧: 用看不見的字元分隔資料

處理文字時, 偶而會需要將類似的東西串在資料庫表格的一個欄位裡或文字檔的一行文字裡。為了避免分隔符號和別的文字衝到, 從同事 P 那學到一個不錯的小技巧: 用看不見的字元分隔, 比方說 unicode 的 1 ~ 5 等。

用 Python 寫就是 "\u0001", 用 mysql 則是 char(1), VIM 會用 ^A 表示 (\u0002 則用 ^B), 最妙的是, Firefox 會用一個小方格在裡面填入 0001 表示它, 所以寫到資料庫後, 用 phpMyAdmin 看也不會有問題, 真是太貼心了。通常為了方便, 可能會用 "\u0001\n" 表示, 這樣在 phpMyAdmin 裡看時, 也會換行表示。

其它應用方式, 像是填入字串表示未定義時, 可以用 "\u0001UNDEFINED", 避免單用 "UNDEFINED" 可能會和這樣的內文衝突。而要用 mysql 取值時, 可配合 concat 表示: concat(char(1), "UNDEFINED")。

MySQL 交集的寫法和效率

之前在這篇提到用 union all + group by + having 的方式寫交集, 當時沒測效率。最近剛好有個簡單例子, 比了一下效率。我要從 T1、T2 兩個表格取出欄位 F, 兩個表格各自的資料有重覆的 F, 我想找出 T1、T2 的  F 的共通值。

寫法如下:

1. SELECT DISTINCT F FROM T1 where F in (SELECT DISTINCT F FROM T2)
2. SELECT F, COUNT(*) FROM (SELECT DISTINCT F FROM T1 UNION ALL SELECT DISTINCT F FROM T2) AS t GROUP BY F HAVING COUNT(*) = 2;

T1、T2 各有數萬筆資料。測的結果是第二個寫法瞬殺, 第一個寫法等個十秒沒結果, 我就懶得等了。

原因出在第一種寫法的 sub-query 被當作 dependency query 來處理, 變成每一筆資料都來個 sub-query。不過 MySQL 會有 query cache, 照理說不會慢到這種程度才對, 有機會再去了解詳細運作過程吧。至少藉這機會確定了用 union all 的寫法較好。

(mysql) 透過建 index 加速 group by

剛才遇到一個情況, 下 SQL 配合一些 where condition 過濾資料 (有 range query), 再 group by GROUP_COLUMN。資料量有些多, 但也沒多到會塞爆記憶體, 於是用 MEMORY engine, 全存在記憶體方便後續處理。

一開始試著對 where condition 用到的欄位建 index, 結果不夠快。看 profiling 的結果, 時間花在 "Copying to tmp table"。轉念一想, 若先建 index 排好 GROUP_COLUMN, 就能確保 mysql 只做一次 table scan, 別搬資料到 tmp table, 應該可以省下不少時間。於是對 GROUP_COLUMN 和 where condition 中用到的欄位建 covering index, 讓 GROUP_COLUMN 排第一個, 確保 mysql 不需用到 filesort 和 tmp table。

這個作法的特點在於, 不管 where condition 為何, 都是一次 table scan, 不用擔心不同輸入資料造成的變化, 可明確測出 worst case 的速度。實測結果, 果然快上不少, 到可以接受的速度。了解 mysql 運作方式後, 做這類設計並配合實測, 順手不少。

btw, 之前也遇過相反的情況, 不見得這樣做較好。招式是活的, 重點還是要看需求 (如是否看重 worst case, 以及 worst case 發生頻率, worst case 有無其它配套解法), 跑 profiling 實測, 再選適合的作法。

MySQL table cache 和 open table 的效能影響

最近同事跑程式時, 發現做大量 join update 時, 偶而會很慢, 但是 flush table 後就恢復正常。依官網所言, flush tables 做兩件事

• 清 query cache (等同於 reset query cache)
• 關掉所有打開的 table

經同事測試, 執行 reset query cache 無效, 剩下的原因只剩關 table。這讓我百思不得其解, 不就是開關檔, 怎麼會有效能差異? 後來經 Tib 提醒, 用 "show global status like '%open%'" 一看, 發覺相當地不得了。table_cache 預設值是 64, 開啟 MySQL server 至今 (應該不到一個月), 竟然開了一百多萬次 table, hit rate 是 0%  (可以用 mysqltuner.pl 摘要 show status 的結果)。

研究一下官網的 How MySQL Opens and Closes Tables 後 (下面留言也很有幫助), 得到以下的心得:

• 當 table_cache 沒東西或新增 temporary table 時, 會增加 Opened_tables, 這個變數存的是 MySQL server 開啟至今, 開啟 table 的次數。
• show tables 或操作 information_schema 會一口氣開一堆檔案, 若用到多個 database, 大家都來個幾下 show tables, 很快就會塞爆 table_cache, 所以不能設太小
• 但若 table_cache 大於等於 open_files_limit, MySQL 執行一會兒後會停住。查看 /var/log/syslog 後發現 errno 24, 結果是超出能開檔的數量限制, 讓 MySQL 無法做事, 因為 table_cache 占掉所有能開檔的數量, 結果要再存取別的 table 時, 卻無法再開檔了。將 open_files_limit 設得比 table_cache 大, 就沒事了
• table_cache 也不能設太大, 照這篇的說法, cache 滿的時候, 找 expired record 會很花時間

調大 table_cache 後, 程式有順利跑完, 但原本就是偶而會發生的事, 所以還要再多觀察一陣子, 才能確定是否有解掉問題。下次再發生時, 再來觀察 status 裡和開關 table 有關的記錄, 確定是否有關。

btw, Set ulimit parameters on ubuntu 說明如何改變 ulimit 的限制。照 MySQL 官網的說法, 改 my.cnf 應該能提高 open_files_limit, 若不行的話, 再來試看看改變 Ubuntu ulimit 開檔的預設值 (1024)。不過那篇文章提到要先設定 PAM 再重開機, server 不方便隨便重開測試, 先備忘吧。

2011-04-09 更新

試過改 my.cnf 無效, 改成修改 /etc/init.d/mysql , 將 /usr/bin/mysqld_safe 加上 --open-files-limit=N 後, 就 OK 了。

MySQL 交集的寫法

一直以為自己寫過這個記錄, 今天來這裡查卻找不到, 真是太神祕了。

參照這篇的作法, 用 UNION ALL + GROUP BY + HAVING COUNT(*) = 2 就可以做到交集的效果。

SELECT * FROM (
             SELECT DISTINCT col1 FROM t1 WHERE...
             UNION ALL
             SELECT DISTINCT col1 FROM t1 WHERE...
) AS tbl
GROUP BY tbl.col1
HAVING COUNT(*) = 2

附帶一提, 聯集自然是用 UNION, 差集可以用 sub query

SELECT * FROM T1 WHERE ... AND id NOT IN (SELECT id FROM T2 WHERE  ...)

MySQL 大量寫入的加速技巧 (bulk insert)

官網 Speed of INSERT Statements 詳細地解釋各種加速方式和它背後做的運算。結合個人經驗, 在這裡摘要相關訊息:

• INSERT 時會依序做 connect、send query、parse query、insert record、update index。雖然可以用 prepared statement 減少 parse query 的時間, 最耗時間的應該還是 update index, 省時的關鍵在於如何減少 update index。
• 有兩個方向可以達到這個目的: 減少 insert 次數或是減少 update index 次數。

最佳解: 一次搞定

• LOAD DATA INFILE 最快。這不難理解, 但我沒試過就是了。
• INSERT ... SELECT ... 也是好方法, 類似 LOAD DATA INFILE, 只是資料來源是 MySQL 本身。讓 MySQL 自己一次搞定取資料和寫資料的操作, MySQL 有最多的彈性決定處理資料的順序。

次佳解: 減少 insert 次數

• INSERT ... VALUES ... (ref: 在該頁搜 values): 一次寫入多筆資料自然能減少 update index 次數, 不過資料太多時可能會超出 SQL 長度限制, 看到相關錯誤訊息時, 記得改 my.cnf 調高長度。我自己覺得一次寫過多筆應該會出問題 (單次操作用過大的記憶體, 不會是好事), 通常都一次寫入個一萬筆或十萬筆。實測的感覺兩者沒差太多, 百筆以下會比較慢。

次佳解: 減少 update index 次數

• LOCK TABLES 後, MySQL 就不會急著 update index, 而會等 UNLOCK TABLES 後再一次更新 index。若不在意其它 thread 會用到這個 table, 用 LOCK TABLES 可在多次 insert 的情況下省下不少 update index 的時間。
• DISABLE KEYS 和 ENABLE KEYS (ref: 在該頁搜這兩個 keyword): 這是 MyISAM 才有的功能, 並且只能用在 non-unique index, 因為 insert 時需要檢查 unique constraint, 不能暫時關掉。實測後效果並不好, 雖然 insert 時有省下時間, 但是連同 enable keys 時重建 index 的時間, 整體來說反而變慢。

其它相關的選擇

• INSERT ... ON DUPLICATE KEY UPDATE: 這個語法相當威, 寫入資料, 發覺違反 unique constraint 後, 再執行指定的更新操作 (合併資料) 。批次寫入常會遇到一些小狀況, 用這招省了不少事。若改拆成批次 insert 和批次 update (用 join update), 相對來說會慢上不少。
• 先 drop unique constraint、drop indexes 再寫入, 寫完再重建 unique constraint、index: 這方法反而是最慢的。問題出在 MyISAM 在修改 schema 時會重建 data file 或 index file, 若有三個 index, 寫完資料後下三次 CREATE INDEX 的 SQL, 結果 MySQL 會重建三次 index file, 每次都會讀出全部資料, 加好新 index, 再寫入新的檔案, 移掉舊檔。附帶一提, 上面的 DISABLE KEYS 和 ENABLE KEYS 比這個作法快, 因為 ENABLE KEYS 後是一次重建全部 non-unique indexes, 不是有幾個 non-unique indexes 就重建幾次檔案。

多個 client 同時寫入的其它選擇

• INSERT DELAYED 讓 client 送完資料立即結束操作, 可提高各 client 減少的時間, 也能集中寫入資料, 一次寫入到硬碟。但是, 整體來說 overhead 較高, 若是單一 client 寫入的情況下, 反而會更慢。我沒有實際用過。

最後, 遇到效能問題時, 一定要在自己的環境實測才準, 了解概念和實測同等重要。

MySQL 將資料塞到 memory 的作法

要加快取資料的速度, 沒有別的方法, 不是先將常用的資料塞到記憶體裡等待查詢, 就是準備好 cache 並先查詢常用的 query, 將結果存在 cache 裡 (俗稱 warm up), 之後再查就會快了。query cache 是 MySQL 能如此快速的原因之一, MySQL 執行 query 前會先查 query cache, SQL 內容一字不差的話, 就直接取值來用。

事先將資料塞入記憶體的作法有幾種, 目前最常用的兩招是:

• 資料不大的話, 就開個 table 用 MEMORY engine 存, 記得將 max_heap_table_size 設大一點。若嫌 MySQL 重開後要重填資料很麻煩, 可以先將資料存在 my_table_on_disk, 照以下步驟產生資料, 三個 SQL 而已:
• CREATE TABLE my_table_in_memory LIKE my_table_on_disk;
• ALTER TABLE my_table_in_memory ENGINE = MEMORY;
• INSERT INTO my_table_in_memory SELECT * FROM my_table;
• 若資料很大或是某些欄位無法存到記憶體裡, 就改對常用的幾個欄位建 covering index。若 engine 能用 MyISAM 的話, 就能用「LOAD INDEX INTO CACHE my_table_on_disk」事先載入 index 到記憶體裡。

使用 MEMORY engine 的注意事項:

• 注意 varchar / char 的長度限制, MEMORY engine 沒有 varchar, 會自動轉成 char, 沒設好可是很揮霍的。
• 用 MEMORY engine 時, 沒指定 index type 的話, 預設用 HASH 而不是 B-Tree。用 = 或 IN 查詢時應該會比用 B-TREE 快, 不過重點是用 HASH 比較省空間, 不管欄位大小為何, hash 後都是一樣大的。但是 HASH 不支援 range query。

用 covering index 的注意事項:

• load index into cache 不是永久性的, 資料有可能被其它 table 的 index 擠走。在意的話, 最好設多個不同的 key cache, 將不希望被擠出 key cache 的 index 放入獨自的 key cache。

其它相關心得:

• MyISAM 無法將資料事先載入記憶體, 而是讓 OS 管 file cache (MyISAM 資料本身是一個大檔案), 先用 select 掃一次 table, 之後操作的確會變快不少, 但之後無法掌握各段資料是否在記憶體裡。所以才會有上面提的 MEMORY engine 和 covering index + load index 的作法。
• InnoDB 有將資料存在 cache 裡, 目前還沒參透 InnoDB 的情況, 可以調的東西太多, 不好上手。

強迫 mysql 照指定的順序 join

語法:

SELECT STRAIGHT_JOIN ... FROM A, B, C ...

這樣就會照 table 出現的順序 (即 A, B, C) join。若你很清楚怎麼 join 最省事, 用 STRAIGHT_JOIN 可以避免 mysql 猜錯順序造成超大 join。

我遇到的情況是需要用 primary key 分批取出 A 部份 rows 和 B, C, D 做 join, 再做些計算。這樣資料不會過大而無法處理。但交給 mysql 自行判斷時, 因為資料分佈的變化, mysql 有時選擇先 join B, C, D 最後才 join A, 但 join B, C, D 會造成大量 IO, 速度極慢。反之, 先取 A 部份的 rows 再往後 join, 取得資料不多。

mysql 取出最後 insert row 的 id

依官網所言, 有設 auto-increment 的情況, 在 insert 完後, 可用「SELECT LAST_INSERT_ID();」取出最後新增的 row 的 id。測試結果不同 mysql client 各自記各自的 last insert id, 不用擔心 race condition。我原本誤以為這個 function 會取出全域的結果, 結果是我多慮了。

這篇提到 Python 可用 cursor.lastrowid 取出, 不過我試了沒效。所以就自己手工多下「SELECT LAST_INSERT_ID();」取資料了。

用 mysqladmin 觀察細部變化

大致上熟悉 explain、profiling、show processlist 等的用法, 但還不熟用 mysqladmin 觀察更細部的變化。網路上的文件提到用 mysqladmin extended -r -i10 觀察 10 秒內各細部資訊的相對變化, 但項目太多, 很難觀察。

後來想注意到可以用 grep 幫忙過濾。比方說只關心是否用到寫入硬碟的暫存表, 就加個 "grep Created_tmp_disk_tables":

mysqladmin -uUSER -pPASSWORD extended -r -i10 | grep Created_tmp_disk_tables

若不知要觀察啥 (我目前的情況...), 至少可以先去掉沒變化的項目:

mysqladmin -uUSER -pPASSWORD extended -r -i10 | grep -v "| 0   "

還在摸索怎麼用較適當。

Scale out, HA, backup (for mysql)

前一篇經 DK 說明, 發覺我沒有細分需求和方法。混在一起寫變得很亂。

Scale out

目的:

• 加機器就能應付成長的流量 (ref.)。

作法:

• Replication 可以應付大量讀取、少量寫入的情況。
• Sharding (partition) 可以應付大量寫入的情況 (各 server 應付不同段的資料)。

High availability

目的:

• 網站能持續提供服務。

作法:

• Master-Slave Replication: 寫入密集的情況下, Master 和 Slave 的資料有時間差,  掛掉 Master 後需要處理資料不同步的情況。
• MMM 或 DRBD 可以確保資料同步。MMM 不需暖機, 服務中斷時間最短。 DRBD 較易設定, 但需要暖機時間。

Backup

目的: 

• 可以找回舊資料。

作法:

• Replication 可以做到「差不多即時」備份, 但若誤砍資料, slave 上的資料也「差不多即時」飛了。
• 使用 replication 後, 可以在 slave 上固定間隔時間備份資料, 降低對網站效能的影響, 且不用停止服務。
• 備份方式分為 logical 和 raw backup 兩種類型, 各有優缺點。搭配支援 snapshot 的 file system 用 raw backup, 時間和空間成本都很划算。
• DB 本身要搭配 crash-safe 的方案 (如 MySQL + InnoDB), 確保 server 掛掉時, 資料有保持一致。
• 針對 InnoDB 做 logical backup 的話, 用 XtraBackup 較快。
• 一定要測 restore, 我半信半疑的測了一下, 馬上發現少備份使用者帳號的 DB ......

結論

Scale out、HA、backup 是三種不同的需求, 各自有不同的實踐手段。接下來會先:

• 做 logical backup: 易於實作, 資料量小時沒什麼缺點。
• 做 replication: 協助 backup 和為 scale out 讀取做準備。
• 將 DB 操作包在自己寫的 lib 裡: 之後需要 scale out 時比較好套。

剩下的東西, 待之後有更深的需求再來細讀吧。

參考資料

• LiveJournal's Backend: A history of scaling, 五年前的文章, 了解作者機器架構的轉變仍很有收獲
• MySQL 的 DRBD 與 MMM (1)
• MySQL Master-Master Replication Manager(1) 
• MySQL HA、MySQL HA 與 Slave 的關係
• MySQL (MyISAM) 的備份
• XtraBackup：線上備份 InnoDB 的好東西
• High performance mysql 2e (太厚了, 只讀了一部份)

Scale out, HA, backup

Scale out、high availability、backup 三者是不同的事, 之前對這幾個詞很陌生, 備忘一下它們的差別。

• Replication 可以同時 scale out 讀取的操作和當作讀取的 HA, 但不是 backup。舉例來說, 不小心刪錯資料, slave 上的資料也一起飛了。所以 backup 要另外做。
• Replication 無法保證寫入的 HA, 因為 master 和 slave 之間會因延遲同步而少資料。
• 針對寫入操作的 HA, DRBD 看起來是最穩且易於實作的 方案。但 DRBD 沒有附帶 scale out, 備份機就是待機狀態。
• MMM 可用作寫入的 HA, 備份機可充當讀取的 replication。
• Sharding (partition) 用來 scale out 寫入的操作, 沒包含 HA。

結論是 backup 要分開規劃, scale out 和 HA 也要分開規劃。寫入量不大的話, 可以先用 replication 擋著。反之, 要用 sharding 來 scale out, 用 DRBD 或 MMM 做 HA。

順便記一下 backup 的心得:

• Backup 分為 raw backup 和 logical backup, 各有利弊。若 file system 有支援 snapshot, 在 slave 上做 raw backup, 不管是執行時間還是占用的空間, 都挺划算的。切記要搭配 crash-safe 的方案, 如 MySQL + InnoDB。
• 針對 InnoDB 做 logical backup 的話, 用 XtraBackup 較快。
• 一定要測 restore, 我半信半疑的測了一下, 馬上發現少備份使用者帳號的 DB ......

大概有個概念了, 接下來先做 replication 和 backup, 將 DB 操作包在自己寫的 lib 裡, 待需要 scale out 時比較好套。之後有更深的需求再來細讀吧。

參考資料

• LiveJournal's Backend: A history of scaling, 五年前的文章, 了解作者機器架構的轉變仍很有收獲
• MySQL 的 DRBD 與 MMM (1)
• MySQL HA
• MySQL (MyISAM) 的備份
• XtraBackup：線上備份 InnoDB 的好東西
• High performance mysql 2e (太厚了, 只讀了一部份)

varchar 與 text 的效能差異, 以及 order by 的運算方式

今天和同事 S 說明 MEMORY engine 512 bytes 限制時, 被問到用 varchar 超過 512 bytes 的話, 和 text 有何差別。想了一會兒才想到使用 text 會讓 mysql 用比較慢的方式排序, 在這裡小記一下。

在官網 7.3.1.11. ORDER BY Optimization 裡寫得很清楚, 使用 order by 或 group by 時, MySQL 可能會用到 filesort (可用 explain 看 extra 欄位確認)。filesort 有兩種版本:

• original filesort: 取出符合條件的 rows, 只留 order by 裡的欄位和 row position。排好後再回頭一個個依 row position 取回需要的欄位。換句話說, 會從硬碟讀兩次同樣的資料。
• modified filesort: 取出符合條件的 rows, 將需要的欄位和 order by 裡的欄位一起排。只會從硬碟讀一次資料。

舉例來說, 若是 select a from table order by b:

• original filesort: 先用 b 排序, 再依排好的結果取出各 row 的 a, 所以會有一堆 random access。
• modified filesort: 排序 (b, a ), 排好後就是最後結果。

mysql 會盡量用 modified filesort, 以減少重覆讀硬碟 (更何況第二次還是 random access。) 但發生以下其中一個情況, mysql 會選用 original filesort:

• 用到 text 或 blob 時。
• 單筆資料 (即上面範例的 (b, a)) 超過 max_length_for_sort_data, 單位為 bytes。

除了避免使用第一種的 filesort, 說明文件最後有提供幾個最佳化的作法, 可以調一些參數。

2010-12-16 更新

original filesort 不見得會比 modified filesort 慢, 今天就踏到雷, 調大 max_length_for_sort_data 讓 mysql 使用 modified filesort 反而慢兩倍多, 原因如文件上所言, 單一 row 變大, buffer 能放 row 的數量變少, 增加排序的次數 (show status like 'Sort_merge_passes')。用 profiling 看則發現 Sorting result 的執行時間變長。

由於我用來排序的欄位就超過 512 bytes, 一定得將暫存 table 寫到硬碟。順便試了用 ram disk 上的 tmpdir, 結果的確省了一半多的時間, 原以為會會瞬殺的說。

另外 How fast can you sort data with MySQL ? 和 Impact of the sort buffer size in MySQL 指出 sort_buffer_size 不是愈大愈好, 使用時記得測看看。結論是, 無論如何, benchmark 和 profiling 都是必要的。

更改 mysql conf 的注意事項

mysql 的設定檔有太多讀入的位置, google 一下 "mysql default config path" 會看到一堆文章, 由此可見這問題多令人困擾。

《High Performance MySQL 2e》提供一個不錯的找法:

$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'

我在 Ubuntu 8.04 上的輸出結果如下:

Default options are read from the following files in the given order:
/etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

另外要注意 App Armor 是否允許 mysql 讀它的設定檔。我參考書上的建議, 另外開一個 repository, 將系統相關設定檔放到 repository 裡, 再用 soft link 指回去 (例如: ln -s my-repository/mysql /etc/mysql)。結果啟動 mysql 後, 設定檔毫無作用, 也沒看到任何錯誤訊息。

更改 /etc/apparmor.d/usr.sbin.mysqld 讓 mysql 可以讀設定檔的實體位置後, 就解決這問題了。

避免 MySQL 使用 temporary table on disk

How MySQL Uses Internal Temporary Tables 解釋的滿清楚的, 使用 explain 可以注意 extra 欄位有無「Using temporary」, 有的話 MySQL 會用 temporary table。temporary table 可能是存在記憶體裡的 MEMORY engine, 或是存在硬碟上的 MyISAM engine。執行 SQL 時可以用「show processlist」看 state, 若有出現「Copying to tmp table on disk」就中獎了, 速度會變很慢。

除不能用 TEXT、BLOB、單一欄位不超過 512 bytes 等注意事項外, 還有兩個參數會影響到是否使用硬碟存 temporary table:

• max_heap_table_size: in-memory table 的上限 (即 MEMORY engine 的上限)。
• tmp_table_size: in-memory temporary table 的上限 (即用 MEMORY engine 當 temporary table 的上限)。

若 temporary table 需要大於 min(max_heap_table_size, tmp_table_size), 就會用硬碟存。

題外話, 大部份 MySQL 的疑問都能很快地從官網文件找到答案, MySQL 官方文件真不錯啊。《High Performance MySQL 2e》也照順序整理了不少有用資訊, 對照兩者學了不少東西。