Performance Monitoring & Tuning: Metrics, Tools & Optimization

TAKEAWAYS:

• Database performance tuning involves monitoring (identifying bottlenecks) and optimizing (fixing inefficiencies) using metrics like response time, throughput, and resource utilization.
• Indexing (B-tree, bitmap, hash) speeds up queries but can slow down writes; choose the right index type for your workload.
• Query optimization includes rewriting SQL, analyzing execution plans, and using hints to guide the optimizer.
• Hardware tuning involves adjusting memory (buffer pool, shared pool), CPU, and storage (RAID, SSDs) for better performance.
• Database tools like Oracle AWR, SQL Server DMVs, and MySQL Enterprise Monitor automate monitoring and provide actionable insights.
• Real-world tuning requires balancing trade-offs (e.g., read vs. write performance, cost vs. speed) based on application needs.

1. Why Performance Monitoring and Tuning?

Databases are the backbone of modern applications. Poor performance leads to:

• Slow user experiences (e.g., eSewa transactions timing out).
• High operational costs (e.g., Ncell’s billing system crashing under load).
• Lost revenue (e.g., Daraz’s checkout process failing during sales).

Key metrics to monitor:

2. Tools for Performance Monitoring

Different DBMS provide built-in tools to monitor performance:

A. Oracle Database

• Automatic Workload Repository (AWR):

  • Captures performance statistics every hour.
  • Generates AWR reports showing SQL execution, wait events, and bottlenecks.
  • Example: If a query takes 5 seconds in AWR, check its execution plan.

  sequenceDiagram
      participant User
      participant OracleDB
      participant AWR
      User->>OracleDB: Runs a slow query
      OracleDB->>AWR: Logs execution time
      AWR->>User: Generates report (SQL_ID: 12345, Elapsed: 5s)

• Oracle Enterprise Manager (OEM):

  • Web-based dashboard for real-time monitoring.
  • Alerts for high CPU, I/O, or locks.

B. Microsoft SQL Server

• Dynamic Management Views (DMVs):
  • sys.dm_exec_query_stats: Shows query execution history.
  • sys.dm_os_wait_stats: Identifies wait types (e.g., PAGEIOLATCH_W for disk I/O).
  • Example:

    SELECT TOP 5
        qs.execution_count,
        qs.total_logical_reads,
        qs.total_elapsed_time/1000 AS total_elapsed_ms,
        qt.text AS query_text
    FROM sys.dm_exec_query_stats qs
    CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
    ORDER BY qs.total_elapsed_time DESC;
    

C. MySQL / MariaDB

• Performance Schema:
  • Tracks query execution, locks, and I/O.
  • Example: Enable it with:

    UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'events_statements_history';
    

• MySQL Enterprise Monitor:
  • Cloud-based tool for cross-database monitoring.

D. PostgreSQL

• pg_stat_activity:
  • Shows running queries and their locks.
  • Example:

    SELECT pid, usename, query, state, now() - query_start AS duration
    FROM pg_stat_activity
    WHERE state = 'active';
    

• EXPLAIN ANALYZE:
  • Shows query execution plan with actual timings.
  • Example:

    EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 100;
    

3. Common Performance Bottlenecks

A. Slow Queries

• Cause: Missing indexes, full table scans, or inefficient joins.
• Solution:
  • Use EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL) to check the execution plan.
  • Add indexes on frequently queried columns (e.g., customer_id in orders table).
  • Rewrite queries (e.g., avoid SELECT *, use JOIN instead of subqueries).

Example: Tuning a Slow Query in eSewa Suppose eSewa’s transaction table has no index on transaction_date:

-- Slow query (full table scan)
SELECT * FROM transactions WHERE transaction_date = '2023-10-01';

Fix: Add an index:

CREATE INDEX idx_transaction_date ON transactions(transaction_date);

Now the query uses the index:

B. High CPU Usage

• Cause: Complex queries, recursive CTEs, or CPU-bound operations.
• Solution:
  • Optimize SQL (e.g., avoid OR in WHERE clauses).
  • Use materialized views for repetitive aggregations.
  • Upgrade CPU or partition large tables.

