12.1 數(shù)據(jù)庫(kù)優(yōu)化準(zhǔn)則和方法

12.1.1 數(shù)據(jù)庫(kù)優(yōu)化準(zhǔn)則

????????數(shù)據(jù)庫(kù)優(yōu)化的思路有很多種。比較常用的是下面兩種優(yōu)化思路。

1. 第一種思路：有人說(shuō)過(guò)，“The fastest way to do something is don't do it”，意思是說(shuō)，“做得最快的方法就是不做”。從這個(gè)思路上來(lái)說(shuō)，把一些無(wú)用的步驟或作用不大的步驟去掉就是一種優(yōu)化。
2. 第二種思路：做同樣一件事情，要想更快有多種方法，最簡(jiǎn)單的方法就是換硬件，讓數(shù)據(jù)庫(kù)跑在更快的硬件上。但換硬件一般都是最后的選擇，除此之外，最有效的方法是優(yōu)化算法，如讓SQL走到更優(yōu)的執(zhí)行計(jì)劃上。

在數(shù)據(jù)庫(kù)優(yōu)化中，主要有以下優(yōu)化指標(biāo)。

• 響應(yīng)時(shí)間：衡量數(shù)據(jù)庫(kù)系統(tǒng)與用戶(hù)交互時(shí)多久能夠發(fā)出響應(yīng)。
• 吞吐量：衡量在單位時(shí)間內(nèi)可以完成的數(shù)據(jù)庫(kù)任務(wù)。進(jìn)行數(shù)據(jù)庫(kù)優(yōu)化時(shí)，筆者都是圍繞著上述指標(biāo)進(jìn)行優(yōu)化的。數(shù)據(jù)庫(kù)優(yōu)化工作中，第一項(xiàng)就是確定優(yōu)化目標(biāo)。
• 性能目標(biāo)：如CPU利用率或IOPS需要降到多少。
• 響應(yīng)時(shí)間：需要從多少毫秒降到多少毫秒。
• 吞吐量：每秒處理的SQL數(shù)或QPS需要提高到多少。

一個(gè)已運(yùn)行的數(shù)據(jù)庫(kù)系統(tǒng)，如果前期設(shè)計(jì)不合理、性能不高，后期在優(yōu)化時(shí)會(huì)非常困難，有可能永遠(yuǎn)無(wú)法達(dá)到高性能，因此，在新建一套數(shù)據(jù)庫(kù)系統(tǒng)前，首要的事應(yīng)該是設(shè)計(jì)優(yōu)化。良好的設(shè)計(jì)能最大限度地發(fā)揮系統(tǒng)的性能。

12.1.2 優(yōu)化方法

????????優(yōu)化的第一件事是確定目標(biāo)，那么要如何確定一個(gè)合理的目標(biāo)呢？這就需要使用測(cè)試工具。熟練使用常用的測(cè)試工具是做數(shù)據(jù)庫(kù)優(yōu)化的基礎(chǔ)。下面是一些常用的測(cè)試工具。

• memtest86+：內(nèi)存測(cè)試工具。
• STREAM：內(nèi)存測(cè)試工具。
• sysbench：綜合測(cè)試工具，可以測(cè)試CPU、I/O、數(shù)據(jù)庫(kù)等。
• pgbench：PostgreSQL自帶的測(cè)試工具，可以仿真TPC-B的測(cè)試模型。
• fio：最強(qiáng)大的免費(fèi)I/O測(cè)試工具。
• orion：Oracle的I/O測(cè)試工具，測(cè)試裸設(shè)備的I/O能力，功能比f(wàn)io要少，但使用簡(jiǎn)單。

熟練掌握以上幾種測(cè)試工具的使用方法，對(duì)數(shù)據(jù)庫(kù)的優(yōu)化很有幫助。

????????在數(shù)據(jù)庫(kù)優(yōu)化中，首先需要了解一些常用硬件的相關(guān)知識(shí)，熟悉這些硬件的特性和性能，才能知道目前數(shù)據(jù)庫(kù)系統(tǒng)使用的硬件是否到達(dá)了瓶頸、更換硬件是否能提高數(shù)據(jù)庫(kù)的性能。

12.2 硬件知識(shí)

CPU、內(nèi)存、網(wǎng)絡(luò)、硬盤(pán)的響應(yīng)時(shí)間和吞吐量都是不一樣的，了解這些知識(shí)，有助于理解如何優(yōu)化硬件。

?12.2.1 CPU及服務(wù)器體系結(jié)構(gòu)

服務(wù)器系統(tǒng)可以分為以下幾種體系結(jié)構(gòu)。
（1）SMP/UMA -Symmetric Multi Processing/Uniform Memory
Architecture

