Home » Posts tagged "myisam"

KPTI (Meltdown Mitigation) 對 MyISAM 的痛點

MariaDB 的「MyISAM and KPTI – Performance Implications From The Meltdown Fix」這篇看到頗驚人的數字,這篇提到了他們收到回報 (回報的 ticket 可以參考「[MDEV-15072] Massive performance impact after PTI fix - JIRA」),說 KPTI (Meltdown Mitigation) 對 MyISAM 效能影響巨大:

Recently we had a report from a user who had seen a stunning 90% performance regression after upgrading his server to a Linux kernel with KPTI (kernel page-table isolation – a remedy for the Meltdown vulnerability).

他們發現 90% 是因為 VMware 舊版本無法使用 CPU feature 加速,在新版應該可以改善不少。但即使如此,文章內還是在實體機器上看到了 40% 的效能損失:

A big deal of those 90% was caused by running in an old version of VMware which doesn’t pass the PCID and INVPCID capabilities of the CPU to the guest. But I could reproduce a regression around 40% even on bare metal.

然後後面就在推銷 MariaDB 的 Aria Storage Engine 了,不是那麼重要... 不過知道 MyISAM 在 KPTI 下這麼傷還蠻重要的,因為接下來五年應該都還是愈的到 KPTI,應該還是有人在用 MyISAM...

Percona 比較 MySQL 與 MariaDB 預設值的差異

Percona 的人花了些時間整理 MySQL 5.7 與 MariaDB 10.2 在預設值上的差異:「MySQL and MariaDB Default Configuration Differences」。

整體可以感覺到 MariaDB 10.2 相較於 MySQL 5.7 還是頗偏 MyISAM 的設計,可能跟 Monty (Michael Widenius) 的偏好有關吧... 不過技術面上來說,MariaDB 10.2 是基於 5.5 分支出來一路改出來的,當時的 InnoDB 跟現在的版本比起來的確沒那麼強...

不過這畢竟只是預設值,看過留個印象就好...

小台機器上的 innodb_purge_threads 對效能的影響

雖然「MyISAM, small servers and sysbench at low concurrency」這篇標題是在講 MySQL 上的 MyISAM,但還是有提到一些 InnoDB 的東西...

其中提到了 innodb_purge_threads 對效能的影響:

the default value for innodb_purge_threads, which is 4, can cause too much mutex contention and a loss in QPS on small servers. For sysbench update-only I lose 25% of updates/second with 5.7.17 and 15% with 8.0.1 when going from innodb_purge_threads=1 to =4.

當機器不大的時候,innodb_purge_threads 對於效能帶來的影響其實頗大的?

另外從作者最近的一系列測試看起來,5.7 在小機器的效能比 5.6 差不少... 這點在考慮 RDS 的時候也許要注意 (因為 t2.* 應該不算大 XD)。

Mobile01 的 Ryan 換 InnoDB 的筆記與心得

沒記錯的話,Mobile01 應該是去年暑假左右從 MyISAM 換成 InnoDB 的?一切的起頭應該是蔣大「現在SSD硬碟可以拿來跑資料庫嗎?」這篇。

另外同場加映,使用 Percona 的工具讓管理上更方便:

MyISAM 是 MySQL 5.0 與 5.1 預設的 storage engine (到 5.5+ 預設的 storage engine 改成 InnoDB),讀取的效能相當好,但總是有些問題。

當時剛好有機會跟蔣大與 Ryan 聊到當時 Mobile01 遇到的問題,問了一些細節後感覺上是 MyISAM 的問題,就提了 MyISAM 與 InnoDB 的優缺點比較,以及幾個 InnoDB 的 High Availability 的解決方案 (晚上就算設備出問題也不用擔心要爬起來救機器):

  • MyISAM 的讀寫互斥、寫入也是互斥。當有資料在讀取時無法更改資料庫內容,而有寫入時其他人不能讀取。另外同時間只能有一個人寫入。(有少數操作是例外,像是 bulk insert,這邊跳過)
  • MyISAM 不是 crash-safe storage engine。機器總是有機會爛掉,這時候除了重開機的時間外,還需要有修 table 的時間,對於網站的 uptime 比較痛。

這兩點是當時 Mobile01 遇到最痛的問題:用 iostat 看起來 I/O 明明就沒有滿,但就是會卡 SQL query,而當機後修資料庫的時間又很長。

一個是已經在業界驗證很久的解決方案 XFS + DRBD + Heartbeat,當機器發生問題時的 downtime 從 30secs 到五分鐘 (依照資料性質與大小而有差異,在切換上線後有資料庫的熱機問題)。

