gb perf 2系統(tǒng)與設(shè)計優(yōu)化

GBase

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

和OLAP

的參數(shù)設(shè)定目錄系統(tǒng)優(yōu)化—統(tǒng)計更新(Update

Statistics)12OLTP－聯(lián)機事務(wù)處理(OLTP)5OLTP(Online

Transaction

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

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

size=2K,buffers=500000,lrus=16,lru_min_dirty=50.00,lru_max_dirty=60.0（為降低checkpoint的IO量可以設(shè)定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

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

Parallel

database

queries)使用Light

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

to

clientScanScanSortScanJoinSortSend

to

clientOLAP配置參數(shù)MAX_PDQPRIORITY100為防止PDQ任務(wù)占用過多資源，推薦設(shè)置為60-80DS_MAX_QUERIES同時支持最大的PDQ查詢個數(shù)，默認2*CPUVP*128建議修改為32-256DS_TOTAL_MEMORYPDQ任務(wù)可用內(nèi)存大小默認值：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線程數(shù)，默認值即可(1048567)onmode

–M(DS_TOTAL_MEMORY)onmode–Q

(DS_MAX_QUERIES)onmode

–D

(MAX_PDQPRIORITY)onmode

–S(DS_MAX_SCANS)ONCONFIG

相關(guān)參數(shù)在線動態(tài)修改PDQ參數(shù)OLAP－適合使用PDQ的場景使用PDQ的前提設(shè)置PDQ相關(guān)參數(shù)Set

PDQPRIORITY

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

by

,group

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

by,order

by,集合操作：union,distinct等創(chuàng)建index利用外部表external

table批量數(shù)據(jù)導(dǎo)入、導(dǎo)出HPL使用PDQ的典型場景OLAP－參數(shù)動態(tài)調(diào)整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

的參數(shù)設(shè)定目錄系統(tǒng)優(yōu)化—統(tǒng)計更新(Update

Statistics)12GBase

8t統(tǒng)計更新(Update

Statistics)14統(tǒng)計更新的目的更新系統(tǒng)對現(xiàn)實數(shù)據(jù)的變化的認知。更新GBase8t系統(tǒng)對現(xiàn)在表中數(shù)據(jù)大小與分布的認識。原因SQL執(zhí)行效能系于GBase8t

optimizer所決定的執(zhí)行計劃(SQL

execution

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

8t

optimizer->執(zhí)行計劃(SQL

execution

plan)執(zhí)行計劃(SQL

execution

plan)->UPDATE

STATISTICS執(zhí)行影響執(zhí)行計劃的決定的兩個重要參考數(shù)據(jù)為統(tǒng)計資料(statistics)和資料分布

(distributions)。UPDATE

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

8t

Update

Statistics–實例1表格系統(tǒng)的表格數(shù)據(jù)

(NRows

inUpdateStatistics)真實累計的表格數(shù)據(jù)

(AccuratedNrows)＃讀出＃寫入table104139870238table211859374493846206787table340000018644384

11565541517922233table42464777

311858759

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

STATISTICS之表格2真05實81的5

數(shù)據(jù)系統(tǒng)在更新前15的認知GBase

8t

Update

Statistics–實例216表格系統(tǒng)的表格數(shù)據(jù)(NRows

inUpdateStatistics)真實累計的表格數(shù)據(jù)

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

catalogs)中的統(tǒng)計信息。UPDATE

STATISTICS

LOW

FOR

TABLE

Table1;UPDATE

STATISTICS

LOW

FOR

TABLE

Table3;Update

Statistics語句-I17執(zhí)行統(tǒng)計更新的時機統(tǒng)計更新的語句表的內(nèi)容大部分發(fā)生變化時UPDATE

STATISTICS

LOW從老版本升級以后UPDATE

STATISTICS

LOW

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

STATISTICS

MEDIUMFOR

TABLE執(zhí)行統(tǒng)計更新的時機當表內(nèi)的列數(shù)發(fā)生劇烈的變化Update

Statistics語句-II18執(zhí)行統(tǒng)計更新的時機統(tǒng)計更新的語句該列沒有索引或者該列不是復(fù)合索引的第一個列UPDATE

STATISTICS

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

STATISTICS

MEDIUMFOR

TABLE執(zhí)行統(tǒng)計更新的時機當表內(nèi)的行沒有有索引Update

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

STATISTICS

HIGHFOR

TABLE

