# Create exporter user
sudo useradd -r -s /usr/sbin/nologin prometheus

# Download mysqld_exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.1/mysqld_exporter-0.15.1.linux-amd64.tar.gz
tar xzf mysqld_exporter-*.tar.gz
sudo cp mysqld_exporter-*/mysqld_exporter /usr/local/bin/

# Create MySQL monitoring user
mysql -u root -e "CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'secure_password';
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';"

# Start exporter
cat > /etc/systemd/system/mysqld-exporter.service << 'EOF'
[Unit]
Description=MySQL Exporter
After=network.target

[Service]
User=prometheus
ExecStart=/usr/local/bin/mysqld_exporter \
  --mysq.address=localhost \
  --config.my-cnf=/etc/mysql/exporter.cnf

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl enable --now mysqld-exporter

# /etc/prometheus/prometheus.yml
global:
  scrape_interval: 15s

scrape_configs:
  - job_name: 'mysql'
    static_configs:
      - targets: ['localhost:9104']
    metrics_path: /metrics

  - job_name: 'node'
    static_configs:
      - targets: ['localhost:9100']

wget https://github.com/prometheus/node_exporter/releases/download/v1.7.0/node_exporter-1.7.0.linux-amd64.tar.gz
tar xzf node_exporter-*.tar.gz
sudo cp node_exporter-*/node_exporter /usr/local/bin/

sudo systemctl enable --now node_exporter

