MySQL InnoDB 的 OPTIMIZE TABLE 的 Lock

Backend Twhttps://www.facebook.com/groups/616369245163622/posts/2467225396744655/ 這邊看到:

先大概回答一下假設,DELETE 後的空間是可以被同一個表格重複使用的,所以應該是還好,不過離峰時間跑一下 OPTIMIZE TABLE 也沒什麼關係就是了。

裡面提到的「13.7.2.4 OPTIMIZE TABLE Statement」(MySQL 5.7 文件) 以及「13.7.2.4 OPTIMIZE TABLE Statement」(MySQL 5.6 文件) 都有講到目前比較新的版本都已經是 Online DDL 了:(這邊抓 5.6 的文件,有支援的版本資訊)

Prior to Mysql 5.6.17, OPTIMIZE TABLE does not use online DDL. Consequently, concurrent DML (INSERT, UPDATE, DELETE) is not permitted on a table while OPTIMIZE TABLE is running, and secondary indexes are not created as efficiently.

As of MySQL 5.6.17, OPTIMIZE TABLE uses online DDL for regular and partitioned InnoDB tables, which reduces downtime for concurrent DML operations. The table rebuild triggered by OPTIMIZE TABLE is completed in place. An exclusive table lock is only taken briefly during the prepare phase and the commit phase of the operation. During the prepare phase, metadata is updated and an intermediate table is created. During the commit phase, table metadata changes are committed.

文件上有提到會有一小段 lock 的時間,不過一般來說應該不會造成太大問題。

這邊要講的是早期的經典工具 pt-online-schema-change (pt-osc),這是使用 TRIGGER-based 的方式在跑,他的範例就直接提供了一個不需要 Online DDL 支援的版本:

Change sakila.actor to InnoDB, effectively performing OPTIMIZE TABLE in a non-blocking fashion because it is already an InnoDB table:

pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor

這在早期的時候還蠻常被拿出來用的,如果還在維護一些舊系統的話還蠻推薦的...

SQLite 目前在規劃的 Strict Table,以及我從來不知道原來可以這樣惡搞...

Hacker News Daily 上看到「STRICT Tables」這篇,在講 SQLite 目前在規劃 strict table,對應的討論可以參考「Strict Tables – Column type constraints in SQLite - Draft (sqlite.org)」這邊。

我在 draft 文件開頭看到這個驚人的事實:轉型失敗的時候會直接寫進去,不是錯誤或是 0 或是 NULL 之類的值 XDDD

For example, if a table column has a type of "INTEGER", then SQLite tries to convert anything inserted into that column into an integer. So an attempt to insert the string '123' results in an integer 123 being inserted. But if the content cannot be losslessly converted into an integer, for example if the input is 'xyz', then the original string is inserted instead. See the Datatypes In SQLite document for additional information.

實際上測試建了一個表格測試:

SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> CREATE TABLE a (id INTEGER PRIMARY KEY NOT NULL, col1 INTEGER);
sqlite> INSERT INTO a (id, col1) VALUES (1, 'a');
sqlite> SELECT * FROM a;
id          col1      
----------  ----------
1           a         

我果然跟 SQLite 不熟...

Amazon VPC 允許直接把整個網段配到某台 EC2 Instance 上了

看到「Amazon Virtual Private Cloud (VPC) customers can now assign IP prefixes to their EC2 instances」這邊的消息,VPC 可以把整個網段配到某台 EC2 Instance 上了。

之前也有其他方法可以做到類似的事情:

  • 用 VPC 提供的 Routing Table 把網段指到某台 EC2 的機器上。
  • 把這台 EC2 機器的「Enable Source/Destination Check」關閉。

目前就是用這個方法搞定 VPN server 的:我們希望機器連上 VPN 後拿到 10.x.x.x 的 IP address,而且可以被 VPC 內直接存取,而不要被 NAT 掉。

好像該開張票轉移過去...

GitHub 對 Issues 增加了一些新功能

GitHub 推出了 Issues 的 beta program:「GitHub Issues · Project planning for developers」。

目前列出來的新功能裡,Board 與 Table 呈現方式 (Bored of boards? Switch to tables.),以及 Subticket 的功能 (Break issues into actionable tasks),這兩個算是在 project management 裡面很重要的功能,不過整體還是很陽春,只能說補上了一些重要的元素...