(index

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

STATISTICS

HIGHFOR

TABLE

(first

differingindexcolumn)自動更新統(tǒng)計數(shù)據(jù)(Automatic

Update

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

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

Update

Statistics)內(nèi)部是通過一系列的數(shù)據(jù)庫調(diào)度，任務(wù)，傳感器來實現(xiàn)可通過OAT方便的管理AUS策略O(shè)AT中自動統(tǒng)計更新的實踐AutoUpdate

Statistics

Evaluation；Auto

Update

Statistics

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

LockWait)目錄設(shè)計優(yōu)化二索引策略(Index)分片(Fragmentation)123鎖等待24系統(tǒng)性能不佳的現(xiàn)象如下用戶可能會發(fā)現(xiàn)CPU利用率很低磁盤I/O也不忙SQL結(jié)果也需要花費很長時間這意味著用戶請求被放在隊列中等待處理鎖等待的可能原因SQL語句是否發(fā)生了順序掃描應(yīng)用程序采用過高的隔離級別，太多的共享鎖造成不必要的鎖等待解決的手段找到鎖等待較多的表如何減少鎖等待隔離級別的選擇鎖等待－找到鎖等待較多的表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界面找到鎖等待較多的表當數(shù)據(jù)庫出現(xiàn)鎖問題時，首先我們需要找到哪些表消耗了最多的鎖資源，哪些表出現(xiàn)了鎖等待和死鎖情況。從而我們可以進一步確定需要監(jiān)控的對象和有針對性的優(yōu)化，可以分析表的鎖模式：頁級鎖還是行級鎖，還需要監(jiān)控訪問表的SQL語句是否發(fā)生了順序掃描和采用的隔離級別。首先我們需要找到哪些表消耗了最多的鎖資最大的lockwaits25鎖等待－如何減少鎖等待26從不同的角度進行調(diào)整，以減少鎖等待提高處理性能，減少記錄被鎖的時間窗口，如：合理的表結(jié)構(gòu)、合理的索引、避免順序掃描合理選擇表的鎖模式：PAGE或ROW合理使用不同的隔離級別，數(shù)據(jù)庫提供了多種不同的隔離級別，需要根據(jù)具體的應(yīng)用場景采用合理的隔離級別，以提高并發(fā)性。TableLock

ModeLockRequestBufferReadBuffer

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

Lock

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

MODE(ROW)，可以使用較小的粒度鎖定并發(fā)性增加。設(shè)置ONCONFIG中DEF_TABLE_LOCKMODE參數(shù)ROW。改變?nèi)笔〉脑O(shè)定。使用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鎖等待－選擇適當?shù)母綦x級別隔離級別決定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鎖等待－檢查當前的隔離級別和數(shù)據(jù)庫會話檢查當前的隔離級別和數(shù)據(jù)庫會話的所模式，使用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”;當發(fā)生鎖的沖突，設(shè)定等待的時間設(shè)定應(yīng)用程序中的鎖等待檢查當前會話(session)中的隔離級別與等待模式鎖等待的模式30鎖等待(Table

LockWait)目錄設(shè)計優(yōu)化二索引策略(Index)分片(Fragmentation)123優(yōu)化索引I－創(chuàng)建索引時的選擇32創(chuàng)建索引時的選擇UniqueDuplicateCompositeClusteredAttached

-Detached

-/*獨一的索引*//*允許重復(fù)的索引*//*允許復(fù)合式的索引*//*強調(diào)物理順序的索引*//*索引與數(shù)據(jù)分片在相同的dbspaces

*//*索引與數(shù)據(jù)分片在不同的dbspaces

*/對不同的數(shù)據(jù)選擇恰當?shù)乃饕x擇，分片索引范例如下。分片索引范例：Detached，數(shù)據(jù)與索引不在同一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;優(yōu)化索引II－索引優(yōu)點與代價33索引好處保證唯一性，先將索引加載到內(nèi)存，鎖定有效的數(shù)據(jù)頁。加速查詢，避免浪費資源的循序讀取。代價在插入、更新、刪除操作時維護索引將索引加載到內(nèi)存間接搜索(Indirect

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

BY和GROUP

BY使用的列設(shè)計索引的限制避免重復(fù)度高的列，(錯誤：SEX=男／女)保持較小的索引鍵值大小(錯誤：char(2048))限制經(jīng)常發(fā)生變化的表上的索引系統(tǒng)必須維護索引，大量的新增，刪除是要付出維護索引的資源避免在大量數(shù)據(jù)導(dǎo)入導(dǎo)出的表上建立索引需要大量導(dǎo)入導(dǎo)出的表刪除索引優(yōu)化索引IV－確定在合適的數(shù)據(jù)列上建立索引35確定在合適的數(shù)據(jù)列上建立索引找到順序掃描次數(shù)較多的表，找到使用了順序掃描的SQL語句，考慮在合適的數(shù)據(jù)列上建立索引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;//第一列為表名，第二列為表的數(shù)據(jù)行數(shù)，第三列為表的順序掃描次數(shù)打開SQLtracing，過一段時間后執(zhí)行如下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;優(yōu)化索引V

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

index

index_1

on

table

item

(length,

width);Create

index

index_2

on

table

item

(length);

//考慮刪除index_2//優(yōu)化索引VI–不鎖表37創(chuàng)建或是刪除索引時會導(dǎo)致鎖表在在線環(huán)境創(chuàng)建和刪除索引CREATE

INDEX

ONLINEDROP

INDEX

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

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

LockWait)目錄設(shè)計優(yōu)化二索引