3.Sybase IQ 性能调优 Server和数据库调优 V1.0ppt课件

1、Sybase IQ性能调优 王兵wangbglobal- 北京寰信通科技有限公司 Beijing Global Technology Co. Ltd. 2020年6月22日,IQ性能调优的主要内容,性能调优的基本理论 设计和查询(SQL)优化 数据库服务器/数据库调优,Sybase IQ性能调优 (Server和数据调优) 王兵 wangbglobal- 北京寰信通科技有限公司 Beijing Global Technology Co. Ltd. 2020年6月22日,主要内容,性能监控概述 监控工具和统计信息 Cache调优 CPU/Threads调优 调整影响查询

2、的数据库选项,性能监控,为什么要进行监控 当系统出现异常（例如：应用缓慢、不能连接等）现象，系统管理员需要诊断和解决问题 主动发现系统陷在异常，提前解决 监控什么 操作系统层面：CPU、内存、I/O 数据库层面: 数据库运行情况、统计信息、可疑的SQLs 怎么监控 使用IQ提供的系统存储过程(数据库运行情况) 使用IQ统计信息监控命令(统计信息),监控工具和统计信息,IQ提供的系统存储过程、系统表和相关函数 查看IQ总体情况 查看用户连接和用户活动 查看IQ存储空间 查看对象存储 IQ UTILITIES sp_iqsysmon,监控工具和统计信息,查看IQ的总体情况 sp_iqstatus

3、包括显示当前数据库的多种状态信息，包括：page size、dbspaces数量和使用情况、block的使用情况、buffer的使用情况、verfsion空间情况、I/O情况、backup信息等等。 查看IQ数据库版本 select version（与ASE相同） 查看字符集 SELECT db_property( charset ) 查看create database指定的选项 select * from sysinfo 查看server启动时的命令行 select property(CommandLine) 查看IQ Server Name select property(Name) 查看

4、server启动时间 select property(StartTime) 查看IQ当前使用的Server Log File select property(ConsoleLogFile),监控工具和统计信息,用户连接和活动 sp_iqwho /显示所有当前连接到IQ的用户或连接信息。 sp_iqconnection /显示连接信息 sp_iqcontext或sa_conn_activity /显示连接当前执行的语句(如果语句执行完可能看不到) 查看连接使用的协议/程序接口 select connection_property(ClientLibrary,connid) 使用sp_iqtran

5、saction查看事物信息 使用sp_iqlocks查看锁信息 使用sp_iqshowpsexe查看连接中用于控制任务优先级和资源使用的数据库选项设置,监控工具和统计信息,查看IQ存储空间 sp_iqstatus select * from sp_iqstatus() where name like %Blocks Used% select * from sp_iqstatus() where name like %Versions% sp_iqdbspace /显示每一个dbspace的详细信息 sp_iqdbspaceinfo /显示每一个dbspaces上有哪些对象 查看对象存储 sp_

6、iqtable sp_iqindexsize 估算表的尺寸 sp_iqestspace customer,1000000,131072 /表名、记录数、IQ PAGE SIZE,监控工具和统计信息,如何找到有问题的/需要调优的SQLs？ 使用PDBA，抓取SQL，找到执行时间长、次数多的SQL 使用IQ提供的SQL Trace功能生成SQL文件，然后使用下面的工具可以得到执行之间最长的SQL 打开SQL Trace（注意trace文件需要占用文件系统空间!） call sa_server_option(request_level_logging, SQL); call sa_server_op

7、tion(request_level_log_file,sqltrace.log); 按执行时间从长到短的顺序查看sql perl tracetime.pl sqltrace.log format=fixed | sort -n -r /tracetime.pl文件在$ASDIR/samples/asa/performancetracetime目录下 关闭SQL Trace(注意trace文件需要占用文件系统空间!) call sa_server_option(request_level_log_file,); call sa_server_option(request_level_loggi

8、ng,NONE);,监控工具和统计信息,找到之后做什么？ 查看SQL中表的定义和索引 select tablewidth(customer) -查看表的行宽 select count(*) from tablename sp_iqcolumn tablename -查看表中字段的数据类型 sp_iqpkeys tablename -查看表的主键信息 sp_iqindex tablename -查看表的主键信息 sp_iqindexmetadata indexname -查看指定索引信息，对于FP索引能够看到是否为优化的FP索引 sp_iqrowdensity tablename -查看FP索引

