Performance Monitoring & Tuning: Metrics, Tools & Optimization

Key Concepts and Tools for Monitoring

What is Performance Monitoring?

Performance monitoring is the process of collecting, analyzing, and interpreting metrics that reflect how a database system is functioning. The goal is to identify bottlenecks—such as slow queries, high CPU usage, or disk I/O delays—that degrade user experience or system efficiency.

Core Metrics to Monitor

1. CPU Utilization: Measures how much processing power the database is consuming. High CPU usage may indicate inefficient queries or insufficient hardware.
2. Memory Usage: Tracks how much RAM is allocated to the database buffer pool, shared pool, and other components. Poor memory management can lead to excessive disk I/O.
3. Disk I/O: Monitors read/write operations on storage devices. High I/O latency often signals disk bottlenecks or inefficient indexing.
4. Query Performance: Measures execution time of SQL queries. Slow queries are a common cause of performance degradation.
5. Lock Contention: Identifies situations where multiple transactions are waiting for the same resource (e.g., a table row), leading to deadlocks or delays.
6. Network Latency: Relevant for distributed databases, where delays in data transfer between nodes can impact performance.

Common Monitoring Tools

---

How Performance Monitoring Works: A Trace Example

Let’s trace how eSewa might monitor its database performance during a high-transaction period (e.g., Dashain festival).

1. Step 1: Identify the Bottleneck

   • Tool Used: AWR reports show that the transaction_processing table is experiencing high disk I/O during peak hours (6 PM–9 PM).
   • Observation: The query SELECT * FROM transaction_processing WHERE status = 'pending' is scanning the entire table (full table scan) instead of using an index.

2. Step 2: Drill Down with SQL Trace

   • Enable SQL Trace for this query:

     EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(
       waits => TRUE,
       binds => TRUE
     );
     

   • The trace log reveals that the query is missing an index on the status column, causing it to read every row.

3. Step 3: Analyze Resource Usage

   • CPU: 85% utilization during peak hours (normal threshold: 70%).
   • Memory: Buffer cache hit ratio is 60% (ideal: >90%), indicating inefficient memory usage.
   • Disk I/O: 120 MB/s read operations (high for a transaction system).

4. Step 4: Correlate with Business Impact

   • During Dashain, eSewa processes 50,000 transactions/hour. A 5-second delay per query translates to 250,000 seconds of wasted user time per hour—equivalent to 70 hours of downtime over the festival period.

---

Visual: Database Performance Metrics Dashboard

---

In the Real World

1. eSewa’s Transaction Processing

   • Idea Used: Query Optimization and Indexing
   • How: During festivals, eSewa’s database faces 10x the usual load. By analyzing AWR reports, they identified that queries filtering transaction_status lacked indexes. Adding a composite index on (status, user_id) reduced query time from 500ms to 5ms, handling 50,000 transactions/hour without timeouts.
   • Real Picture:

2. Ncell’s Customer Data Queries

   • Idea Used: Performance Monitoring with OS Statistics
   • How: Ncell’s CRM database was slow during nightly batch jobs (e.g., generating customer usage reports). OS statistics revealed that disk I/O was the bottleneck due to traditional HDDs. Migrating to SSDs reduced I/O latency from 20ms to 1ms, speeding up report generation from 2 hours to 10 minutes.
   • Visual:

3. Nepal Rastra Bank’s Core Banking System
   • Idea Used: Lock Contention and Deadlock Detection
   • How: During loan processing, the bank’s system frequently hit deadlocks when multiple tellers updated the same customer account simultaneously. By enabling lock contention monitoring, they identified that transactions on the account_balance table were not following a consistent locking order. Implementing row-level locking and transaction isolation levels reduced deadlocks by 90%.

---

Database Tuning Techniques

1. Query Optimization

Slow queries are often the root cause of performance issues. Techniques include:

• Avoiding SELECT *: Fetch only the columns you need.
• Using Indexes: Create indexes on columns frequently used in WHERE, JOIN, or ORDER BY clauses.

  CREATE INDEX idx_transaction_status ON transactions(status);
  

• Rewriting SQL: Replace inefficient constructs (e.g., correlated subqueries with joins).
• Analyzing Execution Plans: Use EXPLAIN PLAN to visualize how the optimizer processes a query.

  EXPLAIN PLAN FOR SELECT * FROM orders WHERE order_date > '2023-01-01';
  

Worked Example: Optimizing a Daraz Order Query

Problem: A Daraz warehouse management system uses this query to check stock levels:

SELECT product_id, quantity
FROM inventory
WHERE warehouse_id = 101 AND quantity < 10;

Issue: The query takes 2 seconds because it scans the entire inventory table (10 million rows).

Solution:

1. Add an Index:

   CREATE INDEX idx_inventory_warehouse_quantity ON inventory(warehouse_id, quantity);
   

