# pku-dair/dbtune architecture

> Architecture diagram of the GitHub repository pku-dair/dbtune, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2025-04-09). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/pku-dair/dbtune
- Repository: https://github.com/pku-dair/dbtune

## Overview

##### Project Type and Purpose
- DBTune is a database tuning system designed for automatically finding optimal configuration knobs for databases (MySQL and PostgreSQL). It supports various tuning scenarios including performance, resource-oriented, and multiple-objective tuning.
- The README and file tree reveal that the solution is built in Python, uses state‐of‐the‐art optimization techniques (Bayesian optimization, genetics, surrogate models, transfer learning) and interfaces directly with database benchmark tools such as OLTPBench, Sysbench, and JOB.

##### Key Components from the File Structure
a. Core Tuning Engine (autotune directory):
- Tuning Pipeline: The autotune/pipleline folder (with modules like base.py and pipleline.py) is responsible for managing the tuning workflow from start to finish.
- Surrogate Models & Acquisition Functions: Under autotune/optimizer/surrogate (and its “base” and “ddpg” subfolders) and autotune/optimizer/acquisition_function, the project contains diverse surrogate models and criteria for candidate evaluation.
- Transfer Learning: The autotune/transfer subdirectory includes modules (e.g., tlbo) dedicated to transferring tuning knowledge from historical tasks.
- Database Connectivity: The autotune/database folder encapsulates database-specific connectivity code (mysqldb.py, postgresqldb.py), while files like dbenv.py and dbenv_bench.py set up the runtime environment.
- Utility and Support Modules: The autotune/utils directory provides logging, configuration handling, parsing, and various helper functions essential for running and debugging the system.
- Query Files & Workload Definitions: The autotune/job_query and autotune/tpch_query directories contain SQL workload queries for tuning benchmarks.

b. Frontend/CLI and Script Integration:
- autotune/cli holds shell scripts to run tuning jobs with different benchmark types.
- The scripts folder contains experiments, configuration files (e.g., config_auto.ini, config_performance.ini), and executables (like optimize.py) which serve as entry points and test harnesses.
- The test folder and tuning_benchmark directory provide testing and benchmarking data resources.

##### Architectural Relationships and Data Flow
- The system design should capture the flow starting from configuration and benchmark preparation to the execution of optimization algorithms and database performance evaluation:
- Configuration Layer: Components in autotune/utils/config (because they parse config files such as config_auto.ini) feed tuning parameters (e.g., objectives, constraints) into the system.
- Database Connection Layer: Modules in autotune/database establish and manage connectivity with either MySQL or PostgreSQL. Data flows from these connectors (e.g., dbenv, dbenv_bench) to the tuning pipeline for performance feedback.
- Tuning Pipeline: The autotune/pipleline modules orchestrate the tuning process – they receive current configuration knobs, invoke an optimization algorithm, update surrogate models, and determine next configurations.
- Optimization & Decision-Making: The autotune/optimizer and its submodules (including surrogate models and acquisition functions) make use of historical performance data (collected via autotune/workload, resource_monitor, etc.) to dynamically select candidate configurations.
- Workload Execution: The query directories (autotune/job_query and autotune/tpch_query) provide benchmark SQL queries to test candidate configurations. The CLI scripts (under autotune/cli) and experimental scripts (in scripts/experiment) trigger these workloads.
- Feedback & Logging: Utility modules (like autotune/utils/logger and autotune/utils/history_container) log the performance metrics; this feedback loop is critical for successive tuning iterations.
- Transfer Learning: The transfer submodule (autotune/transfer/tlbo) interfaces with the optimization engine to incorporate prior tuning knowledge, thereby speeding up convergence on new tuning tasks.

##### Architectural Patterns and Design Principles
- Modularity: The project is clearly modularized with separate directories for optimization, database connectivity, query handling, and utilities. This suggests a plug-and-play-like design where each tuning strategy can be swapped or extended independently.
- Pipeline Orchestration: The tuning pipeline embodies a sequential processing pattern – from reading configurations, executing database queries, processing and logging performance metrics, to updating optimization strategies.
- Extensibility: With many submodules for various optimization techniques (e.g., Bayesian optimization, GA, DDPG-based strategies), the design supports dynamic selection of the most appropriate algorithm per tuning task.
- Separation of Concerns: The isolation of CLI scripts, query definitions, and core tuning logic (inside autotune) emphasizes a clean boundary between user interaction, workload execution, and optimization logic.

## Diagram (Mermaid)

