gb perf 2系统与设计优化

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)目录设计优化二索引