Informix Performance Tuning with DBSonar Aug 2006 Young

Informix Performance Tuning with DBSonar Aug. 2006 Young Tan Director, Product Marketing Platform: Cobrasonic Software Inc.

Company Background Cobrasonic Software Inc. is: • Independent Software Vendor for IBM/Informix • IBM Taiwan Software 1 Tier Reseller • IBM Taiwan Informix Service & Support Partner • International Informix User Group Golden Sponsor • 2006 Q 2 - awarded a contract worth US$5. 5 million by Chunghwa Telecom Co. Ltd. , for providing an IBM Informix Enterprise License and consulting services.

Cobrasonic Partners International U. S. A. Southeast Asia China

IT Executive Dilemma Ø Reality with RDBMS and Database Black Box - Informix or any RDBMS Ø Tens of thousands – SQL Executions daily Ø Few SQL bottlenecks affect the overall performance Ø Ø Suggestions from Vendors Buy larger server with more CPUs? Ø Upgrade to faster hard disk? Ø Upgrade to newer OS version? Ø On-Site consultant? Ø

What deserves my attentions ? For resource intensive performance tuning, What deserves my attentions in performance tuning? SQL statements with High Frequency SQL statements with High Cost SQL statements with Sequential Scan The most influential SQL statements !!!

DBSonar: #1 Informix Application Tuning Tool DBSonar: ‘The ultimate diagnostic and performance tuning tool for IBM Informix databases and applications’ 1. Apply the SQL Skeleton and Convergence Tech. 2. Capture the most influential SQL statements

DBSonar: #1 Informix Application Tuning Tool An expert system Configurable Real time Monitoring Repository DB to contain historical data Online Performance Analysis Periodic Performance Analysis Report Features Light cost by watching shared memory Patent-Pending SQL Convergence Tech Java-based GUI client

DBSonar Shared Memory Informix VP Informix VP Informix Shared Memory DBSonar Process Attached by Informix VP Attach to Shared Memory and Take a snap shot every 60 sec

DBSonar Architecture Informix VP Shared Memory Informix VP Attached by Informix VP DBSonar Virtual Process Informix VP DBSonar Repository Database DBSonar VP passes SM data in separate Repository Database

DBSonar — GUI Presentation Server Switch Panel DB Profile Fatal Error Monitoring and Alert OS Performance Indicator Database Server Performance Trend SQL Network

DBSonar - Proactive Resource Monitoring

DBSonar Online - Drill Down to DB Connections Discovery by drill down

DBSonar Online - Drill Down by Session ID Discovery by ID Association by Session ID 1239501

Trend – Get back to last bottleneck 8: 00 AM this morning?

Trend – SQL at Point in Time 8: 00 AM this morning? The exact SQL statement running at 8: 00 am

Trend – Historical data 24 hours to 30 days

DBSonar — Performance Analysis Report

SQL: Individual Query Freq and Cost SQL Freq Cost UPDATE customer SET (name, age) =(‘Eric’, 36); 1 1000 UPDATE customer SET (name, age) =(‘Brian’, 32); 1 1000 update customer set (name, age) =(‘Spark’, 35); 1 1000 update customer set (name, age) =(‘jack’, 33); 1 1000 select a. product_idrom pd_pdinfo a, proddb: product_offer … 1 1000 select count(*) from u_product_offer_instance … 1 45 Meaningless data for performance when Frequency == 1

SQL – Typical Informix 4 GL with variable DECLARE c_select CURSOR FOR SELECT name, age FROM customer FOR UPDATE LET upd_datate = “UPDATE customer SET (name, age) =(? , ? ” “WHERE CIRRENT OF c_select” PREPARE p_update FROM upd_stmt FOREACH c_select INTO name_variable, age_variable --- modify the variable EXECUTE p_update USING name_variable, age_variable END FOREACH One SELECT statement by 4 GL = 100 UPDATEs for DBA

SQL: Normalization SQL Freq Cost UPDATE customer SET (name, age) =("", ) 1 1000 select a. product_idrom pd_pdinfo a, proddb: product_offer … 1 1000 select count(*) from u_product_offer_instance … 1 45 Normalize the spelling and remove strings and constants

SQL: Convergence and Skeleton SQL Freq Cost UPDATE customer SET (name, age) =("", ) 4 1000 select a. product_idrom pd_pdinfo a, proddb: product_offer … 1 222 select count(*) from u_product_offer_instance … 1 45 SQL Skeleton Converge normalized SQL into SQL Skeleton Frequency by SQL Skeleton is the soul of DBSonar Performance Tuning

The solid evidence between DEV & DBA DB Estimate Cost Rows Seq scan Frequency SQL Statement crmdb 152 246 Yes 851 select * from as_serv_control where offer_comp_instance_id = ? order by produce_no asc 2 crmdb 599 354 Yes 464 select first 1 product_id from pdl_pdinfo where product_no = ? order by created_date desc crmdb 250 246 Yes The number of times that is caught by snap shot 253 select produce_id, produce_no, product_offer_instance_id, offer_kind from as_serv_control where sub_order_id = ? 7

DBSonar SQL Tool Select. . from Select. . from Sorted by skeleton Sorted into groups Sampling Filtering Select. . from Converging Select. . from Categorizing Select. . from Select. . from 100, 000 SQLs 100 SQL Skeletons

SQL 80/20 Rule Select. . from Catch the Fewer SQL (20%-) stmts with the most Influence (80%+) Select. . from Select. . from 80% weight 20%

Summary Performance Tuning Methodology SQL Skeleton & Convergence Technology Identify – The most influential SQL Resolve – SQL Bottlenecks Periodic Performance Analysis Report Guidance – 80/20 Rule Critical stuff first