[InnoDB] 要回刪除table資料後未被釋放的空間

資料庫

最近發生公司的測試環境硬碟容量不足,導致MySQL寫入紀錄失敗。本以為是開發時不小心讓app server寫太多log導致,所以把年代久遠的log file清一清後就沒理它了,沒想到過幾天又出現硬碟容量不足的情況。繼續追查後發現原來MySQL也很肥大,於是也把一些不重要的免洗資料也砍一砍。

奇怪的是,砍完後硬碟使用率竟然沒有下降的跡象,原來這跟DB底層的運作有些關係。當我們下了DELETE FROM table WHERE ...語句後,DB其實只把這些紀錄的存檔註記刪除,並不會真的釋放這些空間還給系統,而是等待下次新增資料時,直接蓋掉舊的資料所佔的空間。

這樣做的好處是可以直接地節省硬碟I/O開銷,想想當我們刪除一筆記錄時,DB把空間還給系統,然後下一筆操作是個INSERT,DB又去把剛剛釋放的空間要回來,這樣在低速裝置一來一往的reclaim過程對要求高效的DB來說不值得。

可是面對今天只是測試環境,被分配到的硬體已經是低配了,我就是想要回那些資料被刪除的空間挪作他用怎麼辦呢。這時,可以執行以下語法:

OPTIMIZE TABLE my_huge_table;

[MySQL / MariaDB] 使用Slow Query Log來偵錯

資料庫

當服務異常緩慢,想要debug有沒有可能是哪些SQL拖慢了整體速度時,可以使用MySQL或MariaDB內建的Slow Query Log將執行很久的SQL語句給記錄下來。

啟用slow query log的機制

> SET GLOBAL slow_query_log = 'ON';
Query OK, 0 rows affected (0.000 sec)

(此篇直接用SET GLOBAL VARIABLES的方式設定的變數在MySQL重新啟動後會回復預設值,如果想要永久修改必須帶在啟動參數或修改設定檔)

設定會被當成slow query的門檻值 (單位: 秒)

> SET GLOBAL long_query_time = 5;
Query OK, 0 rows affected (0.000 sec)

設定slow query log機制的log儲存方式

> SET GLOBAL log_output = 'FILE';
Query OK, 0 rows affected (0.000 sec)

查看log存檔位置

> SHOW VARIABLES LIKE 'slow_query_log_file';
+---------------------+-----------------+
| Variable_name       | Value           |
+---------------------+-----------------+
| slow_query_log_file | ubuntu-slow.log |
+---------------------+-----------------+
1 row in set (0.001 sec)

好了以後,我們來測試看看當發生slow query時有沒有被資料庫正確記錄下來

> SELECT SLEEP(6);
+----------+
| SLEEP(6) |
+----------+
|        0 |
+----------+
1 row in set (6.000 sec)

打開slow query log的存檔 /var/lib/mysql/ubuntu-slow.log (根據作業系統每個人路徑不一定跟此篇一樣),我們可以看到剛剛的slow query被正確的記錄了:)

...
# Time: 210615 17:56:55
# User@Host: root[root] @ localhost []
# Thread_id: 11694  Schema:   QC_hit: No
# Query_time: 6.000225  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 0
# Rows_affected: 0  Bytes_sent: 63
SET timestamp=1652003815;
SELECT SLEEP(6);

最後偵錯完畢時可以將它關閉

> SET GLOBAL slow_query_log = 'OFF';
Query OK, 0 rows affected (0.000 sec)

[Git瘦身] 使用cherry-pick挑出並保留只有master的節點

Git版本控制

當一個專案運行了幾年後Git逐漸開始肥大,因為它記錄了每個分支的每個commit歷史。但對於已經併進master上線運行好幾年的code來說,有沒有辦法只保留master的hotfix commit及merge記錄就好,將其他支幹的commit都拿掉,幫Git瘦身呢?

(這是我主管某天突然丟出的疑問)

於是在我回去研究了一下後,發現可以將這個問題拆成兩個步驟解決。

  • 列出所有master的commit (一般節點及合併的節點)
  • 用cherry-pick的方式挑出剛剛列出的commit

為了模擬這樣的情境,我們開一個新的repository,並隨意commit一下,製造一些偽開發記錄,如下圖。反白的部份是我們希望最後cherry-pick出來保留的節點。

 

[MySQL / MariaDB] 取得每個group的前N筆資料

SQL資料庫

最近在工作上為了產報表,需要寫sql去撈每個group的top N筆紀錄。研究了一會兒後最後用了以下的解法。

假設今天有個payment的資料表記錄了每筆交易所涉及的用戶、幣別、金額。

交易id(id) 用戶id(uid) 交易幣別(currency) 金額(amount)
1 11 NTD 800
2 6 NTD 590
3 7 NTD 1020
4 55 USD 24
5 33 USD 27
6 2 USD 25
7 13 JPY 3870
8 10 JPY 2980
9 41 JPY 3780

我們想要取每個幣別的前2筆最大金額的交易記錄,我們可以這樣寫:

SELECT id, uid, currency, amount
FROM (
  SELECT id, uid, currency, amount,
    @counter := IF(@current_currency = currency, @counter + 1, 1) AS counter,
    @current_currency := currency AS current_currency
  FROM payment
  ORDER BY currency, amount DESC
) ordered_result
WHERE counter <= 2;

FFmpeg剪裁(trim)影片指令

Linux、Mac應用程式

騎車出遊時我幾乎都會戴GoPro在帽子上充當行車記錄器。回家後常常我只想要擷取完整影片的其中一部份,這時候開Premiere等軟體trim完再存檔就感覺有點大材小用了。而且通常trim完存檔時剪輯軟體又包含了轉檔的步驟,這就需要一些等待以及會讓電腦起飛的時間。

利用FFmpeg這個CLI的小工具我們可以只單純做剪裁(trim),而且由於不包含轉檔的動作,所以執行速度很快,電腦也不會起飛。

 

兩種用法

給定 [起始時間] 與 [結束時間] 擷取

ffmpeg -i input.mp4 -ss 01:19:27 -to 01:30:51 -c:v copy -c:a copy output.mp4

給定 [起始時間] 與 [持續時間] 擷取

ffmpeg -i input.mp4 -ss 00:01:10 -t 00:01:05 -c:v copy -c:a copy output.mp4