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