如何開始優化數據庫

如何開始優化數據庫，針對這個問題，這篇文章詳細介紹了相對應的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

對于數據庫的調優, 可以從以下幾個方面來考慮, 硬件的配置, 網絡的配置, 數據庫的配置, SQL 撰寫和解讀的方式, 索引的維護等等問題.去入手, 下面僅僅對數據庫配置和SQL的rewrite進行相關調優的講解.

通常來說調整數據庫性能需要系統性的知識, 例如你要知道數據庫系統服務的對象是在線分析系統還是在線事務系統,這對于硬件系統提供的IO CPU 都是有不同的需求的,例如你是要多少CPU, RAID設置的方式, 多大容量的內存,以及數據庫的設置問題等等.當然可以通過PGBANCH來對數據庫的TPS 進行一個測試.

而第二部對于數據庫的優化就要在數據庫的運行后,在開始,在這個階段需要對系統進行一個觀察和監測例如你可以使用pgbadger監控工具對于系統進行整體的監控,或者powa和pg_stat_statements 對于數據庫進行問題的查找,找到瓶頸和慢查詢等信息.

分析的首要要點就是對于慢查詢的分析,一個較慢的較差的SQL, 就需要重寫除此以外缺失的索引應該被創建,數據庫的配置應該被重設,物理結構應該被重構等等.

下面就要開始對數據庫的configuration 進行調試.

對于數據庫的最大連接數在數據庫的配置中是非常重要的事情, 默認max_ connections設置的默認值是100,如果設置的值較低,可以適當提高work_mem的配置值.

在POSTGRESQL中,通常會使用連接池來提高系統性能降低內存的浪費,并且降低由于連接killing和重建連接鎖消耗的時間.

常用的兩種連接池軟件

pgbouncer

pgpool-II

當然在商業等級中連接池也可以使用類似JAVA的 connection-pooling 或者 C3P0 等等.

以下的幾個設置是關于內存的設定的

共享緩沖區(shared_buffers):共享緩沖區的默認值為

32 MB;但是，建議將其設置為總內存的25%左右，但是在Linux系統上不超過8 GB，在windows系統上不超過512 MB。有時，將shared_buffers增加到一個非常高的值會導致性能的提高，因為數據庫可以完全緩存在RAM中。但是，過多地增加這個值的缺點是無法為諸如排序和散列之類的CPU操作分配內存。

(不是太贊同書中的關于這段的內容,實際上這個值應該根據應用系統的情況來設置,例如是OLTP OLAP, 以及運行一段時間的統計信息,在進行二次調整會更好)

Working Memory (work_mem)

工作內存(work_mem):默認值為1 MB;對于cpu綁定操作，增加這個值很重要。work_mem設置與連接數相關聯，因此使用的RAM總數等于連接數乘以work_mem。工作內存用于排序和散列，因此它會影響使用順序BY的查詢，
不同的、聯合查詢等.

如我們在查詢語句是,通過 explain analyze 時可以看到這個語句的內存使用的情況

擴展:

書中到此內存的內容就沒有了實際上,PG的內存調整還涉及

temp_buffers = 8MB 針對每個數據庫進行的臨時BUFFER的設置,在未使用的狀態下每個SESSION會占用 64bytes作為占位符

maintenance_work_mem = 64MB

指定維護操作(如VACUUM、CREATE INDEX和ALTER TABLE ADD FOREIGN KEY)所使用的最大內存量。它默認為64兆字節(64MB)。由于數據庫會話一次只能執行其中的一個操作，而且安裝通常不會同時運行許多操作，因此可以將這個值設置為比work_mem大得多的值。較大的設置可能提高清空和恢復數據庫轉儲的性能。

autovacuum_work_mem 在數據庫中最重要的autovacuum中使用的內存,默認值 -1表示為使用 maintenance_work_mem的配置來替代這個配置.

shared_memory_type = mmap

dynamic_shared_memory_type = posix

關于如何開始優化數據庫問題的解答就分享到這里了，希望以上內容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關注億速云行業資訊頻道了解更多相關知識。