ResourcePool.Docs/SMARTPOOL_DATABASE_SEPARATION.md

# SmartPool Database Separation

## Tổng quan

SmartPool sử dụng 2 database riêng biệt trong ClickHouse để tối ưu hóa hiệu suất và quản lý:

1. **smart_pool_meta** - Metadata database
2. **smart_pool_logs** - Logs & Analytics database

## Lý do tách riêng

### 1. Performance Optimization

- **Metadata database**: Ít thay đổi, truy vấn nhanh, không cần TTL
- **Logs database**: Ghi liên tục, volume lớn, có TTL để tự động xóa dữ liệu cũ

### 2. Scalability

- Có thể scale từng database độc lập
- Logs database có thể dùng cluster riêng với nhiều replica
- Metadata database có thể dùng SSD nhanh hơn

### 3. Backup & Maintenance

- Backup metadata thường xuyên hơn (quan trọng)
- Backup logs ít hơn hoặc không cần (có thể tái tạo)
- Maintenance window riêng biệt

### 4. Access Control

- Có thể phân quyền riêng cho từng database
- Service chỉ đọc metadata không cần quyền ghi logs

## Cấu trúc Database

### smart_pool_meta

```
smart_pool_meta/
├── proxy_metadata           (ReplacingMergeTree)
│   ├── id, host, port
│   ├── protocol, ip_version, country
│   ├── auth_username, auth_password
│   └── status, cluster, source
│
└── proxy_access_mapping     (ReplacingMergeTree)
    ├── access_token
    ├── cluster              (← Maps to cluster, not proxy_id)
    └── priority
```

**Đặc điểm:**
- Dữ liệu ít thay đổi
- Không có TTL
- Sử dụng ReplacingMergeTree để update
- Truy vấn nhanh với FINAL

### smart_pool_logs

```
smart_pool_logs/
├── proxy_usage_logs         (MergeTree)
│   ├── access_token, target_domain
│   ├── proxy_id, status_code
│   ├── response_time_ms, is_success
│   ├── timestamp
│   └── TTL: 90 days
│
├── proxy_stats_hourly       (AggregatingMergeTree)
│   ├── hour, access_token
│   ├── target_domain, proxy_id
│   └── Aggregate states
│
└── mv_proxy_stats_hourly    (Materialized View)
    └── Auto-aggregate from usage_logs
```

**Đặc điểm:**
- Ghi liên tục, volume lớn
- TTL 90 ngày tự động xóa
- Partition by date
- Materialized View tự động aggregate

## Configuration

### appsettings.json

```json
{
  "ClickHouse": {
    "Host": "localhost",
    "Port": 8123,
    "MetaDatabase": "smart_pool_meta",
    "LogsDatabase": "smart_pool_logs",
    "Username": "default",
    "Password": "",
    "UseCompression": true,
    "CommandTimeout": 30
  }
}
```

### Production Recommendations

```json
{
  "ClickHouse": {
    "Host": "clickhouse-cluster.internal",
    "Port": 8123,
    "MetaDatabase": "smart_pool_meta",
    "LogsDatabase": "smart_pool_logs",
    "Username": "smartpool_user",
    "Password": "secure_password",
    "UseCompression": true,
    "CommandTimeout": 30
  }
}
```

## Code Implementation

### IClickHouseContext Interface

```csharp
public interface IClickHouseContext
{
    /// <summary>
    /// Create connection to metadata database
    /// </summary>
    ClickHouseConnection CreateMetaConnection();

    /// <summary>
    /// Create connection to logs database
    /// </summary>
    ClickHouseConnection CreateLogsConnection();
}
```

### Repository Usage

**ProxyMetadataRepository** - Sử dụng Meta Database:
```csharp
using var connection = _clickHouseContext.CreateMetaConnection();
var proxies = await connection.QueryAsync<SmartProxyServer>(
    "SELECT * FROM smart_pool_meta.proxy_metadata FINAL"
);
```

**ProxyLogRepository** - Sử dụng Logs Database:
```csharp
using var connection = _clickHouseContext.CreateLogsConnection();
await connection.ExecuteAsync(
    "INSERT INTO smart_pool_logs.proxy_usage_logs (...) VALUES (...)"
);
```

**Cross-Database Query** - Khi cần enrich data:
```csharp
// 1. Query metadata từ meta database
using var metaConn = _clickHouseContext.CreateMetaConnection();
var proxyMeta = await metaConn.QueryFirstOrDefaultAsync(
    "SELECT host, protocol FROM smart_pool_meta.proxy_metadata WHERE id = @id"
);

// 2. Insert vào logs database với enriched data
using var logsConn = _clickHouseContext.CreateLogsConnection();
await logsConn.ExecuteAsync(
    "INSERT INTO smart_pool_logs.proxy_usage_logs (...) VALUES (...)"
);
```

## Query Examples

### Metadata Queries

