9029如何有效利用数据库优化技术

1、1DM04如何有效利用数据库优化技术黄劲松高级工程师 sybase Shanghai 2议程在哪些方面花费时间？Server 专题Networking 专题Common SQL 专题Common Java 专题Stored Procedures（存储过程）Common Java 错误Common Database 专题3议程（ continued）优化的方法用于优化的产品的新特征问题4在那里使用你的时间从哪里入手解决问题? sp_sysmon 帮助定位问题找出当前的主要问题提供一系列系统可能出现的问题对应用进行定时在每个屏幕上提供详细信息 10个层面的讨论这用于确定哪个层面可能有问题5在哪里使

2、用你的时间sp_sysmon 帮助定位问题 Task Context Switches Due To: Voluntary Yields 133.3 1.2 15997 4.8 % Cache Search Misses 487.4 4.4 58487 17.5 % - System Disk Writes 6.0 0.1 717 0.2 % I/O Pacing 20.2 0.2 2422 0.7 % Logical Lock Contention 2.8 0.0 331 0.1 % Address Lock Contention 3.8 0.0 452 0.1 % Log Semaphor

3、e Contention 4.0 0.0 481 0.1 % Group Commit Sleeps 4.1 0.0 486 0.1 % Last Log Page Writes 103.4 0.9 12405 3.7 % Modify Conflicts 11.2 0.1 1341 0.4 % I/O Device Contention 11.3 0.1 1358 0.4 % Network Packet Received 290.1 2.6 34809 10.4 % - Network Packet Sent 651.2 5.9 78143 23.4 % - SYSINDEXES Look

4、up 0.0 0.0 0 0.0 % Other Causes 1055.9 9.6 126710 37.9 % -6在那里使用你的时间对应用进行定时- 首先 从整个应用或界面开始 然后 . 把它分成:SQL Statements (定时)ping 时间 + traceroute# SQL statements应用的定时确定使用的连接方式图表的速度7连接速度“C/CT-Library”ODBCJava/JDBC/ODBC bridgeJava/JDBC (type 3)Java/JDBC (type 4)CIS Cross DB/Server 连接8在那里使用你的时间10 层面的讨论应用的调优

5、网络调优server 端的应用调优/中间层 存储过程(stored procedures)/SQL/JSQL数据的逻辑放置logical placement 字段安排/逆规范化加锁/阻塞物理存放/索引管理内存调优Sybase 内核/配置调整系统调优 - O/S 层的调优9Server 讨论常连接和再连接CGI programs, Java code每秒连接数目 如何增加容量?缓存/命名缓存以前的/新的特征ASE 12.0以前版本/ASE 12.010Server 讨论常连接和再连接再连接的开销在0.05 - 2 秒任务传递到 “idle“(空闲) 引擎 当第一个 命令被发出 额外的时间被 “

6、无用的”工作开销距离越远，连接时间越长正文交换 “消耗” 在2个CPU 上的带宽11Server 讨论创建多个 “外部 server使用CIS 功能映射“proxy” 表到 “原始” 表移相关的存储过程到 “外部 server在ASE server 之间增加常连接这样提供给你每秒更多的用户连接Application Servers 使用常连接12Server 专题缓存/命名缓存Server IssuesCaching/Named Caches为tempdb建立单独的命名缓存用 sp_helpdb 来帮助确定cache的大小2/4/16K pools为所有的事务日志(transaction lo

7、g)建立单独的命名缓存它必须比较小 (通常小于 50MB)必须尽量如下值一样大小: biggest transaction size * number of active transactions + fudge factor13缓存12.0 Features使用 11.9.3/12.0 关于cache管理的新特征它必须至少作用于 default data cache使用举例:sp_configure global cache partition, sp_cacheconfig , cache_partition=这会减少资源的竞争根据一个因数 1/(N) N 是1, 2, 4, 8, 16.

8、6414缓存一个性能讨论的例子:Cache: Default data CacheSpinlock Contention n/a n/a n/a 62.0 %Utilization n/a n/a n/a 8.3 %This is a 62% drag on I/Os butCache Searches Cache Hits 8.2 1.4 2447 100.0 % Found in Wash 6.5 1.1 1943 79.4 % Cache Misses 0.0 0.0 0 0.0 % Total Cache Searches 8.2 1.4 244715缓存Cache: Default

9、data cacheSpinlock Contention n/a n/a n/a 34.0 %Utilization n/a n/a n/a 12.3 %显示了这些问题:争用不是线性下降争用可以被消除在别处可以继续移动“hash cache” 到一个较大的值 4 或816缓存以前我习惯于首先推荐使用 named cache 现在我首先使用 hash cache sp_configure global cache partition, 2 or 42: 如果存在 named caches4: 如果没有 named caches然后一个特定的pool/Cache 通常问题是 default da

10、ta cache”有的时候问题出在 “tempdb cache”17网络专题ping 时间冲突arp cache和DNS cachingBandwidth(带宽) 讨论tracert10Mbit/100Mbit/1GBitpacket size(包大小)18网络专题ping 时间 Reply from 10.10.10.120: bytes=32 time=51ms TTL=253Excellent under: 5 msGood between:5-20msAverage times:20-75msSub Average: 75-200msPoor times: 200ms+19网络专题ar