另外一個是當時還很新的 Percona XtraDB Cluster,可以避免資料庫的熱機問題,不過技術很新。

後來 Mobile01 用 RAID 10 的硬體,軟體的部份用 Debian + XFS + DRBD + Heartbeat 跑 Percona Server 的 XtraDB (InnoDB 的加強版),先用 VM 做了 PoC (直接砍掉 mysqld,或是直接關掉 VM 之類的,測試整個機制夠不夠自動化),然後就上線了 :p

記得上線那幾天跟 Ryan 聊,好像效果還不錯吧...

把 MySQL MyISAM 換到 Galera Cluster 的 InnoDB 上...

在「Switching from MySQL/MyISAM to Galera Cluster」這邊看到一個 script 可以檢查 MySQL MyISAM 換到 InnoDB,而且預定要換成 Galera Cluster 時的問題。

常見的問題都有檢查到,還蠻有用的:

  • 針對沒有 Primary Key 的表格提出警告,讓管理者規劃補上 Primary Key。
  • 針對 MyISAM 換成 InnoDB 後造成 Primary Key 太長的表格提出警告,讓管理者想辦法修改。

Galera Cluster 無法處理沒有 Primary Key 表格的刪除動作:(可以參考「MariaDB Galera Cluster - Known Limitations」這邊的說明)

DELETE operation is unsupported on tables without primary key. Also rows in tables without primary key may appear in different order on different nodes. Don't use tables without primary key.

不過每個表格都要有 Primary Key 並不難,因為如果有正規化時通常都會達到目標。就算不去用他也還是可以設計一個 INT UNSIGNED PRIMARY KEY AUTO_INCREMENT 放著。(過大的時候再換成 BIGINT UNSIGNED)

MySQL 內 JOIN 的應用...

Common use cases for the MySQL Join statement」這篇給的範例與把 MySQL 上常用到的幾種 JOIN 提出來分析,包括 index 與 explain。另外在「Managing hierarchical data with MySQL」也提到了要怎麼處理階層式資料。

對於 JOIN 大概分幾個階段:

  • 在使用 MyISAM 的時候會儘量避免 JOIN,因為當 SQL 執行時間久的時候會有好幾個 table 同時卡住無法寫入。
  • 改用 InnoDB 後一直用 JOIN,不論是報表或是 web SQL query,造成 CPU bound (雖然不是 table lock,但在 MVCC 架構下,讀取也還是有 lock 成本存在)。
  • 把 JOIN 形式的 web SQL query 拆成多個 SQL query 以降低 lock 成本;針對計算成本很高的結果 cache,再針對 cache 效果不佳的表格逆正規化。只有報表不受限使用 JOIN。

後面的階段則是 data sharding 與 NoSQL。不過每個階段的界線不是那麼明顯,有時候會重疊在一起...

說到逆正規化,MariaDBVirtual Columns + PERSIST 好像是逆正規化的好工具,再來測仔細一點...

測試 MariaDB 後的一些感想...

Monty Program ABMySQL 的發起人 Monty 在離開 Sun 之後所創辦的公司 (他同時也是 MySQL AB 的創辦人),這家公司目前以 MariaDB 為發展主力。

先說對 MariaDB 目前的看法:暫時還是會用 Percona 所提供的版本,以及 XtraDB (基於 InnoDB 的產品)。

MariaDB 發展的重點在於 Aria storage Engine,但目前的 1.5 版只支援 crash-safe,要到 2.0 才會支援 InnoDB 主要功能,而到了 2.5 才會針對效能調整,看起來要到「能用」必須要到 2.5 之後... (參考「Aria FAQ」的說明)

InnoDB 控制權在 Oracle 手上,XtraDB 控制權在 Percona 手上,而 MyISAM 拿不上檯面。所以 Monty 想要一個控制權在他自己手上的 storage engine...

對於社群來說,因為 XtraDB 還是基於 InnoDB 的分支,所以當 Oracle 從 InnoDB 抽手後大家會擔心 Percona 能夠自己發展到什麼程度。這使得對於 Aria 有所期待,也趁著現在 Oracle 被歐盟盯著不致於做的太明顯趕快發展。

這也可能是 WikimediaMozilla 選 MariaDB 當 slave 測試的原因?當然也有可能只是「看名字比較順眼」之類的原因而選... XD

從 MySQL (單機) 轉到 Galera Cluster 的前置作業...