另外這次的 beta program 算是宣示 GitHub 有投入資源在改善 project management 這部份的功能,也許之後也許會有其他新功能繼續推出?

三不五時被提到的 LackRack

又在 Hacker News Daily 上看到 LackRack 了,這是 IKEA 的 LACK 系列家具,因為尺寸很剛好,有人就 hack 這個邊桌拿來當作機櫃用,甚至還有仿 IKEA 的組裝文件:「Manual」。

另外還可以疊 (不過要注意重量,畢竟這還是木頭):

這張邊桌在台灣也有賣,有四種不同的外觀可以選:「LACK 邊桌, 白色」、「LACK 邊桌, 黑色」、「LACK 邊桌, 高亮面 白色」與「LACK 邊桌, 黑棕色」,尺寸都是 55cmx55cmx45cm。

OpenBGPD 接 AWS Direct Connect 時只讓單區路由的方法

算是繼上篇「用 pfSense 接 AWS Direct Connect (Public VIF) 的方式」的改善,上篇的方法設定完後預設會是全部都會 routing 進來。也就是說,如果你接到新加坡區,美東 routing 也會進來。

這可能是你要的,但也可能不是你要的,所以找了一下方法,在 AWS 的文件裡面有提到可以透過 BGP community 控制這些 routing:「Routing policies and BGP communities」。

第一個方向是從 pfSense 送出去的封包,這個要過濾從 BGP 送進來的 routing table:

AWS Direct Connect applies the following BGP communities to its advertised routes[.]

把:

allow from 1.2.3.4

改成:

allow from 1.2.3.4 community 7224:8100

另外一個是讓 AWS 不要把我們的 network 送到其他區,這是在 network 上加上 BGP community tag:

You can apply BGP community tags on the public prefixes that you advertise to Amazon to indicate how far to propagate your prefixes in the Amazon network, for the local AWS Region only, all Regions within a continent, or all public Regions.

把本來的:

network 1.2.3.4/30

變成:

network 1.2.3.4/30 set community 7224:9100

先這樣搞,用 mtr 看了一下應該沒錯...

讓 Python 輸出變豐富的 Rich

Hacker News 上看到的 Python 專案,讓 terminal 輸出變得更好看:「Rich is a Python library for rich text and beautiful formatting in the terminal.」。

看到當下吸引我的地方在於表格:

from rich.console import Console
from rich.table import Column, Table

console = Console()

table = Table(show_header=True, header_style="bold magenta")
table.add_column("Date", style="dim", width=12)
table.add_column("Title")
table.add_column("Production Budget", justify="right")
table.add_column("Box Office", justify="right")
table.add_row(
    "Dev 20, 2019", "Star Wars: The Rise of Skywalker", "$275,000,000", "$375,126,118"
)
table.add_row(
    "May 25, 2018",
    "[red]Solo[/red]: A Star Wars Story",
    "$275,000,000",
    "$393,151,347",
)
table.add_row(
    "Dec 15, 2017",
    "Star Wars Ep. VIII: The Last Jedi",
    "$262,000,000",
    "[bold]$1,332,539,889[/bold]",
)

console.print(table)

輸出長這樣:

另外還有不少功能也不錯,會讓畫面豐富不少。

Vim 的 virtualedit 與 GNU Make 內的 .PHONY

在「Writing a Book with Pandoc, Make, and Vim」這邊看到作者在講他怎麼用 Pandoc + GNU Make + Vim 寫書,不過我這邊看到兩個有趣的東西 (標題提到的那兩個),拉出來寫一下...

一個是 Vim 的 set virtualedit=all,可以不受限制的移動,等到實際編輯時再產生出對應需要的空白,這對於畫表格會方便不少:

另外一個是 GNU Make 的用法,平常我們都是在 .PHONY 裡指定實際上不會存在的 target:

.PHONY: clean
clean:
        rm -rf ./output

這邊作者提供的方式是產生一個叫做 phony 的 target,然後就不需要在 .PHONY 裡條列,而是各自在自己的 target 裡面引用 phony

.PHONY: phony
clean: phony
        rm -rf ./output

不過作者有提到效能問題:

Note that this trick can slow down huge Makefiles.

另外作者又提醒我 draw.io 這個好用的工具,之前用過幾次後就忘記了...

Amazon Redshift 會自動在背景重新排序資料以增加效能