9、信息 sp_iqtablesize tablename -查看表的占用的存储空间大小,监控工具和统计信息,找到之后做什么？ 获得执行计划 begin set temporary option QUERY_NAME= yourqueryname; set temporary option QUERY_PLAN=ON; set temporary option QUERY_DETAIL=ON; set temporary option QUERY_TIMING=ON; set temporary option INDEX_ADVISOR=ON; set temporary option QUERY

10、_PLAN_AFTER_RUN=ON; set temporary option QUERY_PLAN_AS_HTML=ON; set temporary option QUERY_PLAN_AS_HTML_DIRECTORY=./; -下面SQL查询语句 select a.service_key,sum(local_call_count) call_count,sum(local_call_minutes) call_minutes from telco_facts a join residential_customer b on a.customer_key = b.customer_ke

11、y where b.state = MO group by a.service_key end,SQL调优,找到之后做什么？ 分析查询计划，然后采取行动，并进行测试 增加索引 设置数据库选项 控制查询优化器行为/使用hints 改写SQL 进行测试,监控工具和统计信息,IQ Utilities (所有IQ版本) 通过使用IQ utilities 命令启动/停止监控 监控结果写到server端的文件中 一个数据库连接上只能有一个main 监控文件和temp监控文件 连接退出，监控停止 sp_iqsysmon (要求IQ 12.7及以上版本) 类似于ASE的sp_sysmon，通过存储过程方式启动

12、/停止监控 监控结果可以返回到SQL client或写入文件中 比IQ utilities 命令具有跟多能力 支持“批”和“文件”两种模式,IQ Utilities,使用IQ UTILITIES，比较常用有： -summary -interval 建议为30s或60s IQ UTILITIES MAIN INTO monitor START MONITOR -summary -append -file_suffix summary-iqmon -interval 30; IQ UTILITIES PRIVATE INTO monitor START MONITOR -summary -appe

13、nd -file_suffix summary-iqmon -interval 60; -cache IQ UTILITIES MAIN INTO monitor START MONITOR -cache -append -file_suffix cache-iqmon -interval 30; IQ UTILITIES PRIVATE INTO monitor START MONITOR -cache -append -file_suffix cache-iqmon -interval 30,sp_iqsysmon,sp_iqsysmon按节(sections)输出信息，你可以指定输出“一

14、节”或“多节”信息 一般来说，一个section描述了IQ Server的一个组件的统计信息 Buffer manager、Thread management等 收集和报告指定sections的统计/计数器信息 Section由头信息标识 有一些sections是cache(Main/Temp)相关的 缺省 main 和 Temp的信息都被显示 能够指定m 或 t前缀以限制某一个cache的信息输出. 例如： sp_iqsysmon start_monitor mbufman tbufpool,sp_iqsysmon,sp_iqsysmon有如下sections,sp_iqsysmon,使用例

15、子 sp_iqsysmon 00:05:00 sp_iqsysmon 00:00:30,mbufman tbufman select Stat as StatName,f1 as StatValue from sp_iqsysmon(00:00:10,mbufman mbufpool ) where Stat in (Finds,Hit%,Reads,Writes,GrabbedDirty,BusyWaits,Pinned,Dirty,InUse),sp_iqsysmon,Buffer Manager(bufman/mbufman/tbufman)节 报告IQ Buffer Manager组件

16、的统计信息 用于问题分析的一些重要信息 Physical IO volume Reads/Writes: 物理“读/写”操作的次数 PReadBlks/PWriteBlks：物理读/写的块数 PReadKB/PWriteKB：物理读/写以KB为单位的量 Cache操作相关信息 Finds：buffer cache被请求的次数. 如果Finds的值突然降到并保持为0，那么Server很可能发生内部“死锁”. Hits： buffer cache命中的次数. Hit%: buffer cache命中率, 是指无须发生物理I/O请求，buffer cache就可以满足请求的百分比. Creates/