```sql
-- Get all active proxies
SELECT * FROM smart_pool_meta.proxy_metadata FINAL
WHERE status = 1;

-- Get proxy access mapping
SELECT * FROM smart_pool_meta.proxy_access_mapping FINAL
WHERE access_token = 'your-token';

-- Get proxy by ID
SELECT * FROM smart_pool_meta.proxy_metadata FINAL
WHERE id = 123;
```

### Logs Queries

```sql
-- Get recent logs
SELECT * FROM smart_pool_logs.proxy_usage_logs
WHERE timestamp >= now() - INTERVAL 1 HOUR
ORDER BY timestamp DESC
LIMIT 100;

-- Get proxy stats
SELECT 
    proxy_id,
    avgMerge(avg_response_time_state) as avg_time,
    sumIfMerge(success_count_state) / countMerge(request_count_state) as success_rate
FROM smart_pool_logs.proxy_stats_hourly
WHERE hour >= now() - INTERVAL 24 HOUR
GROUP BY proxy_id;

-- Get success rate by domain
SELECT 
    target_domain,
    proxy_id,
    sumIfMerge(success_count_state) / countMerge(request_count_state) as success_rate
FROM smart_pool_logs.proxy_stats_hourly
WHERE hour >= now() - INTERVAL 7 DAY
GROUP BY target_domain, proxy_id
ORDER BY success_rate DESC;
```

## Migration Guide

Nếu bạn đang migrate từ single database:

### 1. Backup dữ liệu cũ

```bash
clickhouse-client --query "SELECT * FROM smart_pool.proxy_metadata FORMAT Native" > proxy_metadata.native
clickhouse-client --query "SELECT * FROM smart_pool.proxy_access_mapping FORMAT Native" > proxy_access_mapping.native
```

### 2. Tạo databases mới

```bash
clickhouse-client < clickhouse_init.sql
```

### 3. Import dữ liệu

```bash
cat proxy_metadata.native | clickhouse-client --query "INSERT INTO smart_pool_meta.proxy_metadata FORMAT Native"
cat proxy_access_mapping.native | clickhouse-client --query "INSERT INTO smart_pool_meta.proxy_access_mapping FORMAT Native"
```

### 4. Cập nhật configuration

Thay đổi `Database` thành `MetaDatabase` và `LogsDatabase` trong appsettings.json

### 5. Deploy code mới

Deploy API với code đã cập nhật

## Monitoring

### Disk Usage

```sql
-- Check meta database size
SELECT 
    database,
    table,
    formatReadableSize(sum(bytes)) as size
FROM system.parts
WHERE database = 'smart_pool_meta'
GROUP BY database, table;

-- Check logs database size
SELECT 
    database,
    table,
    formatReadableSize(sum(bytes)) as size
FROM system.parts
WHERE database = 'smart_pool_logs'
GROUP BY database, table;
```

### Query Performance

```sql
-- Slow queries on meta database
SELECT 
    query,
    query_duration_ms,
    read_rows,
    read_bytes
FROM system.query_log
WHERE database = 'smart_pool_meta'
    AND type = 'QueryFinish'
    AND query_duration_ms > 1000
ORDER BY query_duration_ms DESC
LIMIT 10;
```

## Best Practices

1. **Metadata Database**:
   - Sử dụng FINAL trong queries
   - Cache kết quả ở application layer
   - Update ít, read nhiều

2. **Logs Database**:
   - Async insert để không block
   - Sử dụng batch insert khi có thể
   - Query từ aggregated tables (proxy_stats_hourly) thay vì raw logs

3. **Connection Management**:
   - Reuse connections khi có thể
   - Set appropriate timeout
   - Handle connection errors gracefully

4. **Security**:
   - Tạo user riêng cho mỗi database
   - Phân quyền READ/WRITE phù hợp
   - Sử dụng SSL trong production

## Troubleshooting

### Connection Issues

```csharp
// Test meta connection
try {
    using var conn = _clickHouseContext.CreateMetaConnection();
    await conn.ExecuteAsync("SELECT 1");
    Console.WriteLine("Meta DB: OK");
} catch (Exception ex) {
    Console.WriteLine($"Meta DB Error: {ex.Message}");
}

// Test logs connection
try {
    using var conn = _clickHouseContext.CreateLogsConnection();
    await conn.ExecuteAsync("SELECT 1");
    Console.WriteLine("Logs DB: OK");
} catch (Exception ex) {
    Console.WriteLine($"Logs DB Error: {ex.Message}");
}
```

### Performance Issues

- Check if using correct database
- Verify indexes and partitions
- Monitor query execution time
- Check disk I/O and memory usage

## Summary

Việc tách metadata và logs thành 2 database riêng mang lại:

✅ **Performance**: Tối ưu cho từng loại workload  
✅ **Scalability**: Scale độc lập  
✅ **Maintenance**: Quản lý dễ dàng hơn  
✅ **Security**: Phân quyền chi tiết  
✅ **Cost**: Tối ưu storage và compute  

Thiết kế này phù hợp cho production với high-volume logging và real-time analytics.