• 優(yōu)點(diǎn)：服務(wù)器中多CPU對(duì)稱(chēng)工作，無(wú)主次關(guān)系。各CPU共享相同的物理內(nèi)存，訪問(wèn)內(nèi)存任何地址所需的時(shí)間相同，因此程序設(shè)計(jì)較為簡(jiǎn)單。
• 缺點(diǎn)：因多CPU無(wú)主次關(guān)系，需要解決內(nèi)存訪問(wèn)沖突，所以硬件實(shí)現(xiàn)成本高。

（2）NUMA-Non-Uniform Memory Access

• 優(yōu)點(diǎn)：多CPU模塊，每個(gè)CPU模塊具有獨(dú)立的本地內(nèi)存（快），但訪問(wèn)其他CPU內(nèi)存（慢），硬件實(shí)現(xiàn)成本低。
• 缺點(diǎn)：全局內(nèi)存訪問(wèn)性能不一致；設(shè)計(jì)程序時(shí)需要特殊考慮。

（3）MPP-Massive Parallel Processing·

• 優(yōu)點(diǎn)：由多個(gè)SMP服務(wù)器通過(guò)節(jié)點(diǎn)互聯(lián)網(wǎng)絡(luò)連接而成，每個(gè)節(jié)點(diǎn)都可訪問(wèn)本地資源（內(nèi)存、存儲(chǔ)等），完全無(wú)共享（Share-Nothing）。最易擴(kuò)展，軟件層面即可實(shí)現(xiàn)。
• 缺點(diǎn)：數(shù)據(jù)重分布；程序設(shè)計(jì)復(fù)雜。

?12.2.2 內(nèi)存

????????內(nèi)存是CPU與外部溝通的橋梁。CPU運(yùn)算時(shí)所需的數(shù)據(jù)都臨時(shí)保存在內(nèi)存中，計(jì)算機(jī)的所有程序也都運(yùn)行在內(nèi)存中，內(nèi)存通常也用于硬盤(pán)等外部存儲(chǔ)器的數(shù)據(jù)緩存。
內(nèi)存從硬件上分為以下幾種。

• SRAM：靜態(tài)隨機(jī)存儲(chǔ)器。隨機(jī)是指數(shù)據(jù)不是線性依次存儲(chǔ)的，而是自由指定地址進(jìn)行數(shù)據(jù)讀寫(xiě)的。CPU的cache一般使用這種存儲(chǔ)方式，特點(diǎn)是速度快但造價(jià)高，不能大規(guī)模使用。
• DRAM：動(dòng)態(tài)隨機(jī)存儲(chǔ)器。動(dòng)態(tài)是指存儲(chǔ)陣列需要不斷刷新來(lái)保證數(shù)據(jù)不丟失。造價(jià)比SRAM低得多，但速度也慢一些。
• SDRAM：同步動(dòng)態(tài)隨機(jī)存儲(chǔ)器，同步是指工作時(shí)需要同步時(shí)鐘，內(nèi)部命令的發(fā)送與數(shù)據(jù)的傳輸都以它為基準(zhǔn)。
• DDR SDRAM：雙倍數(shù)據(jù)傳輸率的SDRAM，DDR是“Double Data Rate”的縮寫(xiě)。普通的SDRAM在一個(gè)時(shí)鐘周期內(nèi)只傳輸一次數(shù)據(jù)，即它在時(shí)鐘的上升期進(jìn)行數(shù)據(jù)傳輸；而DDR內(nèi)存則在一個(gè)時(shí)鐘周期的上升期和下降期各傳輸一次數(shù)據(jù)，因此稱(chēng)為雙倍速率同步動(dòng)態(tài)隨機(jī)存儲(chǔ)器。DDR內(nèi)存又分DDR1、DDR2、DDR3、DDR4幾種，分別對(duì)應(yīng)第一代、第二代、第三代、第四代DDR。目前主流的內(nèi)存為DDR4內(nèi)存。

12.2.3 硬盤(pán)

硬盤(pán)按接口可以分為以下3種。

• ATA系列：包括較早的硬盤(pán)接口，比如IDE（Integrated DriveElectronics）、PATA（Parallel ATA）及SATA（Serial ATA）。
• SCSI系列：包括早期的并行SCSI和現(xiàn)在使用較廣泛的SAS（串行SCSI）。
• FC接口：支持FC協(xié)議接口的硬盤(pán)。

FC接口的硬盤(pán)一般只在專(zhuān)用存儲(chǔ)上使用，通常見(jiàn)到的硬盤(pán)都是SATA或SAS接口的。
硬盤(pán)按存儲(chǔ)介質(zhì)來(lái)區(qū)分，可以分為以下兩種。

1. HDD：普通機(jī)械硬盤(pán)。
2. SSD：固態(tài)硬盤(pán)。

機(jī)械硬盤(pán)和SSD硬盤(pán)都有SATA和SAS接口的這兩種。

