Architecture Overview #

System Architecture #

IceFireDB-SQLProxy is designed as a decentralized proxy layer that enhances traditional MySQL deployments with P2P networking capabilities. The architecture consists of several key components that work together to provide decentralized SQL database functionality.

Core Components #

1. MySQL Protocol Handler

   • Listens on configured port (default: 33306)
   • Parses MySQL wire protocol requests
   • Handles authentication and connection management
   • Manages client connections and session state

2. Backend MySQL Connector

   • Manages connections to backend MySQL servers
   • Supports connection pooling for efficiency
   • Handles both admin and read-only database connections
   • Implements failover and load balancing

3. Query Router

   • Routes SQL queries to appropriate backend databases
   • Supports read-write separation
   • Handles transaction management
   • Provides query caching and optimization

4. P2P Networking Layer

   • libp2p-based decentralized networking
   • Peer discovery and automatic node connection
   • Data synchronization and change propagation
   • Topic-based messaging for efficient communication

5. Synchronization Engine

   • Change detection from database operations
   • SQL command replication across nodes
   • Conflict detection and resolution strategies
   • Eventual consistency maintenance

6. Connection Pool Manager

   • Efficient management of database connections
   • Connection reuse and lifecycle management
   • Health checking and connection validation
   • Resource allocation and limits enforcement

Architectural Diagram #

+----------------+     +-----------------+     +----------------+
| MySQL Client   | --> | MySQL Protocol  | --> | Backend MySQL  |
| (Application)  |     | Handler         |     | Server         |
+----------------+     +-----------------+     +----------------+
                                      |               |
                                      v               v
                             +-----------------+     +----------------+
                             | Query Router    | <-> | Connection     |
                             |                 |     | Pool Manager   |
                             +-----------------+     +----------------+
                                      |               |
                                      v               v
                             +-----------------+     +----------------+
                             | P2P Networking  | <-> | Synchronization|
                             | Layer           |     | Engine         |
                             +-----------------+     +----------------+
                                      |
                                      v
                             +-----------------+
                             | Other IceFireDB |
                             | SQLProxy Nodes  |
                             +-----------------+

Data Flow #

1. Client Request: MySQL client connects and sends SQL queries
2. Protocol Processing: MySQL protocol handler parses and validates requests
3. Query Routing: Query router determines appropriate backend and connection type
4. Backend Execution: SQL commands executed on backend MySQL server
5. Change Detection: Synchronization engine detects data modifications
6. Network Propagation: Changes propagated to other proxy nodes via P2P network
7. Consistency Maintenance: All proxy nodes eventually reach consistent state

Networking Model #

IceFireDB-SQLProxy uses a decentralized P2P networking model with:

• Service Discovery: Automatic peer discovery using DHT (Distributed Hash Table)
• Topic-based Messaging: PubSub system for efficient data propagation
• NAT Traversal: Support for nodes behind firewalls and NAT devices
• Connection Management: Automatic reconnection and fault tolerance
• Mesh Networking: Full mesh connectivity for reliable message delivery

Consistency Model #

The system implements eventual consistency with:

• Operation-based Replication: SQL commands are replicated across nodes
• Conflict Resolution: Timestamp-based resolution for conflicting operations
• Causal Ordering: Preservation of operation causality where possible
• Background Synchronization: Continuous state synchronization between proxies
• Transaction Awareness: Basic transaction support with conflict detection

Performance Characteristics #

• Low Latency: Optimized protocol handling and connection pooling
• High Throughput: Efficient query routing and parallel processing
• Scalability: Horizontal scaling through additional proxy nodes
• Resource Efficiency: Connection pooling reduces backend database load
• Reliability: Automatic retry and failover mechanisms

Deployment Modes #

Single Backend Mode #

• Single backend MySQL server
• Simple deployment for development and testing
• Full P2P synchronization capabilities

Multi-Backend Mode #

• Multiple backend MySQL servers
• Read-write separation support
• Load balancing across backend instances
• Failover and replica promotion support

Security Features #

• Transport Encryption: TLS support for client and backend connections
• Database Authentication: MySQL authentication with configurable credentials
• Access Control: Multi-level access control (admin vs read-only)
• Network Security: Secure peer authentication and encrypted P2P communications
• Audit Logging: Query logging and access monitoring
• SQL Injection Protection: Basic query validation and sanitization

Monitoring and Management #

• Health Checks: Regular backend MySQL health monitoring
• Performance Metrics: Query latency, throughput, and error rates
• Connection Pool Statistics: Active, idle, and total connections
• P2P Network Status: Peer connectivity and synchronization state
• Query Logging: Comprehensive query logging for debugging and audit
• Resource Usage: Memory, CPU, and network usage monitoring

Advanced Features #

Connection Pooling #

• Configurable minimum and maximum connections
• Connection reuse and lifecycle management
• Health checking and connection validation
• Timeout and idle connection management

Read-Write Separation #

• Different connection pools for admin and read-only operations
• Automatic routing of read vs write queries
• Configurable routing rules and policies
• Support for multiple backend database instances

Multi-Tenant Support #

• User-based connection isolation
• Database-level access control
• Resource limits per user or tenant
• Audit logging per user session