how to troubleshoot & optimize database query performance for your application
TRANSCRIPT
![Page 1: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/1.jpg)
Confidential, Dynatrace, LLC
Asad AliDirector, Product Specialist@AsadThoughtsDynatrace
How to Troubleshoot & Optimize Database Query Performance for Your Application
![Page 2: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/2.jpg)
![Page 3: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/3.jpg)
Let the blame game start!
Web Server Application Server Database
DEV Team DBA Team
Blame the database for all performance issues
Blame the SW/HW or system administrators
Network?
![Page 4: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/4.jpg)
Database Heavy: 66.51% (40.27s)
Time Spent in SQL Execs
![Page 5: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/5.jpg)
![Page 6: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/6.jpg)
Excessive SQL: 24889! Calls to Database
Database Heavy: 66.51% (40.27s)
Time Spent in SQL Execs
![Page 7: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/7.jpg)
DatabasePerformance
hotspots
Application Design
DB Design DB Queries
Server/Infrastructure
![Page 8: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/8.jpg)
Database Performance Hotspots
Application Design Database Infrastructure
Application
![Page 9: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/9.jpg)
of databaseperformance issuescan be solved by developers70
%
![Page 10: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/10.jpg)
Database Performance Hotspots
• Lack of use of bind values• Too many SQL statements per user action• Database server overloaded• DB connection pool monitoring
![Page 11: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/11.jpg)
Example 1:performance tuning by using bind variables
![Page 12: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/12.jpg)
Oracle SQL Cache<?php
$db = new PDO('oci:dbname=sid', 'username', 'password');
$data = Array();$data[] = $db->query("select * from country where code = 'AT'");$data[] = $db->query("select * from country where code = 'AU'");$data[] = $db->query("select * from country where code = 'NZ'");$data[] = $db->query("select * from country where code = 'ES'");
?>
?>
![Page 13: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/13.jpg)
Query Plan Evaluation
![Page 14: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/14.jpg)
![Page 15: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/15.jpg)
MSSQL Cache
![Page 16: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/16.jpg)
MSSQL Cache
![Page 17: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/17.jpg)
Little Bobby TablesaddStudentInfo(String lname, String fname) {
String query = “insert into Student(last_name, first_name)values (‘”+lname + ”’, ‘” + fname + “’)”;
}Normal Operation:
First Name: AsadLast Name : Aliinsert into Student (‘Ali’, ‘Asad’)
Malicious Operation:First Name: Asad’); Drop table Student;Last Name : Aliinsert into Student (‘Ali’, ‘Asad’); Drop table
Student
![Page 18: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/18.jpg)
Little Bobby Tables
![Page 19: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/19.jpg)
Oracle SQL Cache<?php
$db = new PDO('oci:dbname=sid', 'username', 'password');
$data = Array();$ps = $db->prepare("select * from country where code = :code");$data[] = $ps->execute(array("code" => "AT"));$data[] = $ps->execute(array("code" => "AU"));$data[] = $ps->execute(array("code" => "NZ"));$data[] = $ps->execute(array("code" => "ES"));
?>
?>
![Page 20: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/20.jpg)
![Page 21: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/21.jpg)
Example 2:application performing
too many SQL statements
![Page 22: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/22.jpg)
Excessive SQL: 24889! Calls to Database
Database Heavy: 66.51% (40.27s)
Time Spent in SQL Execs
![Page 23: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/23.jpg)
Why are there so many SQL statements?$schools = new SchoolEntities();
foreach ($schools as $school) { foreach ($school->departments as $department) { foreach ($department->courses as $course) { echo $department->name . ": " . $course->title); } }}
![Page 24: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/24.jpg)
N+1 problem• 10 schools• 20 departments per school• 50 courses per department
=> 10 x 20 x 50 = 10001 single database statements !!!
![Page 25: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/25.jpg)
Retrieving too many records$schools = new SchoolEntities();
foreach ($schools->departments as $department) { foreach ($department->courses as $course) { if ($course->category == "SQL" && $course->level == "expert")) { echo $department->name . ": " . $course->title); } }}
=> 3 records, still 10001 queries
![Page 26: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/26.jpg)
Solution: use a JOIN query with a WHERE clause$rows = $db->query ("select department.name as department, course.title as course from school join department on school_id = school.id join course on department_id = department.id where course.category = 'SQL' and course.level = 'expert'");
foreach ($rows as $row) { echo $department . ": " . $course);}
=> ONE database statement !!!
![Page 27: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/27.jpg)
Example 3:overloaded database server
![Page 28: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/28.jpg)
Major database performance problem
![Page 29: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/29.jpg)
![Page 30: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/30.jpg)
![Page 31: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/31.jpg)
![Page 32: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/32.jpg)
Example 4: database connection pool settings
![Page 33: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/33.jpg)
Database Connection Pool
![Page 34: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/34.jpg)
Database Connection Pool
![Page 35: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/35.jpg)
Database Connection Pool
![Page 36: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/36.jpg)
Auto DB Pattern Detection
![Page 37: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/37.jpg)
Live Demo
![Page 38: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/38.jpg)
Q & A@AsadThoughts
![Page 39: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/39.jpg)
Performance Tuning – best practices• Use bind variables to leverage SQL caching• Use join queries to avoid N+1 problems• Avoid index killers in queries• Use stored procedures for complex functionality• Leverage connection pooling• Watch for other processes / application on your database
http://apmblog.dynatrace.com/2016/05/23/improve-oracle-database-performance-using-bind-variables/
https://www.infoq.com/articles/Diagnose-Microservice-Performance-Anti-Patterns
![Page 40: How to Troubleshoot & Optimize Database Query Performance for Your Application](https://reader033.vdocuments.us/reader033/viewer/2022052514/587dd4ce1a28ab5b5b8b60ad/html5/thumbnails/40.jpg)