GBase

8t系統與設計優化南大通用數據技術股份有限公司?GBASE2016前言－性能優化簡介2本篇的目的是向讀者闡述在SQL性能不理想時候如何優化8t系統與應用程序。首先，讀者要了解OLTP與OLAP系統的差異，因為OLTP的系統是處理大量的小交易譬如銀行，而OLAP是從龐大的數據中作出分析。性能優化有三個手段系統優化的選擇設計優化應用程序的SQL優化(不包含)查找性能的問題所在是有許多工具，本篇介紹系統優化與設計優化的手段。從系統與設個的宏觀層面確認。系統優化—設計優化二3目錄OLTP

和OLAP

的參數設定目錄系統優化—統計更新(Update

Statistics)12OLTP－聯機事務處理(OLTP)5OLTP(Online

Transaction

Processing)的特點是包含大量的交易

(transactions)。OLTP意味著大量的插入(INSERT)、更新(UPDATE)和刪除(DELETE)操作的短小事務。OLTP的特性:低復雜度高讀寫緩沖率快速的檢查點操作最大的I/O吞吐量優化OLTP手段:積極的設定ONCONFIG參數，充分利用共享內存，CPU的資源妥善的配置鎖，物理日志緩存，與可擴展的虛擬內存段。OLTP–onconfig參數6參數初始設置BUFFERPOOL設置為可用空閑內存的50%~70%BUFFERPOOL

size=2K,buffers=500000,lrus=16,lru_min_dirty=50.00,lru_max_dirty=60.0（為降低checkpoint的IO量可以設定LRU_MAX_DIRTY=2%和LRU_MIN_DIRTY=1%

）VPCLASS綁定1路cpu：VPCLASS

cpu,num=4,aff=(0-3),noage

綁定2路cpu：VPCLASS

cpu,num=8,aff=(0-7),noage

綁定3路cpu：VPCLASScpu,num=12,aff=(0-11),noage綁定4路cpu：VPCLASS

cpu,num=16,aff=(0-15),noageLOCKS1000

*用戶個數PHYSDBS從rootdbs中分離，放置到不同的高速磁盤上其它dbspaceCKPTINTVL300秒或是更小（檢查點是一個事件，內存（緩沖區）中的所有頁面都被寫入磁盤）SHMVIRTSIZE1600000(盡可能的在第一次用占多共享內存，降低將內存交換到磁盤的機會)OLAP－線上分析系統7OLAP 的SQL指令超過最復雜的OLTP交易。常常是從大量數據作出關鍵(Critical)業務分析。OLAP選讀大量數據，執行一個高度復雜SQL。包括復雜SQL的操作(operators)與選擇的限制(constraints)。復雜SQL產生密集的數據庫活動，消耗大量系統資源，耗時完成。在統一機器上，OLAP的工作會與OLTP搶資源而影響OLTP。OLAP適合在HDR的高可用機器運行。OLAP－線上分析系統的優化手段8在OLAP的系統環境，聚焦在會對性能影響最大體領域的焦點。充分的利用內存并行數據庫查詢(PDQ,

Parallel

database

queries)使用Light