2. Rewrite the Query:

   SELECT product_id, quantity
   FROM inventory
   WHERE warehouse_id = 101 AND quantity < 10;
   

   • Now uses the index for a range scan instead of a full table scan.
3. Result: Query time drops to 15ms.

---

2. Indexing Strategies

Indexes speed up data retrieval but add overhead to INSERT, UPDATE, and DELETE operations. Key strategies:

When to Avoid Indexes:

• Tables with high write volume (e.g., logging tables).
• Columns with low selectivity (e.g., status with only 2 values: "active" or "inactive").

---

3. Storage and Memory Tuning

Tablespaces and Datafiles

• Tablespaces: Logical storage units that group related database objects (e.g., USERS tablespace for user data).
• Datafiles: Physical files that store tablespace data. Tuning involves:
  • Sizing: Allocate enough space to avoid frequent extensions.
  • Placement: Store frequently accessed tables on faster disks (e.g., SSDs).
  • Segmentation: Use separate tablespaces for SYSAUX, UNDO, and user data.

Visual: Tablespace Structure

Memory Configuration

Critical parameters in Oracle’s init.ora or spfile:

Worked Example: Tuning NTC’s Billing System Problem: NTC’s billing database has high disk I/O during peak hours (7 PM–11 PM), causing delays in generating customer bills. Solution:

1. Increase db_block_size from 8KB to 16KB to reduce I/O operations.
2. Allocate more memory to buffer_cache_size (from 2GB to 8GB).
3. Move datafiles to SSDs. Result:

• Disk I/O latency: Reduced from 40ms to 5ms.
• Query response time: Dropped from 3 seconds to 100ms.

---

4. Hardware Considerations

Upgrading hardware can resolve bottlenecks when software tuning isn’t enough:

Real Picture:

---

5. Database Configuration Tuning

Oracle-Specific Parameters

MySQL/MariaDB Tuning

Key parameters in my.cnf:

[mysqld]
innodb_buffer_pool_size = 8G       # 70% of total RAM
innodb_log_file_size = 1G          # Controls redo log size
max_connections = 200              # Avoid connection overload
query_cache_size = 0               # Disable if using application caching

---

Advanced Tuning: Partitioning and Sharding

For very large databases (e.g., NEPSE’s stock trading data), consider:

1. Partitioning: Splitting tables by range, list, or hash (e.g., partition orders by order_date).

   CREATE TABLE orders (
     order_id NUMBER,
     order_date DATE
   ) PARTITION BY RANGE (order_date) (
     PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
     PARTITION p_future VALUES LESS THAN (MAXVALUE)
   );
   

2. Sharding: Horizontally splitting data across multiple databases (e.g., Ncell’s customer data by region).

Visual: Database Partitioning

---

Backup and Recovery Impact on Performance

Performance tuning must consider backup/recovery operations:

• Full Backups: Can cause high I/O during execution. Schedule during off-peak hours.
• RMAN (Oracle): Use incremental backups to reduce backup window.

  RUN {
    ALLOCATE CHANNEL c1 TYPE DISK;
    BACKUP INCREMENTAL LEVEL 1 DATABASE;
  }
  

• Archived Logs: Ensure log_archive_dest is configured to avoid filling up disk space.

---

Exam Tip

This unit is heavily tested in TU exams with a mix of:

1. Short Questions (2–5 marks):
   • Define buffer cache hit ratio and its ideal value.
   • List three tools for performance monitoring in Oracle.
   • Explain why a full table scan is inefficient for large tables.
2. Long Questions (10–15 marks):
   • Scenario-Based: Given a slow query (e.g., from NEPSE’s trading system), diagnose the issue and propose optimizations (indexes, query rewrite).
   • Comparison: Compare B-Tree vs. Bitmap indexes with examples.
   • Configuration: Write SQL to adjust shared_pool_size and explain its impact.
3. Practical (15–20 marks):
   • AWR Report Analysis: Interpret a sample AWR report to identify bottlenecks.
   • Execution Plan: Use EXPLAIN PLAN for a given query and suggest improvements.
   • Tuning Script: Write a script to monitor disk I/O latency and set up alerts.

Common Pitfalls:

• Ignoring real-world constraints (e.g., suggesting SSDs for a legacy system with no budget).
• Over-indexing (e.g., adding indexes to all columns in a table).
• Forgetting to validate tuning changes with EXPLAIN PLAN or actual workload tests.

Pro Tip:

• Memorize thresholds (e.g., buffer cache hit ratio >90%, CPU <70%).
• Relate answers to Nepali examples (e.g., "Like eSewa’s transaction system, Ncell’s billing database can be tuned by...").
• Use diagrams in exams to explain concepts like execution plans or tablespace structures.