Example: Ncell’s Billing System If Ncell’s billing query runs a recursive CTE to calculate call durations:

-- CPU-intensive query
WITH RECURSIVE call_durations AS (
    SELECT call_id, duration FROM calls WHERE call_id = 1
    UNION ALL
    SELECT c.call_id, c.duration + cd.duration
    FROM calls c
    JOIN call_durations cd ON c.parent_id = cd.call_id
)
SELECT SUM(duration) FROM call_durations;

Fix: Pre-calculate durations in a stored procedure or use a trigger.

C. I/O Bottlenecks

• Cause: Too many disk reads/writes, slow storage (HDD vs. SSD).
• Solution:
  • Increase buffer pool size (e.g., innodb_buffer_pool_size in MySQL).
  • Use SSDs or RAID 10 for high-performance storage.
  • Partition tables by date (e.g., orders_2023, orders_2024).

D. Lock Contention

• Cause: Long-running transactions or missing isolation levels.
• Solution:
  • Use READ COMMITTED instead of SERIALIZABLE where possible.
  • Shorten transactions (e.g., avoid holding locks while processing).
  • Use optimistic locking (e.g., WHERE version = 1).

Example: Daraz’s Inventory System If two users try to update the same product stock simultaneously:

-- Problem: Lock contention
BEGIN;
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
-- Long-running process...
COMMIT;

Fix: Use row-level locking or optimistic concurrency control:

-- Optimistic approach
UPDATE inventory
SET stock = stock - 1, version = version + 1
WHERE product_id = 100 AND version = 5;

4. Indexing Strategies

Indexes speed up reads but slow down writes. Choose wisely:

Example: Kathmandu Traffic Routes (Analogy) Think of indexes like shortcuts in Kathmandu:

• B-tree index = A well-marked road (fast for long trips).
• Hash index = A direct flight (instant for exact destinations).
• No index = Walking through crowded Thamel (slow for everyone).

5. Query Optimization Techniques

A. Rewrite SQL for Efficiency

• Bad: SELECT * FROM orders WHERE status = 'shipped' OR status = 'delivered'; → Uses function-based index poorly.
• Good: SELECT * FROM orders WHERE status IN ('shipped', 'delivered');

B. Use Execution Plans

Always check the execution plan before optimizing:

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

-- SQL Server
EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01';

Look for:

• Full table scans (no index used).
• Nested loops (inefficient for large tables).
• Hash joins (good for equality joins).

C. Partitioning Large Tables

Split tables by range (e.g., orders_2023, orders_2024) or list (e.g., customers_A, customers_B). Example for NEPSE:

-- Before: Slow query on 10M rows
SELECT * FROM stock_prices WHERE date = '2023-10-01';

-- After: Partitioned by year
CREATE TABLE stock_prices (
    date DATE,
    symbol VARCHAR(10),
    price DECIMAL(10,2)
) PARTITION BY RANGE (YEAR(date)) (
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025)
);

D. Materialized Views

Pre-compute expensive queries and refresh periodically. Example for Daraz:

-- Slow daily query
SELECT product_id, SUM(quantity) AS total_sold
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY product_id;

-- Materialized view (refresh nightly)
CREATE MATERIALIZED VIEW mv_daily_sales AS
SELECT product_id, SUM(quantity) AS total_sold
FROM orders
WHERE order_date = CURRENT_DATE - INTERVAL '1 day';

6. Hardware and Configuration Tuning

A. Memory Optimization

• Buffer Pool: Cache frequently accessed data in RAM.
  • MySQL: innodb_buffer_pool_size = 80% of RAM.
  • Oracle: db_cache_size.
• Shared Pool: Cache SQL execution plans (SQL Server: max_memory_percent).

B. Storage Tuning

• RAID Levels:
  • RAID 0: Fast but no redundancy (use for temp tables).
  • RAID 1: Mirroring (good for critical data).
  • RAID 10: Best for performance + redundancy.
• SSDs vs. HDDs:
  • SSDs reduce I/O latency by 10x but cost more.

C. Parallel Query Execution

