Home » Posts tagged "db"

最近開的課程:程式進階效能優化實作

五倍紅寶石聊了蠻多方向的,決定合作開一門跟資料庫有關的課。上個禮拜先貼連結到我的 social network 上,但一直沒寫文章說明內容。所以就來寫文章推銷了:「| 五倍紅寶石 | 程式課程:💎SQL 程式進階效能優化實作 - 台北假日班 | Accupass 活動通」。

這門課程預定在 5/26 與 6/2 上課,共 12 小時,分成兩個週六上課。課程主要是以 MySQLInnoDB 為內容 (會採用 Percona Server 5.7 的系統),在課堂上會有實際的 MySQL server 與 phpMyAdmin,可以拿預載好的 data set 實際練習。

由於是效能改善的主軸的課程,會從圍繞與效能有關的主題說明:

  • 有哪些資料結構可以使用。像是 VARCHARCHAR 的差異,以及對 Index 的影響。
  • 怎麼樣設計出符合正規化規範的表格,以及使用與不用的時機。
  • 當表格正規化後,有哪些方式可以取得資料。像是各式的 JOIN、GROUP BY 以及 subquery。
  • 在系統內 Index 要怎麼下才會有效率。
  • 怎麼看 MySQL 對一組 SQL query 的解讀,這邊會針對 EXPLAIN 的操作以及輸出結果。
  • 常見的效能問題,像是 ORDER BY RAND()、N+1 以及 LIMIT N,20 造成資料庫效能不佳以及改善的方式。

而這門課主要針對兩種客群:

  • 第一種是會對資料庫操作的 Programmer,像是後端工程師,全端工程師,或是 DevOps。由於課程內包括了系統上線前的設計與預防措施,以及上線後遇到狀況時的判讀與排除,對於 Programmer 來說是不可或缺的課程。
  • 第二種是管理資料庫的 DBA。在前期參與規劃資料表格設計時,可以提供精確的建議,以大幅降低後續維運上可能遇到的問題。以及當真的遇到效能問題,或是接手的系統就已經有效能問題時,提供可能的解決方案。

目標是透過這門課程,讓資料庫 (尤其是 MySQL) 被用的更淋漓盡致,而不是單純的遇到效能問題就加大機器。

MySQL 的 binlog 對效能的影響

Percona 的 CTO Vadim Tkachenko 在比較 InnoDB 與 MyRocks 時意外發現了 binlog 會影響不少效能穩定性,再加上 MySQL 8.0 有改變 binlog 相關的預設值,所以他後續花了不少時間測試,寫了兩篇關於 binlog 對於 MySQL 效能的影響:「How Binary Logs (and Filesystems) Affect MySQL Performance」與「How Binary Logs Affect MySQL 8.0 Performance」。

第一篇是想要知道在 Percona Server 5.7 上開 binlog 的影響,做出來後可以看到明顯的效能波動 (因為 binlog 導致 flush 時效能暴跌):

而其中的 workaround 就是調整 flush 參數,讓他比較頻繁的小量寫入,而不是突然要寫很多資料。這樣一來對平均效能也許比較差,但對前端應用衝擊會比較小:

在測試可以看到 sync_binlog=1000 是個妥協點。各單位要自己去找出適合的數字:

The strict setting also comes with noted performance penalties. I will also test sync_binlog=1000 and sync_binlog=10000, which means perform synchronous writes of binary logs every 1000 and 10000 transactions, respectively.

另外有測試 ext4 與 XFS 是否有影響,就測試的結果看起來 ext4 比 XFS 好一些,但差距有限:

第二篇則是拿 MySQL 8.0 與 Percona Server 5.7 比較,可以發現在 MySQL 8.0 開啟 binlog 時有時會有不少的效能損失:

It seems that binary logs have quite an effect MySQL 8.0, and we see up to a 30% performance penalty as opposed to the 13% for Percona Server for MySQL 5.7.

雖是推銷一下自家產品在這塊還不錯 XD

MySQL 與 GraalVM 的結合...

Twitter 上看到 Oracle 計畫在 MySQL 內包入 GraalVM

這能不能解決 MySQL 的 stored procedure 一直以來都很殘的問題就要多花些時間看看了... 另外 GraalVM 用 GPLv2,但沒有明確的專利授權條款也是大家很在意的問題 (參考 Hacker News 上「GraalVM: Run Programs Faster Anywhere | Hacker News」這邊的討論),因為大家都很清楚,這家公司只要是沒有授權的東西就不要碰...

話說 Percona 都把 PostgreSQL 包進來吃了,來考慮多用用 PostgreSQL 好了... (把 blog 換掉?)

Percona 宣佈提供 PostgreSQL Support...

有點意外的,Percona 宣佈提供 PostgreSQL Support:「Percona Expands Services Offerings with PostgreSQL Support」。看起來是建出熟 PostgreSQL 的團隊來做生意了...

之後應該會在他們站上開始看到與 PostgreSQL 相關的文章了;另外一方面,對於付錢買 Percona 服務的人來說,可以跟同一個廠商通包所有的服務 (從 MySQLMariaDBPercona Server 這三個 MySQL 系列的資料庫,到 MongoDB,然後是昨天推出的 PostgreSQL):

