PostgreSQL 在寫入密集型數據庫上的性能隨時間下降

我觀察到一種奇怪的情況，隨著時間的推移，查詢的性能（下面解釋的查詢組合）會下降，這意味著在測試開始時（幾分鐘）查詢時間為 2 毫秒，然後第二天它達到 15 毫秒然後在 30 毫秒後的一天。

通過查詢，我在這裡指的是兩者的組合：

• 在表2中插入一行，從表2中選擇一行，在表3中選擇一行，更新表3中的一行，送出
• 在表1中插入一行，在表3中選擇一行，在表3中更新一行，送出

**我想知道這可能是什麼原因，或者我應該考慮設置配置文件中的哪些設置以及如何設置？**我在設置了數據庫但未添加主鍵的 Ubuntu 機器上觀察到了這個問題。另一方面，我在其上開發的 Win 沒有被觀察到（它在 7 天內每次查詢平均持續執行 3 毫秒）。

我注意到在新數據庫（在 Ubuntu 上）中，任何表上都沒有主鍵，這與我開發的那個相反。缺少主鍵會對這種查詢產生負面影響嗎？

我想我會在將整個數據庫從我的開發機器移動到測試機器的同時問這個問題。

在開發中我使用了 PostgreSQL 8.4（CPU：Intel i7 740QM，RAM：6GB），在測試中有 PostgreSQL 9.1（CPU：Intel i3-2100，RAM：3.8GB）。

---

更新： autovacuum相關參數：

#autovacuum = on        
#log_autovacuum_min_duration = -1   
#autovacuum_max_workers = 3     
#autovacuum_naptime = 1min      
#autovacuum_vacuum_threshold = 50   
#autovacuum_analyze_threshold = 50  
#autovacuum_vacuum_scale_factor = 0.2   
#autovacuum_analyze_scale_factor = 0.1  
#autovacuum_freeze_max_age = 200000000  
#autovacuum_vacuum_cost_delay = 20ms    
#autovacuum_vacuum_cost_limit = -1  

---

UPDATE2： 似乎問題也出現在開發機器上，但我記得它之前執行良好。儘管如此，我還是做了一些更多的測試，並在查詢上執行了 EXPLAIN ANALYZE，這是我花費最多的時間，它是一個更新（我還看到選擇在表上也需要一段時間），如下所示：

EXPLAIN ANALYZE UPDATE ais_track SET latest_dynamic = '2012-09-10 22:22:22.222' WHERE mmsi = 123456789 AND ais_system = 1;

下面的結果是在 Win 上，因為 Ubuntu 上的恢復仍在進行中，我首先得到了這個：

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=36) (actual time=1.090..2.460 rows=1 loops=1)
 Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 8.681 ms

然後在第二次重複和進一步重複相同的更新查詢幾次我得到這種形式的東西：

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=36) (actual time=0.699..1.797 rows=1 loops=1)
 Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 1.850 ms

經過一百次左右的重複，它達到了 2ms 以上。

解釋選擇的分析：

EXPLAIN ANALYZE SELECT * FROM ais_track WHERE mmsi = 123456789 AND ais_system = 1

第一次執行：

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=38) (actual time=1.283..2.522 rows=1 loops=1)
 Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 2.560 ms

經過一百次左右的執行：

Index Scan using pk_track on ais_track  (cost=0.00..4.46 rows=1 width=38) (actual time=0.027..1.357 rows=1 loops=1)
 Index Cond: ((mmsi = 123456789) AND (ais_system = 1))
Total runtime: 1.382 ms

查詢中使用的表：

CREATE TABLE ais_track
(
 ais_system integer NOT NULL,
 mmsi integer NOT NULL,
 ext_id integer,
 latest_dynamic timestamp without time zone,
 latest_static timestamp without time zone,
 "name" character varying,
 CONSTRAINT pk_track PRIMARY KEY (mmsi, ais_system)
)

還有兩個索引：

CREATE INDEX ais_track_mmsi
 ON ais_track
 USING btree
 (mmsi);

CREATE INDEX ais_track_sys
 ON ais_track
 USING btree
 (ais_system);

注意：表格大小是 11000，它不會改變。

感謝@Frank Heikens、@dezso 和@Colin ’t Hart 的建議和參與，+1 給你們。

問題在於我使用MyBatis的方式。我使用了一種“特殊”:) 單例模式，它能夠在需要時生成新實例，並且對於一些一般的東西有一個共享連接。儘管對於我的問題中提到的操作，我使用了一個新的連接實例。

顯然，使用共享連接不是一個好主意，正如我的經驗和 MyBatis 手冊建議的那樣，在工作完成後提醒關閉連接（會話）。無論如何，問題是我在開始時使用共享連接從數據庫載入靜態表，然後在完成單選後未使用連接。雖然當我在 ais_track 表上進行查詢（即選擇和更新）時，操作的時間在增加。我仍然想知道為什麼不完全關閉共享連接會使查詢在不同的連接上執行以減慢每次執行的速度 - 任何建議都將不勝感激。

使用普通 JDBC 時，我無法複製此問題。所以我認為它一定與MyBatis有關，或者我沒有正確使用它？

我的解決方案是擺脫共享資源，一旦我做了一些操作並且不再使用連接，我就會關閉它。 我還注意到，如果我在使用共享連接進行選擇後強制送出，即使它仍然處於打開狀態，問題也不會出現。

***順便說一句：*如果並且最重要的是出於什麼原因使用單例或換句話說與數據庫建立懸掛連接是不好的，請發表評論（如果事實上我所說的是真的）。

所有（？）RDBMS 中的主鍵和唯一鍵都使用索引來快速確定新插入的值是否確實是唯一的。

這樣做的副作用是通過主鍵和唯一鍵的查詢通常是“快速的”。

現在，如果您尚未在表上定義主鍵或唯一鍵，

1. 你沒有關係表，但你有垃圾（好吧，這是一個有爭議的觀點，但關係模型需要所有表上的鍵）。
2. 隨著更多數據插入表中，對該表的查詢（在沒有任何其他索引的情況下）將變得更慢。

所以是的，沒有主鍵會導致這個！

引用自：https://dba.stackexchange.com/questions/27519