Amazon Redshift 的新功能,會自動在背景重新排序資料以增加效能:「Amazon Redshift introduces Automatic Table Sort, an automated alternative to Vacuum Sort」。

版本要到更新到 1.0.11118,然後預設就會打開:

This feature is available in Redshift 1.0.11118 and later.

Automatic table sort is now enabled by default on Redshift tables where a sort key is specified.

重新排序的運算會在背景處理,另外帶一些行為學習分析:

Redshift runs the sorting in the background and re-organizes the data in tables to maintain sort order and provide optimal performance. This operation does not interrupt query processing and reduces the compute resources required by operating only on frequently accessed blocks of data. It prioritizes which blocks of table to sort by analyzing query patterns using machine learning.

算是丟著讓他跑就好的東西,升級上去後可以看一下 CloudWatch 的報告,這邊沒有特別講應該是還好... XD

在 SQL 裡面避免大量刪除資料的方式

看到 Percona 的「An Overview of Sharding in PostgreSQL and How it Relates to MongoDB’s」這篇,雖然是在講 PostgreSQL 上的 sharding (以及 partition),突然想到好像沒寫過要怎麼避免大量刪除資料的操作...

一個常見的情境是,想要讓某個表格只保留這一個月的資料,所以每個月開頭都會跑一隻 cron job 負責刪掉上個月的資料,像是 DELETE FROM xxx WHERE timestamp < yyy; 這樣的指令。

這個方式無論是在 PostgreSQL 或是 MySQL 都需要很多時間與 I/O 資源,而透過 partition 將不同時間區段切開到不同的表格,再用 TRUNCATE 直接清空表格剛好可以解這樣的問題。

Percona 的文章裡說了一些 PostgreSQL 的歷史與目前的進展。

在 PostgreSQL 9 或更早以前的版本,一個常見的作法是透過 table inheritance 實做 partition,然後用再用 function 實做 INSERT

CREATE TABLE temperature (
  id BIGSERIAL PRIMARY KEY NOT NULL,
  city_id INT NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  temp DECIMAL(5,2) NOT NULL
);

CREATE TABLE temperature_201901 (CHECK (timestamp >= DATE '2019-01-01' AND timestamp <= DATE '2019-01-31')) INHERITS (temperature);
CREATE TABLE temperature_201902 (CHECK (timestamp >= DATE '2019-02-01' AND timestamp <= DATE '2019-02-28')) INHERITS (temperature);
CREATE TABLE temperature_201903 (CHECK (timestamp >= DATE '2019-03-01' AND timestamp <= DATE '2019-03-31')) INHERITS (temperature);

CREATE OR REPLACE FUNCTION temperature_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.timestamp >= DATE '2019-01-01' AND NEW.timestamp <= DATE '2019-01-31' ) THEN INSERT INTO temperature_201901 VALUES (NEW.*);
    ELSIF ( NEW.timestamp >= DATE '2019-02-01' AND NEW.timestamp <= DATE '2019-02-28' ) THEN INSERT INTO temperature_201902 VALUES (NEW.*);
    ELSIF ( NEW.timestamp >= DATE '2019-03-01' AND NEW.timestamp <= DATE '2019-03-31' ) THEN INSERT INTO temperature_201903 VALUES (NEW.*);
    ELSE RAISE EXCEPTION 'Date out of range!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

在 PostgreSQL 10 之後,就直接支援一些與 partition 相關的設計,像是這樣:

CREATE TABLE temperature (
  id BIGSERIAL NOT NULL,
  city_id INT NOT NULL,
  timestamp TIMESTAMP NOT NULL,
  temp DECIMAL(5,2) NOT NULL
) PARTITION BY RANGE (timestamp);

CREATE TABLE temperature_201901 PARTITION OF temperature FOR VALUES FROM ('2019-01-01') TO ('2019-02-01');
CREATE TABLE temperature_201902 PARTITION OF temperature FOR VALUES FROM ('2019-02-01') TO ('2019-03-01');
CREATE TABLE temperature_201903 PARTITION OF temperature FOR VALUES FROM ('2019-03-01') TO ('2019-04-01');

雖然還是有些限制,但可以看出比起以前簡單不少。

而有了 partition 後,文章的後續就在討論這跟 MongoDB 的 sharding 有什麼關係,但這就不是我關注的事情了...