我建議您閱讀 Vladimir Sitnikov 2016 年初報告的文字記錄“PostgreSQL 和 JDBC 正在榨乾所有的汁液”
午安我叫弗拉基米爾·西特尼科夫。 我已經在 NetCracker 工作 10 年了。 我主要關注的是生產力。 一切與Java有關的東西，一切與SQL有關的東西都是我所熱愛的。
而今天我就來談談我們在公司開始使用PostgreSQL作為資料庫伺服器時遇到的事情。 我們主要使用 Java。 但我今天要告訴您的不僅僅是 Java。 實踐表明，這種情況也發生在其他語言中。
我們將討論：
- 關於數據採樣。
- 關於保存資料。
- 還有關於性能。
- 還有埋在那裡的水下耙子。
讓我們從一個簡單的問題開始。 我們根據主鍵從表中選擇一行。
資料庫位於同一主機上。 而所有這些耕種過程只需要 20 毫秒。
這20毫秒很多。 如果你有 100 個這樣的請求，那麼你每秒鐘都要花時間滾動瀏覽這些請求，也就是說，我們在浪費時間。
我們不喜歡這樣做，看看基地為此提供了什麼。 資料庫為我們提供了兩種執行查詢的選項。
第一個選項是一個簡單的請求。 它有什麼好處呢？ 事實上，我們接收並發送它，僅此而已。
該資料庫還有一個高級查詢，它更棘手，但功能更強大。 可以單獨發送解析、執行、變數綁定等請求。
超級擴展查詢是我們在目前報告中不會討論的內容。 也許我們想要從資料庫中得到一些東西，並且有一個以某種形式形成的願望清單，即這就是我們想要的，但現在和明年都是不可能的。 所以我們只是把它錄下來，然後我們就會去搖晃主要人物。
而我們能做的就是簡單查詢和擴充查詢。
每種方法有什麼特別之處？
一個簡單的查詢適合一次性執行。 一旦完成又忘記。 問題是它不支援二進位資料格式，即不適合某些高效能係統。
擴充查詢 - 允許您節省解析時間。 這就是我們所做的並開始使用的。 這真的非常幫助我們。 不僅可以節省解析時間。 可以節省資料傳輸費用。 以二進位格式傳輸資料效率更高。
我們繼續練習吧。 這就是典型應用程式的樣子。 可能是Java等。
我們創建了聲明。 執行了命令。 建立關閉。 這裡的錯誤在哪裡呢？ 問題是什麼？ 沒問題。 所有書上都是這麼說的。 應該這樣寫。 如果你想要最大的效能，就這樣寫。
但實踐證明這是行不通的。 為什麼？ 因為我們有一個“close”方法。 當我們這樣做時，從資料庫的角度來看，它就像一個吸煙者使用資料庫一樣。 我們說「解析執行解除分配」。
為什麼要額外建立和卸載語句？ 沒有人需要它們。 但在PreparedStatements 中通常發生的情況是，當我們關閉它們時，它們會關閉資料庫上的所有內容。 這不是我們想要的。
我們希望像健康人一樣與基地合作。 我們接受並準備了一次聲明，然後多次執行。 事實上，很多次——這在應用程式的整個生命週期中只有一次——它們已經被解析了。 我們在不同的 REST 上使用相同的語句 id。 這是我們的目標。
我們怎樣才能做到這一點？
非常簡單－不需要關閉語句。 我們這樣寫：「準備」「執行」。
如果我們推出這樣的東西，那麼很明顯有些東西會在某個地方溢出。 如果不清楚的話，你可以嘗試一下。 讓我們來寫一個使用這個簡單方法的基準測試。 建立一個聲明。 我們在某些版本的驅動程式上啟動它，發現它很快就會崩潰，並且丟失所有記憶體。
顯然，此類錯誤很容易糾正。 我不會談論他們。 但我要說的是，新版本的運行速度要快得多。 雖然方法很蠢，但是還是可以的。
如何正確工作？ 為此我們需要做些什麼？
實際上，應用程式總是關閉語句。 在所有書籍中他們都說要關閉它，否則記憶體會洩漏。
而且 PostgreSQL 不知道如何快取查詢。 每個會話有必要為自己建立此快取。
我們也不想在解析上浪費時間。
像往常一樣，我們有兩個選擇。
第一個選擇是我們接受它並說讓我們將所有內容包裝在 PgSQL 中。 那裡有一個緩存。 它快取所有內容。 結果會很棒。 我們看到了這個。 我們有 100500 個請求。 不起作用。 我們不同意手動將請求轉化為程序。 不，不。
我們還有第二個選擇──自己拿去剪。 我們打開來源並開始剪輯。 我們看到了又看到了。 事實證明，做起來並沒有那麼難。
這齣現在 2015 年 XNUMX 月。 現在有一個更現代的版本。 一切都很棒。 它運行得非常好，我們無需更改應用程式中的任何內容。 我們甚至不再考慮 PgSQL 的方向，也就是說，這足以讓我們將所有管理成本降到幾乎為零。
因此，伺服器準備的語句在第五次執行時被激活，以避免在每個一次性請求上浪費資料庫中的記憶體。
你可能會問——數字在哪裡？ 你得到什麼？ 這裡我不會給出數字，因為每個請求都有自己的請求。
我們的查詢花費了大約 20 毫秒來解析 OLTP 查詢。 執行時間為 0,5 毫秒，解析時間為 20 毫秒。 請求 – 10 KiB 文本，170 行計劃。 這是一個 OLTP 請求。 它需要 1、5、10 行，有時甚至更多。
但我們根本不想浪費 20 毫秒。 我們將其減少到 0。 一切都很棒。
你能從這裡帶走什麼？ 如果您有 Java，那麼您可以使用現代版本的驅動程式並感到高興。
如果您說不同的語言，那麼想一想 - 也許您也需要這個？ 因為從最終語言的角度來看，例如，如果 PL 8 或您有 LibPQ，那麼您並不清楚您沒有將時間花在執行上、花在解析上，這是值得檢查的。 如何？ 一切都是免費的。
除了存在錯誤和一些特殊性之外。 我們現在就討論它們。 其中大部分將是關於工業考古學，關於我們發現了什麼，我們遇到了什麼。
如果請求是動態產生的。 它發生了。 有人將字串黏合在一起，從而產生 SQL 查詢。
他為什麼不好？ 這很糟糕，因為每次我們都會得到不同的字串。
並且需要再次讀取這個不同字串的hashCode。 這確實是一項 CPU 任務 - 即使在現有哈希中尋找長請求文字也不是那麼容易。 因此，結論很簡單——不產生請求。 將它們儲存在一個變數中。 並歡喜。
下一個問題。 資料類型很重要。 有些 ORM 說，不管有哪一種 NULL，只要有某種類型就好。 如果是 Int，那我們說 setInt。 如果為 NULL，則讓它始終為 VARCHAR。 那麼 NULL 到底有什麼差別呢？ 資料庫本身會理解一切。 而且這張圖不行。
實際上，資料庫根本不關心。 如果你第一次說這是一個數字，第二次你說這是一個VARCHAR，那就不可能重複使用伺服器準備的語句。 在這種情況下，我們必須重新建立我們的語句。
如果您正在執行相同的查詢，請確保列中的資料類型沒有混淆。 您需要注意 NULL。 這是我們開始使用PreparedStatements後常遇到的錯誤
好的，已開機。 也許他們帶走了司機。 生產力下降了。 事情變得很糟糕。
這是怎麼發生的？ 這是一個錯誤還是一個功能？ 不幸的是，無法理解這是一個錯誤還是一個功能。 但有一個非常簡單的場景可以重現這個問題。 她完全出乎意料地伏擊了我們。 它包括從一張表中逐字抽樣。 當然，我們還有更多這樣的要求。 一般來說，他們包括兩個或三個表，但有這樣的回放場景。 從資料庫中取得任何版本並播放。
關鍵是我們有兩列，每一列都有索引。 一個 NULL 欄位中有一百萬行。 第二列僅包含 20 行。 當我們在沒有綁定變數的情況下執行時，一切正常。
如果我們開始使用綁定變數執行，即我們執行“？” 或者我們的請求為“1 美元”，我們最終會得到什麼？
第一次執行符合預期。 第二個稍微快一點。 有些東西被緩存了。 第三、第四、第五。 然後砰的一聲——諸如此類的事情。 最糟糕的是，這種情況發生在第六次處決時。 誰知道必須執行六次才能了解實際的執行計劃？
誰有罪？ 發生了什麼事？ 資料庫包含優化。 它似乎針對一般情況進行了最佳化。 因此，從某個時刻開始，她轉而採用通用計劃，不幸的是，該計劃可能會有所不同。 結果可能是相同的，也可能是不同的。 並且有某種閾值會導致這種行為。
你能為這個做什麼？ 當然，在這裡假設任何事情都更加困難。 我們使用一個簡單的解決方案。 這是+0，OFFSET 0。你一定知道這樣的解決方案。 我們只需接受它並在請求中添加“+0”，一切就都很好了。 我稍後會告訴你。
還有另一個選擇——更仔細地審視計劃。 開發人員不僅要寫一個請求，還要說6次「解釋分析」。 如果是5就不行了。
還有第三種選擇 - 寫一封信給 pgsql-hackers。 我寫道，但是，目前還不清楚這是錯誤還是功能。
當我們思考這是一個錯誤還是一個功能時，讓我們修復它。 讓我們接受我們的請求並添加“+0”。 一切都好。 兩個符號，你甚至不必考慮它是什麼或它是什麼。 很簡單。 我們只是禁止資料庫在該列上使用索引。 我們在“+0”列上沒有索引，就是這樣，資料庫不使用索引，一切都很好。
這就是6解釋的規則。 現在，在目前版本中，如果您有綁定變量，則必須執行 6 次。 如果您沒有綁定變量，這就是我們所做的。 而最終也正是這個要求失敗了。 這不是什麼棘手的事。
看起來，有多少可能？ 這裡有一個錯誤，那裡有一個錯誤。 事實上，這個 bug 無所不在。
讓我們仔細看看。 例如，我們有兩個模式。 方案A與表S以及圖B與表S。 查詢－從表格中選擇資料。 在這種情況下我們會得到什麼？ 我們將會遇到一個錯誤。 我們將擁有以上所有內容。 規則是－錯誤無所不在，我們將擁有以上所有內容。
現在的問題是：“為什麼？” 似乎有文件表明，如果我們有一個模式，那麼就有一個「search_path」變數告訴我們在哪裡查找表。 看來還是有變數的。
問題是什麼？ 問題是伺服器準備的語句不懷疑 search_path 可以被某人更改。 該值對於資料庫來說保持不變。 有些部分可能不會獲得新的意義。
當然，這取決於您正在測試的版本。 取決於您的表格差異的嚴重程度。 9.1 版本將簡單地執行舊的請求。 新版本可能會捕獲該錯誤並告訴您有錯誤。
該如何治療呢？ 有一個簡單的秘訣——不要這樣做。 應用程式在執行階段無需更改 search_path。 如果發生更改，最好建立新連線。
你可以討論，即打開、討論、添加。 也許我們可以說服資料庫開發人員，當有人更改值時，資料庫應該告訴客戶端：「看，你的值已在這裡更新。 也許您需要重置語句並重新創建它們？” 現在，資料庫的行為是秘密的，不會以任何方式報告語句在內部某處發生了更改。
我要再次強調 - 這對 Java 來說是不典型的。 我們將在 PL/pgSQL 中一對一地看到相同的事情。 但它會在那裡被複製。
讓我們嘗試更多的數據選擇。 我們選擇再選擇。 我們有一個包含一百萬行的表。 每行為一千位元組。 大約一千兆位元組的資料。 我們的Java機器有128兆位元組的工作記憶體。
正如所有書籍中所建議的那樣，我們使用流處理。 也就是說，我們打開resultSet並從那裡一點一點地讀取資料。 它會起作用嗎？ 會從記憶中消失嗎？ 你會讀一點嗎？ 讓我們相信資料庫，讓我們相信 Postgres。 我們不相信。 我們會失去記憶嗎？ 誰經歷過記憶體不足？ 後來誰成功修復了它？ 有人設法修復了它。
如果您有一百萬行，您就不能只是挑選。 需要偏移/限制。 誰支持這個選項？ 誰贊成使用 autoCommit？
在這裡，像往常一樣，最意想不到的選擇被證明是正確的。 如果你突然關閉 autoCommit，會有幫助。 這是為什麼？ 科學對此一無所知。
但預設情況下，所有連接到 Postgres 資料庫的用戶端都會取得整個資料。 PgJDBC 在這方面也不例外；它選擇所有行。
FetchSize 主題有一個變體，您可以在單獨的語句層級上說，請按 10、50 選擇資料。但這只有在您關閉 autoCommit 後才會起作用。 關閉自動提交 - 它開始工作。
但是遍歷程式碼並到處設定setFetchSize很不方便。 因此，我們進行了一項設置，表示整個連接的預設值。
這就是我們所說的。 參數已配置。 我們得到了什麼？ 如果我們選擇少量數據，例如，我們一次選擇 10 行，那麼我們的開銷成本就會非常大。 因此，這個值應該要設定為一百左右。
當然，理想情況下，您仍然需要學習如何以位元組為單位限制它，但秘訣是：將 defaultRowFetchSize 設定為大於 XNUMX，然後就高興了。
讓我們繼續插入資料。 插入更容易，有不同的選擇。 例如，插入、值。 這是一個不錯的選擇。 你可以說「插入選擇」。 實際上也是一樣的。 性能上沒有差別。
書上說你需要執行一個Batch語句，書上說你可以用幾個括號執行更複雜的指令。 Postgres 有一個很棒的功能 - 你可以進行 COPY，即做得更快。
如果你測量它，你可以再次得到一些有趣的發現。 我們希望它如何運作？ 我們不想解析也不執行不必要的命令。
實際上，TCP 不允許我們這樣做。 如果客戶端正忙於發送請求，則資料庫不會讀取請求以嘗試向我們發送回應。 最終的結果是客戶端等待資料庫讀取請求，資料庫等待客戶端讀取回應。
因此客戶端被迫定期發送同步資料包。 額外的網路交互，額外的時間浪費。
我們添加的越多，情況就越糟。 驅動程式相當悲觀，並且經常添加它們，大約每 200 行添加一次，具體取決於行的大小等。
碰巧你只修正了一行，一切都會加速 10 倍。 它發生了。 為什麼？ 像往常一樣，這樣的常數已經在某處使用過。 值“128”表示不使用批次。
還好這沒有包含在正式版本中。 在發布開始之前發現。 我給出的所有含義均基於現代版本。
我們來試試吧。 我們衡量InsertBatch很簡單。 我們多次測量 InsertBatch，即同一件事，但有很多值。 棘手的舉動。 不是每個人都能做到這一點，但這是一個如此簡單的舉動，比 COPY 容易得多。
您可以進行複製。
你可以在結構上做到這一點。 聲明使用者預設類型，將陣列和 INSERT 直接傳遞到表。
如果您開啟連結：pgjdbc/ubenchmsrk/InsertBatch.java，則此程式碼位於 GitHub 上。 您可以具體查看那裡產生了哪些請求。 沒關係。
我們推出了。 我們首先意識到的是，不使用批次處理是根本不可能的。 所有批次選項均為零，即與一次性執行相比，執行時間實際上為零。
我們插入數據。 這是一張非常簡單的桌子。 三列。 我們在這裡看到了什麼？ 我們看到所有這三個選項大致具有可比性。 當然，COPY 更好。
這是我們插入碎片的時候。 當我們說一個 VALUES 值、兩個 VALUES 值、三個 VALUES 值或我們表示其中 10 個值時，用逗號分隔。 現在這只是水平的。 1, 2, 4, 128. 可以看到，用藍色繪製的Batch Insert，讓他的心情好了很多。 也就是說，當您一次插入一個甚至一次插入四個時，它的性能會提高一倍，這僅僅是因為我們在 VALUES 中塞入了更多的內容。 更少的 EXECUTE 操作。
在小批量上使用 COPY 是非常沒有希望的。 我甚至沒有畫前兩個。 他們去了天堂，就是這些綠色的數字進行COPY。
當您有至少一百行資料時，應使用 COPY。 打開這個連接的開銷很大。 而且，說實話，我並沒有往這個方向挖掘。 我優化了 Batch，但沒有優化 COPY。
接下來我們做什麼？ 我們試穿了。 我們知道我們需要使用結構或結合多種含義的巧妙的 bacth。
您應該從今天的報告中得到什麼？
- ReadyStatement 是我們的一切。 這對生產力有很大幫助。 美中不足的是，它產生了很大的失敗。
- 並且需要執行 EXPLAIN ANALYZE 6 次。
- 我們需要稀釋 OFFSET 0 和 +0 之類的技巧，以修正有問題的查詢的剩餘百分比。
來源： www.habr.com