• Use multiple CPU cores for large queries (e.g., Oracle’s PARALLEL hint).
• Example:

  SELECT /*+ PARALLEL(4) */ * FROM large_table;
  

7. Real-World Applications

A. eSewa: Transaction Processing

• Problem: High concurrency during festivals (e.g., Dashain) causes lock contention.
• Solution:
  • Use optimistic locking for payment updates.
  • Partition transactions by date (transactions_2023, transactions_2024).
  • Monitor with Oracle AWR and auto-scale during peak hours.

B. Ncell: Billing System

• Problem: Monthly billing queries take 2 hours on HDDs.
• Solution:
  • Migrate to SSDs (reduced I/O latency from 100ms to 10ms).
  • Add composite indexes on (customer_id, billing_cycle).
  • Use materialized views for summary reports.

C. Daraz: Order Fulfillment

• Problem: Slow inventory updates during sales.
• Solution:
  • Implement read replicas to offload reporting queries.
  • Use batch updates instead of row-by-row changes.
  • Monitor with MySQL Performance Schema to detect slow queries.

D. NTC: Network Traffic Analysis

• Problem: Real-time traffic data queries lag.
• Solution:
  • Use columnar storage (e.g., PostgreSQL with TimescaleDB).
  • Partition data by hour/day.
  • Cache frequent queries with Redis.

8. Step-by-Step Tuning Workflow

1. Identify the Problem:
   • Is it slow queries, high CPU, or I/O?
   • Use tools like AWR, DMVs, or Performance Schema.
2. Analyze the Execution Plan:
   • Check for full table scans, inefficient joins.
3. Fix at the Query Level:
   • Add missing indexes.
   • Rewrite SQL (e.g., avoid SELECT *).
4. Tune the Database:
   • Adjust memory (buffer pool, shared pool).
   • Partition large tables.
5. Optimize Hardware:
   • Upgrade to SSDs or RAID 10.
   • Add more CPU cores.
6. Monitor and Repeat:
   • Use tools to verify improvements.
   • Set up alerts for regressions.

flowchart TD
    A["1. Monitor Performance"] --> B["2. Analyze Bottlenecks"]
    B --> C["3. Optimize Queries"]
    C --> D["4. Tune Database Config"]
    D --> E["5. Upgrade Hardware"]
    E --> F["6. Monitor & Iterate"]

Exam Tip

For TU/PU/NEB exams, expect:

1. Short Questions (2-5 marks):

   • Define terms like buffer pool, execution plan, or lock contention.
   • Example:

     "What is the purpose of the EXPLAIN command in SQL?" Answer: It shows the execution plan of a query, including join methods, index usage, and estimated costs.

2. Long Questions (10-15 marks):

   • Scenario-based tuning:

     "The NEPSE stock price database has slow queries on historical data. Suggest 3 optimizations." Answer:

     1. Partition the table by year to reduce I/O.
     2. Add a composite index on (symbol, date).
     3. Use materialized views for daily summaries.

   • Tool-based questions:

     "How would you use Oracle AWR to find the top 5 slowest queries?" Answer:

     1. Run @?/rdbms/admin/awrrpt.sql in SQL*Plus.
     2. Filter by Elapsed Time in the report.
     3. Identify queries with Elapsed Time > 1 second.

3. Practical (15-20 marks):

   • Given a slow query, rewrite it and explain optimizations.
   • Example:

     Given:

     SELECT * FROM employees WHERE department = 'IT' AND salary > 50000;
     

     Optimized:

     SELECT employee_id, name, salary FROM employees
     WHERE department = 'IT' AND salary > 50000;
     

     Explanation:

     • Added index on (department, salary).
     • Removed SELECT * to reduce I/O.

4. Comparison Tables:

   • Compare B-tree vs. Hash indexes or RAID 0 vs. RAID 10.

Key Focus Areas for Exams:

• Know how to read execution plans (look for full scans, nested loops).
• Understand index trade-offs (speed vs. storage).
• Be able to tune a given query (add indexes, rewrite SQL).
• Recall tools (AWR, DMVs, Performance Schema) and their commands.