Troubleshooting Techniques(*)

1
Troubleshooting Techniques()

• Extraction and Analysis of Performance Indicators
• Consumer-producer chain framework
• Tools
• Query plan monitors
• Performance monitors
• Event monitors
• () From Alberto Lerners chapter

2
Event Monitors to Identify Critical Queries

• If no user complains...
• Capture usage measurements at specific events
  (eg, end of each query) and then sort by usage
• Less overhead than other type of tools because
  indicators are usually by-product of events
  monitored
• Typical measures include CPU used, IO used, locks
  obtained etc.

3
Non-clustering indexes can be trouble
For a low selectivity predicate, each access to
the index generates a random access to the table
possibly duplicate! It ends up that the number
of pages read from the table is greater than its
size, i.e., a table scan is way better
Table Scan
Index Scan
76 sec272,618 pages131,425 pages273,173
pages552 pages
5 sec143,075 pages6,777 pages136,319 pages7
pages
CPU timedata logical readsdata physical
readsindex logical readsindex physical reads
4
An example Performance Monitor (query level)
Statement number 1 select C_NAME, N_NAME from
DBA.CUSTOMER join DBA.NATION on C_NATIONKEY
N_NATIONKEY where C_ACCTBAL gt 0 Number of rows
retrieved is 136308 Number of rows sent to
output is 0 Elapsed Time is 76.349
seconds Buffer pool data logical reads
272618 Buffer pool data physical
reads 131425 Buffer pool data writes
0 Buffer pool index
logical reads 273173 Buffer pool
index physical reads 552 Buffer pool
index writes 0 Total
buffer pool read time (ms)
71352 Total buffer pool write time (ms)
0 Summary of Results
Elapsed Agent CPU
Rows Rows Statement Time (s)
Time (s) Fetched Printed 1
76.349 6.670
136308 0

• Buffer and CPU consumption for a query according
  to DB2s Benchmark tool
• Similar tools MSSQLs SET STATISTICS switch and
  Oracles SQL Analyze Tool

5
Investigating Primary Resources

• Answer question 3Are there enough primary
  resources available for a DBMS to consume?
• Primary resources are CPU, disk controllers,
  memory, and network
• Analyze specific OS-level indicators to discover
  bottlenecks.
• A system-level Performance Monitor is the right
  tool here

6
Disk Performance Indicators at the OS Level
Should beclose to zero
Wait queue
New requests
Disk Transfers/second
Wait timesshould also be close to zero
Idle disk with pending requests?Check
controller contention.Also, transfersshould be
balanced amongdisks/controllers
7
Memory Consumption Indicators at the OS Level
Page faults/timeshould be closeto zero. If
paginghappens, at least not DB cache pages.
virtualmemory
real memory
of pagefile inuse (its used a
fixedfile/partition) will tellyou how much
memory is lacking.
pagefile
8
Disk Manager Performance Indicators
Row displacement should be kept under 5 of rows
rows
Free space fragmentation pages with few space
should not be in the free list
page
Storage Hierarchy (simplified)
extent
Data fragmentation ideally files that store DB
objects (table, index) should be in one or few
(lt5) contiguous extents
file
File position should balance workload evenly
among all disks
disk