scans(不用BUFFERPOOL作緩存）對應大的I/O吞吐量OLAP－必須善用并行處理PDQScanJoinSortSend

to

clientScanScanSortScanJoinSortSend

to

clientOLAP配置參數MAX_PDQPRIORITY100為防止PDQ任務占用過多資源，推薦設置為60-80DS_MAX_QUERIES同時支持最大的PDQ查詢個數，默認2*CPUVP*128建議修改為32-256DS_TOTAL_MEMORYPDQ任務可用內存大小默認值：If

SHMTOTAL=0

and

DS_MAX_QUERIES

is

set,

DS_TOTAL_MEMORY

=

DS_MAX_QUERIES

*

128.If

SHMTOTAL=0

and

DS_MAX_QUERIES

is

not

set,

DS_TOTAL_MEMORY

=

num_cpu_vps

*

2

*

128最大值：4G(64-byte)或者2G

(32-byte)DS_MAX_SCANS最大支持的Scan線程數，默認值即可(1048567)onmode

–M(DS_TOTAL_MEMORY)onmode–Q

(DS_MAX_QUERIES)onmode

–D

(MAX_PDQPRIORITY)onmode

–S(DS_MAX_SCANS)ONCONFIG

相關參數在線動態修改PDQ參數OLAP－適合使用PDQ的場景使用PDQ的前提設置PDQ相關參數Set

PDQPRIORITY

80;分片表包含排序操作特定SQL：order

by

,group

by,merge等語句查詢批量數據語句（表分片）大記錄的Group

by,order

by,集合操作：union,distinct等創建index利用外部表external

table批量數據導入、導出HPL使用PDQ的典型場景OLAP－參數動態調整DS_TOTAL_MEMORY使用40M空間給并行查詢(parallel

queries)%onmode

–M

40000(單位是k)DS_MAX_QUERIES允許100個并行查詢共同運作onmode

–Q

100MAX_PDQPRIORITY允許80%的資源用于PDQonmode

–D

80%DS_MAX_SCANS允許100個并行的掃描同時進行onmode

–S

10012OLTP

和OLAP

的參數設定目錄系統優化—統計更新(Update

Statistics)12GBase

8t統計更新(Update

Statistics)14統計更新的目的更新系統對現實數據的變化的認知。更新GBase8t系統對現在表中數據大小與分布的認識。原因SQL執行效能系于GBase8t

optimizer所決定的執行計劃(SQL

execution

plan)；而影響執行計劃的決定的兩個重要參考數據為統計資料(statistics)和資料分布(distributions)。GBase

8t

optimizer->執行計劃(SQL

execution

plan)執行計劃(SQL

execution

plan)->UPDATE

STATISTICS執行影響執行計劃的決定的兩個重要參考數據為統計資料(statistics)和資料分布

(distributions)。UPDATE

STATISTICS指令在SQL執行計劃有兩個主要的功能：更新在系統型錄(systemcatalogs)中統計資料決定執行計劃。更新資料分布(distribution)情形。以此決定執行計劃最有效路徑GBase

8t

Update

Statistics–實例1表格系統的表格數據

(NRows

inUpdateStatistics)真實累計的表格數據

(AccuratedNrows)＃讀出＃寫入table104139870238table211859374493846206787table340000018644384

11565541517922233table42464777

311858759

127744455Table1與Table3因其系統的表格數據(Nrow-in-Update-Statistics)與真實累計的表格數據(Accurated-Nrows)差異甚大，是亟待被執行UPDATE

STATISTICS之表格2真05實81的5

數據系統在更新前15的認知GBase

8t

Update

Statistics–實例216表格系統的表格數據(NRows

inUpdateStatistics)真實累計的表格數據

(AccuratedNrows)＃讀出＃寫入table104139820581570238Table34000001864438411565541517922233DBA執行下列指令以更新系統目錄表(system

catalogs)中的統計信息。UPDATE

STATISTICS

LOW

FOR

TABLE

Table1;UPDATE

STATISTICS

LOW

FOR

TABLE

Table3;Update

Statistics語句-I17執行統計更新的時機統計更新的語句表的內容大部分發生變化時UPDATE

STATISTICS

LOW從老版本升級以后UPDATE

STATISTICS

LOW

DROPDISTRIBUTIONS該列沒有索引或者該列不是復合索引的第一個列UPDATE

STATISTICS

MEDIUMFOR

TABLE執行統計更新的時機當表內的列數發生劇烈的變化Update

Statistics語句-II18執行統計更新的時機統計更新的語句該列沒有索引或者該列不是復合索引的第一個列UPDATE

STATISTICS

LOW列用于連接，或者作為沒有索引的過濾條件UPDATE

STATISTICS

MEDIUMFOR

TABLE執行統計更新的時機當表內的行沒有有索引Update

Statistics語句-III19執行統計更新的時機統計更新的語句列用于連接或者用于簡單索引的過濾條件UPDATE

STATISTICS

HIGHFOR

TABLE

(index

column)列用于連接或者復合索引的過濾條件UPDATE

STATISTICS

HIGHFOR

TABLE

(first

differingindexcolumn)自動更新統計數據(Automatic

Update

Statistics)AUTO_STAT_MODE＝1－自動檢測出陳舊(stale)表和索引統計信息AUTO_STAT_MODE配置參數自動檢測出表和索引統計信息陳舊，當運行UPDATESTATISTICS語句時只刷新陳舊的統計數據。自動維護優化器的統計數據AUS(Auto

UpdateStatistics)簡化數據庫上的重復性的維護工作，確保最好的性能AUS(Auto

Update

Statistics)內部是通過一系列的數據庫調度，任務，傳感器來實現可通過OAT方便的管理AUS策略OAT中自動統計更新的實踐AutoUpdate

Statistics

Evaluation；Auto

Update

Statistics

Refresh動統計更新的總結自動統計更新的實踐DBA打開系統自動統計更新，AUTO_STAT_MODE＝1從OAT調整自動統計更新的頻率與范圍從OAT檢查自動統計更新的效果與警告手動統計更新的實踐當系統升級后當有大量的數據導入當有大量的數據刪除當新建或刪除索引22鎖等待(Table

LockWait)目錄設計優化二索引策略(Index)分片(Fragmentation)123鎖等待24系統性能不佳的現象如下用戶可能會發現CPU利用率很低磁盤I/O也不忙SQL結果也需要花費很長時間這意味著用戶請求被放在隊列中等待處理鎖等待的可能原因SQL語句是否發生了順序掃描應用程序采用過高的隔離級別，太多的共享鎖造成不必要的鎖等待解決的手段找到鎖等待較多的表如何減少鎖等待隔離級別的選擇鎖等待－找到鎖等待較多的表database

sysmaster;select

dbsname,tabname,sum(pf_rqlock)

as

locks,sum(pf_wtlock)

as

lockwaits,sum(pf_deadlk)

as

deadlocksfrom

sysactptnhdr,systabnameswhere

pf_wtlock

>

0and

systabnames.partnum=

sysactptnhdr.partnumgroup

by

dbsname,tabnameorder

by

lockwaits

desc;Sysmaster

SMI界面找到鎖等待較多的表當數據庫出現鎖問題時，首先我們需要找到哪些表消耗了最多的鎖資源，哪些表出現了鎖等待和死鎖情況。從而我們可以進一步確定需要監控的對象和有針對性的優化，可以分析表的鎖模式：頁級鎖還是行級鎖，還需要監控訪問表的SQL語句是否發生了順序掃描和采用的隔離級別。首先我們需要找到哪些表消耗了最多的鎖資最大的lockwaits25鎖等待－如何減少鎖等待26從不同的角度進行調整，以減少鎖等待提高處理性能，減少記錄被鎖的時間窗口，如：合理的表結構、合理的索引、避免順序掃描合理選擇表的鎖模式：PAGE或ROW合理使用不同的隔離級別，數據庫提供了多種不同的隔離級別，需要根據具體的應用場景采用合理的隔離級別，以提高并發性。TableLock

ModeLockRequestBufferReadBuffer

WriteTb_serv_ctrlRow4037914985934541675107850Tb_finishRow262156175077487827916927頻繁的鎖請求會影響系統的性能，因為用戶的請求只有在鎖被釋放后才會被處理。用戶可能會發現即使在CPU利用率很低，磁盤I/O也不忙的情況下返回SQL結果也需要花費很長時間。這意味著用戶請求被放在隊列中等待處理。鎖等待－優化:Table

Lock

Wait鎖等待－優化表的鎖模式當你創建一個表，默認的鎖模式是頁(PAGE)。LOCK

MODE(ROW)，可以使用較小的粒度鎖定并發性增加。設置ONCONFIG中DEF_TABLE_LOCKMODE參數ROW。改變缺省的設定。使用LOCK

MODE(ROW)減少鎖沖突與鎖等待。CREATE

TABLE

new_table(

COL1

INTEGER

NOT NULL，COL2

CHAR(2)，)WITH

NO

LOGLOCK

MODE(PAGE)；ALTER

TABLE

[table

name]

LOCK

MODE(ROW);ALTER

TABLE

[table

name]

LOCK

MODE(PAGE);28鎖等待－選擇適當的隔離級別隔離級別決定SELECT語句的鎖存在或高低。隔離級別的選擇SET

ISOLATION

TO

DIRTY

READ;

（無共享鎖）SET

ISOLATION

COMMITTED

READ

LAST

COMMITTED

（無共享鎖）SET

ISOLATION

TO

READ

COMMITTED;

（缺省，加上共享鎖）SET

ISOLATION

TO

REPEATABLE

READ;

（最高）使用低的隔離級別，降低讀取時的共享鎖，避免鎖的沖突。Query:-database

stores_demo;SET

ISOLATION

TODIRTY

READ;SETLOCKMODE

TOWAIT20;select

*from

customerwhere

hex(rowid)

=

"0x0000010A”;使用低的隔離級別，降低讀取時的共享鎖，避免鎖的沖突29鎖等待－檢查當前的隔離級別和數據庫會話檢查當前的隔離級別和數據庫會話的所模式，使用onstat-g

sql命令：Sess

SQLSQL ISAM

F.E.ERR

ERR Vers

ExplainId Stmt

type18

-16

-CurrentDatabasestores_demostores_demoIso

LockLvl

ModeCRNotWaitRR

Wait

100

0

9.03

Off0

0

9.03

OffQuery:-database

stores_demo;SETLOCKMODE

TOWAIT20;select

*from

customerwhere

hex(rowid)

=

"0x0000010A”;當發生鎖的沖突，設定等待的時間設定應用程序中的鎖等待檢查當前會話(session)中的隔離級別與等待模式鎖等待的模式30鎖等待(Table

LockWait)目錄設計優化二索引策略(Index)分片(Fragmentation)123優化索引I－創建索引時的選擇32創建索引時的選擇UniqueDuplicateCompositeClusteredAttached