# /etc/mysql/conf.d/slow_query.cnf
[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1

sudo systemctl restart mysql
# or
sudo systemctl restart postgresql

# Install Ollama
curl -fsSL https://ollama.com/install.sh | sh

# Pull a model suitable for tuning (qwen2.5 has good SQL understanding)
ollama pull qwen2.5:14b

# Verify
ollama run qwen2.5:14b "Explain the output of EXPLAIN ANALYZE"

pip install openai requests

# /opt/db-optimizer/config.py
OPENROUTER_API_KEY=*** = "anthropic/claude-3.5-sonnet"
ENDPOINT = "https://openrouter.ai/api/v1/chat/completions"

#!/usr/bin/env python3
"""AI-powered database slow query analyzer."""

import subprocess
import json
import os
from datetime import datetime, timedelta
import re

class SlowQueryAnalyzer:
    def __init__(self, db_type="mysql"):
        self.db_type = db_type
        self.query_history = []

    def extract_slow_queries(self, hours=24):
        """Extract slow queries from logs for the last N hours."""
        if self.db_type == "mysql":
            log_file = "/var/log/mysql/slow-query.log"
        else:
            log_file = "/var/log/postgresql/postgresql-slow.log"

        if not os.path.exists(log_file):
            return []

        cutoff = datetime.now() - timedelta(hours=hours)
        queries = []

        with open(log_file, 'r') as f:
            content = f.read()

        # Parse MySQL slow query log format
        pattern = r'# Query_time:\s+([\d.]+).*?SET timestamp=\d+;\s*\n(.+?)(?=# Time:|$)'
        matches = re.finditer(pattern, content, re.DOTALL)

        for match in matches:
            query_time = float(match.group(1))
            sql = match.group(2).strip().rstrip(';')

            if query_time > 1.0:  # Focus on queries over 1 second
                queries.append({
                    "query_time": query_time,
                    "sql": sql,
                    "timestamp": datetime.now().isoformat()
                })

        return queries

    def get_explain_plan(self, sql):
        """Get EXPLAIN ANALYZE output."""
        if self.db_type == "mysql":
            cmd = ["mysql", "-u", "root", "-e", f"EXPLAIN ANALYZE {sql}"]
        else:
            cmd = ["psql", "-U", "postgres", "-c", f"EXPLAIN ANALYZE {sql}"]

        try:
            result = subprocess.run(cmd, capture_output=True, text=True, timeout=30)
            return result.stdout
        except subprocess.TimeoutExpired:
            return "EXPLAIN timed out"

    def generate_ai_report(self, slow_queries):
        """Send slow queries to AI model for analysis."""
        if not slow_queries:
            return "No slow queries detected."

        # Sample top 10 slowest queries
        top_queries = sorted(slow_queries, key=lambda x: x["query_time"], reverse=True)[:10]

        prompt = f"""You are a professional database performance optimization expert. Analyze the following slow queries and provide optimization suggestions.

Database type: {self.db_type}

Slow query list (sorted by execution time, descending):
"""
        for i, q in enumerate(top_queries, 1):
            prompt += f"\n--- Query #{i} ---\n"
            prompt += f"Execution time: {q['query_time']}s\n"
            prompt += f"SQL: {q['sql']}\n"

        prompt += """
Please provide:
1. Performance bottleneck analysis for each query
2. Specific index optimization suggestions (CREATE INDEX statements)
3. Query rewriting suggestions (if applicable)
4. Database configuration parameter adjustment suggestions
5. Expected performance improvement magnitude

Return in JSON format with "analysis" (array) and "summary" fields.
"""

        return self._call_llm(prompt)

    def _call_llm(self, prompt):
        """Call local Ollama or OpenRouter API."""
        # Try local Ollama first
        try:
            result = subprocess.run(
                ["ollama", "run", "qwen2.5:14b", prompt],
                capture_output=True, text=True, timeout=120
            )
            return result.stdout
        except (subprocess.TimeoutExpired, FileNotFoundError):
            pass

        # Fallback to OpenRouter API
        try:
            import requests
            headers = {
                "Authorization": f"Bearer {os.environ.get('OPENROUTER_API_KEY', '')}",
                "Content-Type": "application/json",
                "HTTP-Referer": "https://selfvps.net",
            }
            payload = {
                "model": "anthropic/claude-3.5-sonnet",
                "messages": [{"role": "user", "content": prompt}]
            }
            resp = requests.post(
                "https://openrouter.ai/api/v1/chat/completions",
                headers=headers, json=payload, timeout=60
            )
            return resp.json()["choices"][0]["message"]["content"]
        except Exception as e:
            return f"LLM call failed: {e}"

    def generate_index_recommendations(self, slow_queries):
        """Generate specific index optimization recommendations."""
        report = self.generate_ai_report(slow_queries)
        return report


# Main entry
if __name__ == "__main__":
    analyzer = SlowQueryAnalyzer("mysql")
    queries = analyzer.extract_slow_queries(hours=24)

    if queries:
        print(f"Found {len(queries)} slow queries")
        report = analyzer.generate_ai_report(queries)
        print(report)

        # Save to reports directory
        os.makedirs("/var/log/db-optimizer/reports", exist_ok=True)
        timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
        with open(f"/var/log/db-optimizer/reports/report_{timestamp}.md", "w") as f:
            f.write(f"# Database Optimization Report\n")
            f.write(f"Generated: {datetime.now().isoformat()}\n\n")
            f.write(f"Slow queries found: {len(queries)}\n\n")
            f.write(f"## AI Analysis Report\n{report}\n")
    else:
        print("No slow queries detected. Database is healthy.")

#!/usr/bin/env python3
"""AI-powered database configuration optimizer."""

import subprocess
import os

def get_current_config(db_type="mysql"):
    """Get current database configuration."""
    if db_type == "mysql":
        result = subprocess.run(
            ["mysql", "-u", "root", "-e", "SHOW VARIABLES;"],
            capture_output=True, text=True
        )
        return result.stdout
    else:
        result = subprocess.run(
            ["psql", "-U", "postgres", "-c", "SHOW ALL;"],
            capture_output=True, text=True
        )
        return result.stdout

def get_server_info():
    """Get server resource information."""
    info = {}

    # Total memory
    with open('/proc/meminfo', 'r') as f:
        meminfo = f.read()
        for line in meminfo.split('\n'):
            if line.startswith('MemTotal'):
                info['total_memory_mb'] = int(line.split()[1]) // 1024
            elif line.startswith('MemAvailable'):
                info['available_memory_mb'] = int(line.split()[1]) // 1024

    # CPU cores
    info['cpu_cores'] = os.cpu_count() or 1

    # Disk space
    stat = os.statvfs('/')
    info['disk_total_gb'] = stat.f_blocks * stat.f_bsize // (1024**3)
    info['disk_free_gb'] = stat.f_bavail * stat.f_bsize // (1024**3)

    return info

def generate_config_recommendations(db_type="mysql"):
    """Generate configuration optimization suggestions."""
    config = get_current_config(db_type)
    server = get_server_info()

    prompt = f"""You are a database performance optimization expert. Based on the following server resources and current configuration, provide optimization suggestions.

Server information:
- Total memory: {server['total_memory_mb']} MB
- Available memory: {server['available_memory_mb']} MB
- CPU cores: {server['cpu_cores']}
- Disk free: {server['disk_free_gb']} GB

Current database configuration (key parameters):
{config[:3000]}

Please provide optimization suggestions for the following parameters (based on server resources):
1. innodb_buffer_pool_size (MySQL) / shared_buffers (PostgreSQL)
2. innodb_log_file_size (MySQL) / wal_buffers (PostgreSQL)
3. max_connections
4. query_cache_size / effective_cache_size
5. work_mem (PostgreSQL) / sort_buffer_size (MySQL)
6. Other critical parameters

Provide specific values and rationale. Return in JSON format.
"""

    try:
        result = subprocess.run(
            ["ollama", "run", "qwen2.5:14b", prompt],
            capture_output=True, text=True, timeout=120
        )
        return result.stdout
    except Exception:
        return "Unable to call AI model for configuration analysis"

if __name__ == "__main__":
    recommendations = generate_config_recommendations("mysql")
    print(recommendations)

# Run slow query analysis daily at 2 AM
0 2 * * * /usr/bin/python3 /opt/db-optimizer/analyzer.py >> /var/log/db-optimizer/cron.log 2>&1

# Run configuration optimization analysis every Sunday at 3 AM
0 3 * * 0 /usr/bin/python3 /opt/db-optimizer/config_optimizer.py >> /var/log/db-optimizer/cron.log 2>&1

# /etc/systemd/system/db-optimizer.timer
[Unit]
Description=Daily Database Optimization Analysis

[Timer]
OnCalendar=*-*-* 02:00:00
Persistent=true

[Install]
WantedBy=timers.target

# /etc/systemd/system/db-optimizer.service
[Unit]
Description=Database Optimization Analysis Service

[Service]
Type=oneshot
ExecStart=/usr/bin/python3 /opt/db-optimizer/analyzer.py
WorkingDirectory=/opt/db-optimizer
StandardOutput=append:/var/log/db-optimizer/service.log
StandardError=append:/var/log/db-optimizer/service.log

sudo systemctl enable --now db-optimizer.timer

import matplotlib
matplotlib.use('Agg')
import matplotlib.pyplot as plt

def generate_performance_chart(queries, output_path="/var/log/db-optimizer/performance.png"):
    """Generate query performance trend chart."""
    times = [q['query_time'] for q in queries]
    labels = [f"Q{i+1}" for i in range(len(times))]

    fig, ax = plt.subplots(figsize=(12, 6))
    ax.barh(labels, times, color='#4CAF50' if times[-1] < 2 else '#F44336')
    ax.set_xlabel('Execution Time (seconds)')
    ax.set_title('Top Slow Queries (Last 24h)')
    ax.axvline(x=1.0, color='red', linestyle='--', alpha=0.5, label='Threshold (1s)')
    ax.legend()
    plt.tight_layout()
    plt.savefig(output_path, dpi=150)
    plt.close()

import requests

def send_alert(message, chat_id=None, webhook_url=None):
    """Send alert notification."""
    if webhook_url:
        # Slack/DingTalk/Webhook
        requests.post(webhook_url, json={"text": message})
    elif chat_id:
        # Telegram Bot
        bot_token = os.environ.get("TELEGRAM_BOT_TOKEN")
        url = f"https://api.telegram.org/bot{bot_token}/sendMessage"
        requests.post(url, json={
            "chat_id": chat_id,
            "text": message,
            "parse_mode": "Markdown"
        })

-- Before optimization (3.2 seconds)
SELECT * FROM orders WHERE customer_id = 12345 AND status = 'pending'
ORDER BY created_at DESC LIMIT 20;

-- Index recommended by AI
CREATE INDEX idx_orders_customer_status_created
ON orders(customer_id, status, created_at DESC);

-- After optimization (12ms) — 267x faster

-- Before optimization (loop 500 queries, total 4.8 seconds)
SELECT * FROM users;  -- 1 query
-- Then for each user:
SELECT * FROM orders WHERE user_id = ?;  -- 500 queries

-- AI suggests using JOIN instead
SELECT u.*, o.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active';

-- After optimization (1 query, 280ms) — 17x faster

-- Quick rollback: drop recently created index
DROP INDEX idx_orders_customer_status_created ON orders;

-- Rollback config: edit config file then restart
sudo systemctl restart mysql