rapid response: debugging and profiling to the rescue
TRANSCRIPT
![Page 1: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/1.jpg)
Grab some coffee and
enjoy the pre-
show banter before the top
![Page 2: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/2.jpg)
H TTechnologies of 2017
![Page 3: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/3.jpg)
HOST:Eric Kavanagh
@eric_kavanagh
![Page 4: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/4.jpg)
THIS YEAR is…
![Page 5: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/5.jpg)
THE
LIN
E U
P
ANALYST:
Dez BlanchfieldData Scientist, The Bloor Group
ANALYST:
Robin BloorChief Analyst, The Bloor Group
GUEST:
Bert ScalzoSenior Product Manager,IDERA
![Page 6: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/6.jpg)
INTRODUCING
Robin Bloor
![Page 7: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/7.jpg)
About Bug Hunting
Robin Bloor Ph.D.
![Page 8: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/8.jpg)
Famous Bugs
u 1998: NASA’s Mars Climate Orbiter lost in space: (units problem)
u Ariane 5 Flight 501 self-destructs after multiple computer failures (1996)
u Soviet Gas Pipeline in 1982 – stolen automated control software (CIA hacked)
u Morris Worm – a harmless coding experiment became a worm (Cornell Univ- 1988)
u Pentium chip math error (1993)
u Apple Maps (2012) – disastrous launch
u AT&T long distance outage (1990) costs $60m
u UK insurance company (1993) database wipes data
![Page 9: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/9.jpg)
file://localhost/Users/robinbloor2/Dropbox/~Desktop/bottlenecks.jpg
The Impedance Mismatch
The RDBMS stores data organized in tables
The OO coder manipulates data organized in object structures, with associated methods
The data does not simply map to the database table structures so a mapping is necessary
Basically: hierarchies, types, result sets, poor APIs, language bindings, tools
![Page 10: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/10.jpg)
file://localhost/Users/robinbloor2/Dropbox/~Desktop/bottlenecks.jpg
The Impedance Mismatch
SQL has:
DDL (for data definition)
DML (for Select, Project and Join)
But it has little MML (Math) or TML (Time)
Usually result sets are brought to the client for further analytical manipulation, but this creates problems
Alternatively doing all analytical manipulation in the database creates problems
![Page 11: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/11.jpg)
file://localhost/Users/robinbloor2/Dropbox/~Desktop/bottlenecks.jpg
Database Performance Bottlenecks
CPU: CPU overload, context switches, I/O waits, CPU cache, Backplane throughput
Memory: Thrashing, fragmentation
Disk: Caching, I/O saturation, fragmentation, SSD performance
Network Overhead (SANs, NAS, local disk)
Locking
![Page 12: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/12.jpg)
Database Performance Factors
Bad database design
Bad program design
Concurrency, workload mixing
Load balancing
Capacity planning – data growth
Data layer issues
![Page 13: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/13.jpg)
In A Few Words
Database debugging can be onerous and non-trivial
![Page 14: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/14.jpg)
INTRODUCING
Dez Blanchfield
![Page 15: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/15.jpg)
![Page 16: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/16.jpg)
There are many stories about how the terms “bug” and
“debugging” came about – one of them claims that “On
September 9, 1945, in the USA, a Harvard technical
team looked at Panel F of the Harvard's Mark II
computer, and found something unusual between points
in Relay 70”.
![Page 17: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/17.jpg)
Apparently it was a moth, which they promptly
removed and taped in the log book. Supposedly the
legendary Grace Hopper added a caption which read
“First actual case of bug being found," and it’s long
been claimed that this was the first time anyone used
the word bug to describe a computer glitch. Naturally,
the term debugging followed.
![Page 18: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/18.jpg)
ABoynamedRobert');DROPTABLEstudents;--
LittleBobbleTables..
![Page 19: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/19.jpg)
Idoubtanyprogrammeranticipateda“hit&run”oftheircodebyamotorvehicle,butneverunderestimateangrygeeks..
Notabugbugfunnyallthesame
![Page 20: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/20.jpg)
therewasatimewhen
debuggingandprofilingcode
couldbedonebymeremortals..
thattimehaslongpassed..
![Page 21: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/21.jpg)
Some Code Reviews Require Rocket Science
![Page 22: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/22.jpg)
x$objQuery = $this->db->query( "SELECT rd.*, ((rd.rd_numberofrooms) - (select sum(rn.reservation_numberofrooms) as count_reserve_room from reservation as rn WHERE rn.reservation_rd_id = rd.rd_id AND (str_to_date('$data_Check_in_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_out_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_in_date','%d-%m-%y') <= str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date('$data_Check_out_date','%d-%m-%y') ) )) as reserveFROM room_details rd LEFT JOIN reservation rn ON rd.rd_id = rn.reservation_rd_id WHERE NOT EXISTS(SELECT rn.* FROM reservation rn WHERE rn.reservation_rd_id = rd.rd_idAND (str_to_date('$data_Check_in_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_out_date','%d-%m-%y') BETWEEN
str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_in_date','%d-%m-%y') <= str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date('$data_Check_out_date','%d-%m-%y') >= str_to_date(rn.reservation_check_out_date,'%d-%m-%y'))AND (rd.rd_numberofrooms <= (select sum(rn.reservation_numberofrooms) as count_reserve_room from reservation as rn WHERE rn.reservation_rd_id = rd.rd_id AND (str_to_date('$data_Check_in_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_out_date','%d-%m-%y') BETWEEN str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date(rn.reservation_check_out_date,'%d-%m-%y') OR str_to_date('$data_Check_in_date','%d-%m-%y') <= str_to_date(rn.reservation_check_in_date,'%d-%m-%y') AND str_to_date('$data_Check_out_date','%d-%m-%y') ) ) ))");
www.quora.com/Whats-the-most-complex-SQL-query-you-ever-wrote
![Page 23: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/23.jpg)
INTRODUCING
Bert Scalzo
![Page 24: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/24.jpg)
© 2017 IDERA, Inc. All rights reserved.
RAPID RESPONSE: DATABASE DEBUGGING AND PROFILING TO THE RESCUE
Bert Scalzo, Senior Product Manager
![Page 25: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/25.jpg)
25© 2017 IDERA, Inc. All rights reserved.
PROGRAMMING HISTORY
•1951 – Assembly•1952 – Autocode•1954 – IPL•1955 – FLOW-MATIC•1957 – FORTRAN•1957 – COMTRAN•1958 – LISP•1958 – ALGOL 58•1959 – FACT•1959 – COBOL
•1959 – RPG•1962 – APL•1962 – Simula•1962 – SNOBOL•1963 – CPL•1964 – Speakeasy•1964 – BASIC•1964 – PL/I•1966 – JOSS•1967 – BCPL
•1968 – Logo•1969 – B•1970 – Pascal•1970 – Forth•1972 – C
•1972 – Smalltalk•1972 – Prolog•1973 – ML•1975 – Scheme•1978 – SQL
•1980 – C++•1983 – Ada•1984 – Common Lisp•1984 – MATLAB•1985 – Eiffel•1986 – Objective-C
•1986 – Erlang•1987 – Perl•1988 – Tcl•1988 – Wolfram Language•1989 – FL
•2000 – ActionScript•2001 – C#•2003 – Groovy•2003 – Scala•2005 – F#•2006 – PowerShell
•2007 – Clojure•2009 – Go•2010 – Rust•2011 – Dart•2012 – Julia•2014 – Swift
Note this point in history. Can you guess why it’s marked? (session title)
![Page 26: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/26.jpg)
26© 2017 IDERA, Inc. All rights reserved.
§ Instrumentation / Print Statements / Trace File
§ Interactive Debugger
• First appeared in 1977
• Good for finding logic mistakes (i.e. effectiveness)
• Remote Debugging (special case – e.g. database)
• Common Terminology• Breakpoints
• Watch values
• Step into
• Step over
DEBUGGING
![Page 27: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/27.jpg)
27© 2017 IDERA, Inc. All rights reserved.
PROFILING
§ Sampling / Instruction Set Simulators
§ Instrumentation / Print Statements / Trace File
§ Profiler (Dynamic Program Analysis)
• First appeared in 1979
• Good for finding resource consumption and performance
bottlenecks (i.e. efficiency)
• Generally separate & distinct from the debugger
• Not generally utilized as often as debuggers
![Page 28: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/28.jpg)
28© 2017 IDERA, Inc. All rights reserved.
DB LANGUAGES
§ Some database vendors offer 3GL languages that “compile code” within the database
• Oracle: PL/SQL
• SQL Server: Transact-SQL (T-SQL)
• SQL-1999: SQL/PSM
• PostgreSQL: PL/pgSQL
• DB2: SQL PL
• Informix: SPL
![Page 29: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/29.jpg)
29© 2017 IDERA, Inc. All rights reserved.
DB OBJECTS
§ What database objects are built using procedural languages?
• Triggers
• (Stored) Procedures
• (Stored) Functions
• Packages (Oracle only)
![Page 30: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/30.jpg)
30© 2017 IDERA, Inc. All rights reserved.
DEBUGGER EXAMPLE (OLD WAY)
![Page 31: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/31.jpg)
31© 2017 IDERA, Inc. All rights reserved.
DEBUGGER EXAMPLE (OLD WAY)
![Page 32: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/32.jpg)
32© 2017 IDERA, Inc. All rights reserved.
DEBUGGER EXAMPLE (INTERACTIVE)
Hover over variable to see valueStepping cursor a line at a time
Setting a breakpoint to run to
Local variables auto watched
Call stack for all procedural code
![Page 33: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/33.jpg)
33© 2017 IDERA, Inc. All rights reserved.
PROFILER EXAMPLE #1 (DEBUGGER)
This loop consumed most time, so anything I can do inside it to improve performance should pay dividends
![Page 34: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/34.jpg)
34© 2017 IDERA, Inc. All rights reserved.
PROFILER EXAMPLE #2 (CODE ANALYST)
This loop consumed most time, so anything I can do inside it to improve performance should pay dividends
![Page 35: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/35.jpg)
35© 2017 IDERA, Inc. All rights reserved.
BUSINESS REQUIREMENTS
§ Since we write code to perform some task, we must mandatorily perform the task correctly (i.e. effective)• DEBUGGER
§ Since users are impatient and want the results even before they press the key, or preferably yesterday (i.e. efficient)• PROFILER
§ Without these tools, we’re shooting blindfolded!
![Page 36: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/36.jpg)
36© 2017 IDERA, Inc. All rights reserved.
DEMO
Will demonstrate typical database debugger and profiler in a typical database administration and/or development tool like IDERA’s DBArtisan® and Rapid SQL®
![Page 37: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/37.jpg)
37© 2017 IDERA, Inc. All rights reserved.
Q&A
![Page 38: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/38.jpg)
![Page 39: Rapid Response: Debugging and Profiling to the Rescue](https://reader031.vdocuments.us/reader031/viewer/2022022411/58ec95b41a28ab9c638b45a3/html5/thumbnails/39.jpg)
The Archive Trifecta:• Inside Analysis www.insideanalysis.com• SlideShare www.slideshare.net/InsideAnalysis• YouTube www.youtube.com/user/BloorGroup
THANK YOU!