As a result, organizations can, for the first time, work with a single trusted vendor to meet their support needs for MySQL, MongoDB, MariaDB, PostgreSQL, or any hybrid combination of these database technologies, whether deployed on-premises, in the cloud, or in a Database as a Service (DBaaS) environment.

然後也許有機會看到 Percona 客製的 PostgreSQL?

MySQL 8.0 正式推出 (GA,General Availability)

Oracle 推出了 MySQL 8.0 (GA,General Availability):「MySQL 8.0 – Announcing GA of the MySQL Document Store」。在「What’s New in MySQL 8.0? (Generally Available)」這邊也花了一些篇幅介紹 MySQL 8.0 的新功能。

比較感興趣的是:

  • Descending Indexes
  • Information Schema (speed up)
  • Performance Schema (speed up)
  • INVISIBLE Indexes
  • Scaling Read/Write Workloads
  • Utilizing IO Capacity (Fast Storage)
  • Better Performance upon High Contention Loads (“hot rows”)

不過就實用性來說,效能的提昇還是最直接的... 接下來等 Percona 的人 porting 了。

在 Amazon Aurora 利用 ProxySQL 的讀寫分離提昇效能

Percona 的「Leveraging ProxySQL with AWS Aurora to Improve Performance, Or How ProxySQL Out-performs Native Aurora Cluster Endpoints」這篇有夠長的,其實就是發現 AWSAmazon Aurora 只使用 Cluster Endpoint 無法壓榨出所有效能,只有當你讀寫分離拆開 Cluster endpoint 與 Reader endpoint 時才能提昇效能。主要是在推銷 ProxySQL 啦,其他的軟體應該也能達到類似的效果...

然後這張怪怪的,應該是 copy & paste 上去的關係?

因為事後再疊 ProxySQL 進去不會太困難,一般還是建議先直接用服務本身提供的 endpoint (少了一層要維護的設備),等到有遇到效能問題時再來看是卡在哪邊,如果是 R/W split 可以解決的,才用 ProxySQL 或是其他軟體來解...

MySQL 5.7 的 VIRTUAL column 與 index

看到 Percona 的「Using ProxySQL and VIRTUAL Columns to Solve ORM Issues」這篇後去找 VIRTUAL 的資料,發現其實以前就寫過了,而且是兩年前寫的了:「MySQL 5.7 的 JSON、Virtual Column 以及 Index」。

2NF 的規範中會禁止資料的重複性以及可推導性。以這樣的資料結構開始:

CREATE TABLE t1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birth DATE
);

與後者這樣延伸出來的資料結構:

CREATE TABLE t2 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birth DATE,
    year INT,
    month INT,
    day INT
);

其中 t2 裡的 yearmonthday 都可以被 birth 推導,這就卡到 2NF... 會有 t2 這樣的資料結構通常都是因為效能而需要的設計。

像是 SELECT * FROM t1 WHERE MONTH(birth) = 12; 這樣的 SQL query,即使在 birth 加上 index 也沒用,因為查詢條件不是某個連續的區間。另外建出 month 欄位,再對 month 建立 index 後,SELECT * FROM t2 WHERE month = 12; 才能利用這組 index 提昇效能。

但後者的設計會導致兩個問題,一個是空間的增加,另外一個是資料一致性管理的成本。

空間的增加還蠻好解釋的,來自於多了 yearmonthday 這些欄位要儲存。而資料一致性管理的成本是因為你沒有強制性的方式讓 yearmonthday 的值與 birth 的內容一致,也就是資料庫內有可能會有 birth2018-01-01,但 month 裡卻是 2 之類的數字。

一致性在 PostgreSQL 有 constraint 與 function 計算可以擋下,但對應到 MySQL 的 constraint 就沒辦法用 function 判斷條件,變成需要在 MySQL 外的地方 workaround 確保一致性...

而這次標題提到的 VIRTUAL column 算是 MySQL 5.7 推出來解這個問題的想法,我們可以這樣設計資料結構:

CREATE TABLE t3 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    birth DATE,
    year INT AS (YEAR(birth)) VIRTUAL,
    month INT AS (MONTH(birth)) VIRTUAL,
    day INT AS (DAY(birth)) VIRTUAL
);

然後對 month 建立 index:

ALTER TABLE t3 ADD INDEX idx__month (month);

接著塞資料進去測試:

INSERT INTO t3 (birth) VALUES ('2018-01-02');
INSERT INTO t3 (birth) VALUES ('2018-01-03');

拉資料可以看到,雖然塞資料進去時沒有指定 yearmonthday,但拉資料時會計算出來:

mysql> SELECT * FROM t3;
+----+------------+------+-------+------+
| id | birth      | year | month | day  |
+----+------------+------+-------+------+
|  1 | 2018-01-02 | 2018 |     1 |    2 |
|  2 | 2018-01-03 | 2018 |     1 |    3 |
+----+------------+------+-------+------+
2 rows in set (0.00 sec)

也可以看到 VIRTUAL column 的唯讀特性:

mysql> INSERT INTO t3 (year) VALUES (2018);
ERROR 3105 (HY000): The value specified for generated column 'year' in table 't3' is not allowed.

當你資料量夠多時,可以用 EXPLAIN 看 MySQL 的 optimizer 會使用哪個 index (太少的時候會 table scan...):

mysql> EXPLAIN SELECT * FROM t3 WHERE month = 2 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: idx__month
          key: idx__month
      key_len: 5
          ref: const
         rows: 4
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

在這個例子裡用的欄位比較簡單,但如果在更複雜的案例裡面,應該會有更多地方可以發揮 (因為可以用 function 計算,這使得很多可能性跑出來),像是 Percona 的原文是以 application 沒辦法修改程式碼的前提下,可以在 ProxySQL 與 MySQL 端做出哪些改變讓效能變好。

應該是有不少情境可以用,再多想看看好了...

Amazon DynamoDB 的 Point-In-Time Recovery

Amazon DynamoDB 在 3/26 發出來的功能,以秒為單位的備份與還原機制:「New – Amazon DynamoDB Continuous Backups and Point-In-Time Recovery (PITR)」。

先打開這個功能:

打開後就會開始記錄,最多可以還原 35 天內的任何一個時間點的資料:

DynamoDB can back up your data with per-second granularity and restore to any single second from the time PITR was enabled up to the prior 35 days.

這時候就算改變資料或是刪除資料,實際上在系統內都是 Copy-on-write 操作,所以需要另外的空間,這部份會另外計價:

Pricing for continuous backups is detailed on the DynamoDB Pricing Pages. Pricing varies by region and is based on the current size of the table and indexes. For example, in US East (N. Virginia) you pay $0.20 per GB based on the size of the data and all local secondary indexes.

有這樣的功能通常是一開始設計時就有考慮 (讓底層的資料結構可以很方便的達成這樣的效果),現在只是把功能實作出來... 像 MySQL 之類的軟體就沒辦法弄成這樣 XDDD

最後有提到支援的地區,是用條列的而不是說所有有 Amazon DynamoDB 的區域都支援:

PITR is available in the US East (N. Virginia), US East (Ohio), US West (N. California), US West (Oregon), Asia Pacific (Tokyo), Asia Pacific (Seoul), Asia Pacific (Mumbai), Asia Pacific (Singapore), Asia Pacific (Sydney), Canada (Central), EU (Frankfurt), EU (Ireland), EU (London), and South America (Sao Paulo) Regions starting today.

比對一下,應該是巴黎與美國政府用的區域沒進去... 一個是去年年底開幕的區域,另一個是本來上新功能就偏慢的區域。

Percona 的人接受 AWS 的建議,重新測試了 Percona XtraDB Cluster 在 gp2 上的效能...

去年年底的時候 Percona 的人在 AWS 上測試 Percona XtraDB Cluster 的效能,尤其是針對底層應該選擇哪種 EBS 的部分給了一些建議。可以參考先前寫的「Percona 分析在 AWS 上跑 Percona XtraDB Cluster 的效能 (I/O bound)」這篇。

當時的建議是用 io1,雖然是比較貴,但對於效能比較好。

而後來 Percona 的人收到 AWS 工程師的建議,可以用另外一個方式,可以在 gp2 上拉出類似的效能,但成本會比 io1 低不少:「Percona XtraDB Cluster on Amazon GP2 Volumes」。

這個方式是利用 gp2 會依照空間大小,計算可用的 IOPS。在官方的文件裡是這樣描述 gp2 的效能 (IOPS):

General Purpose SSD (gp2) volumes offer cost-effective storage that is ideal for a broad range of workloads. These volumes deliver single-digit millisecond latencies and the ability to burst to 3,000 IOPS for extended periods of time. Between a minimum of 100 IOPS (at 33.33 GiB and below) and a maximum of 10,000 IOPS (at 3,334 GiB and above), baseline performance scales linearly at 3 IOPS per GiB of volume size. AWS designs gp2 volumes to deliver the provisioned performance 99% of the time. A gp2 volume can range in size from 1 GiB to 16 TiB.

在這個前提下,需要 10000 IOPS 的效能會需要 3.3TB 以上的空間,所以 Percona 就被 AWS 的工程師建議直接拉高空間重新測試:

After publishing our material, Amazon engineers pointed that we should try GP2 volumes with the size allocated to provide 10000 IOPS. If we allocated volumes with size 3.3 TiB or more, we should achieve 10000 IOPS.

首先是測出來的效能,可以看到沒有太大差異:

接下來就比較儲存成本,大約是 io1 版本的一半價錢:

如上面文件中提到的,gp1 不完全保證效能,但統計出來經常能夠提供出 3 IOPS/GB 的效能。而 io1 則是保證效能,不太需要擔心效能不穩定的問題。就是這個差異,反應到成本上面就有蠻大的差距。善用這點設計系統,應該會對整體成本有蠻大的幫助... (但對 latency 就未必了,尤其是 P99 之類的數值)

算是另外一種搞法讓大家可以考慮...

Archives