codership (Galera Cluster 背後的公司) 剛剛發了一篇文章,說明將 MySQL 轉換到 Galera Cluster 有哪些事情要先處理:「5 Tips for migrating your MySQL server to a Galera Cluster」。

純粹技術上的事情大致上是這樣:

  • 先轉到 InnoDB
  • 每個 Table 都加上 Primary Key。
  • 檢查 Event,確認在 Galera Cluster 裡面會怎麼跑,或是直接拆到 cron server 跑...

另外幾點不是技術上的問題,而是 policy 應該規劃的事情... 把事情列出來,多隻眼睛檢查後再一步一步照表操課。

PS:對於 Galera Cluster 不熟的人可以先去看官方網站以及 Percona 的說明,看不懂就不要用,這樣會比較安全...

MySQL 中,MyISAM 與 InnoDB 帶來的差異...

標題所提到的兩個 engine 是在 MySQL 中最常用到的兩個 engine。其中 MyISAM 是在 MySQL 5.1 之前的 default engine,InnoDB 則是 MySQL 5.5 之後的 default engine。

這篇主要是講 MySQL 5.1 + InnoDB Plugin,或是 MySQL 5.5 後的情況。

MyISAM 是 Table-level lock,當有寫入時其他人無法讀取 (有少數例外,像是 bulk insert)。而 InnoDB 設計成 Row-level lock,在寫入時有很大機會還是可以讀取。

另外,InnoDB 支援 ACID transaction,對於學過資料庫理論的人 (像是科班出身) 會覺得比較「親切」,比起 MyISAM 有很多東西可以用。

大多數不換 InnoDB 有幾個原因:

InnoDB 佔用的空間比較大

最常見的原因是認為 InnoDB 相對 MyISAM 所佔用的空間會大不少 (看過三倍大的):但在 InnoDB 自從推出壓縮功能後就接近很多 (看過只大 20% 的,甚至有可能比 MyISAM 小)。而資料量的問題可以參考 Mobile01 的 55GB (2012 年 10 月 uid=1 的蔣大在 Mobile01 上揭露的數字)。

當網站夠大時,記憶體與用 15KRPM SAS 硬碟加上 RAID1+0 的一次性成本其實不高。

InnoDB 不好備份

第二個常見的原因是認為 MyISAM 備份比較容易,直接 copy 就可以備份:這種備份方式其實有很高的風險造成 inconsistent backup (「備份的資料損毀」)。以交易的例子來說,有 auction 與 user 兩個表格,按照字母順序先複製了 auction 表格,再複製 user 表格,中間使用者用點數購買了某個物品 (扣 user 表格裡的點數,在 auction 裡建立一筆資料)。這份備份裡就會備份到「user 內的點數扣掉,但 auction 沒有資料」的情況。

比較好的方式是用 InnoDB 提供的 transaction 建立 consistent backup,備份時長時間讀取不會像 MyISAM 無法寫入。另外一種方式是建立 backup 專用的 slave,要備份時可以整台 slave 停掉備份 (還有像是 filesystem snapshot 之類的方式,這邊跳過)。

書上寫 MyISAM 效能比較好...

是的,不過這個前提是「如果你只有讀取」。

MyISAM 讀取效能比較好並不是指 MyISAM query 一次 <1ms 時 InnoDB query 一次就要 100ms 這種程度。兩個都還是在 <1ms,只是當你用到「效率不好的 query」時,MyISAM 在 Table scan 的效能會比 InnoDB 好。

但當網站變大遇到 MyISAM + table scan 時就完蛋了:一個 query 卡 1 秒就代表網站上寫入時遇到這個 query 時最少要卡一秒,InnoDB 反而能救你。(雖然這不是好的方向。好的方向應該是想辦法解決 Table scan 的問題,可能是改 query,也有可能是增加 index 或修改 index)

也因為以上的特性,當使用 InnoDB 時,可以這樣設計:

只對時間敏感 Query 加上 Index

以前用 MyISAM 時為了避免跑報表會造成其他線上服務的 query 卡住,如果用 InnoDB 因為不會卡,報表的 query 沒有 index 而 table scan 也是可以接受的。

有嚴格資料正確性需求的 Query 使用 Transaction

像是金流相關系統,一筆購買紀錄可能要修改好幾個表格。用 MyISAM 時必須對好幾個表格使用 TABLE LOCK (仍然有 atomic 問題),現在可以用 transaction 解決。

資料庫正規化

有可能會因為 MyISAM 卡 query 的問題而設計出非正規化的 schema。用 InnoDB 可能可以正規化,用適當的設備成本 (像是適當使用 JOIN) 降低人力維護成本。

Archives