3.Sybase IQ 性能调优_Server和数据库调优_V1.0.ppt

SybaseIQ性能调优王angb global 北京寰信通科技有限公司BeijingGlobalTechnologyCo Ltd 2020年3月3日 IQ性能调优的主要内容 性能调优的基本理论设计和查询 SQL 优化数据库服务器 数据库调优 SybaseIQ性能调优 Server和数据调优 王兵wangb global 北京寰信通科技有限公司BeijingGlobalTechnologyCo Ltd 2020年3月3日 主要内容 性能监控概述监控工具和统计信息Cache调优CPU Threads调优调整影响查询的数据库选项 性能监控 为什么要进行监控当系统出现异常 例如 应用缓慢 不能连接等 现象 系统管理员需要诊断和解决问题主动发现系统陷在异常 提前解决监控什么操作系统层面 CPU 内存 I O数据库层面 数据库运行情况 统计信息 可疑的SQLs怎么监控使用IQ提供的系统存储过程 数据库运行情况 使用IQ统计信息监控命令 统计信息 监控工具和统计信息 IQ提供的系统存储过程 系统表和相关函数查看IQ总体情况查看用户连接和用户活动查看IQ存储空间查看对象存储IQUTILITIESsp iqsysmon 监控工具和统计信息 查看IQ的总体情况sp iqstatus包括显示当前数据库的多种状态信息 包括 pagesize dbspaces数量和使用情况 block的使用情况 buffer的使用情况 verfsion空间情况 I O情况 backup信息等等 查看IQ数据库版本select version 与ASE相同 查看字符集SELECTdb property charset 查看createdatabase指定的选项select fromsysinfo查看server启动时的命令行selectproperty CommandLine 查看IQServerNameselectproperty Name 查看server启动时间selectproperty StartTime 查看IQ当前使用的ServerLogFileselectproperty ConsoleLogFile 监控工具和统计信息 用户连接和活动sp iqwho 显示所有当前连接到IQ的用户或连接信息 sp iqconnection 显示连接信息sp iqcontext或sa conn activity 显示连接当前执行的语句 如果语句执行完可能看不到 查看连接使用的协议 程序接口selectconnection property ClientLibrary connid 使用sp iqtransaction查看事物信息使用sp iqlocks查看锁信息使用sp iqshowpsexe查看连接中用于控制任务优先级和资源使用的数据库选项设置 监控工具和统计信息 查看IQ存储空间sp iqstatusselect fromsp iqstatus wherenamelike BlocksUsed select fromsp iqstatus wherenamelike Versions sp iqdbspace 显示每一个dbspace的详细信息sp iqdbspaceinfo 显示每一个dbspaces上有哪些对象查看对象存储sp iqtablesp iqindexsize估算表的尺寸sp iqestspace customer 1000000 131072 表名 记录数 IQPAGESIZE 监控工具和统计信息 如何找到有问题的 需要调优的SQLs 使用PDBA 抓取SQL 找到执行时间长 次数多的SQL使用IQ提供的SQLTrace功能生成SQL文件 然后使用下面的工具可以得到执行之间最长的SQL打开SQLTrace 注意trace文件需要占用文件系统空间 callsa server option request level logging SQL callsa server option request level log file sqltrace log 按执行时间从长到短的顺序查看sqlperltracetime plsqltrace logformat fixed sort n r tracetime pl文件在 ASDIR samples asa performancetracetime目录下关闭SQLTrace 注意trace文件需要占用文件系统空间 callsa server option request level log file callsa server option request level logging NONE 监控工具和统计信息 找到之后做什么 查看SQL中表的定义和索引selecttablewidth customer 查看表的行宽selectcount fromtablenamesp iqcolumn tablename 查看表中字段的数据类型sp iqpkeys tablename 查看表的主键信息sp iqindex tablename 查看表的主键信息sp iqindexmetadata indexname 查看指定索引信息 对于FP索引能够看到是否为优化的FP索引sp iqrowdensity tablename 查看FP索引信息sp iqtablesize tablename 查看表的占用的存储空间大小 监控工具和统计信息 找到之后做什么 获得执行计划beginsettemporaryoptionQUERY NAME yourqueryname settemporaryoptionQUERY PLAN ON settemporaryoptionQUERY DETAIL ON settemporaryoptionQUERY TIMING ON settemporaryoptionINDEX ADVISOR ON settemporaryoptionQUERY PLAN AFTER RUN ON settemporaryoptionQUERY PLAN AS HTML ON settemporaryoptionQUERY PLAN AS HTML DIRECTORY 下面SQL查询语句selecta service key sum local call count call count sum local call minutes call minutesfromtelco factsajoinresidential customerbona customer key b customer keywhereb state MO groupbya service keyend SQL调优 找到之后做什么 分析查询计划 然后采取行动 并进行测试增加索引设置数据库选项控制查询优化器行为 使用hints改写SQL进行测试 监控工具和统计信息 IQUtilities 所有IQ版本 通过使用IQutilities命令启动 停止监控监控结果写到server端的文件中一个数据库连接上只能有一个main监控文件和temp监控文件连接退出 监控停止sp iqsysmon 要求IQ12 7及以上版本 类似于ASE的sp sysmon 通过存储过程方式启动 停止监控监控结果可以返回到SQLclient或写入文件中比IQutilities命令具有跟多能力支持 批 和 文件 两种模式 IQUtilities 使用IQUTILITIES 比较常用有 summary interval建议为30s或60sIQUTILITIESMAININTOmonitorSTARTMONITOR summary append file suffixsummary iqmon interval30 IQUTILITIESPRIVATEINTOmonitorSTARTMONITOR summary append file suffixsummary iqmon interval60 cacheIQUTILITIESMAININTOmonitorSTARTMONITOR cache append file suffixcache iqmon interval30 IQUTILITIESPRIVATEINTOmonitorSTARTMONITOR cache append file suffixcache iqmon interval30 sp iqsysmon sp iqsysmon按节 sections 输出信息 你可以指定输出 一节 或 多节 信息一般来说 一个section描述了IQServer的一个组件的统计信息Buffermanager Threadmanagement等收集和报告指定sections的统计 计数器信息Section由头信息标识有一些sections是cache Main Temp 相关的缺省main和Temp的信息都被显示能够指定 m 或 t 前缀以限制某一个cache的信息输出 例如 sp iqsysmon start monitor mbufmantbufpool sp iqsysmon sp iqsysmon有如下sections sp iqsysmon 使用例子sp iqsysmon 00 05 00 sp iqsysmon 00 00 30 mbufmantbufman selectStatasStatName f1asStatValuefromsp iqsysmon 00 00 10 mbufmanmbufpool whereStatin Finds Hit Reads Writes GrabbedDirty BusyWaits Pinned Dirty InUse sp iqsysmon BufferManager bufman mbufman tbufman 节报告IQBufferManager组件的统计信息用于问题分析的一些重要信息PhysicalIOvolumeReads Writes 物理 读 写 操作的次数PReadBlks PWriteBlks 物理读 写的块数PReadKB PWriteKB 物理读 写以KB为单位的量Cache操作相关信息Finds buffercache被请求的次数 如果Finds的值突然降到并保持为0 那么Server很可能发生内部 死锁 Hits buffercache命中的次数 Hit buffercache命中率 是指无须发生物理I O请求 buffercache就可以满足请求的百分比 Creates Destroys buffers 创建 销毁 操作被调用的次数Dirties buffer被修改的次数GrabbedDirty 某一操作为获取buffers 必须停止以等待这些脏buffers中的数据写到磁盘上的次数 PrefetcheffectivenessPrefetchReqs PrefetchNotInMem PrefetchInMem按MainCache和TempCache分别输出信息 sp iqsysmon BufferManager bufman mbufman tbufman 节输出信息示例 sp iqsysmon BufferPool bufpool mbufpool tbufpool 节报告IQBufferPool组件的统计信息用于问题分析的一些重要信息Cache中buffers的移动情况MovedToMRU 在使用之后被放回MRU端的buffers数量MovedToWash 在使用之后被直接放到wash区的buffers数量RemovedFromLRU 被从MRU LRU链表中删除的buffers数量RemovedFromwash 被从wash区删除的buffers数量Cache尺寸和buffers使用情况Pages cache中的buffer page的数量InUse cache中被使用的buffer的数量Dirty cache中buffer被修改的次数Pinned cache中被使用并且被锁住的buffers的数量 sp iqsysmon BufferPool bufpool mbufpool tbufpool 节用于问题分析的一些重要信息Cache刷新和wash区情况Flushes flush操作被调用的次数FlushedBufferCount 被刷新到磁盘的buffers的数量Washed 通过washmarker的buffers的数量TimesSweepersWoken 没有工作可做的sweeper线程被唤醒的次数WashTeamSize ofthreadsinthesweeperteamWashMaxSize wash区中buffers的数量washNBuffers 通过washmarker的 干净 buffers的数量washNDirtyBuffers 通过washmarker的 脏 buffers的数量washSignalThreshold 在sweeper线程被唤醒之前能够通过washmarker的 脏 buffers的数量washNActiveSweepers 实际正在工作中的sweeper线程数washIntensity 内部使用的标记按MainCache和TempCache分别输出信息 sp iqsysmon ThreadManagement threads 节ThreadManagement组件的统计信息用于问题分析的一些重要信息ThrNumOfCpus CPU数量ThreadLimit IQ能够使用的最大线程数ThrNumThreads ofthreadsactuallyuseableThrReserved 保留的线程数ThrNumFree 当前可以用来被分配的线程数NumThrUsed 当前正在使用的线程数UsedPerActiveCmd ofthreadspercommand是Server级的 sp iqsysmon 在sp iqsysmon输出上使用查询语句FieldsarefixedwidthtosimplifyparsingUsederivedtableorviewoversp iqsysmonforcomplexqueriesResultscanbeselectedintotableforaggregation例如 selectsubstr f1 11 5 as ThreadsUsedPercent fromsp iqsysmon 00 00 00 threads whereStat NumThrUsed selectStatasStatName f1asStatValuefromsp iqsysmon 00 00 10 mbufmanmbufpool whereStatin Finds Hit Reads Writes GrabbedDirty BusyWaits Pinned Dirty InUse Cache调优 QueryOperationsandIQCaches IQMainCache IQTempCache HashesHashJoins HJ HPDJ GroupBy Hash SortsSortJoins SMJ SMPDJ GroupBy Sort OrderbyNestedLoopJoins NLJ NLPDJoinsReadsfromtheIQStore Cache调优 了解Cache的使用 MainCache对于如下的操作是重要的 Verticalgroup by Group Byindex NLPDJoinsCorrelatedsubqueriesPrefetchTempCache对如下的操作是重要的 NestedLoopJoin的Smallside存储将消耗TempCache内存HashJoin HashGroup bySorts join和groupby等 Cache调优 Cache调整的思路 隔离问题查询获得多个查询的执行时间 然后绘制一个条形图是一个好的开始点焦点是那些对Cache造成较大压力的查询对于问题查询使用工具捕获监控数据在查询执行的高峰期间捕获监控数据应深入分析 不要盲目 轻率地增加Cache尺寸应当有理由地增加maincache或tempcache在一些情况下 增加cache可能会使得查询性能降低 Cache调优 一个案例 焦点是 Q10 因为它的执行时间最长 隔离问题查询 多个查询的执行时间图 Cache调优 一个案例 对问题查询使用sp iqsysmon获得Cache的统计信息 Cache调优 一个案例 对问题查询Q10使用sp iqsysmon获得Cache的统计信息 Cache调优 一个案例 对问题查询进行分析 依据 统计信息 和 查询计划 分析监控统计信息Maincache和Tempcache的Hitrates看上去比较好Tempcache的Hashfind值比较高Hashfind统计值占整个find统计值的比重较大 接近100 Finds比物理读代价小 但是也需要开销的 Hash已经超过了它的pinquota 325hash页被创建 但是quota只是183 BufferAllocator Temporary section Cache调优 一个案例 对问题查询进行分析 依据 统计信息 和 查询计划 分析查询计划 优化器对Hashjoin的估算不准确 Cache调优 一个案例 调整maincache和tempcache的比例 3 2Maincache Cache调优 一个案例 调整maincache和tempcache的比例 3 2Tempcache Cache调优 一个案例 注意 hashfinds值已经从19 5M减少到9 8M 调整maincache和tempcache的比例 2 3Tempcache Cache调优 一个案例 调整maincache和tempcache的比例 2 3maincache Cache调优 一个案例 采取行动 调整cache的比例以main tempcache3 2比例开始以相同的负载运行Q10查询 并在执行期间运行sp iqsysmon收集监控信息什么时候maincache的比例合适 Excellentfindrates 命中率不会由于rebalancing而下降 Verticalgroup byorNLPDjoinnotrequiredforperformanceWhenisitoktotakeawaytempcache HashPages创建的数量 看sp iqsysmon输出的Creates指标 不能超过hashpinquota值 看sp iqsysmon输出的PinUserQuota指标 Onlywritesarefromlargesorts 看tempcachedebug输出的Writes PWriteBlks PWriteKB指标值 hash的写不能太高 Maxpinned 看sp iqsysmon输出的Pinned指标 wellwithincachesizeHashFinds的值与总Finds值的比率不能太高在调整比例时一次不要太大 不超过10 Cache调优 Cache负载过重 Cache繁忙的症状Sweepers写的速度赶不上pagedirites的速度命令执行需要cachebuffers 但是没有可用的 干净 buffers 必须等待 脏 buffers中修改的数据写盘下面的统计值可以用来反映Cache是否繁忙Dirtyat100 HighGrabbedDirtyHighBusyWaits纠正措施Increasesweeper towriteoutdirtycacheasynchronouslyIncreasewasharea Cache调优 Cache负载过重 影响CacheWash区操作繁忙程度的数据库选项Wash Area Buffers PercentSpecifiesthesizeofthewashareaasapercentoftotalbuffersDirtybuffersinthewashareawillbewrittenoutasynchronouslySweeper Threads PercentSpecifiesthepercentofthreadsassignedtoasynchronouslywritedirtybuffersIncreaseifI OboundcommandsarecausingGrabbedDirty Cache调优 多并发用户分析 确定并发用户的高峰值注意sp iqsysmon输出中 BufferAllocator节 的NActiveCommands统计值 这个值可以反映出当前并发活动用户的数量使用sp iqcontext可以看到当前活动的SQL语句并发用户多可能会导致资源峰值GrabbedDirty统计值增加意味着cache中充满了 脏 buffers比较高的NActiveCommands值 可能会伴随cache有比较大的使用记住 buffercache是非常宝贵的资源 线程调优 CPUUsage 标识CPU密集的操作有很多排序或hash操作的查询BitmappedIndexesCorrectiveActionsUseindexestoreducecomputationcostRewritequerytogainparallelism 线程调优 降低Cache的使用 增加索引Indexesarepre computedresultsLessdatawillbeprocessedhorizontallyintempBettermetadatawillreducesortusage使用 谓词 hints能够用于指定谓词和join算法Tryforpush downjoinswhenpossible especiallyforsort merge 线程调优 限制命令的并行 LoadSetoptionMax IQ Threads Per TeamDeleteSetoptionMax IQ Threads Per TeamtolimitdeletethreadsQueriesPredicates ParallelPrepare setoptionMax IQ Threads Per TeamParallelHashGroup by setoptionParallel GBH UnitsDBCCSpecify resources percenttolimitpercentofCPUsused 线程调优 数据库级线程设置 iqnumbercpusIftheoperatingsystemreportsthosehyper threadsasCPUs reportedintherootnodeinaqueryplanas NumberofCPUs thenusetheserverstartupoptiontotelltheservertherealnumberofCPUsinthesystem iqmtLimitstotalnumberofthreads iqtssLimitsstacksizeforIQthreadsIncreaseonlytosupportquerieswithdeeplynestedexpressions Multiplyby threadstodeterminememoryusage 线程调优 连接级线程设置 SetoptionMax IQ Threads Per ConnectionMaximumcumulativenumberofthreadsthatwillbeallocatedtoasingleconnection NotstrictlyenforcedforallcommandsSetoptionMax Threads Per Team制定了分配给一个并行操作的最大线程数如果Max IQ Threads Per Connection选项修改了 那么这个选项也应该进行调整 影响查询的数据库选项 DatabaseOptions能够影响查询计划DatabaseOptions有不同层次的影响entirequerysessionallusersontheserver 影响查询的数据库选项 下面是一些能够影响用户查询的普通数据库选项Query Temp Space Limit default2000 MB LimitsuseofanyTempresources cache disk tousersSuggestsettingtozero 0 toallowunlimiteduseMinimize Storage defaultOffAffectsFPindexescreatedfornewtablesWithfewexceptions thisoptionshouldalwaysbesetONForce No Scroll Cursors defaultOffCausesqueryresultstobebufferedtoTempcacheAtmostsitesoptionshouldbechangedtopermanentlyON 影响查询的数据库选项 影响join操作的数据库选项有两个选项可以影响查询优化器分别影响joinordr和join算法缺省情况下 优化器可以做出正确的选择 不需要设置这两个选项Youcantrytochangethebehaviorusingtheseoptions butthereisnoguaranteetheoptimizerwillcomply影响join算法数据库选项 Join Preference对于多表关联查询 这个选项影响所有joins影响joinorderDatabaseOption Join Optimization 影响查询的数据库选项 Join PreferenceOption 1 PreferSort Merge 2 PreferNestedLoop 3 PreferNestedLoopPD 4 PreferHash 5 PreferHashPD 6 PreferPreJoin 7 PreferSortMergePD 1 AvoidSort Merge 2 AvoidNestedLoop 3 AvoidNestedLoopPD 4 AvoidHash 5 AvoidHashPD 6 AvoidPreJoin 7 AvoidSort MergePD Join PreferenceDefault 0 OptimizerDecides YoucanonlyinfluencejointypefromthelistofvalidjoinalgorithmsintheQueryPlan otherwiseitwillignoretheoption 影响查询的数据库选项 Join Preference 下图中的join节点有6种有效的join算法为了避免Sort Mergejoins 使用下面的方法SettemporaryoptionJoin Preference 1 影响查询的数据库选项 Join Optimization 选项设为OFF 将按照FROM子句中从左向右的顺序决定表关联的顺序设置这个选项对特定查询有可能起作用 但是只有查询优化器判断错误时才设置这个选项不会影响join算法的选择 Join OptimizationDefault ON OptimizerDecides 影响Hash算法 JoinorGroup 的数据库选项 一般来说Hash算法要比其他算法快通过改变一些数据库选项 可以增加优化器选择Hash算法的可能性Hash操作总是在TempCache中进行OnereasonwerecommendallocatingcachestoallowmoreTempCachememoryTheoptionsdiscussedaffectmemoryintheTempCache注意 Thedefaultsfortheseoptionswereestablishedforthe average systemSystemswithlargememorymaybetoabletobenefit 分析Hash Based操作 AlltempcachepagesinahashmustbekeptpinnedinthecachetoachieveoptimalperformanceInaqueryplanlookatthelineslike Maximumhashpinquote 1712Finalhashbuffercount 1711如果 Finalhashbuffercount 的值超过了 Maximumhashpinquota 的值 那么说明hashtable需要的buffers数量已经超过了IQServer允许该hash对象所能使用的pinnedbuffers最大数量 Max Hash Rows数据库选项 这个选项设置优化器考虑使用hash操作的