```mermaid
flowchart TD
    %% Main Components
    ConfigSetup["Configuration & Setup"]:::configuration
    TuningPipeline["Tuning Pipeline"]:::pipeline
    OptEngine["Optimization Engine"]:::optimization
    TransLearn["Transfer Learning Module"]:::transfer
    UtilLog["Utility & Logging"]:::utility

    %% Sub-components inside Optimization Engine
    Surrogate["Surrogate Models"]:::optimization
    Acquisition["Acquisition Functions"]:::optimization

    %% Database Connector Subgraph
    subgraph "Database Connector"
        DBConn["DB Connectivity"]:::db
        DBEnv["DB Environment"]:::db
        DBBench["DB Bench"]:::db
    end

    %% Workload & Benchmark Subgraph
    subgraph "Workload & Benchmark"
        JobQ["Job Queries"]:::workload
        TPCHQ["TPCH Queries"]:::workload
    end

    %% Frontend/CLI Integration Subgraph
    subgraph "Frontend/CLI Integration"
        CLIS["CLI Scripts"]:::frontend
        ExpScr["Experiment Scripts"]:::frontend
        OptScr["Optimize Script"]:::frontend
    end

    %% Testing & Benchmarking Subgraph
    subgraph "Testing & Benchmarking"
        Tests["Tests"]:::test
        BenchData["Benchmark Data"]:::test
    end

    %% Configuration Files Subgraph
    subgraph "Configuration Files"
        ConfigFiles["Config Files"]:::configuration
    end

    %% Connections / Data Flows
    ConfigFiles --> ConfigSetup
    ConfigSetup -->|feeds| TuningPipeline
    ConfigSetup -->|feeds| DBConn

    TuningPipeline -->|invokes| OptEngine
    TuningPipeline -->|sends| DBConn
    TuningPipeline -->|triggered by| OptScr
    DBConn -->|returns| TuningPipeline

    OptEngine -->|feedback| TuningPipeline
    TransLearn -->|enhances| OptEngine

    %% Internal breakdown of Optimization Engine
    OptEngine --> Surrogate
    OptEngine --> Acquisition

    JobQ -->|executes on| DBConn
    TPCHQ -->|executes on| DBConn

    CLIS -->|triggers| JobQ
    ExpScr -->|triggers| JobQ

    UtilLog -->|logs to| TuningPipeline

    Tests -->|validates| TuningPipeline
    BenchData -->|supports| JobQ

    %% Styles for clarity - Legend
    classDef configuration fill:#a2d9ff,stroke:#333,stroke-width:2px;
    classDef pipeline fill:#cce5ff,stroke:#333,stroke-width:2px;
    classDef optimization fill:#d1c4e9,stroke:#333,stroke-width:2px;
    classDef transfer fill:#f5c6cb,stroke:#333,stroke-width:2px;
    classDef db fill:#c1e1c1,stroke:#333,stroke-width:2px;
    classDef workload fill:#ffebcc,stroke:#333,stroke-width:2px;
    classDef frontend fill:#ffcccc,stroke:#333,stroke-width:2px;
    classDef utility fill:#ffe0b2,stroke:#333,stroke-width:2px;
    classDef test fill:#d1f2eb,stroke:#333,stroke-width:2px;

    %% Click Events
    click TuningPipeline "https://github.com/pku-dair/dbtune/tree/main/autotune/pipleline"
    click OptEngine "https://github.com/pku-dair/dbtune/tree/main/autotune/optimizer"
    click Surrogate "https://github.com/pku-dair/dbtune/tree/main/autotune/optimizer/surrogate"
    click Acquisition "https://github.com/pku-dair/dbtune/tree/main/autotune/optimizer/acquisition_function"
    click TransLearn "https://github.com/pku-dair/dbtune/tree/main/autotune/transfer/tlbo"
    click DBConn "https://github.com/pku-dair/dbtune/tree/main/autotune/database"
    click DBEnv "https://github.com/pku-dair/dbtune/blob/main/autotune/dbenv.py"
    click DBBench "https://github.com/pku-dair/dbtune/blob/main/autotune/dbenv_bench.py"
    click UtilLog "https://github.com/pku-dair/dbtune/tree/main/autotune/utils"
    click JobQ "https://github.com/pku-dair/dbtune/tree/main/autotune/job_query"
    click TPCHQ "https://github.com/pku-dair/dbtune/tree/main/autotune/tpch_query"
    click CLIS "https://github.com/pku-dair/dbtune/tree/main/autotune/cli"
    click ExpScr "https://github.com/pku-dair/dbtune/tree/main/scripts/experiment"
    click OptScr "https://github.com/pku-dair/dbtune/blob/main/scripts/optimize.py"
    click Tests "https://github.com/pku-dair/dbtune/tree/main/test"
    click BenchData "https://github.com/pku-dair/dbtune/tree/main/tuning_benchmark"
    click ConfigFiles "https://github.com/pku-dair/dbtune/blob/main/scripts/config_auto.ini"
```