硬盤(pán)通常通過(guò)SAS或SATA接口的卡連接到主機(jī)上。SAS卡既能接SAS硬盤(pán)，也能接SATA硬盤(pán)，但SATA卡只能接SATA硬盤(pán)。
目前SAS的接口速度一般是3Gb/s或6Gb/s。

12.3 文件系統(tǒng)及I/O調(diào)優(yōu)

????????目前PostgreSQL數(shù)據(jù)庫(kù)還不支持直接在裸設(shè)備上存儲(chǔ)數(shù)據(jù)，也就是說(shuō)，PostgreSQL的數(shù)據(jù)必須存儲(chǔ)在文件系統(tǒng)上，故而選擇一個(gè)合適的文件系統(tǒng)對(duì)PostgreSQL數(shù)據(jù)庫(kù)來(lái)說(shuō)非常重要。

12.3.1 文件系統(tǒng)的崩潰恢復(fù)

????????文件系統(tǒng)中除記錄文件內(nèi)容信息外，還記錄了一些元數(shù)據(jù)（如目錄樹(shù)、文件名、文件的塊分配列表），以及和文件相關(guān)的一些屬性（如文件名、文件的創(chuàng)建時(shí)間等），還有磁盤(pán)的空間分配信息（如哪些塊已被分配、哪些塊是空閑的）。
????????在寫(xiě)一個(gè)文件時(shí)，除了寫(xiě)文件的內(nèi)容信息外，還會(huì)寫(xiě)一些元數(shù)據(jù)。為了保證數(shù)據(jù)的可靠性，在出現(xiàn)宕機(jī)等異常情況后，文件系統(tǒng)除了要保證元數(shù)據(jù)本身一致，還要求文件內(nèi)容的數(shù)據(jù)與元數(shù)據(jù)之間也是一致的。
????????元數(shù)據(jù)一致性當(dāng)然是最重要的，不能將同一個(gè)數(shù)據(jù)塊分配給兩個(gè)文件，這會(huì)導(dǎo)致一個(gè)文件的內(nèi)容被另一個(gè)文件覆蓋。分配出去的數(shù)據(jù)塊必須有文件在使用，否則會(huì)導(dǎo)致明明現(xiàn)有文件并未占用多少空間，但文件系統(tǒng)上卻沒(méi)有空間了。
????????當(dāng)向一個(gè)文件的末尾添加數(shù)據(jù)時(shí)，文件會(huì)擴(kuò)大，如果元數(shù)據(jù)記錄了該文件的擴(kuò)大，但新數(shù)據(jù)沒(méi)有實(shí)際寫(xiě)入，就會(huì)導(dǎo)致新擴(kuò)大的數(shù)據(jù)塊中存在垃圾數(shù)據(jù)，這有可能導(dǎo)致問(wèn)題產(chǎn)生。
????????早期的文件系統(tǒng)并不能保證元數(shù)據(jù)與數(shù)據(jù)的一致性，如Windows下的FAT文件系統(tǒng)和Ext2文件系統(tǒng)。當(dāng)一個(gè)操作需要多次寫(xiě)元數(shù)據(jù)或一次寫(xiě)元數(shù)據(jù)一次寫(xiě)數(shù)據(jù)時(shí)，操作中的多個(gè)步驟通常不是原子性的，要保證一致性就必須要有類(lèi)似數(shù)據(jù)庫(kù)中的事務(wù)的概念。要有事務(wù)就需要有日志，也就是說(shuō)，要通過(guò)日志來(lái)保證整個(gè)操作的一致性。
????????所以現(xiàn)在流行的文件系統(tǒng)都被設(shè)計(jì)成有日志的，如Ext3、Ext4及Windows下的NTFS文件系統(tǒng)。
????????但寫(xiě)日志相當(dāng)于原先的一次寫(xiě)變成了兩次寫(xiě)，可能會(huì)降低寫(xiě)的性能。為了降低對(duì)性能的影響，多數(shù)文件系統(tǒng)通常只是把元數(shù)據(jù)寫(xiě)入日志，而實(shí)際數(shù)據(jù)塊內(nèi)容的變更并不會(huì)寫(xiě)入日志。
????????如果一個(gè)文件已被寫(xiě)過(guò)，再寫(xiě)以前的數(shù)據(jù)塊時(shí)，不會(huì)分配新的數(shù)據(jù)塊；關(guān)于空間分配的元數(shù)據(jù)也不會(huì)被更新，通常只更新文件上的時(shí)間戳。對(duì)于數(shù)據(jù)庫(kù)來(lái)說(shuō)，這種情況下通常不會(huì)產(chǎn)生不一致，所以數(shù)據(jù)庫(kù)使用重寫(xiě)會(huì)更安全一些，PostgreSQL中WAL日志的寫(xiě)就是這樣的。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-499857.html

到了這里，關(guān)于PostgreSQL修煉之道之?dāng)?shù)據(jù)庫(kù)優(yōu)化（十八）的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！