11、p (地址解析) cacheperiodically the IP/DNS 的主机名(hostname) 会周期性的 丢失 观察 netstat -a 或 arp -a 的输出server 会周期性的 “消失client/server 会是在不同的机器上DNS 问题确保 DNS 名字被传播确保 DNS server 没有被淹没20网络专题Bandwidth(带宽) 问题察看开销带宽的查询大量的通信量来回于计算机已找到问题的查询的结果许多独立的select 在一个问题下工作许多 “内部有效” 查询images/text 数据类型密切注意这些数据类型Java 可以发 “大数组类型” 结果级12.5

12、 能支持 Larger varchar(XX )21网络专题tracert (NT)/traceroute (AIX.)这显示“通道”或网络路径 允许你找到 “不牢固”的连接寻找: 大量的 “通道”( 5)在特别的区域重要的速度降低22网络专题traceroute 输出.10ms10.10.10.2 71ms: slow281ms 390ms 7 links:why?.120msbr1. 10150ms 23网络专题10Mbit/100Mbit/1GBit越宽越好尽可能在server上能有最快的速度 尽量匹配:clienthub/switchserver24网络专题packet size(网络

13、包大小):看 sp_sysmon 获取更多的信息做 网络跟踪.11.1.1+ CT-Library 使用 streaming reads多包/行有1个结果集输送Collisions(冲突):25常见的SQL 讨论许多单个SQL statements不重要的查询结果select distinct columnselect count(*)在Java中使用“大” 结果集 26常见的SQL 讨论这说明SQL statements 会被用于 joins.许多 Java 开发者使用很好的OO 技术但是. 忘记了about stored procedures/Java procedures不使用时间调试S

14、QL statements不坚持一些数据一个应用会有操作1000行 Java code 但只有大概 50 行的SQL code(stored procedure)27常见的SQL 讨论非重要的结果Non-pivoted results什么是最重要的? 如何使用它们?旧的特性函数 functions新的功能好处减少网络流量较少的网络跳跃较少的fetch 操作28常见的SQL 讨论重点示例:select day, value from table_valuesvs.select sum(case when day = 1 then valueelse 0end) as day_1 from tab

15、le_values使用这种方法在 12.0/12.5中更加高效!29常见的SQL 讨论Select distinct column from table新的程序员会有这种习惯这种使用会带来额外的工作检索所有的结果对结果排序排除重复值将最终结果传给client端确信这种操作是根据应用不得不做的，否则尽量少用distinct30常见的SQL 讨论select count(*)在每个client端都可以看到这种用法绝大多数的使用通常是错误的这条语句实际上计算满足条件的行数大部分人只需要“if exists” 语句既可满足应用再确定数组大小时是必须使用的.相类似: upper(lastname)另一个

16、常见的错误执行比 count(*)更多的工作引起索引不被使用另外, 数学函数频繁的作用在列上是很常见的问题31通用JAVA讨论使用“char”区域作为程序的输入 数据类型不匹配 system.out.println写过多的信息到控制台日志debug 和生产代码在 client 或 server端进行计算?特别重要的clients端32通用JAVA讨论使用“char”区域作为程序的输入 例如:.and soc_sec_number = convert(numeric(9,0), var)这强迫server 做更多的额外工作优化器必须使用 “未知” 的优化策略 潜在的数据类型不匹配潜在的使用低效执

17、行方案33通用JAVA讨论数据类型不匹配这些字符类型作为存储过程的输入或是直接在SQL 中使用在使用字符类型时它会增加可能性的转换工作象一个 char(xx) 数据类型 但实际上是char(yy) 甚至与是 Numeric另外，优化器被“愚弄”为使用较差的执行方案34通用JAVA讨论system.out.println看来是 没有危急的通常这用于 “debugging” 目的并被 遗忘用于显示 SQL statements 或状态信息 用于 “监控” 应用这会降低应用的性能达50%35通用JAVA讨论写过多的信息到主控台日志通常这用于对应用的debugging 目的有意的用它来做 “troub

18、leshooting” 另外这会导致性能下降50% debug 和 生产代码PowerJ 有一个特征是用 “builds” 来消除生产代码创建时的Debug.log() . 这对于开发十分有用.36通用JAVA讨论在 client 或 server端进行计算?这是个普通的问题答案不是绝对的较好的方法是将计算放在较空闲的一方最好使用应用服务器 (i.e. EAS)大部分时间ASE通常有额外的开销37通用JAVA讨论特别重要的clients端和 client/server 的讨论相同内存被应用消耗 现在的应用额外的 沉重 - JVM (1MB)最小的Windows 95 配置要 64MB 内存 Windows (95,98,NT) 要最少 64MB 通常70+MB38常见的数据库讨论不匹配的连接字段索引错失(missing)Clustered 索引错失(missing)39常见的数据库讨论不匹配的连接字段不好的数据类型convert functions(explicit)通常导致索引被错失隐含的转换char(5)到 char