-Detached

-/*獨一的索引*//*允許重復的索引*//*允許復合式的索引*//*強調物理順序的索引*//*索引與數據分片在相同的dbspaces

*//*索引與數據分片在不同的dbspaces

*/對不同的數據選擇恰當的索引選擇，分片索引范例如下。分片索引范例：Detached，數據與索引不在同一dbspace上CREATE

TABLE

tb1

(a

int)

FRAGMENT

BY

EXPRESSION

(a

<=

10)INtabdbspc1,

(a<=20)

IN

tabdbspc2,

(a<=30)

IN

tabdbspc3; /*三分片*/?CREATE

INDEX

idx1

ON

tb1

(a)

FRAGMENT

BY

EXPRESSION

(a

<=

10)

INidxdbspc1,(a<=

20)IN

idxdbspc2,(a<=

30)INidxdbspc3;優化索引II－索引優點與代價33索引好處保證唯一性，先將索引加載到內存，鎖定有效的數據頁。加速查詢，避免浪費資源的循序讀取。代價在插入、更新、刪除操作時維護索引將索引加載到內存間接搜索(Indirect

Search)常見的錯誤一個表格上有，5個以上的索引，在插入、更新時付出很大的代價。類似而重復的索引，浪費資源維護相同／類似的索引。優化索引III－索引的目的34了解索引的目的連接表使用的列作為過濾器(filter)的列ORDER

