淺入淺出 MySQL & PostgreSQL // Speaker Deck
(speakerdeck.com)
HYL
積分 4
MySQL對我來說，只是隻堪用的瑞士小刀，可以開一些簡單的 Table 放資料，然後就可以把 MySQL 放在那邊不用去理他。
但是除此之外都很難用，像是我昨天要把一個 160 MB 的 CSV 檔丟到我的 MySQL 去，用預設的指令，會卡在 4MB 的地方就慢到像停下來一樣。
CREATE TABLE website {
owner INT NOT NULL DEFAULT 0,
id CHAR(18) NOT NULL,
url VARCHAR(255),
PRIMARY KEY (owner, id)
}
LOAD DATA INFILE 'website.csv' ......
就這麼簡單的事，在我的 macbook 上匯到一個沒有其它人用的 MySQL ，竟然會卡住越寫越慢。
而在 PG 上，我把 3GB 的 csv 檔，寫入一個有 30 個 column 的 Table 只花了四分鐘，一秒可以寫 12.5 MB/s
innodb_log_file_size 跟 buffer 都要開大一點，用預設的很杯具
IngramChen
積分 1
其實 postgres 的 buffer 預設的更小，預設值根本是給 1G ram 不到的電腦用的...
因為 PostgreSQL 與 MySQL 理念哲學不同。
PostgreSQL 認為 OS file caching 已經做得很好了，所以很多 cache 都是在 OS level；反之 MySQL 認為什麼都自己管效率才高，因此什麼東西都放在自己的 cache。
以致於，
PostgreSQL 較 MySQL 更依賴作業系統及其檔案系統，稍有不同效能就會有差 (例如 Linux kernel 或 FS 的選用就會有不一樣的差異)；而 MySQL 比較沒有這個問題。這也導致 PostgreSQL 社群會特別關注 Linux kernel 每個版本所帶來的影響，但 Linux kernel 也不可能為了 PostgreSQL 就調整什麼，畢竟 Linux kernel 不是只給 PostgreSQL 執行。
PostgreSQL shared buffer 不能設定太高，因為要保留給 OS file caching；反之，MySQL 因為都自己管，所以要設定得很高。放在同一個 pool 有一個好處，就是資源共享，反之 PostgreSQL 的 shared buffer 不管太高或太低都沒有辦法跨出這個設定值去用預留太多或太少的 OS file caching。另外，這也造成 MySQL 在單機多實例的運用下較有優勢 (如果有需要的話)。
以上，是我在簡報沒講的 Part 2 內容之一。
IngramChen
積分 5
謝謝解說，我自己的經驗也是如此。前些日子將 Java App 的 heap 調低 1GB，將它歸還給 OS 運用。果不期然 PostgreSQL 的 Disk IO 就變小了 (db 和 web app 放同台機器)
HYL
積分 0
岔題，很久很久前，我們單位的習慣是，既使是有 8GB Ram 的機器，仍是留了一半的空間給 OS 來調用，只有 4GB 是給 JVM 用。
不知道大家的習慣是？
qrtt1
積分 2
我比較習慣看峰值的剩餘空間，希望 OS 至少還有 1GB 能用比較安心，不然記憶體用多的程式可能會遇上 kernel 的 oom killer 就麻煩了。
IngramChen
積分 3
我們以前部署的機器，15GB ram ，我的 JVM heap 就開 14GB，留 1GB 給 OS，結果十幾台主機一週就當個二、三個 jvm。一開始以為踩到 JVM bug，造成 segment fault (畢竟沒有開過 1xGB heap 過，怕怕)，查到最後才發現是 OOM killer 會殺。這就很納悶了，不是已經留 1GB 給 OS 了？後來調降到 12 GB 後就再也沒發生過了。
原因沒有詳查，但多半是服務太大，所以 OS 會需要更多的資源 (socket...etc)
所以你講到重點了，是 峰值的剩餘空間 後，還保留個 1GB 左右是安全值，尤其是配置大量的 JVM heap。以前傻傻的直接用硬體 ram 去估算做不得準啊。
IngramChen
積分 0
看主機上有沒有其他服務，如果沒有的話大部份都會配給 JVM，除非已經知道該服務是 I/O heavy，需要大量 OS disk cache。
不過我沒有配置過 16GB 以上的 JVM heap 了，聽說到了這個階段 garbage collection 就很難調了 (指 hotspot jvm)，很多服務到這階段就會開始拆 server，而不是硬撐 vertical scale up 上去。
其實不要開太大還有個好處是 memory leak 時，能夠及早發現及早治療。萬一是爆在 1x GB 把 memory dump 出來要用 mat1 來看誰是兇手也很麻煩。
HYL
積分 12
這邊是比較好的討論平台，所以把我的心得放這邊好了。
就我看完的想法是， Database 效能影響的點有兩個，第一個是 index tree 的維護，第二個是「資料在硬碟上的儲存方式」
前者兩者用的都是 b+tree ，所以有同樣的問題，如果照 PK 循序寫入，因為樹會變的不平衡，會需要常常重新平衡樹，會有 lock tree or sub-tree 的發生，這時 DB 的效能會下降。
在儲存方式上，MySQL用的是 Index Optimized Table ，儲存資料的方式是照 index 的型式去寫入，所以循序寫入也有「平衡」的需求。而 PostgreSQL 則是有洞就塞，再用另外一張表去儲存 key -> 儲存位址。在 Update 時，MySQL是 in-place 把值給替換掉， PostgreSQL 則是把「儲存位址」標個墓碑，等 full vaccum 時再清掉
因為不想造成 index 上的 hotspot ， Triton Ho 主張是要用 UUID ，不要在 insert 時，全部都往同一個 sub-tree 寫入，同時也省去了維護 Sequence Generator 的困擾。
這點我是同意的，對 PG 來說，是不是依 PK 的順序寫入跟本沒差，不依 PK 順序寫入，反而對 PG 來說維護 index 的成本較小，在寫入硬碟時，仍是循序寫入。
但是對 MySQL 來說就不一樣了，不依 PK 順序寫入的話，在寫入硬碟時，是 random access ，效能低 40~50%。
所以 Ant 才會說，要用 v1 Time-based UUID ，但是我覺得兩個人的對話跟本沒交集，用 v1 UUID ，那就直接用 Int/Long Sequence 就好了， index size 還可以更小。
至於 VACUUM 我更不覺得是個問題，因為除了 MYSQL 外，我用過的 MongoDB CouchDB Cassandra 全都是設計成要 FULL VACUUM 才會把空間釋放出來，
至於 PG 為什麼要引入 AUTO VACUUM 呢？我猜想是因為有太多非 DBA 的工程師，因為跟本不了解 DB 底層的運作，一跑就跑了半年沒清理過，所以 PG 才要把雞婆的幫使用者跑 VACUUM
Ant
積分 14
( 這裡好酷呀，首波 )
我只是覺得作者 "用 PostgreSQL 的優勢直接對等套用在 MySQL 上，而不考慮 MySQL 特性做修正，然後再說 MySQL 很差"，以藉此推廣 PostgreSQL 比較好的方式很不妥，所以寫了這篇簡報。
PostgreSQL 寫入時，單位是 block，然後會與 page 對齊，所以如果 PK 不是順序寫，那表示會落在很多個不同的 pages，如此在 checkpoint 時雖然是順序寫入，但若順著 PK scan 讀出時，就會變成 random read。但後者對 SSD 效能其實影響不大，只是剛好跟 MySQL 建議的方式有點不同。
使用 UUID 是要看情況，因為 MySQL 的 AUTO_INCREMENT 及 PostgreSQL Sequence 本身依賴的就是 locking，所以若要避用的話，PostgreSQL 天生有優勢，但 MySQL 則要小心不要用到 Random UUID (例如 UUIDv4)，否則效能如你所言會差很多。而 MySQL 要用 INT 還是 UUIDv1 就看個人了，因為 UUID 是標準資料交換時通用的 KEY。
VACUUM 其實就跟程式語言的 GC 一樣，讓人又愛又恨，一旦累積太多，GC 時就要一點有心理準備，抖動會比平常大一些。MongoDB 及 Cassandra 在發生 GC 時，也一樣很令人頭痛。AUTO VACUUM 其實最大的重點就是，依據內部的偵測機制，讓 PostgreSQL 自行決定什麼時候 VACUUM 最好；再者，PostgreSQL 官方也說了，若是定期 VACUUM，要小心累積太多的 dead tuples，而導致碎片嚴重到 VACUUM 修不好，最終只能 VACUUM FULL 而導致 table lock。
我兩個資料庫都用，確實要看應用而選。不然以 PostgreSQL 狂派傳教士而言，好似用 MySQL 的 Google / Facebook / 阿里巴巴都是笨蛋一樣。
kaif
積分 2
我對DB (含RDBMS, NoSQL)性能的想法： 只要能配置到運作狀態相近，單就性能表現應該就會差不多才對。
例如mysql/innodb只要能確保資料都被warmup，cache在記憶體裡，那表現應該會和同樣B+tree的PostgreSQL、mongoDB差不多。
因為我熟mysql，網路上mysql的資源也多，所以單就效能，沒有理由切換到PostgreSQL或是mongoDB。
IngramChen
積分 4
mysql 就是效能不錯，replicate 的機制很好設定，所以如果能滿足需求自然可以用。
不過用 postgresql 的人通常看重其他 mysql 沒有的優點，例如支援更多 SQL 語法標準 (WITH 、WINDOW function 之類的)；支援 json；預設的資料檢查嚴謹；還有變動 schema 時超快，還可以上 transaction。在 mysql 下 alter table，資料大時就會想哭了，我們有做過一億筆資料的 alter table ，超過 24 小時還沒跑完...
像我已經習慣 pg 提供的這些功能，很難在回去寫什麼都沒有的 mysql 了...
qrtt1
積分 2
Ant
積分 5
pt-online-schema-change 是過渡期工具，主要供 MySQL 5.6 之前版本。它的執行原理非常暴力， copy table + trigger，所以沒有 locking。
到了 MySQL 5.6 之後，因為支援 Online DDL (MySQL 5.7 支援更全面)，很多 ALTER TABLE 都不再 locking，可以在生產線上直接運行，只是免不了效能的抖動。因為 MySQL 資料結構與 PostgreSQL 不同，所以 schema change 再怎麼做還是不會比 PostgreSQL 來得有效率。
對我來說，PostgreSQL 最大的問題在於沒有很成熟的 master-master 及跨數據中心的同步方案。master-master 有它先天的問題。可是 master-slave 有 SPOF 及 single write hotspot 問題，前者可用 HA 解，但後者無解。
跨數據中心又是另一個麻煩事，如果不支援就要自己搞 Sharding，不是做不到。而且 Sharding 很難維護又很可能一不小心就破壞商業邏輯。就算要搞 Sharding，MySQL 還是有比 PostgreSQL 相對成熟的方案，就是官方的 Fabric，夠傻瓜夠直覺夠彈性。
從 Enterprise requirement 可以看出 PostgreSQL 與 MySQL 發展思路上的差異，如果是走穩定、數據保證，目前 PostgreSQL 絕對會是首選 (但 MySQL 5.7 追上來了)；如果是走網路服務的高延展性、跨數據中心，那目前就是 MySQL 的天下了。
這個應該主要是來處理alter table locking的，效能不確定有沒有改善。不過聽我們不太專業的DBA講好像用起來也有些issue
如果postgresql可以在上億筆資料一秒alter table without locking，那還蠻吸引我的。
HYL
積分 4
PG 的檔案結構跟 MySQL 不一樣， PG 是一個 column 一個檔案，所以新增一個 nullable column 的話，就是開一個空的檔案而已，刪掉某個欄位也是刪掉一個檔案而已。
MySQL 是整個 Table 一個檔案，所以 ADD/DELETE column 都要整個 Table 重寫一次。
Ant
積分 3
目前大家討論的是 MySQL InnoDB & PostgreSQL，但 MySQL 還有一個很大的特色：可熱抽換的引擎。MogoDB 3.0 也引用了這項優點。
如果覺得 InnoDB engine 不好，可以換成別的 engine，甚至 MySQL 可以讓某些 engines 彼此 replication 混用。
前者，可以因應業務場景的改變抽換 engine，例如 OLTP 換成 OLAP 再換成 Warehouse 時，一行指令就可以搞定其對應的適用 engine。
後者，讓 DBA 可以用多個 engines 應付不同的業務，同時維運管理仍可視為同一集群而降低成本及風險。
PostgreSQL 只有一種 engine，不可抽換，這會限制它可適用的範圍。未來 PostgreSQL 有沒有可能引入可熱抽換 eninge？也許有可能，但至今沒看到相關計畫，而且 PostgreSQL 太多底層都跟現行的 eninge 相依太高，要改變實屬不易。
ryudoawaru
積分 0
要用外部工具這點不是很好啊...如果是用 ORM 做 migration 的話就 GG 了
我家做zero down time service通常不會讓ORM自動做migration。畢竟ORM頂多只能作到schema change，遇到要配合程式邏輯update資料的時候，還是要手工，不如就全手工了。此外也不太信任ORM。
IngramChen
積分 3
同意。
其實我也不知道為什麼 ORM migration 會盛行。程式邏輯變更要手動下 update 很常發生啊，怎麼可能自動 migrate ? 而且常常遇到的情況是：
- 先修改 schema 一輪，讓新舊 app servers 能同時運作
- rolling upgrade app servers
- 再修改 schema 一次，這次可以將舊的 table/column 都清理掉
說真的我不太了解 ORM migration 可以做到什麼程度，但像上面這樣的流程我如果不一行行看 SQL 執行，一行行 review alter schema 的結果，我完全不放心啊。(強迫症...)
IngramChen
積分 0
原來現在已經有這種工具了啊。很快就會用到了
我是都有在追 gslin 的 RSS 才知的，不過要注意一下提醒事項。要先在 staging 測過，而且使用前記得備份。 :P
我自己只用過他的備份工具，還沒機會用到 pt-* 系列的工具。 這篇的 master/slave 強制同步妙計1 真令人驚豔啊。
IngramChen
積分 4
投影片內容好像是跟另一個人在吵架？
anyway, 我幾乎看不懂他們在說什麼.... orz，深入到 source code 的世界了這。
我只有一個共同經驗：很多 db (postgresql, cassandra) 都有自動清理的機制，量不大的話平常都很 ok。但難免會有一兩個 model 會涉及大量修改與刪除，遇到這種還是得手動做清理。不然就是學乖，盡可能將 model 重構成都是新增導向。
ps. 我自己的網站有 10GB 大小的 postgresql db，每週跑一次 full vaccum，一次要 20 分鐘，這段期間網站整個鎖死不能動...