17、Destroys:buffers “创建” / “销毁”操作被调用的次数 Dirties：buffer被修改的次数 GrabbedDirty: 某一操作为获取buffers，必须停止以等待这些脏buffers中的数据写到磁盘上的次数. Prefetch effectiveness PrefetchReqs 、PrefetchNotInMem 、PrefetchInMem 按Main Cache和Temp Cache分别输出信息,sp_iqsysmon,Buffer Manager(bufman/mbufman/tbufman)节 输出信息示例,sp_iqsysmon,Buffer Pool(b

18、ufpool/mbufpool/tbufpool)节 报告IQ Buffer Pool组件的统计信息 用于问题分析的一些重要信息 Cache中buffers的移动情况 MovedToMRU：在使用之后被放回MRU端的buffers数量 MovedToWash：在使用之后被直接放到wash区的buffers数量 RemovedFromLRU：被从MRU-LRU链表中删除的buffers数量 RemovedFromwash：被从wash区删除的buffers数量 Cache尺寸和buffers使用情况 Pages：cache中的buffer/page的数量 InUse： cache中被使用的buf

19、fer的数量 Dirty： cache中buffer被修改的次数 Pinned：cache中被使用并且被锁住的buffers的数量,sp_iqsysmon,Buffer Pool(bufpool/mbufpool/tbufpool)节 用于问题分析的一些重要信息 Cache刷新和wash区情况 Flushes：flush操作被调用的次数 FlushedBufferCount：被刷新到磁盘的buffers的数量 Washed：通过wash marker的buffers的数量 TimesSweepersWoken：没有工作可做的sweeper线程被唤醒的次数 WashTeamSize： # of

20、threads in the sweeper team WashMaxSize：wash区中buffers的数量 washNBuffers：通过wash marker的“干净”buffers的数量 washNDirtyBuffers：通过wash marker的“脏”buffers的数量 washSignalThreshold：在sweeper线程被唤醒之前能够通过wash marker的“脏”buffers的数量 washNActiveSweepers：实际正在工作中的sweeper线程数 washIntensity：内部使用的标记 按Main Cache和Temp Cache分别输出信息,

21、sp_iqsysmon,Thread Management(threads)节 Thread Management组件的统计信息 用于问题分析的一些重要信息 ThrNumOfCpus：CPU数量 ThreadLimit：IQ能够使用的最大线程数 ThrNumThreads：# of threads actually useable ThrReserved：保留的线程数 ThrNumFree：当前可以用来被分配的线程数 NumThrUsed：当前正在使用的线程数 UsedPerActiveCmd：# of threads per command 是Server级的,sp_iqsysmon,在sp

22、_iqsysmon输出上使用查询语句 Fields are fixed width to simplify parsing Use derived table or view over sp_iqsysmon for complex queries Results can be selected into table for aggregation 例如: select substr(f1,11,5) as ThreadsUsedPercent from sp_iqsysmon(00:00:00, threads) where Stat = NumThrUsed select Stat as

23、StatName,f1 as StatValue from sp_iqsysmon(00:00:10,mbufman mbufpool ) where Stat in (Finds,Hit%,Reads,Writes,GrabbedDirty,BusyWaits,Pinned,Dirty,InUse),Cache调优 - Query Operations and IQ Caches,IQ Main Cache,IQ Temp Cache,Hashes Hash Joins (HJ, HPDJ) Group By (Hash) Sorts Sort Joins (SMJ, SMPDJ) Grou

24、p By (Sort) Order by Nested Loop Joins (NLJ),NLPD Joins Reads from the IQ Store,Cache调优 - 了解 Cache 的使用,Main Cache 对于如下的操作是重要的： Vertical group-by(Group-By index) NLPD Joins Correlated subqueries Prefetch Temp Cache 对如下的操作是重要的： Nested Loop Join 的Small side存储将消耗Temp Cache内存 Hash Join/Hash Group-by Sort

25、s (join和group by等),Cache调优- Cache调整的思路,隔离问题查询 获得多个查询的执行时间，然后绘制一个条形图是一个好的开始点 焦点是那些对Cache造成较大压力的查询 对于问题查询使用工具捕获监控数据 在查询执行的高峰期间捕获监控数据 应深入分析，不要盲目、轻率地增加Cache尺寸 应当有理由地增加main cache或temp cache 在一些情况下，增加cache可能会使得查询性能降低,Cache调优- 一个案例,焦点是Q10，因为它的执行时间最长,隔离问题查询多个查询的执行时间图,Cache调优- 一个案例,对问题查询使用sp_iqsysmon获得Cache的

