MySQL 對 VARCHAR 的 Index 空間佔用的問題...

之前不知道從哪邊學到錯的東西... 實驗後發現搞錯了。對 MySQL 的 VARCHAR 欄位下 index 所實際佔用的空間仍是實際大小,而非最大長度。

測試方法是建立表格,schema 是 CREATE TABLE test (id INT UNSIGNED PRIMARY AUTO_INCREMENT, data VARCHAR(255)) ENGINE=InnoDB;,在 inndo_per_file 打開的情況下測試。

這是 1M rows,其中 data 都是 "a",這是 OPTIMIZE TABLE 後的結果:(以下每個都有 OPTIMIZE TABLE)

-rw-rw---- 1 mysql mysql     8586 Jul 30 22:42 test.frm
-rw-rw---- 1 mysql mysql 37748736 Jul 30 22:42 test.ibd

這是 ADD INDEX (data) 後的結果:

-rw-rw---- 1 mysql mysql     8586 Jul 30 22:46 test.frm
-rw-rw---- 1 mysql mysql 50331648 Jul 30 22:46 test.ibd

一樣是 1M rows,但 data 都是 "a" * 100 (一百個 a) 的結果:

-rw-rw---- 1 mysql mysql      8586 Jul 30 23:14 test.frm
-rw-rw---- 1 mysql mysql 146800640 Jul 30 23:15 test.ibd

這是 ADD INDEX (data) 後的結果:

-rw-rw---- 1 mysql mysql      8586 Jul 30 23:21 test.frm
-rw-rw---- 1 mysql mysql 260046848 Jul 30 23:23 test.ibd

實驗可以看出來 MySQL 的確是依照內容的實際長度索引,而非用欄位的最大長度做。

PostgreSQL 筆記...

純粹是筆記...

對於架設 server 的文件可以參考 Ubuntu 這份「PostgreSQL - Community Ubuntu Documentation」,雖然 Debian 官方也有一份「PostgreSql - Debian Wiki」,不過沒講到遠端這塊...

設定的部份:

  • 要讓遠端可以存取有兩個地方要開,一個是 postgresql.conflisten_addresses 改成 "*",另外一個是增加 pg_hba.conf 遠端連線的權限。
  • CREATE USER test WITH PASSWORD 'test_password'; 以及 CREATE DATABASE test WITH OWNER = test; 把基本的東西建好。

然後把 firewall 打開,接下來就可以從其他台連進去了。

Percona 的「Advanced MySQL Query Tuning」...

先前在「Percona 要講進階的 MySQL Query Tuning...」提到 Percona 所辦的 Webniar「Advanced MySQL Query Tuning」的投影片放出來了:「Advanced MySQL Query Tuning」。

這份內容需要 B+Tree 操作的背景知識才能了解。裡面講了很多 GROUP BYORDER BY 混用時的 index 技巧,以及各種奇怪的 hack 方式。

內容很有用,能吸收多少就看個人造化 :p

Percona 的 Crash-resistant replication

前幾天 Percona 寫了篇文章說明自家專有的 Crash-resistant replication (用在 Percona Server 5.1 與 5.5):「Crash-resistant replication: How to avoid MySQL replication errors」。

這是 async replication 用在 slave server crash 時的保護機制。

當 slave 更新資料後,會更新 relay log 寫下「目前 apply 到哪個位置」(預設值是 relay-log.info),也就可以依照這個資訊計算出 replication lag 的時間。在 mytop 裡看到的 Delay 欄位就是由此算出來的。

但當 MySQL 寫入後,但 relay-log.info 還沒更新時當掉,會造成下次啟動時重複 apply 同一筆資料。

而 Crash-resistant replication 就是把這個資訊寫到 transaction 內,避免這個問題。

也因此這個功能只有 InnoDB 類的 Engine 才有用,MyISAM 仍然是不受 Crash-resistant replication 保護的。

要打開這個功能也很簡單,只要 my.cnf 設起來就好了,設定說明可以參考原文。

MySQL 的 SQL Query...

Percona 的 blog 上跑出一篇「What kind of queries are bad for MySQL?」,分析哪些 SQL query 對 MySQL 的效能不好...

內容相當偏激,然後在 comment 引起討論... (文章作者反而就跑去休息了)

舉例來說,他認為只要有 JOIN 就算是 bad query。作者舉了這樣的 SQL query 為例:

SELECT t2.value FROM t2 JOIN t1 ON (t1.id=t2.tid) WHERE t1.orderdate=NOW()

應該要被展開成:

SELECT t2.value FROM t2 WHERE t2.orderdate=NOW()

看得出來有重度 denormalize 與 cache 症狀... XD

Percona 要講進階的 MySQL Query Tuning...

看到「MySQL Webinar: Advanced Query Tuning」這個 Webinar,要講的主題看起來不賴:

1. GROUP BY and ORDER BY optimization
2. MySQL temporary tables and filesort
3. Using covered indexes to optimize your queries
4. Loose and tight index scans in MySQL

事後花時間來看投影片應該就夠了 :p

MySQL subquery 的限制...

官方文件參考 MySQL 5.5 的「Restrictions on Subqueries」這篇。

直接拿官方的範例:

SELECT * FROM t1 WHERE s1 IN (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1);

這會出現 subquery 不支援 LIMIT 的錯誤訊息:

ERROR 1235 (42000): This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

如果試著用 Google 找解法,會找到用 temporary table 解決的方法:

SELECT * FROM t1 WHERE s1 IN (SELECT * FROM (SELECT s2 FROM t2 ORDER BY s1 LIMIT 1) AS t);

這個方法是可以用啦,但還是讓人很囧啊 XD

另外一個 subquery 對效能的影響是 SQL-92 定義 WHERE 裡 subquery 的行為:(參考 sql1992.txt)

2) Each <subquery> in the <search condition> is effectively executed for each row of T and the results used in the application of the <search condition> to the given row of T. If any executed <subquery> contains an outer reference to a column of T, then the reference is to the value of that column in the given row of T.

因為如此,並不是想像中「先算完 subquery 再拿結果算前面」...

在「MySQL Limitations Part 3: Subqueries」也有一些討論可以看 (在 comment 裡),雖然文章有點舊了... 把這部份 subquery 的結果自己拉出來組 SQL query 可能會比較快。

大陸網站「下廚房」對資料庫事故文章的神回覆...

前陣子看到「下厨房6月26日数据丢失事故总结」這篇文章,本來也就看看就算了... 結果剛剛在 Facebook 上看到有人說有神回覆... 趕快回去看 XDDD

這回覆... 太神啦 XDDD

這資料到底能不能用呢... XDDD

配合 Percona Xtrabackup 的新功能以及對 Percona XtraDB Cluster 初始化的速度...

在「2 new features added to Percona XtraDB Cluster (PXC) since 5.5.31」看到 Percona Xtrabackup 引入新功能後,在 Percona XtraDB Cluster 就能使用這些新功能加快初始化的速度。

首先是 bootstrap-pxc 這個參數:

# use this...
/etc/init.d/mysql bootstrap-pxc
# or...
service mysql bootstrap-pxc

當第一次啟動時需要對 my.cnf 的 hack (啟動完成後就可以改回來) 現在可以在參數直接處理。

另外是對 xbstream 的支援,這聽一陣子了,這邊就不講怎麼用了,原文講得很清楚。不過目前測出來的效果不怎樣啊:

文章作者是說對於小資料沒差,晚點再來看看有沒有對大資料的 benchmark...