上海交通大学高级数据库课件陆朝俊ed6ch24课案

24.1 第第2424章章 高级应用开发高级应用开发 n性能调优 n性能测试基准 n应用系统开发的其他问题 n标准化 24.2 性能调优性能调优 n系统性能调优: 调整各种参数和设计选项,以 改善针对特定应用的性能. l模式和事务设计 l数据库参数 l硬件 n执行性能调优的人 l应用开发者 lDBA 24.3 改善面向集合特性改善面向集合特性 n应用程序经常频繁执行一个查询Q(p),每次执行时参数p的 值不同. l每次调用的开销:服务器上的处理开销+与服务器的通信开销 l将多个查询合并为一个查询 n例如: 应用程序对各系循环, 每次循环执行如下SQL查询 select sum(salary) from instructor where dept_name = ? l如果表instructor在dept_name上没有聚簇索引,则每次执行都导致 表扫描 l即使有索引, 对每个系名都需要一次随机I/O操作 24.4 改善面向集合特性改善面向集合特性( (续续) ) n相反, 执行如下单一查询可获得各系薪酬总额: select dept_name, sum(salary) from instructor group by dept_name l一遍扫描instructor表, 避免为每个系查询时导致的随机I/O l利用一轮通信即可将结果取到客户端 l客户端程序即可遍查该结果中各系的薪酬汇总 n在许多情况下, 将多个SQL查询合并成单个查询可以大幅 降低执行代价 l例如: instructor表很大, 系很多 24.5 改善面向集合特性改善面向集合特性( (续续) ) nJDBC API提供称为批量更新批量更新功能,可以用一轮与 数据库的通信执行一批插入操作. l没有此功能时, 每插入一条记录都需要一轮通信 nC/S系统中常用的另一种技术:存储过程. l存储在DB中的查询,预编译 l客户端可直接调用,无需编译 l通信开销极小:只需传送调用命令而非整个查询 n改写包含嵌套子查询的复杂查询 l许多查询优化器尚不能很好地优化这种查询 l去相关性: 查询优化器或手动改写 24.6 批量加载批量加载 n批量加载: 将大量数据加载到DB中 n如果通过分开的insert语句来执行插入: 性能很差 l每次解析语句的开销 l更重要的是: 为每条插入元组进行完整性约束检查和更 新索引的代价 n如果批量插入: l完整性约束检查和索引更新即可以更加面向集合的方 式来执行 24.7 批量导入批量导入/ /导出导出 n多数数据库系统都有批量导入/导出实用程序 n批量导入 l从文件读数据, 并以高效方式执行完整性约束检查和索 引维护 l数据文件格式: CSV, XML, 或者依赖于数据库系统的二 进制格式等 n批量导出 24.8 批量更新批量更新 n例如 update department set budget = budget + ( select amount from funds_received where funds_received.dept_name = department.dept_name) where exists( select * from funds_received where funds_received.dept_name = department.dept_name); nSQL:2003提供merge完成这类任务. 24.9 瓶颈定位瓶颈定位 n多数系统的性能通常受制于一个或几个组件,称为 瓶颈. l例如一个程序花80% 时间在一个小循环上, 而其他代码 占用20% 时间 4值得对这占用80时间的代码进行改善 n系统调优就是发现瓶颈, 并改善导致瓶颈的系统 组件, 从而去除瓶颈 n消除一个瓶颈常常暴露出另一个 n解决瓶颈问题就是不断去除瓶颈直至平衡 n非瓶颈组件可用便宜的低性能部件替换 24.10 发现瓶颈发现瓶颈 n不同于简单程序, 数据库可建模为排队系统 l事务向数据库系统请求各种服务 4如 CPU, 磁盘I/O, 锁 l每个服务都有一个与之关联的队列 4小事务所花时间多数是在队列中等待服务而非执行代码 n数据库系统中的瓶颈典型地发生在当某个服务具有极高使用率时 l相应地有个很长的队列 4如果请求到达是一致随机分布的, 队列长度随使用率指数增长 l故资源使用率应保持足够低. 4经验规则: 70% 左右的利用率较合适, 避免超过90% 24.11 数据库系统中的队列数据库系统中的队列 24.12 可调参数可调参数 nDBA可在三个层次对数据库系统调优: l硬件:例如增加磁盘或使用RAID, 增加内存, 移 到更快的处理器上. l数据库系统参数:例如缓冲大小, 检查点间隔. 4系统应尽可能自动调优. l高层数据库设计:如模式, 索引与事务 n三个层次相互影响,必须整体考虑. 24.13 硬件调优硬件调优 n事务需要I/O l事务处理系统调优的一个重要因素是确保磁盘子系统 能处理I/O操作的频率 4当今典型的磁盘支持大约每秒100次随机I/O操作 4假设每个事务只需要 2 次随机I/O操作. 则每个磁盘每秒能支持 50个事务 4为了支持每秒n 个事务, 我们需要把数据分到 n/50 个磁盘上 n通过将更多数据保持在内存可减少每事务的I/O操作数 l如果所有数据都在内存中, 只需对写进行磁盘操作 l将频繁使用的数据保持在内存中减少了磁盘I/O, 减少 了所需磁盘数, 但花费了内存代价 24.14 硬件调优硬件调优: : 五分钟规则五分钟规则 n问题: 什么数据要保持在内存中? l若某页每秒被访问 n 次, 将它保持在内存中可节省: n × ( 每个磁盘驱动器的价格/ 每磁盘每秒存取数) l将该页保持在内存中的代价: 每兆内存的价格 / 每兆内存的页数 l损益两平点: 使得上面两个代价相等的n 的值 4如果存取数更多, 则节省大于花费 n经验规则-5分钟规则: 如果某页的使用频率超过每5分钟一次, 该页就 应该缓存在内存中. l值得购买足够的内存缓存所有这样的数据页! l对其他页, 购买足够磁盘以支持所需的I/O频率 l用当前的价格可得:2小时访问一次的页就值得放在内存. 24.15 硬件调优硬件调优: : 一分钟规则一分钟规则 n对于顺序存取的数据, 每秒可以读更多的页. n假设每次读取1MB 的数据,几年前有经验规则- 1分钟规则: 顺序存取的数据如果1分钟至少存取 一次, 就应该缓存在内存中. n用当前的磁盘和内存价格: 30秒左右. n这两条经验规则只考虑磁盘I/O,未考虑响应时间. 否则可能需要把非频繁访问的数据也缓存在内存 中. 24.16 硬件调优硬件调优: : 闪存闪存/ /固态硬盘固态硬盘 n经常存取的数据可存储在闪存上,而非磁盘上. n闪存作为缓冲区:持久缓存 l每一块在磁盘上有永久位置,但写入闪存而非磁盘 l闪存满时,回收不常用的块 l更新过的块要写回磁盘 24.17 模式调优模式调优 n垂直划分关系以分隔经常存取的数据 例如, 将course (course_id,title,dept_name,credits) 分割成两个表 (course_id, credits) 和(course_id,title,dept_name),前者更常访问. 列存储: 极端情形是每个属性存储在一个文件中.DW应用常用. n通过存储去规范化关系改善性能 例如, 存储instructor 和department 的连接; 则对系里的每个教师 都重复存储了系的信息, 但是不必重复计算连接 使用物化视图 代价: 更多空间,以及更新时保持一致性 n将在一个频繁请求的连接中匹配的元组聚集存储在同一磁盘页上, 24.18 索引调优索引调优 n索引调优 l创建合适的索引以加速较慢的查询 l删除过多的索引以加速较慢的更新(在查询和更新之间 权衡) l选择合适的索引类型 (B-树/散列) 4例如, 范围查询适合用B树索引而非散列索引 l选择聚簇索引 4能使大多数查询/更新受益的索引应当成为聚簇索引 n多数商用数据库系统提供索引调优向导检查查询和更新( 工作负载)的过去历史并推荐哪些索引会对工作负载最好 24.19 使用物化视图使用物化视图 n物化视图：可以帮助加速某些查询 l特别是聚合查询 n开销 l空间 l视图维护所花的时间 n视图维护 l立即视图维护: 作为更新事务的一部分进行 4更新事务要付出的时间开销 l延迟视图维护: 仅当需要访问时才进行或周期性维护 4更新事务不受影响, 但在更新前, 视图可能与数据库不一致 n比反规范化模式更可取, 因为视图维护是系统的职责, 而非程序员的 l避免由于更新程序中的错误导致的不一致性 24.20 使用物化视图使用物化视图( (续续) ) n如何选择物化视图集合 l引入一个物化视图以有利于一种事务类型, 但可能伤及其他事务 l物化视图选择依赖于代价 4用户通常对实际操作代价没有概念 l总之, 人工选择物化视图是枯燥冗长的, 且难以做出好的选择 n某些数据库系统提供工具以帮助DBA选择物化视图 l“物化视图选择向导” 24.21 物理设计的自动调优物理设计的自动调优 n多数商用DBS提供工具帮助DBA进行 l索引选择 l物化视图选择 n自动选择工具的实现 l生成工作负载 4记录一段时间内执行的查询和更新 l工作负载压缩 4用少量的查询/更新代表总的工作负载 l利用查询优化器选择有益于工作负载的索引和物化视图 4穷尽所有组合是不可能的 4贪婪法:评估代价,选最优;余下的重新评估,再选最优 24.22 并发事务调优并发事务调优: :读写竞争读写竞争 n不同类型的事务并发执行会因锁竞争导致性能低 下 n读写竞争:日常小事务+大查询(如数据分析) l为大查询使用快照隔离, 避免竞争.例如SQL Server: set transaction isolation level snapshot l或等到不繁忙时再统计查询 l或采用较弱一致性级别 24.23 并发事务调优并发事务调优: :写写竞争写写竞争 n写写竞争: 锁机制下, 频繁更新的数据项(更新热点)可导致 大量事务等待,因此性能差 n即使在快照隔离下,更新热点也有问题:写验证失败导致频 繁的事务中止 n更新热点例:序号计数器 l为待插入到DB中的数据项分配唯一标识:读取并递增序号计数器 l为提高并发度,事务读取并递增序号计数器后立即释放之. 4若事务回滚,是否回滚序号计数器? T1取号a;T2取号a+1; T1中止,回滚计数器(回到a或a+1);.Tx取号a+1! 不应回滚.但会导致序号空缺 lSQL标准:create sequence或为某个属性指定为IDENTITY 24.24 长事务调优长事务调优 n长更新事务 l带来日志性能问题 4在事务完成前日志满,导致事务回滚 4长事务阻塞日志删除旧记录, 同样导致日志满 l增加崩溃后的恢复时间 n解决方法: l对单个事务的更新数目进行限制 l将长更新事务分解成一组小更新事务,称为minibatch事务 4可能导致部分更新提交 n长事务还可导致锁表满 l自动锁升级:大量元组锁自动升级为页锁乃至表锁 24.25 性能模拟性能模拟 n使用排队模型进行性能模拟对预测瓶颈以及调优的效果是 很有用的, 即使没有存取真正的系统 n模拟模型通常只考虑每个服务的主要方面,而省略一些细 节 l对服务时间建模, 但忽略服务细节 4例如用平均磁盘读时间作为磁盘读时间的近似 n可以在模型上做实验, 以提供诸如平均吞吐量/响应时间这 些度量的估计值 n可以在模型中调整参数, 然后在真实系统中复制 l例如磁盘数, 内存, 算法等等 24.26 性能测试基准性能测试基准 n性能测试基准:用来定量测度软件系统性能的标准化任务 集 n对于比较数据库系统很重要, 尤其是当系统越来越遵循标 准时,性能成为区分因素. n常用的性能度量: l吞吐量 (每秒事务数, 或TPS) l响应时间 (从提出事务到返回结果的