BY和GROUP

BY使用的列設計索引的限制避免重復度高的列，(錯誤：SEX=男／女)保持較小的索引鍵值大小(錯誤：char(2048))限制經常發生變化的表上的索引系統必須維護索引，大量的新增，刪除是要付出維護索引的資源避免在大量數據導入導出的表上建立索引需要大量導入導出的表刪除索引優化索引IV－確定在合適的數據列上建立索引35確定在合適的數據列上建立索引找到順序掃描次數較多的表，找到使用了順序掃描的SQL語句，考慮在合適的數據列上建立索引select

p.tabname::char(20)

tabname,

t.nrows,

p.seqscansfrom

sysmaster:sysptprof

p,

systables

twhere

p.tabname=t.tabnameand

t.tabid

>99

and

p.seqscans>

0order

byp.seqscans

desc;//第一列為表名，第二列為表的數據行數，第三列為表的順序掃描次數打開SQLtracing，過一段時間后執行如下SQL語句select

distinct

sql_statement

from

sysmaster:Syssqltrace

tinner

join

sysmaster:syssqltrace_iter

ion

t.sql_id=i.sql_idwhere

i.sql_itr_info='Seq

Scan'

order

by

1;優化索引V

－刪除多余的索引36刪除多余的索引:系統中有可能存在大量的多余的索引，通過刪除多余的索引以提高插入、刪除、更新記錄的性能，同時可以節省大量的不必要的存儲。Create

index

index_1

on

table

item

(length,

width);Create

index

index_2

on

table

item

(length);

//考慮刪除index_2//優化索引VI–不鎖表37創建或是刪除索引時會導致鎖表在在線環境創建和刪除索引CREATE

INDEX

ONLINEDROP

INDEX

ONLINETheCREATEINDEXONLINE語句允許在創建索引的時候不在相關表上防止排他鎖。可以在讀取或更新表時使用CREATE

INDEXONLINE語句。這意味著索引創建可以立即開始。在線環境創建和刪除索引會消耗更多時間鎖等待(Table

LockWait)目錄設計優化二索引