26、统计信息,Cache调优- 一个案例,对问题查询 Q10使用sp_iqsysmon获得Cache的统计信息,Cache调优- 一个案例,对问题查询进行分析(依据“统计信息”和“查询计划”) 分析监控统计信息 Main cache和Temp cache的Hit rates看上去比较好 Temp cache的Hash find 值比较高 Hash find统计值占整个find统计值的比重较大(接近100%) Finds比物理读代价小，但是也需要开销的！ Hash已经超过了它的pin quota! 325 hash 页被创建，但是quota只是183,Buffer Allocator（Tempora

27、ry） section,Cache调优- 一个案例,对问题查询进行分析(依据“统计信息”和“查询计划”) 分析查询计划,优化器对Hash join的估算不准确,Cache调优- 一个案例,调整main cache和temp cache的比例(3:2 Main cache),Cache调优- 一个案例,调整main cache和temp cache的比例(3:2 Temp cache),Cache调优- 一个案例,注意：hash finds值已经从 19.5M减少到9.8M!,调整main cache和temp cache的比例(2:3 Temp cache),Cache调优- 一个案例,调整m

28、ain cache和temp cache的比例(2:3 main cache),Cache调优- 一个案例,采取行动：调整cache的比例 以main/temp cache 3:2比例开始 以相同的负载运行Q10查询，并在执行期间运行sp_iqsysmon收集监控信息 什么时候main cache的比例合适？ Excellent find rates(命中率不会由于rebalancing而下降) Vertical group-by or NLPD join not required for performance When is it ok to take away temp cache? H

29、ash Pages创建的数量（看sp_iqsysmon输出的Creates指标）不能超过hash pin quota值（看sp_iqsysmon输出的PinUserQuota指标） Only writes are from large sorts(看temp cache debug输出的Writes/PWriteBlks/PWriteKB指标值，hash 的写不能太高) Max pinned (看sp_iqsysmon输出的Pinned指标)well within cache size Hash Finds的值与总Finds值的比率不能太高 在调整比例时一次不要太大（不超过10%）,Cache

30、调优 Cache 负载过重,Cache繁忙的症状 Sweepers写的速度赶不上page dirites的速度 命令执行需要cache buffers，但是没有可用的“干净”buffers，必须等待“脏”buffers中修改的数据写盘 下面的统计值可以用来反映Cache是否繁忙 Dirty at 100% High GrabbedDirty High BusyWaits 纠正措施 Increase sweeper % to write out dirty cache asynchronously Increase wash area,Cache调优 Cache 负载过重,影响Cache Was

31、h区操作繁忙程度的数据库选项 Wash_Area_Buffers_Percent Specifies the size of the wash area as a percent of total buffers Dirty buffers in the wash area will be written out asynchronously Sweeper_Threads_Percent Specifies the percent of threads assigned to asynchronously write dirty buffers Increase if I/O bound c

32、ommands are causing GrabbedDirty,Cache调优- 多并发用户分析,确定并发用户的高峰值 注意sp_iqsysmon输出中“Buffer Allocator节”的NActiveCommands 统计值 ，这个值可以反映出当前并发活动用户的数量 使用sp_iqcontext可以看到当前活动的SQL语句 并发用户多可能会导致资源峰值 GrabbedDirty 统计值增加意味着cache中充满了“脏”buffers 比较高的 NActiveCommands 值，可能会伴随cache有比较大的使用 记住：buffer cache是非常宝贵的资源,线程调优 CPU Usa

33、ge,标识CPU密集的操作 有很多排序或hash操作的查询 Bitmapped Indexes Corrective Actions Use indexes to reduce computation cost Rewrite query to gain parallelism,线程调优 降低Cache的使用,增加索引 Indexes are pre-computed results Less data will be processed horizontally in temp Better metadata will reduce sort usage 使用“谓词” hints 能够用于指

