PostgreSQL 應用程序設計的效能要訣(二)

原文作者簡介:

Josh Berkus 在 2002年加入 PostgreSQL 的核心開發成員. 現在他在 Sun Microsystems 的開放源碼資料庫團隊中擔任領導者.
擁有 11 年的資料庫經驗, 同時亦曾工作於其它專案包括: OpenOffice.org, Microsoft SQL Server, Oracle PL/SQL, and (shudder) COM+...


更新:2007-03-27
對映章節:PostgreSQL Application Performance Tips

內容:
這是一篇由 Josh Berkus 撰寫的好文章, 阿益翻譯分享給大家參考:

為 PostgreSQL 性能的應用程序設計

Tip 4: DELETE 是昂貴的

一般的應用開發者認為 DELETE 宣告實際上是沒有成本的。您是合理的認同這些嗎?
相反的。DBMSes 不是檔案系統(Filesystems); 當您刪除一筆資料列(row), 索引需要被更新並且釋放空間需要被清掃, 使得 delete 在實際上比 insert 更昂貴。
因此應用程序習慣地 delete 全部詳細資料列並且取代它們及每一次使用新的製造任何改變的儲存結果在這應用的資料庫上。這應該是被複寫及更多區分 row-replacement 行為, 看似更新唯一修改過的列。
同樣的, 當清理一張完整資料表, 時常使用 TRUNCATE TABLE 宣告總是比 DELETE FROM TABLE 來的恰當。比以往要 100 次時間期要來的快速是由於它採用批量處理(batch processes)整張資料表代替比以一列一次為單位。

Tip 5: 查詢迴圈使用 PREPARE/EXECUTE

有時儘管您企圖統合相近似的查詢式到一個大宣告式裡, 它不可能正當的歸因於您的應用程序結構。那就用 PREPARE ... EXECUTE 來做; 它允許資料庫引擎略過解析和計劃對每個查詢疊代。就範例來說:

Prepare:
query_handle = query('SELECT * FROM TABLE WHERE id = ?')(parameter_type = INTEGER)

接著開始您的迴圈:
for 1..100
query_handle.execute(i);
end

Classes for prepared statements for C++ are explained in the libpqxx documentation.

This will reduce query execution time in direct proportion to the size of the looped set.

Tip 6: 使用連結池是有效地(connection pools)

由於網路應用程序, 您將發現您潛在的性能非常大約 50% 可能被受制於您經過的用途, 及適當的組態配置, 連線中的連結池(pooling). 這是因為創建與移除中的資料庫連線消耗重要的系統時間總量, 以及過多閒置空轉的連線仍請求著記憶體和系統資源.

There are a number of tools you can use to do connection pooling on PostgreSQL. 一個第三方的開放源碼工具非常受歡迎是 pgPool. 無論如何, 由於一個 C++ 應用程序 HA 必要條件, it's probably better to use libpqxx's native pseudo-pooling technique called "lazy connections" . I suggest contacting the libpqxx mailing list for more information on how to use this utility.

With PostgreSQL, you want to have as many persistent connections (or connection objects) defined as your normal peak concurrent connection usage. So if max normal usage (first thing in the morning, say) is 200 concurrent connections from agents, users and components, then you want to have that many defined so that your application does not have to wait for new connections during a peak when they will be slow to fork.

(未翻完....)