實作建立新的 Tablespace

上個星期發表的 PostgreSQL 的 Tablespace 只有文字說明。事實上，我曾想用圖來解說 Oracle 的 tablespace 的架構，順便也畫一張 PostgreSQL 的架構圖，但是人懶沒畫；不是啦，其實是我不太會用繪圖軟體，等我有空再慢慢把圖補起來。但是，如果實作一下在 PostgreSQL 建立一個新的 tablespace，然後 demo 一下是做得到的。所以我就在這幾天大致上把 64 bits 版本的 PC-BSD 7.0.2 的 VM 裝好後，試著模擬新增一顆硬碟，然後在新的硬碟裏建立一個 tablespace，讓大家參考一下，順便感覺一下在 PostgreSQL 下的 tablespace 是長得什麼樣子，以便將來考量以 tablespace 來提昇效能的參考。至於 PostgreSQL 的那兩個 default tablespace，到底被系統放在什麼地方？ 過幾天我再另文介紹，今天先饒了我，打字和抓圖打得好累！ X(
這篇文章採用的例子是使用 PC-BSD，也就是 FreeBSD 7.0。為了不讓主題 focus 在 FreeBSD 系統面上，所以我儘量跳過 FreeBSD 新增硬碟的步驟。有興趣的朋友可以參考 FreeBSD 手冊，或是王俊斌先生所著「FreeBSD 6 架設管理與應用」。
在文章開始前，我必須先列出要建立 tablespace 的六個要件：

1. 你必須是資料庫的 superuser。一般來說，PostgreSQL 的資料庫內第一個帳號，也就是 postgres 就是 superuser，你也可以建立一個新的，擁有 superuser 權限的帳號。
2. PostgreSQL 必須執行在支援 symbolic links 的作業系統。這也就是之前我說 Windows 版本的 PostgreSQL 恐怕不能使用 tablespace 的原因；但是由於有一個東西叫 Cygwin，所以這一條我還得找時間在 Windows 上試一下。
3. 要建立 tablespace 的目錄必須先存在，也就是說，PostgreSQL 本身不會幫你建立這個目錄，你必須在建立 tablespace 之前先把這個目錄建好。
4. 上述這個目錄必須是空的。這也就是我等一下不直接用 mount 點建立，而另外再建立一個子目錄的原因；因為在掛載點，作業系統常常會多擺一兩個小東西，我可不敢隨便砍它們，免得系統出現出乎我預料外的慘事。
5. 這個目錄的名稱長度必須小於 991 個字元。
6. 這個目錄的擁有者，要是執行 postmaster 或 pg_ctl 程序的那個系統帳號。比如，在 FreeBSD 就是 pgsql，而 Ubuntu/RHEL/Windows 就是 postgres。這一點很容易忘掉，等一下我會特別展示，一旦忘掉，系統會怎麼回應你的。

在開始前，我打算先加上一顆硬碟，讓模擬更真實。由於我是用 VMWare 測試，所以可以很輕鬆的新增一顆「假的」硬碟。事實上真實的系統上要新增硬碟也很簡單，只需要 $$ 多一點就夠了！ 在 VMWare 上新增硬碟，必須用到 Workstation 版本，方法如下：請到 VM 選單，找到最下面一項「Settings...」按下去，這時會出現一個「Virtual Machine Settings」對話方塊，如下圖。我這張圖是已經建立好一顆 2GB 的 SCSI 虛擬硬碟的狀況。

圖一

要新增硬碟，請先按下左半邊下方的「Add...」按鍵，這時候會出現如下圖一樣的「Add Hardware Wizard」，選 Hard Disk，按 Next>。

圖一之二

這時就請遵照 Wizard 指示，一步一步做，就可以很輕鬆的新增一個「SCSI 虛擬硬碟」。在這裏我就不多做說明了。

圖一之三

加好了以後，再來就是使用 FreeBSD 的工具程式把這顆「新硬碟」Fdisk、Label，也就是建立 partition。能夠的話，修改一下 /etc/fstab，把新硬碟加上，讓系統重新開機時就會自動 mount 上。像我的例子，新硬碟叫 /dev/da1，我建立了一個 label 是 /dev/da1s1d。在 FreeBSD，SCSI 硬碟叫 da?，而 ATA/SATA 硬碟叫 ad?。我把它 mount 在 /pgdata 這個掛載點上。記得我文章一開頭說的建立 tablespace 要注意的六點的第四點嗎？這裏看來在我建立新 partition 並且把它掛在 /pgdata 時，系統在這裏塞了一個叫 .snap 的檔案。我不知道它的作用是什麼？但是為了預防萬一，我寧願不去惹它，把它留下來，所以接著我決定在它下面建立一個目錄 /pgdata/cheeren 來放 tablespace；但是，這時請注意，目前這個目錄和 /pgdata 的擁有者是 root:wheel。還記得剛才提的那六點嗎？看起來最後的第六點違背了，這在後續的動作中會產生問題。我這裏是故意這樣做，因為我發現很多人都會不小心忘記第六點，而在這裏撞牆撞半天。😛

圖二

接著建立一個 tablespace，把它放在 /pgdata/cheeren：

圖三

圖四

接著......，哇啦哇啦！哈！記得嗎？/pgdata/cheeren 的擁有者是 root，所以 pgsql 當然沒有寫入的權限：

圖五

這時候該怎麼辦？切回到 SHELL，用 su 把自己的權限切成 root，然後把 /pgdata 和它下面的所有檔案和目錄的擁有者全部改一下：

圖六

這時再切回到剛才圖四的「新表空間」對話方塊，再按一次「確定」，這次應該可以順利建立了。再來就可以把一些物件存放在這個 tablespace 裏了。這裏我建立一個 database，就放在這個新的 tablespace 'cheeren' 裏。注意看圖八，新的 database 'cheeren' 我是把它建立在 'cheeren' 表空間，而不是 pg_default：

圖七

圖八

來，我們先來看看圖九，建立成功。

圖九

再來再回到主題來。
記得我在 PostgreSQL 的 Tablespace 這篇文章中有提到說，tablespace 是以 symbolic link 連結到新的硬碟上的目錄。喔，但是看來看去，我都只有在新的硬碟上動手腳，並沒有看到原來的 $PGDATA 下有什麼更動呀？別急，我們先看看我們剛才建立的東西的 OID，回想一下，我曾經在 PostgreSQL 如何組織資料 這篇文章中提到，PostgreSQL 會以 OID 為檔名，將物件以一個一個實體檔案儲存在檔案系統中。至於資料庫，則是以和 OID 同名的目錄名稱存在。而在資料庫中的所有物件，則存在該目錄下。那我們來看看我們自己建立的新 tablespace，PostgreSQL 是不是如我們所預期的儲存呢？先來看看下一張圖：

圖十

參考一下圖十，我先切換成 pgsql，然後用客戶端程式 psql 連進去 cheeren database。然後我想查看一下 PostgreSQL 的系統表格 pg_tablespace。這個系統表格裏，正是存著整個 PostgreSQL cluster 的所有 tablespace 的清單。在圖十我先看看這個表格的定義。接著我打算 tablespace 清單列出 OID (實際檔案名，在這裏是在 $PGDATA 下的 symbolic link 的檔名)、tablespace 名和 tablespace 座落位置，正如圖十一所示：

圖十一

在圖十一裏，我多做了一件無聊的事，在 tablespace 'cheeren' 裏建立一個表格名為 foo 的資料表。事實上我用不著加上後面的 tablespace 'cheeren' 特別註明，因為我目前就在 database 'cheeren' 裏，而 database 'cheeren' 就存在於 tablespace 'cheeren'；因此，這是多此一舉。無所謂，這樣定義得更清楚。接著我從系統資料表 pg_class 裏查詢 table 'foo' 的 OID，再從系統資料表 pg_database 裏查詢 database ' cheeren' 的 OID。請先來看看圖十二，先來看看在 $PGDATA 下是不是有一個 symbolic link 連到 /pgdata/cheeren？

圖十二

在 FreeBSD 上的 PostgreSQL 的 $PGDATA 是在 /usr/local/pgsql/data。我在 PostgreSQL 如何組織資料 這篇文章中曾說過，PostgreSQL 會把所有資料放在 $PGDATA 下。tablespace 這東西看起來這裏好像違反了我之前說的話，其實沒有。在圖十二中，我們可以看到，在 $PGDATA 下有一個子目錄 pg_tblspc，它裏面存放的，就是兩個預設的 tablespace 以外的 tablespace，而在這個子目錄裏，放的都只有 symbolic link。請看一下圖十二，目前 pg_tblspc 裏只有一個子目錄 16403，而 16403 就是 tablespace 'cheeren'，所以我們看到 16403 其實是一個 symbolic link，連結到 /pgdata/cheeren 這個實際目錄。剛才在圖十一，可以看到 tablespace 'cheeren' 的 OID 正是 16403！

圖十三

我們再來看看圖十三，/pgdata/cheeren 裏，除了標示建立該 tablspace 的 PostgreSQL 版本的 PG_VERSION 以外，只有一個目錄 16404。從圖十一的最後一段，我們可以知道 database 'cheeren' 的 OID 正是 16404，也就是說，這個子目錄正是 database 'cheeren' 實際在檔案系統中的位置。再往下看，由於目錄 16404 之下有很多屬於 database 'cheeren' 的系統物件，所以我只列出 16xxx 的檔案，注意一下，是不是有一個 16411？從圖十一我們可以知道這個空空的檔案正是 table 'foo'！
好了，介紹到這裏，我快打不下去了。希望這樣一個 demo 可以讓您了解 PostgreSQL 的 tablespace 實際運作的情況，將來在考慮用 tablespace 來提昇效能時可以比照建立。還有，等我打完這篇文章我才想起，事實上同一個 database 裏的資料表格或 index 可以存在不同的 tablespace 裏，方法就像我在圖十一那樣，在 create table 或 create index 時附加一個 tablespace 子句特別註明。這樣子就能把資料表格和索引放在不同的硬碟，來避開兩者的存取競爭，提昇效能。

---

2010-3-18 補充：就在我伺服器硬碟掛掉的前兩天，William Tsai 網友留了以下的兩篇留言。因為我看都還沒看到這兩篇留言，硬碟就掛了，所以我也還沒時間測試。但是我特在此將他這份很有價值的留言留下(一字不改)，請各位參考一下。待我驗證之後(無論結果如何)，尤其是有關 NTFS 的 Junction 的功能後，再來寫一篇文章報告。

週五, 02/12/2010 - 10:24 — william Tsai (未確認)

真是用心的好文啊

其實我也是用大象用很多年了
只是大部份的IT都仍選用Mysql為主
剛剛讀了您的PostgreSQL在 Linux/Unix下的 tablespace實作
源于我正好在測試windows 版的Postgresql 8.4版本 tableSapce管理
(其實我習慣用Linux.但這次被迫測試Windows)
因為我正在把Mysql 143Gb的data 移轉到Postgresql 上來測試大象的實力
但卻發現一個很不可思議的問題
postgresql裝在C:
所以我建了 一個新的在E: disk (size比較大) .要先建好table space的目錄 (e:\pg_big)CREATE TABLESPACE pg_big
OWNER XXXXX
LOCATION 'e:/pg_big';
這樣是ok的.也會build table 使用該tablespace 該有table 也會建立在另一顆disk .
本來還很開心
卻發現一個很恐怖的問題
您有提到Postgresql 的資枓都base on $PG_DATA
因為window沒有symbolic links
沒錯~~~~
但它的確可以work 在另一顆disk E:上
更可怕的是它居然在 c: 的$PG_DATA 中 build 出 一模一樣的資料
而且 做 DML 處理時 兩塊的資料均會同步異動
形成一種另類的 mirror disk
PostgreSQL的tablespace 在windows底下果然是個很令人失望的不滿意的地方
而且沒法子做到像oracle 那種tablespace
(一整個data file tablespace 有利于減少 inode 或是 windows 資料碎塊的問題 , 我做完 大量insert 後 disk io 因碎塊問題 performace大減)
連Mysql 都可以有連似的結構了 postgresql 居然沒有
與您分享

週五, 02/12/2010 - 11:25 — william Tsai (未確認)

上篇補註

後來發現那個不是Mirror
如果你是用NTFS來玩
現在NTFS有新玩具我發現在做e : tablespace
postgresql 會在 $PG_DATA 中的tablespace 目錄建立一樣的目錄並且...
做了 類似 symbolic links 的功能
在NTFS中
有了新的結構 junction
http://en.wikipedia.org/wiki/NTFS_junction_point
所以在$PG_DATA 內建立的是對應其它disk 的 junction link
用來實現與 linux/unix 一樣的 symbolic links
與您分享囉.....

PostgreSQL 的 Tablespace

這一篇文章原本是三個月前就開始寫，原先預定是剛好九月底時出刊，但是在發展的過程，突然覺得似乎應該先介紹一下 PostgreSQL 如何來放置、維護資料，再來聊聊 Tablespace 可能比較適當，接著生了一場重病再加上發神經買了一台新電腦，所以就放到現在才發表。
PostgreSQL 在進入 8.0 版時，整套系統的架構大變，新增了不少功能和架構， Tablespace 就是其中一項。當我第一次在 pgAdmin III 上看到 Tablespace 這個字眼出現時，我是非常興奮的，因為要在較大型的資料庫中，才會有這種概念。尤其是之前有 Oracle 的實作經驗，所以看到熟悉的字眼出現時，備感親切，而內心對它充滿期待。但是在進一步深入研究後，就有如被潑了一桶冷水一樣，PostgreSQL 的 Tablespace 和 Oracle 的 Tablespace 功能上差距還不小，實作上更是不一樣。
首先來說明一下，什麼是 Tablespace？Tablespace 一般都翻譯成表格空間。而表格空間，按照手冊上的定義，是整個系統的資料主體。其實呢，用白話一點的說法，Tablespace 是實體資料儲存的基本單位。以官方的說法來說，Tablespace，也就是表格空間，當初設計的目的是為了 DBMS 整體調校之用。這個目的和 Oracle 的 Tablespace 的設計目的很像，但是實作上卻是有不少差距。
在作業系統層面，Tablespace 是以目錄的形式存在檔案系統中。而所有存放在該 Tablespace 的物件，則是以子目錄或檔案的形式儲存在硬碟中。還記得前一陣子我所發表的 PostgreSQL 如何組織資料 的內容吧！PostgreSQL 把它所有的物件都以目錄和檔案的形式，用物件的 OID 為識別，分別儲存起來。Tablespace 身為 Databses、Tables 等等物件的 container，當然一定也是以類似的方式儲存在檔案系統當中。不同的是，除了預設的兩個 Tablespaces 以外，其餘自訂的 Tablespace 在檔案系統中是以 symbolic links 的形式存在的。就是因為它在檔案系統中，是以 symbolic links 的形式存在，因此，Windows 版本的 PostgreSQL，由於作業系統的功能限制，「理論上」使用者無法建立自己的 tablespace。我不清楚 PostgreSQL Win32 版是否設計了一個新的方式，能夠讓 Windows 2003/2008 打破這個理論上的藩籬。但是我確定在 Windows XP/Vista 上，很不幸的，可說是「幾乎」沒有可能性，讓使用者使用這部份的功能(或許加裝 Cygwin 能夠解決，但是這一點我還要測試一下)。
至於為什麼 Tablespace 要以 Symbolic Links 形式存在於檔案系統中，要知道原因，我想先從介紹 Oracle 的架構說起。Oracle 的資料物件，並不像 PostgreSQL 一樣，是以各別的檔案儲存起來的。Oracle 的 DBMS 底層是一堆黑盒子。Oracle 的底層是由一堆 Datafiles 和 Control files 組合而成。資料物件全部統一儲存在 Datafiles 中，而儲存的方式和細部，由 Control files 的定義來設定。而 Datafile 的內部儲存方式，則是一個黑盒子。也由於還要搭配 Control files 才能存取資料，所以你無法用簡單而有效率的方式來備份、維護資料庫，而要去準備一堆認證課程，用一堆工具；不管是 Oracle 的或第三方的；才能好好維護 Oracle 中的資料。而 Tablespace 在 Oracle 當中，雖然也是 DBMS 的最基本的 container，但是它卻是由一堆 Datafiles 和 Control files 組合而成，自成一個戰鬥單位。所以當你要做系統調校時，你可以讓該 Tablespace 所屬 Datafiles 和 Control files 分別儲存在不同的硬碟中，或是其它的安排。在這些實體儲存單位的妥善安排，來提高系統的效能。再來是當原本的儲存空間不足的時候，你也可以再找一顆新的硬碟來掛上，再在這顆新硬碟中新增新的 Datafiles，這樣就可以輕鬆的解決儲存空間不足的問題。當然，Oracle 如何把資料分散在這些 Datafiles 中，手冊故意寫得很神祕，感覺上 DBMS 會很神奇的幫你找出最有效率的儲存方式來安排在這些 Datfiles 當中，但是，who knows？sales talk 吧！
至於 PostgreSQL，我在前一篇 PostgreSQL 如何組織資料 已經展示了，PostgreSQL 是以各別的目錄和檔案方式儲存在實體儲存媒介上。這個方法的優點是開放、透明，缺點就是吃掉不少 inode(以 Unix 來說)。由於每個物件都是以各別檔案存在，所以就以邏輯上而言，身為它們的 container，Tablespace 必然要以目錄的形式存在。但是，要如何做到像 Oracle 一樣，如何使用 Tablespace 把資料物件分散到另一顆硬碟或以 NFS 掛上另一台機器的檔案系統？這時就出了邏輯上的問題了。在檔寀系統中，同一台機器中，在目錄以上已經沒有更大的 container 單位了。那麼如果要在 PostgreSQL 上實作出類似的功能，恐怕對 Tablespace 的定義就要有所改變。因此，在 PostgreSQL 上，Tablespace 不但是邏輯的存取容器，也是基本的實體儲存容器；不像 Oracle，Tablespace 只是邏輯上的容器，實體則是那些 Datafiles 和 Control files。而要做到跨硬碟或 NFS mount，那麼就用到了 Unix 的 Symbolic links，因為用 Symbolic links，可以在 $PGDATA 下留下一條連結，連結到另一顆硬碟或另一台機器分享出來的空間。這樣做又簡單、又有效率。
但是問題就出在跨到 Windows 下時，由於 Windows 沒有 Symbolic links 的概念，只有一個很難用的「捷徑」，這下子問題就來了，因為「捷徑」並不等於 symbolic links，兩者的使用和定義完全不同。把 Tablespace 轉移到 Windows 的實作上，看樣子還有不少努力的空間。
接著，我們來聊聊那兩個預設的 Tablespace：pg_global 和 pg_default。官方文件中對於這兩個 Tablespace 的用途是這樣說的：「PostgreSQL 使用 pg_global 來存放和系統有關的物件；使用 pg_default 表格空間來存放使用者建立的資料庫物件」。也就是說，在 Oracle 當中，每個 Tablespace 的 Control files 當中的內容，在 PostgreSQL 是由一個專屬的 Tablespace pg_global 來統一管理；而預設是你所建立的所有物件會存放在 pg_default 這個表格空間；當然如果你指定存放在你所建立的自訂表格空間，那麼物件就會存到你指定的表格空間裏；若是沒指定，PostgreSQL 就當作你打算使用預設位置，所以就會存在 pg_default。
在這篇文章的最後，我們來看看在官方的文件定義上，有關 Tablespace 的文字。在中文化文件中，有下列幾行文字：

表格空間決定著DBMS整體效能的重點調校之一

，還有：

在以下的情況 DBA 就有必要考量 Tablespace 的分割與配置：
資料間具有競爭系統效能.
大型物件資料表與小型物件資料表應該分別在不同的 Tablespace.
分離資料和索引的存放空間是不錯的效能強化

以上是中國 PostgreSQL 社群翻的「葡萄牙文式」的中文化文件的內容。只要是台灣人，大概都懂得我在說些什麼。其實這段文字主要是在說明為什麼要有表格空間。PostgreSQL 社群加入表格空間這個物件的目的，和 Oracle 是一樣的，主要目的在於效能的調校。所以當發生單一硬碟中的 transaction logs 和資料本身存取的存取競爭時(這東西要說明還要再寫一篇文章！)，你就必須考慮把資料物件存到另一顆硬碟上，因為存取競爭勢必降低效能。另外，大型物件和小型物件並存在一個表格空間時，由於硬碟存取資料是「隨機」的，所以小型物件勢必淹沒在大型物件中，這樣小型物件的存取效能也一定會降低。再來就是資料本身和索引也是會產生類似存取競爭的情況，所以一個存取量大的資料表格，或許要考慮把它的索引放到另一個硬碟中。而要把物件存到另一個硬碟中的方法，就是採用 tablespace。
至於如何建立 pg_global 和 pg_default 之外的 tablespace，以及這兩個預設的 tablespace 又存在哪裏呢？就留在下一篇文章中介紹，本文就先在此打住。