34、定谓词和join算法 Try for push-down joins when possible, especially for sort-merge,线程调优 限制命令的并行,Load Set option Max_IQ_Threads_Per_Team Delete Set option Max_IQ_Threads_Per_Team to limit delete threads Queries Predicates/Parallel Prepare set option Max_IQ_Threads_Per_Team Parallel Hash Group-by set option

35、Parallel_GBH_Units DBCC Specify “resources” percent to limit percent of CPUs used,线程调优 数据库级线程设置,iqnumbercpus If the operating system reports those hyper-threads as CPUs (reported in the root node in a query plan as “Number of CPUs”), then use the server startup option to tell the server the real num

36、ber of CPUs in the system. -iqmt Limits total number of threads -iqtss Limits stack size for IQ threads Increase only to support queries with deeply nested expressions. Multiply by #threads to determine memory usage,线程调优 连接级线程设置,Set option Max_IQ_Threads_Per_Connection Maximum cumulative number of t

37、hreads that will be allocated to a single connection. Not strictly enforced for all commands Set option Max_Threads_Per_Team 制定了分配给一个并行操作的最大线程数 如果 Max_IQ_Threads_Per_Connection选项修改了，那么这个选项也应该进行调整,影响查询的数据库选项,Database Options能够影响查询计划 Database Options有不同层次的影响 entire query session all users on the serve

38、r,影响查询的数据库选项,下面是一些能够影响用户查询的普通数据库选项 Query_Temp_Space_Limit default 2000 (MB) Limits use of any Temp resources (cache/disk) to users Suggest setting to zero (0) to allow unlimited use Minimize_Storage default Off Affects FP indexes created for new tables With few exceptions, this option should always

39、be set ON Force_No_Scroll_Cursors default Off Causes query results to be buffered to Temp cache At most sites option should be changed to permanently ON,影响查询的数据库选项,影响join操作的数据库选项 有两个选项可以影响查询优化器 分别影响join ordr和join算法 缺省情况下，优化器可以做出正确的选择，不需要设置这两个选项 You can try to change the behavior using these options,

40、 but there is no guarantee the optimizer will comply 影响join算法 数据库选项 - Join_Preference 对于多表关联查询，这个选项影响所有joins 影响join order Database Option - Join_Optimization,影响查询的数据库选项- Join_Preference Option,1 Prefer Sort/Merge 2 Prefer Nested Loop 3 Prefer Nested Loop PD 4 Prefer Hash 5 Prefer Hash PD 6 Prefer Pr

41、eJoin 7 Prefer Sort Merge PD,-1 Avoid Sort-Merge -2 Avoid Nested Loop -3 Avoid Nested Loop PD -4 Avoid Hash -5 Avoid Hash PD -6 Avoid PreJoin -7 Avoid Sort-Merge PD,Join_Preference Default = 0 (Optimizer Decides),You can only influence join type from the list of valid join algorithms in the Query Pl

42、an, otherwise it will ignore the option,影响查询的数据库选项- Join_Preference,下图中的join节点有6种有效的join算法 为了避免Sort-Merge joins，使用下面的方法 Set temporary option Join_Preference = -1; ,影响查询的数据库选项- Join_Optimization,选项设为OFF，将按照FROM子句中从左向右的顺序决定表关联的顺序 设置这个选项对特定查询有可能起作用 但是只有查询优化器判断错误时才设置 这个选项不会影响join算法的选择,Join_Optimization

43、Default :ON (Optimizer Decides),影响Hash算法(Join or Group)的数据库选项,一般来说Hash算法要比其他算法快 通过改变一些数据库选项，可以增加优化器选择Hash算法的可能性 Hash操作总是在Temp Cache中进行 One reason we recommend allocating caches to allow more Temp Cache memory The options discussed affect memory in the Temp Cache 注意: The defaults for these options we

44、re established for the average system Systems with large memory may be to able to benefit,分析Hash-Based 操作,All temp cache pages in a hash must be kept pinned in the cache to achieve optimal performance In a query plan look at the lines like: Maximum hash pin quote: 1712 Final hash buffer count: 1711 如果“Final hash buffer count” 的值超过了“Maximum hash pin quota”的值，那么说明hash table需要的buffers数量已经超过了IQ Server允许该hash对象所能使用的pinned buffers最大数量,Max_Hash_Rows 数据库选项,这个选项设置优化器考虑使用hash操作的最大记录行数