# AI DBA Assistant Source: https://docs.rapydo.io/ai-dba/assistant Your intelligent database assistant that answers questions about your entire database fleet using natural language ## Overview AI DBA Assistant AI DBA Assistant transforms how you interact with your databases. Instead of writing complex queries or navigating multiple monitoring dashboards, simply ask questions in plain English. The AI analyzes your fleet's monitoring data and returns insights with actionable recommendations. **Key capability:** The Assistant doesn't just identify problems—it provides complete query analysis with remediation plans to fix them. *** ## Getting Started Access the Assistant from the left navigation panel under **AI DBA → Assistant**. The interface is straightforward: type your question in the input field and press enter or click the send button. **Conversation Controls:** * **New conversation** — Starts a fresh conversation, clearing the current context * **Clear history** — Removes all messages from the current view (disabled when no messages exist) **Important:** The Assistant maintains conversation history within a session using a conversation ID. However, each new conversation starts fresh with no memory of previous sessions. Include all relevant context when starting a new conversation or asking follow-up questions that reference earlier messages. **Example first question:** > "Show me the top 3 longest queries from the last day" *** ## Key Features ### Natural Language Queries Ask questions the way you would ask a senior DBA. The AI understands context and database terminology across both MySQL and PostgreSQL environments. **What you can ask:** * "Are there any performance issues in my databases over the last 24 hours?" * "How many locks do we have today on the fleet?" * "Which instances have the highest connection count right now?" * "Show me slow queries affecting the payments table" * "What's causing high CPU on our production database?" * "Analyze the queries running longer than 5 minutes" * "Why is this query slow: SELECT \* FROM orders WHERE created\_at > '2024-01-01'" *** ### Rich Analysis Results Every response includes multiple components designed to give you complete context: #### **Findings Summary** A clear statement of what the AI discovered, including: * Affected database instances * Timeframes and frequency * Severity indicators * Impact on performance *** #### **The Query** When relevant, the AI displays the actual SQL being analyzed, formatted for readability. This includes: * Full query text with syntax highlighting * Execution time and resource usage * Database and user information *** #### **Query Analysis** The AI doesn't just show you what's slow—it explains why and how to fix it: **Root Cause Identification:** * Missing indexes * Inefficient joins or subqueries * Full table scans * Locking or blocking issues * Resource constraints (CPU, memory, I/O) **Remediation Plans:** Each analysis includes a step-by-step remediation plan with: * **Specific actions** to implement * **Ready-to-run SQL** for creating indexes or modifying queries * **Estimated performance impact** (e.g., "Expected 80% reduction in execution time") * **Implementation guidance** with considerations and best practices **Example Remediation Plan:** ``` Problem: Full table scan on `orders` table (5M rows) Root Cause: Missing index on `created_at` column Recommended Action: CREATE INDEX idx_orders_created_at ON orders(created_at); Expected Impact: - Reduce query time from 45s to ~2s (95% improvement) - Reduce CPU usage during query execution Considerations: - Index creation will lock the table briefly - Recommended to run during low-traffic period ``` *** #### **Recommendations** Prioritized action items beyond the immediate query fix: * Related optimizations for similar queries * Schema design improvements * Configuration adjustments * Monitoring suggestions *** #### **Follow-up Questions** Suggested next steps to continue your investigation. Click any suggestion to drill deeper into the issue. **Examples:** * "Show me all queries using the orders table" * "What other indexes might help this workload?" * "Are there any blocking queries right now?" *** ### Results & Visualizations Panel AI DBA Assistant Chat For queries that return data, a side panel displays interactive results: #### **Data Tables** Searchable result sets showing: * Query details and full SQL text * Execution metrics (duration, CPU, rows examined) * User information and connection details * Query state and status **Actions:** Export data to CSV or copy directly from the view *** #### **Charts & Graphs** Visual representations of fleet-wide metrics: * Lock counts by database instance * Average wait times across the fleet * Distribution breakdowns (lock types, query types, etc.) * Time-series trends (CPU, connections, query duration) *** #### **Quick Stats** At-a-glance metrics: * Total fleet locks * Instance counts * Aggregate values (total queries, average duration, etc.) *** ### Action Buttons Each analysis includes action buttons to move from insight to resolution: **Result Table** View the raw data behind the analysis in a sortable, filterable table format. **Copy Generated Query** Get the SQL that Assistant used internally, ready to paste into your own tools or save for future reference. **Execute Query** Opens Rapydo's **Execute Query** tool with the generated SQL pre-loaded. From there you can: * Review and modify the query * Run it against RapydoDB for further investigation * Save it as a template for future use * Share it with your team *** ## Best Practices ### Be Specific About Timeframes "Last 24 hours" or "since yesterday" helps Assistant focus on relevant data. **Examples:** * ❌ "Show me slow queries" (too vague) * ✅ "Show me queries running longer than 10 seconds in the last 6 hours" *** ### Name Your Instances If you're investigating a specific server, include its identifier in your question. **Examples:** * ❌ "Why is the database slow?" * ✅ "Why is production-db-01 experiencing high CPU?" *** ### Include Full Context Since each question is independent, provide all necessary details in a single message rather than relying on follow-ups. **Examples:** * ❌ "What about yesterday?" (no context from previous question) * ✅ "Show me CPU usage on production-db-01 yesterday between 2pm and 4pm" *** ### Ask for Analysis When Needed If you want recommendations and remediation plans, explicitly ask for analysis. **Examples:** * "Analyze this query and suggest optimizations: SELECT \* FROM..." * "What's causing slow performance on database X and how can I fix it?" *** ## How It Works Assistant uses two data sources depending on the type of question asked: * **RapydoDB** — Rapydo's dedicated monitoring database, queried for performance metrics, process history, slow queries, CPU/memory trends, locks, and fleet-wide activity data. * **Your database instances** — Queried directly for schema information and data retrieval intents (e.g., listing tables, retrieving schema details). ### **Fast Responses** Performance and monitoring data is already aggregated in RapydoDB, so those queries return quickly regardless of your fleet size. ### **Historical Analysis** All monitoring data is retained in RapydoDB, enabling you to investigate past incidents and identify long-term trends. **💡 Important:** The Assistant cannot modify your production databases or execute DML/DDL commands. It is read-only in all cases. *** ## Supported Databases Assistant analyzes monitoring data from all database engines supported by Rapydo: * **MySQL** (all versions) * **PostgreSQL** (all versions) The AI automatically adjusts its analysis and recommendations based on the database engine, ensuring relevant and accurate guidance for your specific environment. *** ## Common Use Cases ### **Performance Investigation** "Show me queries running longer than 30 seconds on production-db-01 in the last hour" → Get full analysis with root causes and remediation plans *** ### **Fleet-Wide Monitoring** "Which instances have the highest CPU usage right now?" → View visual charts ranking your instances by resource usage *** ### **Lock and Blocking Analysis** "Are there any blocking queries affecting the orders table?" → Identify lock chains and get recommendations to resolve contention *** ### **Query Optimization** "Analyze this query: SELECT \* FROM users WHERE email = '[test@example.com](mailto:test@example.com)'" → Receive index recommendations and query rewrite suggestions *** ### **Capacity Planning** "Show me connection count trends across all instances for the last 7 days" → Visual time-series data to identify growth patterns *** ## Tips for Better Results ✅ **Use specific table or database names** when investigating schema-specific issues ✅ **Include query text** when asking for optimization help ✅ **Specify instance names** when troubleshooting specific servers ✅ **Combine multiple conditions** for precise results: "Queries longer than 10s on production AND using the payments table" *** ## What's Next? * [Query Analysis](/ai-dba/query-analysis) - Learn about Rapydo's comprehensive query analysis and remediation features * [Execute Query](/execute-query/introduction_to_execute_query) - Explore the SQL editor for running custom queries against RapydoDB * [Queries](/queries/introduction_to_queries) - Understand real-time query monitoring # Query Analysis Source: https://docs.rapydo.io/ai-dba/query-analysis AI-powered query analysis with actionable remediation plans ## Overview Query Analysis Query Analysis provides comprehensive, AI-powered investigation of query performance. It connects directly to your database instances to generate execution plans and collect metadata, then uses multiple AI analysts to examine the evidence and deliver: * **Root cause identification** — Pinpoint exactly why queries are slow * **Remediation plans** — Step-by-step implementation guidance with ready-to-run SQL * **Impact estimates** — Understand expected performance improvements before making changes * **Supporting evidence** — Complete technical details backing every recommendation *** ## Getting Started New Analysis Access Query Analysis from the left navigation panel under **AI DBA → Query Analysis**. ### Analysis Settings Configure your analysis with these required fields: **Analysis Mode** — Select the type of execution plan analysis: * **Explain Only (Basic)** — Generates an estimated execution plan based on table statistics and metadata. Fast and safe for production use since the query is not actually executed. * **Explain Analyze (Advanced)** — Actually executes the query and measures real performance metrics. Provides accurate row counts and timing data instead of estimates. Use with caution on production systems as the query will run. **Server** — Select the database instance to analyze from your monitored fleet. **Database** — Choose the specific database on that server. **Schema** — Optionally select a schema to provide additional context. Available for **PostgreSQL only**; this field is disabled for MySQL instances. **SQL Query** — Enter or paste the query you want to analyze. The editor provides syntax highlighting for readability. Click **Analysis** to begin. *** ## Ways to Access Query Analysis ### Direct Entry Navigate to Query Analysis and manually enter your server, database, and SQL query. *** ### From Query Workspace When viewing queries in any query workspace (like Process count history or other query lists), hover over any query row to reveal the **Analyze Query** button. Clicking it opens Query Analysis with all required fields automatically populated—server, database, and query are pre-filled and ready for one-click analysis. *** ### From Dashboard Dashboard widgets like **Longest Query** and **Query Avg Time** display your slowest queries. Each query row includes an **Analyze Query** button that opens Query Analysis with all fields pre-filled for immediate analysis. *** ## Analysis Results After clicking Analysis, the system connects to your database, generates an execution plan, collects metadata, and runs AI analysis. Results include: ### Analysis Configuration A summary header showing: * Server name * Analysis mode (Explain Only or Explain Analyze) * Database name * Timestamp * The SQL query being analyzed (with syntax highlighting) *** ### Analysis Summary Analysis Summary The AI provides a confidence-rated summary of findings. Each issue is clearly explained with context on why it matters and how it impacts performance. **Example findings:** **Inefficient per-row function call** * Using `pg_sleep(4)` creates deliberate 4-second delay per row * Function dominates performance regardless of other optimizations * Affects every row processed during query execution **Missing indexes for query optimization** * No indexes on join columns `employeeNumber` and `customerNumber` * Forces expensive nested loops processing billions of combinations * Sequential scans required for all table access operations **Under-optimized join order algorithm** * Processes large tables before applying selective payment filter * Creates massive intermediate results exploding to billions of rows * Should start with filtered payments then smallest tables **Outdated statistical information** * Employees and payments tables never analyzed despite autovacuum enabled * Optimizer uses default assumptions creating unrealistic row estimates * Inconsistent statistics across tables affects join decisions *** ### Remediation Plan Remediation Plan For each identified issue, Query Analysis provides a complete remediation plan with: #### **Specific Actions** Clear, prioritized steps to resolve the issue. #### **Ready-to-Run SQL** Pre-written SQL statements you can execute immediately: ```sql theme={null} -- Create missing index on employeeNumber CREATE INDEX idx_employees_employee_number ON employees(employeeNumber); -- Update table statistics ANALYZE employees; ANALYZE payments; ``` #### **Estimated Impact** Expected performance improvements after implementing the fix: **Example:** * **Current performance:** Query takes 88 seconds * **After index creation:** Expected execution time \~2 seconds (98% improvement) * **After statistics update:** Optimizer will choose more efficient join order #### **Implementation Guidance** Important considerations for safely applying the fix: **Example:** * Index creation will lock the table briefly (\~10 seconds for 56K rows) * Recommended to run during low-traffic periods * Consider using `CREATE INDEX CONCURRENTLY` on PostgreSQL for zero-downtime creation * Verify index usage with `EXPLAIN` after creation #### **Priority & Order** When multiple issues exist, the plan indicates the order to address them: **Example:** 1. **High Priority:** Create index on `employeeNumber` (biggest impact) 2. **Medium Priority:** Update table statistics (improves optimizer decisions) 3. **Low Priority:** Consider query rewrite (minor optimization) *** ### Supporting Evidence Overview Detailed technical evidence backing each finding. This section shows exactly what the AI observed in the execution plan and metadata: **Function Analysis:** * "Query includes `pg_sleep(4)` function call in SELECT clause, introducing deliberate 4-second delay per row processed" **Join Analysis with Calculations:** * "Join condition `e.employeeNumber = c.salesRepEmployeeNumber` forces nested loop with 56,078 × 249,856 row comparisons, creating 3.08 million intermediate results" **Cost Analysis:** * "Query plan shows astronomical cost of 2.6 billion units due to nested loop joins processing 6.17 billion estimated row combinations" **Statistics Status:** * "Tables employees and payments show 'Statistics were never analyzed' despite having autovacuum analyze enabled" **Join Order Recommendations:** * "Optimal approach should start with most selective filtered dataset—payments with 1,503 rows after WHERE amount \< 1000" *** ### Metadata Panel Analysis Metadata Click **Metadata** to view the raw technical details: **Execution Plan** The complete JSON execution plan from the database, showing: * Node types (Sequential Scan, Index Scan, Nested Loop, etc.) * Cost estimates (startup cost, total cost) * Row estimates vs. actual rows (when using Explain Analyze) * Filter conditions and join predicates *** **Query Metadata** Additional context including: **Table Statistics:** * Row counts * Last analyze timestamp * Last vacuum timestamp * Statistics age **Indexes:** * Existing indexes on affected tables * Index definitions (columns, type, conditions) * Or confirmation that no indexes exist **Table Schema:** * Column definitions with data types * Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL) * Default values **Partitions:** * Partition status for each table * Partition strategy (RANGE, LIST, HASH) * Partition boundaries **Column Cardinality:** * Distinct value counts (n\_distinct) * Null percentages * Average column width * Correlation coefficient * Histogram availability *** ## Key Features ### Confidence Ratings Each analysis includes a confidence indicator (e.g., "High confidence") showing how certain the AI is about its findings based on the available evidence. **Confidence levels:** * **High:** Strong evidence from execution plan and metadata * **Medium:** Reasonable inference based on available data * **Low:** Suggestion that requires verification *** ### Multiple Analysis Perspectives The AI examines queries from several angles simultaneously: * **Execution efficiency** — Scan types, join algorithms, row estimates * **Index utilization** — Missing indexes, unused indexes, index suggestions * **Statistics freshness** — Outdated statistics, missing statistics * **Schema design** — Join patterns, data types, constraints *** ### Actionable Output Findings are presented as specific, actionable items rather than generic advice: ❌ **Generic:** "Consider adding an index" ✅ **Specific:** "Create index on `employees(employeeNumber)` — Expected 98% performance improvement" ❌ **Generic:** "Query is slow" ✅ **Specific:** "Nested loop processing 3.08M row combinations due to missing index on join column" *** ### Results Panel Controls Each analysis result in the panel has the following controls: * **Delete** (trash icon) — Removes that specific analysis from the results panel * **Reanalyze** — Opens the **Reanalyze settings** dialog, pre-filled with the original query and settings, to run a fresh analysis * **Metadata** — Opens the raw metadata viewer (execution plan, table statistics, indexes, schema) * **Clear history** (top of results panel) — Removes all analyses from the panel at once *** ### Reanalyze Reanalyze After making changes to your database (like adding an index or updating statistics), click **Reanalyze** to open the **Reanalyze settings** dialog and run a fresh analysis to verify the improvement. **What Reanalyze checks:** * ✅ New execution plan with improved costs * ✅ Index usage confirmation * ✅ Actual vs. estimated performance * ✅ Remaining optimization opportunities *** ## Example Workflow **Scenario:** Dashboard shows an UPDATE query taking 88 seconds **Step 1:** Dashboard → Hover over the slow query → Click **Analyze Query** **Step 2:** Query Analysis opens with everything pre-filled (server, database, SQL) **Step 3:** Click **Analysis** **Step 4:** AI identifies: * Missing index on `employeeNumber` column * Outdated table statistics * Sequential scan of 56,019 rows **Step 5:** Review Remediation Plan: ```sql theme={null} CREATE INDEX idx_employees_employee_number ON employees(employeeNumber); ANALYZE employees; ``` **Step 6:** Review execution plan showing sequential scan and high cost **Step 7:** Execute the recommended SQL in your database **Step 8:** Click **Reanalyze** to confirm improvement **Result:** Query now takes \~2 seconds (98% improvement) and uses the new index *** ## Best Practices ### Start with Problematic Queries Focus on queries identified by: * AI DBA Assistant ("What's causing high CPU?") * Dashboard insights (Longest Query widget) * Query monitoring (Realtime view, Process History) Rather than analyzing every query, prioritize those causing actual performance issues. *** ### Use the Right Mode for Your Environment **Production databases:** * ✅ Use **Explain Only (Basic)** — Safe, no query execution * ❌ Avoid **Explain Analyze (Advanced)** — Actually runs the query **Test/staging environments:** * ✅ Use **Explain Analyze (Advanced)** — Provides actual metrics * Get real row counts instead of estimates * Measure actual execution time *** ### Review the Metadata The execution plan and table statistics often reveal issues the summary might not emphasize: * Check row estimate accuracy (estimates vs. actual) * Review filter selectivity * Examine join algorithms chosen * Verify statistics last update timestamps *** ### Implement in Order When multiple issues exist, follow the priority order in the Remediation Plan: 1. **High priority** fixes first (biggest impact) 2. **Medium priority** after verifying high priority worked 3. **Low priority** if needed *** ### Reanalyze After Changes Always verify that your optimizations had the intended effect: * ✅ Execution plan changed as expected * ✅ Cost decreased significantly * ✅ Index is being used * ✅ No new issues introduced *** ### Consider the Environment Before implementing recommendations: * **Index creation:** May lock tables briefly, plan for low-traffic periods * **Statistics update:** Safe anytime but consider impact on running queries * **Query rewrites:** Test thoroughly in staging before production * **Schema changes:** Coordinate with application deployments *** ## Common Issues Detected ### Missing Indexes **Symptom:** Sequential scans on large tables, high query cost **Remediation:** Create indexes on frequently filtered or joined columns **Expected Impact:** 80-99% reduction in query time for index-eligible queries *** ### Outdated Statistics **Symptom:** Optimizer choosing wrong join order, inaccurate row estimates **Remediation:** Run `ANALYZE` on affected tables **Expected Impact:** Better query plans, 30-70% performance improvement *** ### Inefficient Join Order **Symptom:** Processing large tables before applying filters **Remediation:** Rewrite query or update statistics to help optimizer **Expected Impact:** Fewer intermediate rows, 40-80% improvement *** ### Full Table Scans **Symptom:** Reading entire table when only few rows needed **Remediation:** Add WHERE clause filters, create appropriate indexes **Expected Impact:** Read only necessary rows, 70-95% I/O reduction *** ### N+1 Query Pattern **Symptom:** Many small queries instead of one join **Remediation:** Rewrite as single JOIN query **Expected Impact:** Eliminate round trips, 90%+ improvement *** ## Integration with Other Features ### From AI DBA Assistant Assistant-identified slow queries link directly to Query Analysis for deep investigation. ### To Execute Query From Query Analysis, click action buttons to open the Execute Query tool with remediation SQL pre-loaded. ### From Dashboard Dashboard widgets (Longest Query, Query Avg Time) provide direct access to Query Analysis. ### To Automation Create Scout Rules based on Query Analysis findings to automatically detect similar issues in the future. *** ## What's Next? * [AI DBA Assistant](/ai-dba/assistant) - Ask questions about your database fleet in natural language * [Execute Query](/execute-query/introduction_to_execute_query) - Run SQL queries and remediation commands * [Queries](/queries/introduction_to_queries) - Monitor real-time query activity # API Reference Source: https://docs.rapydo.io/api-reference/introduction ### Coming Soon # Alert Rules - Metrics and Notifications Source: https://docs.rapydo.io/automation/alerts_triggers_and_actions Complete reference for Alert Rules configuration ## Overview Alert Rules Alert Rules monitor database and system metrics, sending notifications when performance thresholds are exceeded. This guide covers all available metrics, operators, notifications, and configuration parameters. *** ## Core Concepts Create Alert Rule **Metrics** — Conditions defined by you that activate the rule * Example: CPU Utilization exceeds 90% * Example: Free Memory drops below 2048 MB **Notifications** — How you are informed when metric conditions are met * Example: Send email notification * Example: Send webhook to a channel **Select Database(s)** — Choose one or more database instances to apply the rule to * Select All: Apply to all monitored instances * Specific Instances: Target individual databases *** ## Available Metric Types ### Resource Utilization **CPU Utilization** * Measures processor usage across database instances * **Unit:** Percentage (%) * **Use when:** Monitoring compute resource consumption * **Example:** Alert when CPU > 80% *** **Free Memory** * Available RAM on the database instance * **Unit:** Megabytes (MB) * **Use when:** Preventing out-of-memory errors * **Example:** Alert when Free Memory `<` 2048 MB *** **Read IOPS** * Input/output operations per second (reads) * **Unit:** Operations per second * **Use when:** Monitoring disk read performance * **Example:** Alert when Read IOPS > 5000 *** **Write IOPS** * Input/output operations per second (writes) * **Unit:** Operations per second * **Use when:** Monitoring disk write performance * **Example:** Alert when Write IOPS > 3000 *** ### Query Performance **Max Query Duration** * Duration of the longest-running query * **Unit:** Seconds * **Use when:** Detecting slow queries impacting performance * **Example:** Alert when Max Query Duration > 60 seconds *** ### Connection & Activity **Connections Count** * Number of active database connections * **Unit:** Count (integer) * **Use when:** Monitoring connection usage * **Example:** Alert when Connections Count > 200 *** **Connection Utilization** * Percentage of maximum connections in use * **Unit:** Percentage (%) * **Use when:** Preventing connection pool exhaustion * **Example:** Alert when Connection Utilization > 90% *** **DB Count** * Number of database schemas on the instance * **Unit:** Count (integer) * **Use when:** Detecting unexpected database creation/deletion * **Example:** Alert when DB Count > 10 *** **Users Count** * Number of unique users connected to the database * **Unit:** Count (integer) * **Use when:** Monitoring user activity patterns * **Example:** Alert when Users Count > 50 *** **Hosts Count** * Number of unique client hosts with connections * **Unit:** Count (integer) * **Use when:** Detecting unusual connection sources * **Example:** Alert when Hosts Count > 100 *** **Waits Count** * Number of queries currently in wait state * **Unit:** Count (integer) * **Use when:** Detecting blocking or resource contention * **Example:** Alert when Waits Count > 20 *** ## Metric Operators Use operators to define threshold conditions for your metrics: **Greater than** * Symbol: `>` * Alert when metric exceeds the threshold * Example: `CPU Utilization > 80%` **Greater than or equal** * Symbol: `>=` * Alert when metric meets or exceeds the threshold * Example: `CPU Utilization >= 80%` **Less than** * Symbol: `<` * Alert when metric falls below the threshold * Example: `Free Memory < 2048 MB` **Less than or equal** * Symbol: `<=` * Alert when metric meets or falls below the threshold * Example: `Free Memory <= 2048 MB` *** ## Notification Methods ### Email * Send email notification to specified recipients * **Configuration:** * **Recipients:** List of email addresses (comma-separated) * **Subject:** Auto-generated based on trigger * **Content:** Metric values, threshold, timestamp, instance details * **Mail per instance:** Toggle to control how alerts are grouped * **Checked:** A separate email is sent for each database that triggers the alert * **Unchecked:** A single summary email covers all triggered databases **Example:** ``` Recipients: dba-team@company.com, ops-alerts@company.com Mail per instance: Enabled ``` *** ### Webhook * Send HTTP POST request to configured webhook URL * **Configuration:** * **Webhook Destinations:** Select from configured webhooks (Settings → Webhook) * **Payload:** JSON containing alert details **Common Integrations:** * Slack (via Incoming Webhooks) * Microsoft Teams (via Incoming Webhook connector) * PagerDuty (via Events API) * Custom alerting systems **Setup:** 1. Go to Settings → Webhook 2. Configure your webhook URL 3. Test the connection 4. Select the webhook when creating Alert Rules **Example Payload:** ```json theme={null} { "alert_type": "Alert Rule", "rule_name": "High CPU Alert", "metric": "CPU Utilization", "current_value": 92, "threshold": 80, "operator": "greater_than", "instance": "production-db-01", "timestamp": "2024-03-01T14:30:00Z" } ``` *** ## Configuration Parameters ### Samples to Trigger Number of consecutive checks that must exceed the threshold before alerting. **Purpose:** Prevents false alerts from temporary spikes **Example:** ``` Samples to Trigger: 5 Metric: CPU Utilization > 80% Check 1: 85% ✓ (1/5) Check 2: 88% ✓ (2/5) Check 3: 75% ✗ (reset to 0/5) Check 4: 90% ✓ (1/5) Check 5: 92% ✓ (2/5) ... ``` **Recommended values:** * **Volatile metrics (CPU, IOPS):** 4-5 samples * **Critical failures (Connection Utilization):** 1-2 samples * **Gradual changes (Memory):** 3-4 samples *** ### Notification Interval Minimum time (in minutes) between repeated alerts for the same condition. **Purpose:** Prevents alert flooding while keeping you informed **Example:** ``` Notification Interval: 3 minutes Condition: CPU still > 80% Minute 0: Alert sent 🚨 Minute 3: Alert sent 🚨 (3 min passed) Minute 6: Alert sent 🚨 (3 min passed) ``` **Recommended values:** * **Critical alerts:** 3-5 minutes * **Warning alerts:** 15-30 minutes * **Informational alerts:** 60+ minutes *** ## Multi-Metric Rules (AND Logic) Combine multiple metrics to create sophisticated monitoring conditions. All specified conditions must be true simultaneously for the rule to trigger. ### How AND Logic Works When you add multiple metrics to a single Alert Rule, the rule only triggers when **ALL conditions are met at the same time**. **Example - High CPU AND High Connections:** ``` Metric 1: CPU Utilization > 85% AND Metric 2: Connections Count > 200 ``` **Result:** Alert is sent only when: * ✅ CPU is above 85% **AND** * ✅ Connections exceed 200 **at the same time** If only one condition is true, no alert is sent. *** ### Why Use Multi-Metric Rules? **Reduce False Positives** Single metrics can trigger during normal operations. Combining metrics creates high-confidence alerts. **Example:** * High CPU alone might be normal during batch jobs * High Connections alone might be normal during peak hours * High CPU + High Connections together suggests a real performance issue *** **Identify Specific Scenarios** Multi-metric rules let you target exact situations that matter. **Examples:** **Performance Degradation:** ``` Metric 1: Max Query Duration > 30 seconds AND Metric 2: Waits Count > 50 ``` → Alerts when slow queries correlate with blocking/contention *** **Resource Saturation:** ``` Metric 1: CPU Utilization > 85% AND Metric 2: Connection Utilization > 80% ``` → Alerts when both compute and connection resources are strained *** **Memory Pressure with High Activity:** ``` Metric 1: Free Memory < 2048 MB AND Metric 2: Connections Count > 150 ``` → Alerts when low memory occurs during high connection load *** ### Best Practices for Multi-Metric Rules ✅ **Combine 2-3 metrics** for balance between specificity and maintainability ✅ **Use complementary metrics** that indicate the same underlying issue from different angles ✅ **Test each metric individually first** to understand their behavior before combining ✅ **Avoid too many metrics** - 4+ conditions can make rules too specific and miss real issues ❌ **Don't combine unrelated metrics** - CPU + DB Count makes little sense *** ## Configuration Examples ### Example 1: High CPU Alert (Single Metric) **Scenario:** Alert when CPU remains elevated **Configuration:** ``` Type: Alert Rule Status: Active DB Instances: Select All Metric 1: Type: CPU Utilization Operator: Greater than Value: 80% Samples to Trigger: 5 Notification: Webhook Webhook Destination: #database-ops Notification Interval: 3 minutes ``` **Result:** Alerts via webhook after 5 consecutive checks of CPU > 80%, repeats every 3 minutes while condition persists *** ### Example 2: Low Memory Warning (Single Metric) **Configuration:** ``` Type: Alert Rule Status: Active DB Instances: production-db-01, production-db-02 Metric 1: Type: Free Memory Operator: Less than Value: 2048 MB Samples to Trigger: 3 Notification: Email Email Recipients: dba-team@company.com Notification Interval: 5 minutes ``` *** ### Example 3: Connection Pool Saturation (Single Metric) **Configuration:** ``` Type: Alert Rule Status: Active Metric 1: Type: Connection Utilization Operator: Greater than Value: 90% Samples to Trigger: 2 Notification: Webhook Webhook Destination: #database-ops Notification Interval: 10 minutes ``` *** ### Example 4: Performance Degradation (Multi-Metric) **Configuration:** ``` Type: Alert Rule Status: Active Metric 1: Type: Max Query Duration Operator: Greater than Value: 30 seconds AND Metric 2: Type: Waits Count Operator: Greater than Value: 50 Samples to Trigger: 3 Notification: Email Email Recipients: dba-team@company.com Notification Interval: 5 minutes ``` **Result:** Alerts when slow queries correlate with high wait states for 3 consecutive checks, suggesting blocking or resource contention *** ### Example 5: Resource Saturation (Multi-Metric) **Configuration:** ``` Type: Alert Rule Status: Active Metric 1: Type: CPU Utilization Operator: Greater than Value: 85% AND Metric 2: Type: Connection Utilization Operator: Greater than Value: 80% Samples to Trigger: 4 Notification: Webhook Webhook Destination: #critical-alerts Notification Interval: 3 minutes ``` **Result:** Only alerts when both CPU and connection pool are under pressure simultaneously *** ## Best Practices ✅ **Use appropriate Samples to Trigger values** to avoid false positives from transient spikes ✅ **Set Notification Intervals** that balance awareness with alert fatigue ✅ **Combine metrics with AND logic** to create high-confidence alerts and reduce false positives ✅ **Test rules in non-production** before deploying to critical instances ✅ **Use webhook for team channels** (Slack, Teams) and **email for individual recipients** — each rule supports one notification method ✅ **Review and adjust thresholds** based on actual workload patterns ✅ **Start with single-metric rules** and add additional metrics as you understand your environment *** ## Troubleshooting ### Alert Not Triggering **Check:** * Samples to Trigger count is reasonable for your metric check frequency * Thresholds are appropriate for actual metric values * For multi-metric rules, verify **ALL** conditions are being met simultaneously * Rule status is Active, not Disabled * Selected databases are correct *** ### Too Many Alerts **Solutions:** * Increase Samples to Trigger to require sustained issues * Increase thresholds to appropriate levels * Extend Notification Interval to reduce frequency * **Add additional metrics with AND logic** to make the rule more specific *** ### Alerts Stop After First Notification **Check:** * Notification Interval is set (not too high) * Rule is still Active * Condition is still met (metric hasn't dropped below threshold) * For multi-metric rules, all conditions are still true *** ### Multi-Metric Rule Never Triggers **Check:** * Monitor each metric individually to see if they align timing-wise * Conditions might not occur simultaneously - consider if they should be separate rules * Thresholds might be too strict when combined *** ## What's Next? * [Defining Rules](/automation/defining_rules) - Step-by-step guide to creating rules * [Scout Rules Reference](/automation/scout_triggers_and_actions) - Complete Scout Rules documentation * [Automation Overview](/automation/introduction) - Return to automation overview # Defining Rules Source: https://docs.rapydo.io/automation/defining_rules Step-by-step guide to creating automation rules in Rapydo ## Creating Automation Rules Rapydo automation rules enable proactive database management by automatically responding to specific conditions. This guide walks you through the process of creating effective rules. *** ## Step 1: Identify What to Monitor Before creating a rule, understand your database's normal behavior and identify what needs attention. **Key questions to ask:** * What query duration is acceptable for your workload? * At what CPU/memory threshold does performance degrade? * How close do you get to connection limits during peak hours? * Are there specific users or databases requiring special monitoring? **Common scenarios:** * "Kill any query running longer than 5 minutes" * "Alert when CPU exceeds 80% for more than 3 minutes" * "Notify when connections reach 90% of the limit" * "Terminate reporting queries exceeding 10 minutes" *** ## Step 2: Choose Your Rule Type ### Scout Rules **Use for:** Query monitoring and automatic action. * Monitor long-running queries in real-time * Automatically kill queries exceeding duration thresholds * Filter by user, database, or query pattern * Get alerts when specific queries are detected **Example:** Kill queries from `analytics_user` running longer than 600 seconds *** ### Alert Rules **Use for:** Metric monitoring and notifications * Monitor CPU, memory, connections, IOPS * Send alerts when thresholds are exceeded * Multi-metric support (combine multiple conditions) * Email and webhook notifications **Example:** Alert when CPU > 80% for 5 consecutive checks *** ## Step 3: Define Triggers and Conditions Specify the exact conditions that activate your rule. ### For Scout Rules **Query Duration Trigger** ``` Condition: Query running longer than X seconds Example: Duration > 300 seconds ``` **Optional Filters:** * **Database**: Apply only to specific databases (e.g., `production_db`) * **User**: Target specific users (e.g., `reporting_user`) * **Query Pattern**: Match SQL text or patterns (e.g., `SELECT * FROM large_table`) * **IP Address**: Filter by client IP address **💡 Important:** Filters are optional but help target rules precisely and avoid impacting legitimate queries. *** ### For Alert Rules **Metrics** Available metrics for monitoring: **Resource Utilization:** * **CPU Utilization (%)**: Processor usage across instances * **Free Memory**: Available RAM (not percentage-based) * **Read IOPS**: Read input/output operations per second * **Write IOPS**: Write input/output operations per second * **Connection Utilization (%)**: Percentage of maximum connections in use **Query Performance:** * **Max Query Duration**: Duration of the longest-running query **Database Activity:** * **Connections count**: Number of active connections * **DB count**: Number of databases * **Users count**: Number of connected users * **Hosts count**: Number of client hosts with connections * **Waits count**: Number of queries in wait state **Operators:** * Greater than (`>`) * Greater than or equal (`>=`) * Less than (`<`) * Less than or equal (`<=`) *** **Example single metric:** ``` Metric: CPU Utilization Operator: Greater than Value: 80% ``` *** **Multi-Metric Rules (Advanced)** Combine multiple metrics with AND logic for sophisticated monitoring. All conditions must be true simultaneously for the rule to trigger. **Example - High CPU AND High Connections:** ``` Metric 1: CPU Utilization > 80% AND Metric 2: Connections count > 100 ``` → Alert only when BOTH conditions are true simultaneously **Example - Query Performance Issues:** ``` Metric 1: Max Query Duration > 60 seconds AND Metric 2: Waits count > 20 ``` → Alerts when slow queries correlate with high wait states **💡 Important:** Multi-metric rules let you create precise conditions that reduce false alerts. For example, alerting on high CPU is more meaningful when combined with high connections. *** ## Step 4: Specify Actions Define what happens when trigger conditions are met. ### Scout Rule Actions When a Scout Rule is triggered, you can execute one of these actions: *** **Kill query** * Terminates the specific query that triggered the rule * **Use when:** Query exceeds acceptable duration or consumes excessive resources * **Example:** Kill any query running longer than 300 seconds *** **Kill connection** * Terminates the entire database connection (closes all queries from that connection) * **Use when:** A connection is causing persistent issues or needs to be forcibly closed * **Example:** Kill connections from problematic clients or applications *** **Kill idle connections** * Terminates idle connections that aren't actively running queries * **Use when:** Too many idle connections are consuming resources * **Example:** Close connections that have been idle for more than 1 hour *** **Rate limit** * Automatically kills connections when they exceed a defined threshold to enforce the limit * **How it works:** If Rapydo detects multiple simultaneous connections matching the trigger, it kills enough connections to reach the defined limit * **Use when:** Need to limit concurrent connections from specific users or databases * **Example:** Limit `reporting_user` to maximum 5 concurrent connections—if 10 connections are detected, kill 5 to reach the limit **💡 Important:** Rate limit controls the NUMBER of simultaneous connections, not queries per second. *** **RCA (Query Analysis)** * Triggers automatic AI-powered query analysis for queries matching the trigger * **Results are sent via email** with complete analysis and remediation plan * **What you get:** * Root cause identification (missing indexes, inefficient joins, etc.) * Step-by-step remediation plan with SQL statements * Estimated performance impact * Table statistics and execution plan details * **Use when:** You want to understand WHY queries are slow and get optimization recommendations * **Common trigger:** Analyze any query running longer than a defined threshold (e.g., 60 seconds) **💡 Important:** RCA goes beyond just identifying the problem—it provides complete solutions with implementation guidance. **⚠️ Required:** A notification destination (email or webhook) must be configured when using RCA. The analysis report cannot be delivered without a valid notification target. *** **No action (Notification Only)** * Select **No action** as the action type, then enable notifications with your email or webhook * Sends alert without taking any database action — queries continue running unaffected * The event is logged in Rapydo for audit purposes * **Use when:** You want visibility without automatic intervention * **Example:** Monitor query patterns to build baselines before taking action *** ## Step 5: Configure Details ### Basic Settings **Type** * Alert rule (metric monitoring) * Scout rule (query monitoring) **Status** * **Active**: Rule is monitoring and will execute actions * **Disabled**: Rule is saved but inactive (useful for testing) **DB Instances** * **Select All**: Apply rule to all monitored instances * **Specific Instances**: Choose individual databases * **💡 Tip:** Start with specific instances, then expand to "Select All" after testing *** ### Alert Rule Parameters **Samples to Trigger** Number of consecutive checks that must exceed the threshold before alerting. ``` Example: Samples: 5 Metric: CPU > 80% Check 1: 85% ✓ (1/5) Check 2: 88% ✓ (2/5) Check 3: 82% ✓ (3/5) Check 4: 90% ✓ (4/5) Check 5: 87% ✓ (5/5) → ALERT SENT 🚨 ``` **Why this matters:** Prevents false alerts from temporary spikes. Only alerts on sustained issues. **Recommended values:** * Volatile metrics (CPU, IOPS): 4-5 samples * Critical failures (connections, deadlocks): 1-2 samples *** **Notification Interval (minutes)** Minimum time between repeated alerts for the same condition. ``` Example: Notification Interval: 3 minutes Condition: CPU still > 80% Minute 0: Alert sent 🚨 Minute 3: Alert sent 🚨 (3 min passed) Minute 6: Alert sent 🚨 (3 min passed) ``` **Why this matters:** Prevents alert flooding while keeping you informed of ongoing issues. *** ## Complete Example: Scout Rule **Scenario:** Analytics queries sometimes run for hours, impacting production. **Goal:** Terminate analytics queries exceeding 10 minutes. **Configuration:** ``` Type: Scout rule Status: Active DB instances: production_db Trigger: Query Duration > 600 seconds Filters: User: analytics_user Database: production_db Action: Kill Query + Send Webhook Webhook Destination: #database-alerts ``` **What happens:** 1. Rapydo monitors all queries from `analytics_user` on `production_db` 2. If a query runs longer than 10 minutes, it's automatically killed 3. Webhook notification sent to Slack #database-alerts with query details 4. Team is informed and can investigate root cause *** ## Complete Example: Alert Rule **Scenario:** Production database occasionally experiences CPU spikes degrading performance. **Goal:** Get alerted when CPU remains high for sustained periods. **Configuration:** ``` Type: Alert rule Status: Active DB instances: Select All (production instances) Metric 1: Type: CPU Utilization Operator: Greater than Value: 80% Samples to Trigger: 5 Notification: Webhook Webhook Destination: #database-ops Notification Interval: 3 minutes ``` **What happens:** 1. Rapydo checks CPU every few minutes on all production instances 2. If CPU > 80% for 5 consecutive checks (\~15 minutes), webhook alert sent 3. While CPU remains high, alerts repeat every 3 minutes 4. If CPU drops below 80%, counter resets and alerts stop *** ## Best Practices ✅ **Start conservative**: Higher thresholds, longer durations. Tighten after observing behavior. ✅ **Use descriptive names**: "Kill Analytics Queries >10min" not "Rule 1" ✅ **Leverage filters**: Target rules precisely to avoid impacting legitimate activity. ✅ **Test thoroughly**: Always validate in non-production before deploying. ✅ **Review regularly**: Audit rules monthly to ensure they're still relevant. ✅ **Avoid alert fatigue**: Don't create so many alerts that teams start ignoring them. *** ## Multi-Metric Examples **Example 1: High CPU + High Connections** ``` Metric 1: CPU Utilization > 85% AND Metric 2: Connection Count > 200 Notification: Webhook to #critical-alerts ``` → Only alerts when database is both CPU-bound AND connection-saturated *** **Example 2: Query Performance Degradation** ``` Metric 1: Max Query Duration > 30 seconds AND Metric 2: Waits count > 50 Notification: Webhook to #database-ops ``` → Alerts when slow queries correlate with high lock contention *** ## What's Next? * [Scout Rules Reference](/automation/scout_triggers_and_actions) - Complete guide to Scout Rules * [Alert Rules Reference](/automation/alerts_triggers_and_actions) - Complete guide to Alert Rules * [Back to Automation Overview](/automation/introduction) - Return to main automation page # Automation Source: https://docs.rapydo.io/automation/introduction Automate database monitoring and performance management with intelligent rules ## Introduction In the current era of data-centric operations, ensuring peak database performance and robust security is essential for delivering uninterrupted user experiences and avoiding expensive system failures. The exponential growth of applications and their associated data volumes presents significant challenges for developers and database administrators in efficiently managing database operations. Rapydo's automation capabilities provide a powerful solution to these challenges through intelligent, user-defined rules that proactively monitor and manage your database infrastructure. These flexible rules enable comprehensive management across various aspects of database operations, including performance optimization, security reinforcement, and resource management. By triggering precise actions in response to specific conditions, automation rules help you maintain Service Level Agreement (SLA) commitments, strengthen security protocols, and streamline overall database administration—all without manual intervention. *** ## How Automation Works in Rapydo Automation Rules Rapydo's automation system operates on a simple principle: **define conditions, set thresholds, and specify actions**. When a condition is met (for example, a query running longer than 60 seconds), Rapydo automatically executes the action you've defined (such as killing the query or sending an alert). This proactive approach enables you to: * **Prevent Issues Before They Escalate**: Automatically respond to performance degradation * **Maintain Consistent Performance**: Enforce query duration limits and resource constraints * **Stay Informed**: Receive alerts via email or webhook when thresholds are breached * **Reduce Manual Overhead**: Let automation handle routine monitoring and responses *** ## Automation Modules Rapydo provides two types of automation rules, each designed for specific monitoring and management scenarios: ### Scout Rules Scout Rules monitor long-running queries and database activity in real-time. These rules are ideal for: * **Query Duration Monitoring**: Detect and respond to queries that exceed time thresholds * **Automatic Query Termination**: Kill queries that run too long and consume excessive resources * **Performance Protection**: Prevent runaway queries from impacting overall database performance * **Custom Alerting**: Get notified when specific query patterns or durations are detected **Common Use Cases:** * Kill any query running longer than 5 minutes * Alert when specific users run queries exceeding duration limits * Automatically terminate queries from specific databases that exceed thresholds * Monitor and respond to queries matching specific patterns (e.g., full table scans) **How Scout Rules Work:** Scout Rules continuously monitor active queries across all your database instances. When a query matches your defined criteria (duration, user, database, query pattern), the rule triggers the specified action—whether that's killing the query, sending an alert, or both. *** ### Alert Rules Alert Rules monitor database and system metrics, notifying you when performance thresholds are exceeded. These rules are ideal for: * **Resource Monitoring**: Track CPU, memory, connections, and IOPS usage * **Threshold-Based Alerting**: Get notified when metrics exceed defined limits * **Multi-Channel Notifications**: Receive alerts via email or webhook * **Proactive Issue Detection**: Identify resource constraints before they cause outages **Common Use Cases:** * Alert when CPU usage exceeds 80% for more than 5 minutes * Notify team via webhook when connection pool reaches 90% capacity * Send email alerts when IOPS usage spikes unexpectedly * Monitor memory usage and alert before out-of-memory errors occur **How Alert Rules Work:** Alert Rules continuously evaluate metrics across your database instances. When a metric crosses your defined threshold for a specified duration, Rapydo sends notifications to your configured channels (email, webhook, or both), enabling your team to respond quickly. *** ## Rule Components Every automation rule in Rapydo consists of three key components: ### 1. Triggers (Conditions) The conditions that must be met for the rule to activate. Examples: * Query duration exceeds 300 seconds * CPU usage is above 85% * Number of connections exceeds 100 * Specific query pattern is detected ### 2. Filters (Optional) Additional criteria to narrow down when the rule applies. Examples: * Only for specific databases * Only for specific users * Only for specific instance types * Only during certain time periods ### 3. Actions What happens when the trigger conditions are met. Examples: * Kill the query * Send email notification * Send webhook notification * Log the event *** ## Benefits of Automation ### **Proactive Management** Move from reactive firefighting to proactive prevention. Address issues automatically before they impact users. ### **24/7 Monitoring** Your rules work around the clock, monitoring and responding even when your team is offline. ### **Consistent Enforcement** Apply the same standards and responses across all database instances without manual intervention. ### **Reduced Downtime** Automatically terminate problematic queries and alert teams to resource constraints before they cause outages. ### **Improved SLA Compliance** Maintain performance commitments by enforcing query limits and responding to threshold breaches instantly. ### **Team Efficiency** Free your team from routine monitoring tasks and let them focus on strategic improvements. *** ## Getting Started with Automation Ready to set up your first automation rule? Here's the typical workflow: **Step 1: Choose Your Rule Type** * **Scout Rules** for query monitoring and termination * **Alert Rules** for metric-based notifications **Step 2: Define Your Conditions** * Set thresholds (duration, CPU %, connections, etc.) * Add filters (database, user, query pattern) * Specify evaluation frequency **Step 3: Configure Actions** * Choose what happens when conditions are met * Set up notification channels (email, webhook) * Test the rule with sample scenarios **Step 4: Monitor and Refine** * Review rule execution logs * Adjust thresholds based on real-world behavior * Add or modify rules as your needs evolve *** ## What's Next? * [Defining Rules](/automation/defining_rules) - Learn the step-by-step process for creating automation rules * [Scout Rules Reference](/automation/scout_triggers_and_actions) - Complete guide to Scout Rules triggers and actions * [Alert Rules Reference](/automation/alerts_triggers_and_actions) - Complete guide to Alert Rules triggers and actions *** ## Best Practices ✅ **Start Conservative**: Begin with higher thresholds and adjust down as you understand normal behavior ✅ **Test Before Deploying**: Create rules in test environments first to validate behavior ✅ **Use Multiple Notification Channels**: Configure both email and webhook for critical alerts ✅ **Document Your Rules**: Keep track of why each rule exists and what problem it solves ✅ **Review Regularly**: Periodically audit your rules to ensure they're still relevant and effective ✅ **Avoid Over-Automation**: Don't create so many rules that legitimate activity gets blocked or generates alert fatigue # Scout Rules - Triggers and Actions Source: https://docs.rapydo.io/automation/scout_triggers_and_actions Complete reference for Scout Rules configuration ## Overview Scout Rules List Scout Rules monitor long-running queries and database activity in real-time, automatically responding to performance issues. This guide covers all available triggers, actions, and configuration options for Scout Rules. *** ## Rule Setup Flow ### **1. DB Instances** (Required First Step) Select which database instances this rule will monitor. **Interface:** Dropdown with checkboxes * **Select All**: Choose all available instances at once * **Individual Selection**: Pick specific instances **Important:** Your selection here determines what databases and users appear in trigger dropdowns later. *** ## **2. Triggers** (Up to 3 Maximum) Configure conditions that will activate your rule. All triggers use **AND** logic—ALL must be true simultaneously for the rule to trigger. *** ### **Query Duration** Monitors how long queries have been running. **Operator Options:** * **Greater Than**: Trigger when runtime exceeds duration * **Less Than**: Trigger when runtime is below duration **Value:** Enter time in seconds **Example:** Greater Than 30 = Trigger for queries running longer than 30 seconds **Use Cases:** * Stop runaway queries that slow the database * Enforce query timeout policies * Protect database performance from inefficient queries * Prevent resource exhaustion from long-running reports *** ### **Connection Idle Time** Monitors connections in "sleep" state (connected but not executing queries). **Operator Options:** * **Greater Than**: Trigger when idle time exceeds value * **Less Than**: Trigger when idle time is below value **Value:** Enter time in seconds **Example:** Greater Than 600 = Trigger for connections idle over 10 minutes **Use Cases:** * Free up memory from abandoned connections * Prevent connection pool exhaustion * Clean up development/test connections left open * Reclaim resources from inactive sessions *** ### **Connection Utilization** Monitors database connection pool usage as percentage (%) of maximum connections. **Operator Options:** * **Greater Than**: Trigger when usage exceeds percentage * **Less Than**: Trigger when usage falls below percentage **Value:** Enter percentage (0-100) **Example:** Greater Than 80 = Trigger when 80% of connection pool is in use **Use Cases:** * Prevent "too many connections" errors * Proactive connection pool management * Emergency connection cleanup during high load * Kill idle connections when pool approaches capacity *** ### **Query Pattern** Matches running queries against patterns using SQL LIKE syntax with `%` wildcards. **Interface:** Manual text entry **Pattern Behavior:** * The system is **case-insensitive** and **automatically removes semicolons** before matching * Use **%** wildcard to represent any content: `select % from table_name` * Patterns must match query structure exactly (except case and semicolons) * **⚠️ Critical**: Pattern matching is **sensitive to spacing and line breaks**—extra spaces or different formatting will cause pattern mismatches **Operator Options:** * **Like**: Trigger when queries match the pattern * **Not Like**: Trigger when queries don't match the pattern **Value:** Enter SQL patterns using `%` wildcards *** #### **Simple Pattern Examples** **Security - Detect Destructive Operations:** ```sql theme={null} Pattern: %DROP TABLE% Result: Triggers the configured action (e.g., Kill Query) for any query attempting to drop tables ``` **Performance - Detect Queries Without LIMIT:** ```sql theme={null} Pattern (Not Like): %LIMIT% Result: Triggers the configured action (e.g., Kill Query) for SELECT queries missing pagination ``` **Compliance - Monitor Sensitive Data Access:** ```sql theme={null} Pattern: %SELECT * FROM salary% Result: Triggers the configured action (e.g., No action + notification) when the salary table is queried ``` *** #### **Complex Pattern Examples** **Example 1: Simple Query with Wildcards** **Your rule query pattern:** ```sql theme={null} SELECT e.lastName, e.firstName, p.amount, pg_sleep(%) FROM employees e JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber JOIN payments p ON c.customerNumber = p.customerNumber WHERE p.amount < 1000 LIMIT 1; ``` **✅ This WILL match (case variations):** ```sql theme={null} select e.lastname, e.firstname, p.amount, pg_sleep(10) from employees e join customers c on e.employeenumber = c.salesrepemployeenumber join payments p on c.customernumber = p.customernumber where p.amount < 1000 limit 1; ``` **❌ This will NOT match (extra spaces):** ```sql theme={null} SELECT e.lastName, e.firstName , p.amount, pg_sleep(10) FROM employees e JOIN customers c ON e.employeeNumber = c.salesRepEmployeeNumber JOIN payments p ON c.customerNumber = p.customerNumber WHERE p.amount < 1000 LIMIT 1; ``` **❌ This will NOT match (different line breaks):** ```sql theme={null} select e.lastname, e.firstname, p.amount, pg_sleep(10) from employees e join customers c on e.employeenumber = c.salesrepemployeenumber join payments p on c.customernumber = p.customernumber where p.amount < 1000 limit 1; ``` *** **Example 2: Multi-line Query with Multiple Wildcards** **Original Query:** ```sql theme={null} -- Customer order analysis query SELECT c.[customer_id], c.[first_name] + ' ' + c.[last_name] AS [full_name], cat.[category_name], p.[product_name], p.[price], oi.[quantity], (oi.[quantity] * p.[price]) AS [line_total], o.[order_date], r.[rating], r.[review_text], CASE WHEN r.[rating] >= 4 THEN 'Positive' WHEN r.[rating] = 3 THEN 'Neutral' ELSE 'Negative' END AS [review_sentiment] FROM [customers] c INNER JOIN [orders] o ON c.[customer_id] = o.[customer_id] INNER JOIN [order_items] oi ON o.[order_id] = oi.[order_id] INNER JOIN [products] p ON oi.[product_id] = p.[product_id] LEFT JOIN [categories] cat ON p.[category_id] = cat.[category_id] LEFT JOIN [reviews] r ON p.[product_id] = r.[product_id] AND c.[customer_id] = r.[customer_id] WHERE o.[order_date] >= '2023-01-01' AND p.[price] > 50.00 AND c.[country] IN ('USA', 'Canada', 'UK') ``` **Rule Pattern (flexible matching on dates, prices, countries):** ```sql theme={null} -- Customer order analysis query SELECT c.[customer_id], c.[first_name] + ' ' + c.[last_name] AS [full_name], cat.[category_name], p.[product_name], p.[price], oi.[quantity], (oi.[quantity] * p.[price]) AS [line_total], o.[order_date], r.[rating], r.[review_text], CASE WHEN r.[rating] >= 4 THEN 'Positive' WHEN r.[rating] = 3 THEN 'Neutral' ELSE 'Negative' END AS [review_sentiment] FROM [customers] c INNER JOIN [orders] o ON c.[customer_id] = o.[customer_id] INNER JOIN [order_items] oi ON o.[order_id] = oi.[order_id] INNER JOIN [products] p ON oi.[product_id] = p.[product_id] LEFT JOIN [categories] cat ON p.[category_id] = cat.[category_id] LEFT JOIN [reviews] r ON p.[product_id] = r.[product_id] AND c.[customer_id] = r.[customer_id] WHERE o.[order_date] >= % AND p.[price] > % AND c.[country] IN (%) ``` **Key Points:** * **Exact line break formatting** preserved between original and pattern * **Multiple wildcards** (%) used for flexible matching on dates, prices, and countries * **Comments preserved** to maintain exact structure * **All spacing and indentation** must match exactly **⚠️ Important:** If the original query has line breaks, the pattern must also have line breaks in the same places. A single-line pattern will NOT match a multi-line query, and vice versa. *** #### **Best Practices for Query Patterns** **Recommended Sources for Copy-Paste:** Copy query text directly from these sources to ensure exact spacing and formatting: * **Rapydo Query Workspace** (Process count history, Realtime queries, Query lists) * **Rapydo Dashboard** (Longest Query widget) * **Database Query Logs** (PostgreSQL pg\_stat\_statements, MySQL slow query log) * **Application Logs** (if they capture full SQL statements) * **Database Audit Trails** **Why This Matters:** This ensures the **exact spacing, line breaks, and formatting** that will actually be executed, preventing pattern matching failures due to formatting differences. *** **Pattern Matching Tips:** ✅ **Use wildcards liberally** for values that change: dates, IDs, amounts ✅ **Copy from actual query logs** rather than typing manually ✅ **Test your pattern** on a few real queries before deploying ✅ **Keep structural keywords exact** (SELECT, FROM, WHERE, JOIN) ❌ **Don't modify spacing** after copying—even small changes break matching ❌ **Don't reformat queries** for readability—match the actual format *** ### **DB Name** Triggers based on which database is being accessed. **Interface:** Dropdown (shows databases from selected instances only) * **Search Function**: Find specific databases quickly * **Select All**: Choose all databases in the dropdown **Operator Options:** * **In**: Trigger for activity on specified databases * **Not In**: Trigger for activity NOT on specified databases **Examples:** * **Production protection**: In "production\_db" - Apply strict rules to production database * **Development monitoring**: Not In "dev\_db, test\_db" - Monitor non-development access * **Schema-specific rules**: In "analytics\_db" - Different rules for analytics workload *** ### **User** Triggers based on which database user is running processes. **Interface:** Dropdown (shows users from selected instances only) * **Search Function**: Find specific users quickly * **Select All**: Choose all users in the dropdown * **Shared Indicators**: Shows users that exist across multiple instances **Operator Options:** * **In**: Trigger for processes from specified users * **Not In**: Trigger for processes NOT from specified users **Examples:** * **Admin monitoring**: In "admin, dba, root" - Enhanced monitoring for privileged users * **Service account limits**: In "app\_user, backup\_user" - Control service account query duration * **Developer restrictions**: Not In "prod\_app\_user" - Apply rules to everyone except production app *** ### **IP Address** Triggers based on source IP address of database connections. **Interface:** Manual text entry **Format:** * Single IP: `192.168.1.100` * Multiple IPs: Comma-separated, no spaces: `192.168.1.100,10.0.0.5` * CIDR notation: `10.0.0.0/8` or `203.0.113.0/24` **Operator Options:** * **In**: Trigger for processes from specified IPs * **Not In**: Trigger for processes NOT from specified IPs **Examples:** * **Security monitoring**: In "203.0.113.45" - Watch specific suspicious IP * **External access**: Not In "10.0.0.0/8" - Monitor connections from outside internal network * **Office restriction**: In "203.0.113.0/24" - Limit long queries to office network only * **Cloud environment**: Not In "172.16.0.0/12" - Monitor non-VPC connections *** ### **Custom Script** Advanced trigger that executes a custom SQL query. If the query returns any data (one or more rows), the rule is considered "triggered" and the associated action will be executed. **Interface:** Manual text entry (SQL editor) **How It Works:** * You write a SQL query that checks for a specific condition * Query runs periodically against RapydoDB (monitoring database) * If query returns results (rows), trigger activates * If query returns no results (empty), trigger does not activate **Value:** Enter SQL query that returns data when condition is met **Action Restrictions:** When a Custom Script trigger is used, only the following actions are available: * Kill Query * Kill Connection * No action (with notification) * Custom Script (action) **Use Cases:** * **Complex business rules**: Combine multiple conditions beyond standard triggers * **Time-based rules**: "Kill queries running during business hours (9am-5pm)" * **Custom thresholds**: "Kill queries when combined CPU + Memory exceeds 150%" * **External integration**: Query external monitoring tables * **Advanced patterns**: "Kill queries with more than 3 joins AND no index usage" **Example - Time-Based Kill:** ```sql theme={null} SELECT query_id FROM rapydo_queries WHERE query_duration > 300 AND HOUR(NOW()) BETWEEN 9 AND 17; ``` Result: Returns query IDs for queries running longer than 5 minutes during business hours *** ## **3. Actions** Create Scout Rule Choose what happens when trigger conditions are met. You can select **one action per rule**. *** ### **Kill Query** Terminates only the running query while keeping the connection alive. **How It Works:** * PostgreSQL: Uses `pg_cancel_backend()` * MySQL: Uses `CALL mysql.rds_kill_query()` **Use When:** * Query is slow but you don't want to disrupt the application connection * Client can retry or handle the cancellation gracefully * You want to stop the current operation without closing the session **Example Scenario:** ``` Trigger: Query duration > 300 seconds Action: Kill query Result: The specific slow query is canceled, but the database connection remains open for the application to use ``` *** ### **Kill Connection** Terminates the entire database connection (and all queries from that connection). **How It Works:** * PostgreSQL: Uses `pg_terminate_backend()` * MySQL: Uses `CALL mysql.rds_kill()` **Use When:** * Connection itself is problematic (not just a single query) * You need to forcibly disconnect a user or application * Multiple queries from the same connection are causing issues **Difference from Kill Query:** * **Kill Query**: Stops the query, keeps connection → Less disruptive * **Kill Connection**: Closes connection completely → More aggressive **Example Scenario:** ``` Trigger: Connection idle time > 3600 seconds Action: Kill connection Result: Abandoned connection is closed, freeing resources ``` *** ### **Kill Idle Connections** Automatically terminates idle connections when connection utilization exceeds a threshold. Includes rate limiting to avoid killing too many connections at once. **How It Works:** * Monitors connection pool utilization * When threshold is exceeded, identifies idle connections * Kills idle connections up to the defined rate limit per cycle * Continues over multiple cycles until utilization drops below threshold **Use When:** * Connection pool is approaching maximum capacity * Many idle connections are consuming resources unnecessarily * You need gradual cleanup without disrupting all idle sessions at once **Configuration:** | Field | Required | Description | | --------- | -------- | ---------------------------------------------------- | | **Value** | Yes | Maximum number of idle connections to kill per cycle | * Typically paired with "Connection Utilization > 80%" trigger * Rate limit prevents killing too many connections simultaneously **Example Scenario:** ``` Trigger: Connection utilization > 80% Action: Kill idle connections Value: 10 (kill up to 10 idle connections per cycle) Result: Idle connections are gradually terminated until pool utilization drops below 80% ``` *** ### **Rate Limit** Limits the number of concurrent connections or operations matching the trigger criteria by automatically killing excess connections to reach the defined limit. **How It Works:** * You define a maximum number of allowed concurrent connections * When more connections match your triggers, Rapydo counts them * If count exceeds limit, Rapydo kills enough connections to reach the limit * Example: Limit = 5, Current = 10 → Kills 5 connections **Use When:** * Need to enforce concurrent connection limits per user * Want to prevent a single user/application from monopolizing resources * Controlling resource usage for specific workloads (reporting, analytics) **Important:** Rate limit controls the **NUMBER of simultaneous connections**, not queries per second or query frequency. **Example Scenario:** ``` Triggers: - User: In "reporting_user" Action: Rate limit to 5 connections Result: If reporting_user has 10 concurrent connections, Rapydo kills 5 to enforce the 5-connection limit ``` *** ### **Query Analysis (RCA)** Triggers AI-powered query analysis for queries matching the trigger conditions. Results include root cause identification and complete remediation plan sent via email or webhook. **Available With These Triggers Only:** Query Analysis (RCA) is only available when the rule includes at least one of: * Query Duration * Query Pattern * Custom Script **How It Works:** * Query matching triggers is automatically analyzed by Rapydo AI * Analysis includes: * Root cause identification (missing indexes, inefficient joins, etc.) * Step-by-step remediation plan with ready-to-run SQL * Estimated performance impact * Table statistics and execution plan details * Complete analysis report is sent to configured notification targets **Notification Interval:** Configure how frequently RCA reports are sent to avoid alert fatigue: | Interval | Value | | ---------- | -------- | | 15 minutes | 15 min | | 30 minutes | 30 min | | 45 minutes | 45 min | | 1 hour | 60 min | | 3 hours | 180 min | | 6 hours | 360 min | | 9 hours | 540 min | | 12 hours | 720 min | | 24 hours | 1440 min | **Use When:** * You want to understand WHY queries are slow, not just kill them * Building a knowledge base of query optimization opportunities * Investigating performance patterns over time * Educating team on query optimization **Important:** Query Analysis goes beyond just identifying the problem—it provides complete solutions with implementation guidance. **⚠️ Required:** A notification destination (email or webhook) must be configured for Query Analysis (RCA) to work. The analysis report cannot be delivered without a valid notification target. **Example Scenario:** ``` Trigger: Query duration > 60 seconds Action: Query Analysis (RCA) Notification: dba-team@company.com Result: 1. Query is analyzed by AI 2. Email sent with: - Root cause: Missing index on employee_id - Remediation: CREATE INDEX idx_employee_id ON employees(employee_id); - Estimated impact: 95% reduction in execution time ``` *** ### **Custom Script** Executes a custom SQL script when the rule triggers. Provides maximum flexibility for complex automation scenarios. **How It Works:** * You write SQL statements that execute when triggers activate * Scripts can query data, update tables, call procedures, etc. * Useful for complex logic beyond built-in actions **Use When:** * Need custom logic beyond standard actions * Want to identify and kill lock chains (find blocker, kill primary blocker only) * Execute diagnostic queries when issues occur * Log events to custom tracking tables * Call external procedures or webhooks **Common Use Cases:** **Kill Lock Chains:** ```sql theme={null} -- Find the root blocking query and kill only that -- Instead of killing all blocked queries SELECT kill_blocker_query(blocker_pid); ``` **Log to Custom Table:** ```sql theme={null} INSERT INTO query_kill_log (query_text, kill_time, reason) VALUES (?, NOW(), 'Exceeded duration threshold'); ``` **Call Maintenance Procedure:** ```sql theme={null} CALL emergency_cache_clear(); ``` **Example Scenario:** ``` Trigger: Waits count > 50 Action: Custom script Script: -- Identify lock chain -- Kill only the primary blocker, not all blocked queries -- Log the action Result: Resolves blocking with minimal disruption ``` *** ### **Notification Only** Scout Rule Notification Sends an alert via email or webhook without taking any database action. Queries continue running unaffected. **How to configure it in the UI:** Select **No action** as the action type, then enable notifications and enter your email or webhook destination. This combination monitors and alerts without intervening. **How It Works:** * Rule monitors for trigger conditions * When conditions are met, notification is sent * No queries are killed, no connections terminated * Event is logged in Rapydo for audit **Use When:** * Testing rules before enabling aggressive actions * Gathering data to understand query patterns * Building baseline metrics before setting kill thresholds * You want awareness without automatic intervention * Monitoring for compliance/audit purposes **Example Scenario:** ``` Trigger: Query pattern LIKE "%DELETE FROM users%" Action: No action Notification: Active Email: security-team@company.com Result: Team is alerted to potentially dangerous query, but no action is taken on the query (allows investigation) ``` *** ## **4. Notifications** **Toggle:** Active/Inactive **Notification Method:** Choose how to receive alerts: * **Email**: Enter one or more email addresses (comma-separated) * **Webhook**: Enter a webhook URL to receive POST notifications **When Notifications Are Sent:** * Rule triggers and action is executed * Notification includes: * Trigger conditions that were met * Action that was taken * Query details (SQL text, duration, user, database) * Instance information * Timestamp **Use With:** * Any action type (Kill Query, Kill Connection, Query Analysis, etc.) * Particularly useful with **No action** for monitoring without intervention *** ## Multiple Trigger Logic (AND Relationship) When you configure multiple triggers (up to 3), **ALL triggers must be true simultaneously** for the rule to activate. **How AND Logic Works:** Each trigger is evaluated independently: * ✅ If ALL triggers are true → Rule activates, action executes * ❌ If ANY trigger is false → Rule does not activate *** ### **Example 1: Targeted Query Kill** **Configuration:** ``` Trigger 1: Query duration > 300 seconds Trigger 2: User In "reporting_user, analytics_user" Trigger 3: DB name In "production_db" Action: Kill query ``` **Result:** Only kills queries that are: * ✅ Running longer than 5 minutes **AND** * ✅ From reporting or analytics users **AND** * ✅ On production database **at the same time** If a query from "reporting\_user" runs for 400 seconds on "dev\_db", it will NOT be killed (DB name doesn't match). *** ### **Example 2: Idle Connection Cleanup** **Configuration:** ``` Trigger 1: Connection idle time > 600 seconds Trigger 2: User In "test_user, dev_user" Trigger 3: IP In "192.168.1.0/24" Action: Kill connection ``` **Result:** Only kills connections that are: * ✅ Idle for over 10 minutes **AND** * ✅ From test or dev users **AND** * ✅ From the specified IP range **simultaneously** *** ### **Example 3: Pattern-Based Monitoring** **Configuration:** ``` Trigger 1: Query pattern LIKE "%DROP TABLE%" Trigger 2: User NOT In "admin, dba" Trigger 3: DB name In "production_db" Action: Kill query Notification: Active ``` **Result:** Only triggers for queries that: * ✅ Attempt to DROP TABLE **AND** * ✅ Are NOT from admin/dba users **AND** * ✅ Target production database **all at once** *** ### **Why Use Multiple Triggers?** **Precision Targeting:** * Avoid killing legitimate queries by being specific * Example: Kill long queries, but only from specific users **Reduced False Positives:** * Multiple conditions create high-confidence rules * Example: Kill idle connections, but only when from external IPs **Workload-Specific Rules:** * Different thresholds for different users/databases * Example: 30-second limit for app users, 300-second limit for analysts *** ### **Best Practices:** ✅ **Start with 1-2 triggers** and add more as you understand your workload ✅ **Test each trigger independently** before combining ✅ **Use NOT In operators** to exclude legitimate traffic ✅ **Combine duration + user + database** for surgical precision ❌ **Don't over-specify** - Too many triggers might never match reality *** ## Configuration Examples ### Example 1: Kill Long-Running Queries (Simple) **Scenario:** Stop queries that run too long **Configuration:** ``` DB Instances: Select All Trigger 1: Query duration > 300 seconds Action: Kill query Notification: Active Email: dba-team@company.com ``` **Result:** Any query running longer than 5 minutes is killed, team is notified *** ### Example 2: Cleanup Idle Connections (Multi-Trigger) **Scenario:** Free resources from abandoned development connections **Configuration:** ``` DB Instances: dev-db-01, test-db-01 Trigger 1: Connection idle time > 1800 seconds Trigger 2: User In "dev_user, test_user, qa_user" Action: Kill connection Notification: Active Email: dev-team@company.com ``` **Result:** Dev/test/QA connections idle for 30+ minutes are closed *** ### Example 3: Rate Limit Reporting User **Scenario:** Prevent reporting user from monopolizing connections **Configuration:** ``` DB Instances: production-db-01 Trigger 1: User In "reporting_user" Action: Rate limit to 5 connections Notification: Active Email: ops-team@company.com ``` **Result:** Reporting user limited to max 5 concurrent connections *** ### Example 4: Kill Dangerous Query Patterns **Scenario:** Terminate destructive operations from non-admin users **Configuration:** ``` DB Instances: Select All Trigger 1: Query pattern LIKE "%DROP TABLE%" Trigger 2: User NOT In "admin, dba, root" Action: Kill query Notification: Active Email: security-team@company.com ``` **Result:** DROP TABLE attempts from non-admins are killed and reported *** ### Example 5: Analyze Slow Analytics Queries **Scenario:** Build optimization knowledge base for analytics workload **Configuration:** ``` DB Instances: analytics-db-01 Trigger 1: Query duration > 60 seconds Trigger 2: User In "analytics_user" Trigger 3: DB name In "analytics_db" Action: Query Analysis (RCA) Notification: Active Email: dba-team@company.com ``` **Result:** Slow analytics queries are analyzed, remediation plans emailed to DBAs *** ## Execution Logs Execution Logs The Execution Logs panel shows a history of all rule activations — when each rule fired, which query triggered it, and what action was taken. Use this to verify rules are working correctly and to review past interventions. *** ## Best Practices ✅ **Start with No action + notification** before enabling kill actions—understand query patterns first ✅ **Use Query Pattern carefully** - Copy exact formatting from real queries, not from documentation ✅ **Test rules in non-production** before deploying to critical databases ✅ **Combine triggers with AND logic** to avoid killing legitimate queries ✅ **Set appropriate thresholds** based on actual workload, not arbitrary numbers ✅ **Enable notifications** to maintain visibility even with automatic actions ✅ **Review killed query logs** regularly to ensure rules aren't too aggressive *** ## Troubleshooting ### Rule Not Triggering **Check:** * All triggers are being met simultaneously (remember AND logic) * Query pattern formatting matches exactly (spacing, line breaks) * Selected DB instances include the databases where queries are running * User/DB name/IP filters are not too restrictive * Rule status is Active, not Disabled *** ### Query Pattern Not Matching **Solutions:** * Copy query directly from Rapydo Query Workspace or query logs * Don't manually type or reformat—preserve exact spacing * Use `%` wildcards for values that change (dates, IDs, amounts) * Test pattern against real query text before deploying * Remember: case-insensitive but spacing-sensitive *** ### Too Many Queries Being Killed **Solutions:** * Add more specific triggers (user, database, IP) * Increase duration thresholds * Use NOT In operators to exclude legitimate traffic * Switch to **No action** + notification to monitor before killing * Review killed query logs to identify patterns *** ### Rate Limit Not Working **Check:** * Trigger correctly identifies the connections to limit * Rate limit value is appropriate for workload * Connections are actually matching all trigger criteria * Multiple users aren't being lumped together unintentionally *** ## What's Next? * [Defining Rules](/automation/defining_rules) - Step-by-step guide to creating rules * [Alert Rules Reference](/automation/alerts_triggers_and_actions) - Complete Alert Rules documentation * [Automation Overview](/automation/introduction) - Return to automation overview # Dashboard View Source: https://docs.rapydo.io/dashboard/dashboard_view Detailed breakdown of all dashboard metrics and widgets *** Dashboard Performance Overview ## Dashboard Controls ### Time Range This widget displays the time range for the data represented in the graphs and metrics. You can adjust the view to show performance and activity from the last 3, 6, 12, 24, or 48 hours based on your monitoring needs. **Use this to:** * Focus on recent incidents or performance spikes * Compare current activity to historical patterns * Zoom in on specific time windows for detailed analysis *** ## Instance & Health Metrics ### DB Instances Status This widget displays the total number of database instances across three states: * **Configured**: Instances discovered and actively monitored by Rapydo * **Not Configured**: Instances detected but not yet configured for monitoring * **Not Available**: Instances that cannot be reached or are unavailable Clicking this widget navigates to the **Databases** page for full instance details. ### DB Connection Health This widget shows the count of unclosed (unhealthy) connections across all monitored instances. **What to watch for:** A non-zero unclosed connection count could indicate network issues, resource constraints, authentication problems, or connection pool exhaustion. Investigate immediately to prevent system downtime or performance degradation. *** ## User & Client Activity ### User Activity Displays the current number of active versus inactive users across your database infrastructure: * **Active Users**: Users currently executing queries or maintaining active sessions * **Inactive Users**: Users connected but not performing any significant activity **Optimization tip:** A high inactive user count with low activity might indicate a need for session timeout adjustments or automated cleanup of inactive connections to free up resources. ### Number of Clients This metric shows the total number of active database clients. A client refers to an application or user connection to the database. **Why track this:** * Understand connection patterns throughout the day * Plan for peak usage periods * Identify unexpected spikes in client connections *** ## Query Performance Metrics ### Query AVG Time This widget shows the average query execution time across all database instances over the selected time range. It provides a quick snapshot of overall query performance. **What to look for:** * Sudden increases in average time may indicate performance degradation * Compare to historical baselines to identify trends * Use this as a starting point for query optimization efforts ### Longest Query Displays the duration of the longest-running query currently executing or completed during the selected time range. **Why this matters:** This metric helps identify potential bottlenecks and inefficiencies: * Long-running queries can block other operations * May indicate missing indexes or inefficient query structure * Could signal the need for query optimization or resource scaling **Click action:** Opens a detail view with two tabs: * **Summary statistics** — aggregated stats for the longest queries * **Detailed information** — full per-query details including query text, with options to analyze using Query Analysis ### Caching Shows the current caching activity and efficiency across your database instances. **What to watch for:** * Low cache hit rates may indicate opportunities to optimize query patterns * Sudden drops in caching efficiency can indicate configuration or workload changes *** ## Database Locking & Contention ### Locks & Deadlocks This bar chart shows both locks and deadlocks over time across your database instances. Use the **Locks** / **Deadlocks** toggle on the widget to switch between the two views. Clicking a bar navigates to the **Queries** page filtered to that database instance and a 1-hour window starting at the clicked timestamp. Clicking the widget title opens a detail view with two tabs: * **Number of locks** * **Number of deadlocks** **Understanding locks:** Database locks are mechanisms to control concurrent access to data during transactions. While normal, excessive locking can indicate contention issues. **What to investigate:** * Large spikes in lock or deadlock counts * Sustained high lock levels * Correlation between locks and slow queries ### DEADLOCKS This bar chart shows the number of deadlocks encountered across your database instances over time. **What are deadlocks?** Deadlocks occur when two or more queries block each other, preventing further execution. For example: * Transaction A locks Table 1, waiting for Table 2 * Transaction B locks Table 2, waiting for Table 1 * Neither can proceed, creating a deadlock **Why it's critical:** * Deadlocks can cause transaction failures * May indicate poor transaction design or locking strategies * Repeated deadlocks suggest the need for query refactoring *** ## Resource Utilization Metrics ### Max CPU Usage This line chart shows CPU usage (%) across different database instances over time. Each colored line represents a specific instance. **What to monitor:** * Instances approaching 100% CPU utilization * Sustained high CPU usage indicating resource-intensive workloads * Sudden spikes that correlate with slow query performance **Click action:** Clicking a data point navigates to the **Queries** page filtered to that database instance and a 1-hour window ending at the clicked timestamp. **Performance tip:** If an instance consistently runs above 80% CPU, consider: * Optimizing resource-intensive queries * Scaling to a larger instance size * Distributing load across additional instances ### Max Connections Usage This line chart displays the percentage (%) of maximum database connections being used across different instances over time. **Why this matters:** Each database instance has a connection limit. When you approach this limit: * New connection attempts will fail * Applications may experience connection errors * Database performance can degrade **Click action:** Clicking a data point navigates to the **Queries** page filtered to that database instance and a 1-hour window ending at the clicked timestamp. **Best practice:** * Monitor instances approaching 80-90% connection utilization * Implement connection pooling in applications * Consider scaling instance size if sustained high connection usage occurs ### IOPS Usage This line chart shows Input/Output Operations Per Second (IOPS) for each database instance. IOPS measures the performance of your storage system. Values are shown as raw numbers (no percentage). **Understanding IOPS:** * **High IOPS**: Indicates heavy read/write activity * **Low IOPS**: Suggests light database activity or caching effectiveness * **Spikes**: Often correlate with batch jobs, reports, or data loads **Click action:** Clicking a data point navigates to the **Queries** page filtered to that database instance and a 1-hour window ending at the clicked timestamp. **Optimization strategies:** * If sustained high IOPS occur, consider: * Scaling to faster storage (e.g., Provisioned IOPS on AWS) * Optimizing queries to reduce disk reads * Implementing better indexing strategies * Adding read replicas to distribute load *** ## Drilling Down for Details Every widget on the dashboard is clickable, providing deeper insights into specific metrics. When you click on a widget, you'll see detailed information, historical data, and actionable options for that metric. ### Example: Clicking the Longest Query Widget When you click on the **Longest Query** widget, you open a detailed view showing all currently running queries sorted by execution time. Longest Query Detail View Longest Query Hover **What you can do in the detailed view:** * **View Query Details**: See the full SQL query text, execution time, and database instance * **Analyze Performance**: Understand which queries are consuming the most resources * **Identify Bottlenecks**: Spot queries that may be causing performance issues * **Take Action**: * Kill long-running queries that are blocking resources * Analyze the query using Rapydo's AI-powered Query Analysis * Copy the query for optimization in the Execute Query tool * View the query execution plan and get index recommendations ### Interactive Widget Navigation Each widget provides a gateway to more detailed information: | **Widget** | **Detailed View Shows** | | ------------------------- | --------------------------------------------------------------------------------------- | | **Longest Query** | All running queries sorted by duration, with ability to analyze or kill queries | | **Locks & Deadlocks** | Active locks and deadlocks split across two tabs: Number of locks / Number of deadlocks | | **DEADLOCKS** | Deadlock history, involved queries, and resolution timeline | | **Max CPU Usage** | Per-instance CPU trends, peak usage times, and resource-intensive queries | | **Max Connections Usage** | Connection pool status, active vs idle connections, connection history | | **IOPS Usage** | Disk I/O patterns, read/write breakdown, storage performance trends | | **DB Instances Status** | Full instance details, health checks, configuration, and metrics | **Pro Tip:** Click on any metric that looks unusual or concerning to investigate further and take immediate action. *** ## Quick Reference: When to Investigate | **Metric** | **Investigate When** | | ------------------------- | --------------------------------------- | | **DB Instances Status** | Instances are stopped unexpectedly | | **DB Connection Health** | Unhealthy connections appear | | **User Activity** | High idle users with low activity | | **Query AVG Time** | Sudden increase vs. baseline | | **Longest Query** | Exceeds acceptable thresholds | | **DEADLOCKS** | Any deadlocks occur | | **Locks & Deadlocks** | Large spikes or sustained high counts | | **Max CPU Usage** | Sustained >80% or spikes to 100% | | **Max Connections Usage** | Approaching 90% utilization | | **IOPS Usage** | Sustained high levels or unusual spikes | *** ## Next Steps * [Set Up Alert Rules](/automation/alerts_triggers_and_actions) - Get notified when metrics exceed thresholds * [Configure Scout Rules](/automation/scout_triggers_and_actions) - Automatically respond to long-running queries * [Analyze Query Performance](/queries/introduction_to_queries) - Deep dive into slow queries * [Monitor Locks & Waits](/queries/queries_view) - Investigate blocking queries # null Source: https://docs.rapydo.io/dashboard/introduction_to_dashboard **Dashboard Overview** This Database Observability and Caching Dashboard provides a centralized, real-time view of key database performance indicators and caching efficiency metrics. Its primary purpose is to empower database administrators (DBAs), system architects, and developers to monitor and maintain the health, performance, and stability of the database infrastructure. The dashboard displays critical performance data, user activity, and system health, enabling users to quickly identify and troubleshoot issues, optimize query performance, and manage resources effectively. The dashboard is designed to deliver a comprehensive understanding of how different aspects of your database system interact, making it easier to pinpoint inefficiencies, bottlenecks, and potential failures before they impact application performance or user experience. **Purpose of the Dashboard** The dashboard is aimed at ensuring the following objectives: * Proactive Monitoring: Continuously track the health and performance of your database, including resource usage, query performance, and user activity. This enables administrators to identify and resolve issues before they escalate. * Real-Time Insights: Data on the dashboard is updated in real-time, allowing for immediate awareness of potential issues such as resource bottlenecks (e.g., CPU or memory overload), slow-running queries, or excessive database locks. * Efficient Resource Management: By visualizing metrics such as CPU usage, memory usage, IOPS, and connection limits, DBAs can manage system resources more effectively, ensuring optimal allocation based on current workload. * Performance Tuning: The data provided enables detailed performance analysis, allowing for query optimization, identification of resource-intensive processes, and fine-tuning of database parameters to improve overall efficiency. * Error and Anomaly Detection: The dashboard highlights critical events like deadlocks, unhealthy connections, and failed caching operations, helping to detect and address anomalies that could lead to downtime or performance degradation. * User and Client Activity Tracking: The dashboard provides visibility into user activity, helping administrators understand user behavior and optimize the system to handle current or future user loads effectively. # null Source: https://docs.rapydo.io/db-configuration/mysql # Configure MySQL Settings Configure the following in the DB Parameter Group and then restart the server for the settings to take effect. ## MySQL 8.0+ | Parameter | Value | Description | | ---------------------------------------- | ------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `performance_schema` | `1` | Required. Enables the Performance Schema. | | `max_digest_length` | `4096` | Required for collection of larger queries. Increases the size of SQL digest text in `events_statements_*` tables. If left at the default value, queries longer than `1024` characters will not be collected. | | `performance_schema_max_digest_length` | `4096` | Must match `max_digest_length`. | | `performance_schema_max_sql_text_length` | `4096` | Recommended to match `max_digest_length`. Controls the maximum length of full SQL text stored in `events_statements_*` tables. | | `slow_query_log` | `1` | Required for capturing uncut long queries. Enables the slow query log to record queries that exceed the `long_query_time` threshold. | | `long_query_time` | `0` | Recommended. Set to `0` to capture all queries regardless of execution time, or adjust to your preferred threshold in seconds. | ## MySQL 5.7 | Parameter | Value | Description | | ---------------------------------------- | ------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `performance_schema` | `1` | Required. Enables the Performance Schema. | | `max_digest_length` | `4096` | Required for collection of larger queries. Increases the size of SQL digest text in `events_statements_*` tables. If left at the default value, queries longer than `1024` characters will not be collected. | | `performance_schema_max_digest_length` | `4096` | Must match `max_digest_length`. | | `performance_schema_max_sql_text_length` | `4096` | Recommended to match `max_digest_length`. Controls the maximum length of full SQL text stored in `events_statements_*` tables. | | `slow_query_log` | `1` | Required for capturing uncut long queries. Enables the slow query log to record queries that exceed the `long_query_time` threshold. | | `long_query_time` | `0` | Recommended. Set to `0` to capture all queries regardless of execution time, or adjust to your preferred threshold in seconds. | > **Note:** These parameters require MySQL 5.7.6 or later. ## MySQL 5.6 | Parameter | Value | Description | | -------------------- | ----- | ------------------------------------------------------------------------------------------------------------------------------------ | | `performance_schema` | `1` | Required. Enables the Performance Schema. | | `slow_query_log` | `1` | Required for capturing uncut long queries. Enables the slow query log to record queries that exceed the `long_query_time` threshold. | | `long_query_time` | `0` | Recommended. Set to `0` to capture all queries regardless of execution time, or adjust to your preferred threshold in seconds. | > **Note:** `max_digest_length`, `performance_schema_max_digest_length`, and `performance_schema_max_sql_text_length` are not available in MySQL 5.6. ## Important Notes for RDS Users * The `slow_query_log` parameter is **dynamic in RDS** and does not require a server restart to take effect. * Changes to `slow_query_log` and `long_query_time` can be applied immediately through the RDS console or CLI. * All other parameters listed above require a server restart to take effect. # null Source: https://docs.rapydo.io/db-configuration/postgresql # Configure PostgreSQL Settings Configure the following in the DB Parameter Group (RDS) or `postgresql.conf` and then restart the server for the settings to take effect. ## PostgreSQL 12+ | Parameter | Value | Description | | ---------------------------- | -------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `shared_preload_libraries` | `pg_stat_statements` | Required. Enables the pg\_stat\_statements extension for query tracking. | | `pg_stat_statements.track` | `all` | Recommended. Tracks all statements including those inside functions. Default is `top` (top-level statements only). | | `pg_stat_statements.max` | `10000` | Maximum number of statements tracked. Increase if you have many unique queries. | | `track_activity_query_size` | `4096` | Controls the maximum length of query text stored in `pg_stat_activity` and `pg_stat_statements`. Default is `1024`. | | `log_min_duration_statement` | `0` | Required for capturing uncut long queries. Logs all statements regardless of execution time. Set to `-1` to disable, or adjust to your preferred threshold in milliseconds. | > **Note:** After configuring `shared_preload_libraries`, you must restart PostgreSQL. Then run `CREATE EXTENSION IF NOT EXISTS pg_stat_statements;` in each database you want to monitor. ## Important Notes for RDS Users * The `log_min_duration_statement` parameter is **dynamic in RDS** and does not require a server restart to take effect. * Changes to `log_min_duration_statement` can be applied immediately through the RDS console or CLI. * The `shared_preload_libraries` parameter requires a server restart to take effect. * Changes to `pg_stat_statements.track`, `pg_stat_statements.max`, and `track_activity_query_size` can be applied without restart in most cases. ## Optional: Extensions for Enhanced RCA The following extensions improve Rapydo's Root Cause Analysis capabilities. Rapydo will work without them, but with reduced RCA functionality. ### HypoPG (No Restart Required) Allows Rapydo to test hypothetical indexes without actually creating them, enabling smarter index recommendations. **Installation:** Run in each database you want to monitor: ```sql theme={null} CREATE EXTENSION IF NOT EXISTS hypopg; ``` > **Note:** HypoPG is safe for production — it creates virtual indexes in memory only during your session, without affecting actual data or consuming disk space. Available on Amazon RDS. ### pg\_hint\_plan (Requires Restart) Allows Rapydo to test alternative execution plans and validate optimization recommendations. **Installation:** 1. Add `pg_hint_plan` to `shared_preload_libraries`: ``` shared_preload_libraries = 'pg_stat_statements,pg_hint_plan' ``` 2. Restart PostgreSQL 3. Run in each database you want to monitor: ```sql theme={null} CREATE EXTENSION IF NOT EXISTS pg_hint_plan; ``` > **Note:** pg\_hint\_plan is safe for production — it only affects queries that explicitly use hints. Available on Amazon RDS. # null Source: https://docs.rapydo.io/db-details/database_details_view ## Database Details View The **Database Details View** provides a comprehensive interface for monitoring and managing the databases connected to the system. It is divided into several sections, each serving a specific purpose to assist administrators in managing database instances effectively. ### Top Bar: Summary of Key Database Metrics The top bar presents an overview of the key metrics across all databases connected to the system. It includes: * **CPU Usage:** Displays the maximum CPU utilization percentage among the databases. * **Available RAM:** Shows the maximum amount of available RAM for the connected databases. * **Connections:** Shows the maximum number of active connections across databases. * **Duration:** Shows the maximum session duration. * **Users:** Displays the number of active users for the database with the highest activity. * **Locks:** Indicates the number of database locks on the most utilized database. * **Databases:** Displays the total number of databases in the system. * **Hosts:** Shows the number of hosts running databases. * **IOPS (Input/Output Operations Per Second):** Displays the IOPS for the database under the highest load. This bar gives a quick snapshot of overall system performance. ### Configured Databases Table The table presents all currently monitored databases with real-time performance data for each instance. Columns include: | Column | Description | | --------------------- | ------------------------------------------------------ | | **DB Name** | The unique identifier of the database instance | | **Account** | The AWS account ID the instance belongs to | | **Region** | The AWS region where the instance is hosted | | **Availability Zone** | The specific availability zone of the instance | | **Engine** | The database engine (e.g., MySQL, PostgreSQL) | | **Engine Version** | The version of the database engine | | **Size** | The instance class (e.g., db.t3.medium) | | **Provisioned IOPS** | Provisioned IOPS value if applicable | | **Parameter Groups** | The parameter group(s) applied to the instance | | **Metrics Available** | Whether CloudWatch metrics are available (Yes / No) | | **CPU** | Current CPU utilization (%) | | **Memory** | Available free memory (formatted as KB / MB / GB) | | **Connections** | Max connections / active connections / running queries | | **Max Duration** | Duration of the longest currently running query | | **Users** | Number of active users | | **Locks** | Number of current lock waits | | **Databases** | Number of databases on the instance | | **Hosts** | Number of connected hosts | | **Read IOPS** | Current read I/O operations per second | | **Write IOPS** | Current write I/O operations per second | Click the **Configure Databases** button to open the databases configuration page and add or remove instances from monitoring. ### Configuration View: Database Connections Database Configuration Table Selected This section allows administrators to connect new databases to the system. It presents the list of databases that are available for connection and those that are already configured. Administrators can check off the databases they want to add to the system. This view also enables administrators to review and update the list of databases being actively monitored, ensuring that the system always has the latest database connections. ### Supply RDS Credentials Modal When deploying Rapydo to a database, the **Supply RDS Credentials** modal prompts for your Amazon RDS master credentials: * **Username:** The master username for the RDS instance. * **Password:** The corresponding master password. These credentials are securely encrypted and used only during the one-time installation process. Click **Deploy Rapydo** to confirm, or **Cancel** to close without changes. ## Deployment Process When you click **"Deploy Rapydo"** and provide your RDS credentials, the following setup occurs automatically based on your database engine: ## PostgreSQL Setup ```sql theme={null} -- 1. Create dedicated monitoring user CREATE USER rapydo_admin WITH PASSWORD '[secure_generated_password]' INHERIT; -- 2. Grant comprehensive monitoring privileges GRANT rds_superuser, pg_monitor, pg_read_all_settings, pg_read_all_stats, pg_stat_scan_tables TO rapydo_admin; -- 3. Enable query statistics tracking CREATE EXTENSION IF NOT EXISTS pg_stat_statements; ``` ## MySQL Setup ```sql theme={null} -- 1. Create dedicated monitoring user CREATE USER 'rapydo_monitor'@'%' IDENTIFIED BY '[secure_generated_password]'; -- 2. Grant monitoring privileges GRANT PROCESS, EXECUTE, SELECT ON *.* TO 'rapydo_monitor'@'%'; -- 3. Apply changes FLUSH PRIVILEGES; ``` ## Required Privileges Explained ### PostgreSQL Privileges * **rds\_superuser** View all user sessions in pg\_stat\_activity, not just monitoring user's own sessions * **pg\_monitor** Access to all pg\_stat\_\* views for performance monitoring * **pg\_read\_all\_settings** Read database configuration for optimization recommendations * **pg\_read\_all\_stats** Access to detailed database statistics * **pg\_stat\_scan\_tables** Analyze table sizes and storage usage ### MySQL Privileges * **PROCESS** **Required** - View all user sessions in `SHOW PROCESSLIST` * **SELECT** Read performance schema tables and system statistics * **EXECUTE** Run RDS procedures like `mysql.rds_kill_query()` and `mysql.rds_kill()` for query termination. ### Security Notes * **Credentials are encrypted** and used only for initial setup * **Read-only monitoring** - no data modification capabilities * **Dedicated user** - isolated from your application users * **Minimal required access** - only what's needed for database monitoring **Your master credentials are never stored and are only used during this one-time setup process.** # null Source: https://docs.rapydo.io/db-details/introduction_to_database_details ## Introduction to the Database Details View The **Database Details View** is an essential component of any modern database management system, offering a unified interface for monitoring, configuring, and managing a wide range of databases. As organizations scale and their infrastructure becomes more complex, managing multiple databases across different environments can become a daunting task. This complexity is compounded by the need to ensure optimal performance, monitor usage, secure sensitive data, and prevent downtime across a potentially diverse database ecosystem. The **Database Details View** addresses these challenges by providing database administrators (DBAs) and operations teams with a comprehensive, user-friendly dashboard. This centralized view allows for real-time monitoring of key metrics, including CPU usage, available RAM, active connections, and other performance indicators. The ability to access this information in a single location streamlines the monitoring process, helping teams quickly identify and resolve potential issues before they impact the overall system performance or cause downtime. ### Why the Database Details View is Needed In today's digital landscape, organizations rely heavily on their database infrastructure to support everything from customer-facing applications to internal data processing. As the volume of data grows and as systems evolve, the complexity of managing databases increases. A simple database instance can quickly transform into a web of interconnected instances, clusters, and engines, often deployed across multiple regions and availability zones. Without a unified monitoring and management solution like the **Database Details View**, DBAs would need to juggle multiple tools, dashboards, and reports to keep track of the health and performance of their database instances. This fragmented approach not only consumes time and resources but also increases the likelihood of human error, as issues may go unnoticed until they become critical. The **Database Details View** eliminates these inefficiencies by offering a single pane of glass to visualize, monitor, and manage the entire database landscape. From configuration to monitoring key performance indicators (KPIs), this view empowers DBAs with the insights they need to ensure high availability, security, and scalability. ### Benefits of the Database Details View The **Database Details View** provides several key benefits, making it indispensable for organizations with complex database infrastructures: 1. **Centralized Monitoring:** * One of the primary benefits of the **Database Details View** is its ability to provide a centralized interface for monitoring all connected databases. By having real-time access to critical metrics such as CPU usage, memory utilization, and active connections, DBAs can quickly assess the overall health of the system and proactively address any potential bottlenecks or issues. * This centralized monitoring saves time by reducing the need to switch between various tools and reports, allowing administrators to maintain focus on optimizing performance. 2. **Real-Time Performance Insights:** * The top bar of the **Database Details View** displays the maximum values for key metrics across all databases, offering a quick, at-a-glance snapshot of the highest resource utilization. This real-time insight into metrics such as CPU usage, RAM, and IOPS helps DBAs identify resource-hungry databases that may need attention. * These real-time insights are particularly valuable in dynamic environments where database workloads fluctuate frequently. By continuously monitoring resource usage, teams can make informed decisions about scaling databases, adjusting configurations, or even offloading workloads to other instances. 3. **Simplified Database Configuration and Management:** * In addition to monitoring, the **Database Details View** allows for easy configuration and management of databases. The configuration view enables administrators to connect new databases to the system with minimal effort, streamlining the process of expanding the database infrastructure. This is especially beneficial in environments where multiple database engines or instances are used across various applications and services. * The configuration modal further simplifies the process by securely requesting the necessary credentials to integrate new databases into the system. This helps ensure that only authorized users can add new instances, enhancing security while maintaining ease of use. 4. **Proactive Problem Resolution:** * By providing DBAs with detailed insights into the status and health of each database, the **Database Details View** enables proactive problem resolution. The ability to track performance metrics, identify anomalies, and spot potential issues early on helps prevent larger problems, such as system outages, poor application performance, or data loss. * For example, a DBA might notice that CPU usage for a particular instance is consistently high, indicating a need for vertical scaling or query optimization. Similarly, observing an increase in locks or connections can alert the team to potential deadlock situations or resource contention, prompting them to take corrective action before the issue escalates. 5. **Enhanced Security and Access Control:** * The **Database Details View** includes security features that ensure only authorized personnel can configure and manage database instances. The configuration modal, for example, requires admin credentials to connect new databases, adding a layer of security to the process. By securing these critical actions, the view reduces the risk of unauthorized changes that could compromise system stability or data security. 6. **Scalability and Flexibility:** * As organizations grow, their database needs evolve. The **Database Details View** supports this growth by allowing easy management of additional databases and instances. Whether a new application requires its own database instance or an existing instance needs to be scaled, the view makes it easy to configure and connect new databases, ensuring that the system remains flexible and scalable as requirements change. * This scalability is particularly valuable for companies that operate in cloud environments, where databases can be quickly spun up or down based on demand. The view integrates seamlessly with cloud database services such as Amazon RDS, making it a vital tool for cloud-based database management. # null Source: https://docs.rapydo.io/execute-query/execute_query_view ## Execute Query View Execute Query The Execute Query View is divided into three main areas: the instance selector at the top, the SQL editor in the middle, and the results panel below. ### Instance Selector At the top of the screen, select one or more database instances to target. The query will be executed against all selected instances simultaneously, with results returned in separate tabs per instance. When one or more PostgreSQL instances are selected, a **Connected to database** field appears. This field must be filled before the **Execute** button becomes active. ### SQL Editor Type or paste your SQL query directly into the editor. The editor supports multi-line queries. * Click **Execute** to run the full query, or use **Cmd+Enter** (Mac) / **Ctrl+Enter** (Windows/Linux) as a keyboard shortcut. * When text is selected in the editor, the **Execute** button changes to **Run Selection**, allowing you to run only the highlighted portion. * Click **Cancel** to stop a running query at any time. ### Templates Query Templates Below the SQL editor, a list of commonly used query templates is displayed. Selecting a template loads its SQL into the editor, ready to run or modify. Templates cover tasks such as: * **Schema Drift** — Compares column names and ordinal positions across selected instances to detect schema inconsistencies between replicas or environments. * **View User Privileges** — Retrieves all privileges granted to a specified user (e.g., `SHOW GRANTS FOR admin`), useful for access control audits. * **Poor Index Usage** — Identifies queries where more rows are examined than necessary, highlighting candidates for index improvements. * **Top 10 Time-Consuming Queries** — Calculates the percentage of total execution time consumed by each query, sorted by total wait time. Result columns include Percentage, Total, Calls, Mean, and SQL Digest Text. * **Full Table Scan** — Detects queries that scan entire tables without using indexes. Result columns include SUM\_ROWS\_EXAMINED, SUM\_ROWS\_SENT, DIGEST\_TEXT, and a RECOMMENDATION column with optimization advice. ### Save As Template Save As Template After writing a query, click **Save As Template** to save it for future reuse. Saved templates appear in the templates list and can be modified or deleted at any time. ### Results Panel Query results are displayed in a tabbed panel below the editor — one tab per selected database instance. If no `LIMIT` clause is included in your query, a default limit of 1,000 rows is applied automatically. # null Source: https://docs.rapydo.io/execute-query/introduction_to_execute_query ### Introduction to Execute Query View The **Execute Query View** is a pivotal feature for database administrators and developers who need to efficiently manage and interact with multiple databases simultaneously. This view is designed to streamline query execution, providing users with the ability to run custom SQL queries across one or more database instances. By offering a centralized and user-friendly interface, the Execute Query View eliminates the need for manual, repetitive execution of queries on individual databases, enhancing both efficiency and productivity. In today's dynamic database environments, organizations often operate multiple database instances across various regions, handling diverse workloads. As a result, managing these databases can become complex, particularly when troubleshooting, performance optimization, or regular maintenance tasks are involved. The Execute Query View helps address these challenges by offering a powerful tool to execute queries in bulk, making it easier to gather critical insights, diagnose issues, and perform optimizations across multiple instances. One of the primary benefits of the Execute Query View is the flexibility it offers. Users can input their own custom SQL queries to perform tailored operations, or they can choose from a range of predefined templates to run common database management tasks quickly. These templates cover a variety of use cases, such as identifying poor index usage, examining user privileges, optimizing tables, or detecting schema drifts. The availability of these templates reduces the time spent writing repetitive queries and ensures consistency across different operations, especially when managing multiple environments. A standout feature of the Execute Query View is the ability to save queries as templates for future use. This is particularly useful for database administrators who regularly run the same queries for maintenance or monitoring purposes. Once saved, these templates can be executed with a few clicks, further streamlining the query execution process and reducing the possibility of human error when writing complex queries from scratch. This functionality is also useful for teams, as templates can be standardized and shared, ensuring that best practices are followed consistently across all database management activities. Another critical aspect of the Execute Query View is its ability to display query results in a tabbed format, making it easy to manage multiple result sets. For example, if a query is executed on 10 different database instances, each result set will appear in its own tab, allowing users to quickly switch between databases and compare performance or identify discrepancies. This structure not only keeps the interface organized but also provides a comprehensive view of how queries are executed across the entire database landscape. Furthermore, the view includes the capability to cancel long-running queries, which is essential in large-scale environments where performance issues may arise due to complex queries. The **Cancel Query** function ensures that administrators maintain full control over query execution, allowing them to stop any operations that may be consuming too many resources or affecting database performance. The Execute Query View plays a vital role in database performance tuning and troubleshooting. For instance, templates like the **Top 10 Time-Consuming Queries** or **Poor Index Usage** help administrators quickly identify bottlenecks or inefficient queries that need optimization. Similarly, the **Schema Drift** template allows for the detection of differences in schema definitions across instances, ensuring that the database structure remains consistent and aligned with organizational standards. In addition to these functionalities, the Execute Query View also provides enhanced security control through templates like **View User Privileges**, allowing administrators to quickly review and manage user access rights across databases. This feature is critical for maintaining data security and ensuring compliance with internal or external security policies. In summary, the Execute Query View is an indispensable tool for database administrators looking to efficiently manage multiple database instances. Its flexibility, combined with powerful templates, the ability to save and reuse queries, and its organized tabbed interface, provides users with the tools they need to optimize performance, maintain consistency, and ensure the security of their database environments. Whether running ad-hoc queries, diagnosing performance issues, or performing regular maintenance, the Execute Query View is a vital component of any robust database management system. *** ## Key Technical Notes **Read-Only Enforcement** All queries executed through the Execute Query View are enforced as **read-only** at the database session level. MySQL sessions use `SET SESSION TRANSACTION READ ONLY` and PostgreSQL sessions use the equivalent session-level setting. DML and DDL operations (INSERT, UPDATE, DELETE, DROP, etc.) are not permitted. **Row Limits** If your query does not include a `LIMIT` clause, a default limit of **1,000 rows** is automatically applied. If your query already includes a `LIMIT` clause, that value is always respected — including values larger than 1,000 (e.g., `LIMIT 5000`). A message is shown in the result when a limit was automatically added. **Canceling Queries** There is no enforced execution timeout. Use the **Cancel** button to stop a long-running query at any time while it is executing. **Querying RapydoDB** In addition to your production database instances, you can select **Rapydo-DB** from the instance selector to run queries directly against RapydoDB — Rapydo's internal monitoring database. This is useful for exploring performance metrics, process history, and monitoring data. **PostgreSQL: Database Name Required** When one or more PostgreSQL instances are selected, a **"Connected to database"** field appears in the interface. This field must be filled before the **Execute** button becomes active. Use it to specify which database on the PostgreSQL server to connect to. **Running a Selected Portion of a Query** When text is selected in the SQL editor, the **Execute** button changes to **Run Selection**, allowing you to run only the highlighted portion of the query without executing the full script. **Keyboard Shortcut** Use **Cmd+Enter** (Mac) or **Ctrl+Enter** (Windows/Linux) to execute the query without clicking the Execute button. # Rapydo Source: https://docs.rapydo.io/introduction AI-powered database monitoring and optimization for MySQL and PostgreSQL ### Welcome to Rapydo! Managing MySQL and PostgreSQL databases can sometimes feel like navigating a maze—there are so many moving parts that keeping everything running smoothly can be a challenge. That's where we come in. Rapydo is here to simplify database monitoring and optimization, so you can focus on what really matters: growing your business and keeping your users happy. #### What We Do Rapydo is all about helping you get the most out of your MySQL and PostgreSQL databases. Whether you're trying to fix performance bottlenecks, scale your infrastructure, or simply make sure everything is running as efficiently as possible, we've got the tools to make it happen. We provide real-time monitoring, actionable insights, and AI-powered recommendations to keep your databases in top shape—without the headaches. #### Why Rapydo? We know that no two databases are the same. That's why Rapydo is designed to adapt to your unique setup, whether you're running a single MySQL or PostgreSQL instance or managing multiple clusters across the globe. We don't believe in cookie-cutter solutions. Instead, we give you the flexibility to monitor, tweak, and optimize your databases exactly how you want. Plus, we're all about transparency. With Rapydo, you get a clear view of what's happening under the hood of your MySQL and PostgreSQL instances. No more guesswork or reactive troubleshooting—our platform gives you the information you need, when you need it, so you can stay ahead of potential issues. #### Who Is It For? Whether you're a database administrator (DBA) or part of a development team, Rapydo is built with you in mind. Our goal is to make database monitoring and optimization as seamless as possible, so you can focus on building great products rather than putting out database fires. No matter your level of expertise, our platform is designed to guide you through best practices, optimizations, and performance enhancements. #### AI-Powered Intelligence Rapydo goes beyond traditional monitoring with cutting-edge AI capabilities that work around the clock to keep your databases performing at their best. * **Automated Query Analysis**: When a problematic query is detected in real-time, our AI doesn't just identify the root cause—it provides a complete analysis with actionable recommendations. Rapydo automatically examines the query, pinpoints the issue, and delivers step-by-step remediation plans so you can fix problems immediately, not just understand them. * **Smart Query Analysis**: Every night, Rapydo collects and analyzes your queries at scale, identifying patterns and correlations across your database activity. The result? Consolidated insights like "adding index X will resolve issues A, B, C, and D"—giving you high-impact optimizations that address multiple problems at once. * **AI Database Assistant**: Get instant answers and recommendations from our intelligent assistant. Whether you need help optimizing a complex query, understanding execution plans, or troubleshooting an issue, our AI assistant is there to guide you with context-aware insights tailored to your specific environment. * **Proactive Prevention**: Move from reactive firefighting to proactive prevention. Our AI continuously learns from your database behavior, predicting potential issues before they impact your users and suggesting optimizations to keep you ahead of the curve. #### Key Features at a Glance **Real-Time Monitoring & Analysis** * **Dashboard Performance**: Track CPU, IOPS, connections, and query performance across all your database instances * **Live Query Monitoring**: Watch queries execute in real-time with detailed execution metrics * **Process History**: Analyze database activity patterns over time to identify trends and anomalies * **Locks & Waits**: Identify and resolve blocking queries instantly before they impact performance * **Longest Query Tracking**: Monitor your slowest-running queries and understand their impact **Intelligent Automation** * **Scout Rules**: Automated monitoring for long-running queries with customizable triggers and actions * **Alert Rules**: Multi-metric alerting system with email and Slack notifications—get notified when CPU, memory, connections, or query duration exceed your defined thresholds **AI-Powered Optimization** * **Automated Query Analysis**: Real-time investigation of problematic queries with complete analysis and recommendations * **Nightly Query Analysis**: Consolidated recommendations based on comprehensive query pattern analysis * **AI Database Assistant**: Context-aware insights and troubleshooting guidance * **Remediation Plans**: Step-by-step implementation guidance with estimated performance impact and SQL statements **Developer Tools** * **Execute Query**: Built-in SQL editor with syntax highlighting, multi-database support, and real-time results * **Query Templates**: Save and reuse common queries across your team * **Query Analysis**: Deep-dive into execution plans, table statistics, indexes, and performance bottlenecks * **Database Schema Explorer**: Navigate tables, columns, and indexes with ease **Enterprise Ready** * **Multi-Account AWS Integration**: Manage databases across multiple AWS accounts from a single interface * **Slack & Email Notifications**: Keep your entire team informed with integrated alerting * **User Management & Access Control**: Define roles and permissions for your team members * **License Management**: Track usage and manage your subscription #### How We Help You **Monitor Everything, Everywhere** * Real-time dashboard with CPU, memory, IOPS, and connection metrics across all instances * Live query execution tracking with automatic detection of blocking queries * Process count history and trend analysis to understand usage patterns * Multi-instance support across AWS accounts, regions, and database engines * Longest query tracking to quickly identify performance outliers **Automate What Matters** * **Scout Rules**: Automatically detect and respond to long-running queries—kill them, send alerts, or trigger custom actions * **Alert Rules**: Define thresholds for any metric and get notified via email or Slack when they're breached * Custom triggers based on CPU utilization, memory usage, query duration, connection counts, and more * Schedule rules to run at specific times or continuously monitor your databases **Optimize with AI** * Automated query analysis identifies not just the "why" but also the "how to fix" for slow queries * Nightly query analysis delivers consolidated recommendations: "Add index X to solve problems A, B, C, and D" * AI Database Assistant provides instant troubleshooting and optimization guidance * Remediation plans with estimated performance impact, implementation steps, and ready-to-run SQL * Query rewrite suggestions to improve execution efficiency **Work Smarter** * Built-in SQL editor with syntax highlighting and autocomplete * Save frequently-used queries as templates for quick access * Visualize locks and waits to understand blocking relationships * Analyze any query with detailed execution plan breakdowns * User-friendly interface—no complex onboarding or training required #### Get Started with Rapydo We know the world of databases can sometimes feel overwhelming, but that's exactly why we created Rapydo. Our platform takes the complexity out of MySQL and PostgreSQL management, giving you peace of mind and more time to focus on innovation. Ready to take your database game to the next level? Let's get started! *** **Next Steps:** * [Configure Your First Database](/db-configuration/postgresql) - Connect MySQL or PostgreSQL instances * [Explore the Dashboard](/dashboard/introduction_to_dashboard) - Understand your database health at a glance * [Set Up Automation Rules](/automation/introduction) - Automate monitoring and alerting * [Meet Your AI Assistant](/ai-dba/assistant) - Get intelligent help with database optimization # null Source: https://docs.rapydo.io/queries/introduction_to_queries # Queries View ## The Complexity of Real-Time Monitoring Without the right tools, monitoring databases in real time is complex and time-consuming. Administrators need to keep a vigilant eye on the performance and efficiency of queries and processes to ensure that the database doesn't become a bottleneck. ## Difficulty in Troubleshooting Identifying the root causes of database performance issues can be like finding a needle in a haystack, especially when queries do not perform as expected or when unexpected loads overwhelm the system. Impact of Uncontrolled Processes: Runaway or inefficient queries can consume excessive resources, leading to slowdowns or crashes that affect all applications relying on the database. Lack of Historical Data for Analysis: Often, performance issues are transient and may not be present during live monitoring sessions. Without historical data, it's challenging to analyze and understand past events to prevent future occurrences. ## Rapydo offers the Processes & Queries View which solves these issues in the following ways ### Real-Time Query and Process Monitoring Rapydo provides a robust solution for monitoring database queries and processes as they occur — supporting both MySQL and PostgreSQL instances. This real-time visibility is crucial for database administrators who need to ensure optimal performance and quick troubleshooting. The Queries view displays every query in execution along with key details such as execution time, the user who initiated the query, and resource usage. This immediate insight helps prevent bottlenecks and ensures that your database operates smoothly under various loads. ### Detailed Visualizations To accommodate different user preferences and requirements, Rapydo offers two distinct views: **Table View** - This view presents a detailed tabulation of ongoing queries and processes, providing specifics like query text, start time, execution duration, and more. Advanced filtering options let users sort and filter this data by various metrics, such as duration, to quickly isolate long-running or resource-intensive queries. **Pie Chart View** - For a more graphical representation, the pie chart view breaks down query statistics into visual segments, making it easy to digest the proportional impact of different queries on your system. This can be particularly useful for presentations and quick assessments during team meetings or reports. ### Process & Queries Management Direct process management is a standout feature of Rapydo. Users can instantly terminate any problematic or unresponsive queries with just a click, directly from the GUI. This functionality is crucial for maintaining system stability and preventing a single query from consuming disproportionate resources that could impact other operations. The ability to take immediate action enhances control over the database environment, reducing downtime and improving overall efficiency. ### Table State Persistence The Queries table saves your layout preferences — column order, column visibility, widths, and sort settings — automatically to your local profile. Your configuration is restored the next time you open the view, so you can set up the table once and pick up exactly where you left off. With these features, Rapydo's database management tool provides comprehensive insights and control, making it an essential asset for any organization looking to optimize their database operations. From real-time monitoring to historical analysis, each feature is designed to enhance understanding, streamline management, and boost the performance of your database servers. # null Source: https://docs.rapydo.io/queries/queries_view ### Queries View Process Count History The Queries View provides a detailed view of ongoing and past queries, providing specifics like query text, start time, execution duration, and more. Advanced filtering options let users sort and filter this data by various metrics, such as duration, to quickly isolate long-running or resource-intensive queries. ### DB Instance Selector Use the **DB Instance** dropdown in the top filter bar to select which database instance to monitor. The view updates in real time based on the selected instance. ### Process Count History The process count history chart shows three data series over time: * **Active processes** (blue) — the number of queries actively executing * **Locks** (orange) — the number of locks held * **Deadlocks** (red) — the number of deadlocks detected This combined view makes it easy to correlate spikes in locks or deadlocks with peaks in active process count, helping diagnose contention and performance issues. ### Total Processes The total processes bar provides a real-time snapshot of the number of queries currently being executed on your database. It shows the current active process count against the maximum connection limit, helping you understand the current load on your database and identify potential bottlenecks. ### Longest Running Query Displays the longest currently running query, including its execution duration and full query text. This real-time visibility is crucial for database administrators who need to ensure optimal performance and quick troubleshooting. ### Locks & Waits The locks and waits count provides insights into the queries that are currently waiting for locks or resources to be released. This metric is essential for identifying potential bottlenecks and performance issues caused by locking contention. The count is color-coded: gray (\< 10), yellow (10–20), red (≥ 20). Click to open a detailed locks table. ### Pie Charts Four pie charts break down query statistics into visual segments showing the proportional distribution across your database activity: * **Users** — Distribution of queries by database user * **Commands** — Distribution by command type (SELECT, INSERT, etc.) * **States** — Distribution by query state (executing, waiting, etc.) * **Dbs** — Distribution by database name ### Queries Table The queries table presents a detailed tabulation of ongoing queries and processes, including query text, execution time, user, state, command, client host, and more. Advanced filtering options let users sort and filter this data by various metrics, such as duration, to quickly isolate long-running or resource-intensive queries. Select one or more rows and click **Kill selected processes** to immediately terminate problematic or unresponsive queries. Kill Query ### Real-time Queries Real-time Queries Hover over any query row to reveal the **Analyze Query** button for immediate AI-powered analysis. Real-time Hover AI Analysis ### Process Count History Process History Use the date picker to navigate to specific historical time windows. History Date Picker ### Locks & Waits Locks and Waits # null Source: https://docs.rapydo.io/schema/rapydo_database_schema # Database Schema Information This document describes key tables and views in a database monitoring system built on PostgreSQL with TimescaleDB. Each component follows the same documentation pattern for consistency. ## Core Monitoring Tables ### Table: process\_history **Purpose**: Captures 1-second snapshots of all running database processes. **Primary Key**: (db\_identifier, sampled\_at, connection\_id, thd\_id, event\_id) **Key Fields**: * `db_identifier`: Database instance identifier * `sampled_at`: Snapshot timestamp * `connection_id`, `thd_id`: Connection and thread identifiers * `time`: How long query has been running (seconds) at snapshot time * `digest_text_md5`: Hash of normalized query for pattern matching **Storage**: TimescaleDB hypertable (1 day chunks, 1000 partitions) **Usage**: Long-running queries appear multiple times with increasing `time` values. **Key Characteristic**: Source table for real-time monitoring and aggregations. ### Table: aggregated\_process\_history **Purpose**: Complete query execution log with start-to-finish information. **Primary Key**: (start\_time, db\_identifier, connection\_id, thd\_id, event\_id) **Key Fields**: * `start_time`, `end_time`: Query start/end timestamps * `time`: Total execution time * `db_identifier`, `connection_id`, `thd_id`: Instance and connection identifiers * `digest_text_md5`: Query pattern hash **Derivation**: Created from process\_history with ETL calculating start time as: sampled\_at - (time \* INTERVAL '1 second') **Key Characteristic**: Each query execution appears as exactly one row. ### Table: digest\_lookup **Purpose**: Central store of query patterns, mapping MD5 hashes to full query text. **Primary Key**: `digest_text_md5` **Key Fields**: * `digest_text_md5`: Hash of normalized SQL * `digest_text`: Full normalized SQL text * `last_seen`: When pattern was last observed **Usage**: Referenced by other tables to avoid storing duplicate query text. ### Table: database\_configuration **Purpose**: Tracks database configuration changes over time. **Primary Key**: (db\_identifier, sampled\_at) **Key Fields**: * `db_identifier`: Database instance identifier * `sampled_at`: Configuration capture timestamp * `config` (jsonb): Complete configuration as JSON **Configuration Content**: Engine settings, memory allocations, connection limits, etc. **Usage**: Configuration change detection and historical tracking. ### Table: metadata\_lock **Purpose**: Tracks schema-level lock contentions. **Primary Key**: (sampled\_at, db\_identifier, locking\_pid, locking\_owner\_event\_id, waiting\_pid, waiting\_owner\_event\_id) **Key Fields**: * `sampled_at`: Contention observation timestamp * `waiting_age_secs`: Duration waiting session has been blocked * Locking fields: Details about session holding the lock * Waiting fields: Details about blocked session **Usage**: Diagnosing schema-level blocking issues (DDL operations). ### Table: transaction\_lock **Purpose**: Tracks row-level and transaction lock contentions. **Primary Key**: (sampled\_at, db\_identifier, locking\_pid, locking\_trx\_id, waiting\_pid, waiting\_trx\_id) **Key Fields**: * `sampled_at`: Contention observation timestamp * `waiting_started`: When waiting began * `waiting_age_secs`: Duration of waiting * Locking/waiting fields: Similar to metadata\_lock * Transaction IDs: `locking_trx_id`, `waiting_trx_id` **Usage**: Identifying row-level blocking between transactions. ### Table: deadlock\_history **Purpose**: Records database deadlock events. **Primary Key**: (id, timestamp, db\_identifier) **Key Fields**: * `timestamp`: When deadlock was detected * `parsed_data` (jsonb): Structured deadlock information * `raw_data`: Original deadlock log **Usage**: Post-mortem analysis of deadlock patterns and affected transactions. ## Performance Aggregation Layer ### Materialized View: mv\_daily\_db\_stats **Purpose**: Pre-computes daily performance metrics by database. **Key Fields**: * `bucket_day`: Daily time bucket * `db_identifier`, `db`: Database identifiers * `avg_duration`, `total_queries`, `min/max_duration`: Performance metrics **Implementation**: TimescaleDB continuous aggregate with 4-day retention. ### Materialized View: mv\_top\_queries\_summary **Purpose**: Pre-computes daily statistics for each query pattern. **Key Fields**: * `bucket_day`, `db_identifier`, `db`: Time and database identifiers * `query_hash`: Digest hash identifying the query pattern * `min/avg/max_duration`, `query_count`: Performance metrics **Implementation**: TimescaleDB continuous aggregate with max\_duration index. ### Materialized View: mv\_top\_queries\_detail **Purpose**: Preserves individual query execution details. **Key Fields**: * Time and DB identifiers * `query_hash`: Query pattern identifier * `start_time`, `duration`: Specific execution details **Usage**: Deep analysis of execution variability for query patterns. ## Dashboard Optimization Layer ### Table: yesterdays\_stats **Purpose**: Pre-formatted snapshot of yesterday's database metrics. **Schema**: * `date`: Statistics date (yesterday) * `db_identifier`, `db`: Database identifiers * Performance metrics: Pre-formatted and rounded **Refresh**: Via custom function that rebuilds table daily. ### Table: yesterdays\_top\_queries **Purpose**: Top 200 slowest query patterns from yesterday. **Schema**: * Date and database identifiers * `query_hash`, `query_text`: Query identification * Performance metrics * `query_rank`: Position within top 200 **Usage**: Instant access to slowest queries for dashboards. ### Table: yesterdays\_top\_queries\_detail **Purpose**: Individual executions of yesterday's slowest queries. **Schema**: Similar to summary but with execution-specific timestamps. **Usage**: Detailed analysis of timing variations. ### Procedure: refresh\_all\_daily\_stats **Purpose**: Coordinates refresh of all snapshot tables. **Implementation**: Calls individual refresh functions in sequence. **Scheduling**: Runs twice daily for redundancy. ### Materialized View: mv\_top\_queries\_summary **Purpose**: A TimescaleDB continuous aggregate that pre-computes daily summary statistics for each unique query pattern across database instances, supporting query performance analysis. **Refresh Policy**: * Automatic refresh every day * Covers data between 3 days ago and 1 day ago * Real-time aggregation enabled (not materialized\_only) * Retention policy: 4 days **Key Fields**: * `bucket_day` (timestamp): Daily time bucket from aggregated\_process\_history.start\_time * `db_identifier` (text): Database instance identifier * `db` (text): Database name accessed * `query_hash` (text): The digest\_text\_md5 hash identifying the normalized query * `max_duration` (float): Maximum query execution time in seconds * `min_duration` (float): Minimum query execution time in seconds * `avg_duration` (float): Average query execution time in seconds * `query_count` (integer): Total number of executions of this query pattern **Source Data**: * Aggregates data from the aggregated\_process\_history table * Filters for leaf events only (is\_leaf = true) * Groups by day, db\_identifier, database name, and query hash **Indexes**: * `idx_mv_top_queries_summary_duration`: Index on max\_duration DESC for efficient ranking of slowest queries **Usage Patterns**: * Finding the most resource-intensive query patterns * Tracking query performance trends over time * Identifying problematic database instances * Supporting query optimization efforts ### Materialized View: mv\_top\_queries\_detail **Purpose**: A TimescaleDB continuous aggregate that preserves individual query execution details for deeper performance analysis, while maintaining efficient storage and retrieval. **Refresh Policy**: * Automatic refresh every day * Covers data between 3 days ago and 1 day ago * Real-time aggregation enabled (not materialized\_only) * Retention policy: 4 days **Key Fields**: * `bucket_day` (timestamp): Daily time bucket from aggregated\_process\_history.start\_time * `db_identifier` (text): Database instance identifier * `db` (text): Database name accessed * `query_hash` (text): The digest\_text\_md5 hash identifying the normalized query * `start_time` (timestamp): When the query execution began * `duration` (float): Execution time in seconds for this specific execution **Source Data**: * Stores specific executions from aggregated\_process\_history * Filters for leaf events only (is\_leaf = true) * Preserves individual execution timing data **Indexes**: * `idx_mv_top_queries_detail_duration`: Index on duration DESC for efficient access to slowest individual executions **Usage Patterns**: * Detailed analysis of performance variability for specific query patterns * Investigating outlier execution times * Correlating slow queries with specific time periods * Identifying parameter-sensitive query patterns ### Table: yesterdays\_top\_queries **Purpose**: A daily snapshot table that stores the previous day's top 200 slowest query patterns per database, with complete query text and formatted metrics for immediate dashboard use. **Schema**: * `date` (date): The date of the statistics (always yesterday) * `db_identifier` (text): Database instance identifier * `db` (text): Database name accessed * `query_hash` (text): The digest\_text\_md5 hash identifying the normalized query * `query_text` (text): Full normalized SQL text of the query (from digest\_lookup) * `max_duration` (numeric): Maximum execution time rounded to 2 decimal places * `min_duration` (numeric): Minimum execution time rounded to 2 decimal places * `avg_duration` (numeric): Average execution time rounded to 2 decimal places * `query_count` (integer): Total number of executions * `query_rank` (integer): Rank of this query within its database by max\_duration (1 = slowest) **Data Source**: * Derived from mv\_top\_queries\_summary joined with digest\_lookup * Limited to top 200 slowest queries per database * Contains only the previous day's data * Numbers are pre-formatted (rounded to 2 decimal places) **Refresh Mechanism**: * Refreshed by the refresh\_yesterdays\_top\_queries() function * Function drops and recreates the table daily * Uses a temporary table (tmp\_top\_queries) for atomic replacement * Automatically joined with digest\_lookup to include full query text **Sample Data** (from screenshot): * Shows ranked queries (UPDATE, SELECT statements) * Includes actual SQL text with parameter placeholders * Metrics like max\_duration (64.00s), avg\_duration (54.32s) * Query execution counts (467) * Rankings (1-200) ### Table: yesterdays\_top\_queries\_detail **Purpose**: A daily snapshot table that stores individual executions of the previous day's 200 slowest queries per database, providing execution-level details for deeper analysis. **Schema**: * `date` (date): The date of the statistics (always yesterday) * `db_identifier` (text): Database instance identifier * `db` (text): Database name accessed * `query_hash` (text): The digest\_text\_md5 hash identifying the normalized query * `query_text` (text): Full normalized SQL text of the query (from digest\_lookup) * `start_time` (timestamp): Exact time when this specific execution began * `duration` (numeric): Execution time in seconds, rounded to 2 decimal places * `query_rank` (integer): Rank of this execution within its database by duration (1 = slowest) **Data Source**: * Derived from mv\_top\_queries\_detail joined with digest\_lookup * Limited to 200 slowest individual executions per database * Contains only the previous day's data * Execution times are pre-formatted (rounded to 2 decimal places) **Refresh Mechanism**: * Refreshed by the refresh\_yesterdays\_top\_queries\_detail() function * Function drops and recreates the table daily * Uses a temporary table (tmp\_top\_queries\_detail) for atomic replacement **Usage Patterns**: * Detailed analysis of specific slow query executions * Investigating time-of-day patterns for query performance * Finding individual problematic executions for queries that usually perform well ### Procedure: refresh\_all\_daily\_stats **Purpose**: A comprehensive scheduled procedure that refreshes all daily statistics tables in a coordinated manner, ensuring dashboard data consistency. **Implementation**: * PL/pgSQL stored procedure * Calls each individual refresh function: * refresh\_yesterdays\_stats() * refresh\_yesterdays\_top\_queries() * refresh\_yesterdays\_top\_queries\_detail() * Scheduled to run twice daily via pg\_cron (at 00:05 and 00:30) * Includes retry logic for resilience **Refresh Flow**: 1. Refreshes basic database metrics (yesterdays\_stats) 2. Refreshes top query summaries (yesterdays\_top\_queries) 3. Refreshes detailed query executions (yesterdays\_top\_queries\_detail) **Benefits**: * Ensures all dashboard tables are refreshed in a coordinated manner * Multiple scheduled runs provide redundancy in case of failures * Centralizes refresh logic for easier maintenance * Atomically updates all tables without dashboard disruption **Relationship to Continuous Aggregates**: * Works alongside TimescaleDB's automatic continuous aggregate policies * The procedure handles the final step of creating dashboard-ready snapshot tables * Creates an additional caching layer optimized for dashboard presentation **Purpose**: Records database deadlock events with both structured and raw information, enabling analysis of deadlock patterns, root causes, and affected transactions. **Primary Key**: Compound (id, timestamp, db\_identifier) * `id` (integer): Auto-incrementing identity column, not nullable * `timestamp` (timestamptz): When the deadlock was detected, not nullable * `db_identifier` (text): Database instance identifier, not nullable **Content Fields**: * `parsed_data` (jsonb): Structured representation of the deadlock graph as JSON, not nullable * `raw_data` (text): Original raw deadlock event log from the database, not nullable **Storage**: TimescaleDB hypertable * Time dimension: timestamp (chunk interval: 1 day) * Secondary dimension: db\_identifier (1000 partitions) * Retention policy: 7 days * Compression: Enabled after 4 days * Compression strategy: * Segment by: db\_identifier * Order by: timestamp DESC * Autovacuum: Disabled (managed by TimescaleDB) **Indexes**: * `idx_deadlock_history_timestamp`: Index on timestamp DESC (for time-based queries) * `idx_deadlock_history_db_identifier`: Index on db\_identifier (for instance-specific queries) * `idx_deadlock_history_parsed_data`: GIN index on the JSONB parsed\_data field (for complex queries into the JSON structure) **JSONB Structure** (parsed\_data typically includes): * Transaction identifiers involved in the deadlock * Waiting-for relationships between transactions * SQL statements that were executing in each transaction * Tables and rows involved in the lock conflict * Lock types that caused the conflict (X, S, IX, IS, etc.) * User information for transactions involved * Transaction isolation levels * Wait times before the deadlock was detected **Usage Patterns**: * Post-mortem analysis of deadlock events * Identifying application patterns that lead to deadlocks * Finding tables frequently involved in deadlocks * Correlating deadlocks with specific queries or transaction patterns * Creating alerts for increasing deadlock frequencies * Monitoring effectiveness of deadlock reduction strategies **Key Benefits**: * Preserves both structured (parsed\_data) and unstructured (raw\_data) forms of deadlock information * Enables complex querying through JSONB operators on parsed\_data * Maintains historical record of deadlocks for pattern analysis * Supports joining with other monitoring tables based on timestamp and db\_identifier **Common Investigation Queries**: ```sql theme={null} -- Find tables most frequently involved in deadlocks SELECT jsonb_path_query(parsed_data, '$.transactions[*].relations[*]') AS table_name, COUNT(*) AS deadlock_count FROM deadlock_history WHERE timestamp > NOW() - INTERVAL '7 days' GROUP BY table_name ORDER BY deadlock_count DESC; -- Find deadlocks involving a specific table SELECT timestamp, db_identifier, parsed_data FROM deadlock_history WHERE timestamp > NOW() - INTERVAL '7 days' AND parsed_data::text ILIKE '%customer%' ORDER BY timestamp DESC; -- Analyze deadlock frequency over time SELECT date_trunc('hour', timestamp) AS hour, db_identifier, COUNT(*) AS deadlock_count FROM deadlock_history WHERE timestamp > NOW() - INTERVAL '7 days' GROUP BY hour, db_identifier ORDER BY hour DESC, deadlock_count DESC; ``` **Relationships**: * Can be correlated with transaction\_lock and metadata\_lock tables based on timestamp * Often analyzed alongside process\_history to understand the database state during deadlocks * May be joined with database\_metric to correlate deadlocks with system load conditions **Deadlock vs. Lock Contention**: * Deadlocks represent circular wait conditions automatically detected by the database * Distinct from lock contentions (in transaction\_lock and metadata\_lock tables) which show active blocking * Deadlocks trigger automatic transaction aborts by the database engine * This table captures the post-mortem data after deadlock detection and resolution **Purpose**: High-frequency monitoring table that captures point-in-time snapshots of all running database processes at 1-second intervals. **Primary Key**: Compound (db\_identifier, sampled\_at, connection\_id, thd\_id, event\_id) **Core Fields**: * `db_identifier` (text): Database instance identifier (multi-tenant architecture) * `sampled_at` (timestamptz): Exact moment when the snapshot was captured * `connection_id` (integer): Database connection identifier * `thd_id` (integer): Thread ID for the database process **Process Metadata**: * `client_host` (text): Client hostname/IP address * `user` (text): Database username * `db` (text): Database being accessed * `command` (text): SQL command type (Query, Update, etc.) * `state` (text): Process state at snapshot time * `time` (integer): Query runtime in seconds as of snapshot time * `resource_group` (text): Resource allocation group **Query Information**: * `current_statement` (text): SQL statement executing at snapshot time * `sql_text` (text): Complete SQL text * `digest_text` (text): Normalized SQL statement (parameters removed) * `digest_text_md5` (text): Hash of normalized statement for pattern matching * `object_name` (text): Database object being accessed **Event Tracking**: * `event_id` (integer): Unique event identifier * `nesting_event_id` (integer): Parent event ID for nested operations * `nesting_time` (double precision): Time spent in nested operations **Storage**: TimescaleDB hypertable * Time dimension: sampled\_at (chunk interval: 1 day) * Secondary dimension: db\_identifier (1000 partitions) * Retention policy: Likely 1-3 days based on ETL stored procedure **Query Patterns**: * Time-series aggregation for concurrent load visualization * Point-in-time analysis for seeing exactly what was running at specific moments * Input for ETL process that populates aggregated\_process\_history **Volume Characteristics**: * Very high write volume (1 snapshot per second per running query) * Snapshots include all active database processes * Long-running queries appear multiple times with increasing "time" values ### Table: aggregated\_process\_history **Purpose**: Complete query history log that provides full lifecycle information for each query execution. **Primary Key**: Compound (start\_time, db\_identifier, connection\_id, thd\_id, event\_id) **Time Range**: * `start_time` (timestamptz): Calculated query start time * `end_time` (timestamptz): Time when query execution completed * `time` (integer): Total execution time in seconds **Connection Information**: * `db_identifier` (text): Database instance identifier * `connection_id` (integer): Database connection identifier * `thd_id` (integer): Thread ID * `client_host` (text): Client hostname/IP * `user` (text): Database username * `db` (text): Database name accessed **Query Classification**: * `command` (text): Command type executed * `state` (text): Final query state * `digest_text_md5` (text): Hash for identifying query patterns * `resource_group` (text): Resource group classification **Query Details**: * `sql_text` (text): Full SQL statement executed * `object_name` (text): Primary object accessed * `event_id` (integer): Unique event identifier * `nesting_event_id` (integer): Parent event ID for nested operations * `nesting_time` (double precision): Time spent in nested operations * `is_leaf` (boolean): Flag for leaf-level operations **Storage**: TimescaleDB hypertable * Time dimension: start\_time (chunk interval: 1 day) * Secondary dimension: db\_identifier (1000 partitions) * Retention policy: Likely weeks to months for historical analysis **Derivation Process**: * Created by ETL procedure that runs periodically * Reconstructs query lifecycles from process\_history snapshots * Uses sophisticated start time calculation: sampled\_at - (time \* INTERVAL '1 second') * Selects most recent sample for each unique query execution * Excludes system processes (Connect, Daemon, event\_scheduler) * Only processes meaningful queries (with digest\_text\_md5 or nesting\_event\_id) **Usage Patterns**: * Historical query analysis and performance troubleshooting * Slow query identification and optimization * User activity auditing and pattern analysis * Trend analysis of query execution times * Resource usage analysis by query type, user, and database **Key Characteristic**: Each complete query execution is represented by exactly one row, containing the calculated start time, observed end time, and derived total execution time. ### Table: digest\_lookup **Purpose**: Centralized lookup table that provides a single storage location for SQL query patterns, mapping compact MD5 hashes to their corresponding full normalized SQL text to optimize storage across the system. **Primary Key**: `digest_text_md5` (text) **Core Fields**: * `digest_text_md5` (text): MD5 hash of the normalized SQL statement * `digest_text` (text): Full normalized SQL statement with parameters removed * `last_seen` (timestamptz): Most recent timestamp when this query pattern was observed **Storage**: * Regular PostgreSQL table (not a TimescaleDB hypertable) * Indexed on digest\_text\_md5 for efficient lookups **ETL Process**: * Populated by a scheduled procedure (`populate_digest_lookup`) * Extracts unique digest patterns from `process_history` * Processes data in hourly batches for performance * Uses sophisticated upsert logic: * For new patterns: Inserts hash, full text, and last seen timestamp * For existing patterns: Updates only the last\_seen timestamp if newer * Only processes records where both digest\_text and digest\_text\_md5 are not null * Tracks progress with logging for operational monitoring **Update Frequency**: * Runs periodically to capture new query patterns * Processes data from the latest recorded timestamp up to the newest available data * Batches data processing in 1-hour increments for efficiency and progress tracking **Usage Patterns**: * Primarily a reference table joined to other monitoring tables * Acts as a compression mechanism by storing large text strings only once * Provides historical record of all observed query patterns * Facilitates query categorization and pattern analysis **Data Characteristics**: * Each unique SQL pattern appears exactly once * Growing table, but at a much slower rate than transaction tables * New entries added only when previously unseen query patterns are observed * Last\_seen date continuously updated for existing patterns when observed again **Relationships**: * Referenced by process\_history.digest\_text\_md5 * Referenced by aggregated\_process\_history.digest\_text\_md5 * May be referenced by other monitoring/analytics tables **Key Benefit**: * Dramatically reduces storage requirements by eliminating duplicate storage of potentially large text strings * Enables efficient queries while maintaining full SQL text availability when needed * Creates a canonical reference for each unique query pattern in the system ### Table: database\_metric **Purpose**: Core time-series table that stores performance metrics for database instances at one-minute intervals, combining both RDS CloudWatch metrics and calculated database statistics. **Primary Key**: Compound (db\_identifier, sampled\_at) **Database Identification**: * `db_identifier` (text): Database instance identifier * `sampled_at` (timestamptz): Timestamp when the metrics were collected * `engine` (text): Database engine type (e.g., MySQL, PostgreSQL) * `engine_version` (text): Version of the database engine * `instance_class` (text): RDS instance type (e.g., db.m5.large) * `availability_zone` (text): AWS availability zone where the instance is running **Resource Utilization Metrics**: * `cpu_utilization` (double precision): Percentage of CPU utilized * `freeable_memory` (double precision): Amount of available memory in bytes * `freeable_memory_percent` (double precision): Percentage of total memory available * `swap_usage` (double precision): Amount of swap space being used * `read_iops` (double precision): Read operations per second * `write_iops` (double precision): Write operations per second * `free_local_storage_percent` (double precision): Percentage of storage space available * `provisioned_iops` (double precision): IOPS provisioned for the instance **Connection Metrics**: * `connections_count` (double precision): Current number of active connections * `max_connections` (double precision): Maximum allowed connections * `connection_utilization_percentage` (double precision): Percentage of connection capacity used * `sleeping` (double precision): Number of sleeping/idle connections * `running` (double precision): Number of running/active connections * `maximum_query_duration` (double precision): Duration of the longest-running query * `users_count` (double precision): Number of unique database users with active connections * `hosts_count` (double precision): Number of unique client hosts connected * `db_count` (double precision): Number of unique databases being accessed * `waits_count` (double precision): Number of sessions in a wait state * `parameter_groups` (text): RDS parameter groups applied to the instance **Storage**: TimescaleDB hypertable * Time dimension: sampled\_at (chunk interval: 1 day) * Secondary dimension: db\_identifier (1000 partitions) * Retention policy: Likely months to years for trend analysis **Collection Process**: * Collected at one-minute intervals * Combines metrics from AWS CloudWatch (for OS/instance metrics) * Augments with calculated metrics from database queries * Provides a unified view of both infrastructure and database performance **Usage Patterns**: * Source data for real-time monitoring * Used by ETL processes to generate hourly aggregates * Detailed analysis of short-term performance issues * Input for alerting on critical thresholds **Visualization Usage**: * Powers CPU usage charts in dashboards * Supports connection usage monitoring * Used for IOPS tracking and analysis * Database instance health monitoring ### Table: hourly\_database\_metrics **Purpose**: Aggregated time-series table that provides hourly summaries of database performance metrics for efficient dashboard visualization and trend analysis. **Primary Key**: Compound (db\_identifier, hour) **Time and Identification**: * `hour` (timestamptz): Hour timestamp (truncated to hour precision) * `db_identifier` (text): Database instance identifier * `instance_class` (text): RDS instance type (e.g., db.m5.large) * `engine_version` (text): Version of the database engine **CPU Utilization Metrics**: * `min_cpu_utilization` (float8): Minimum CPU utilization percentage within the hour * `avg_cpu_utilization` (float8): Average CPU utilization percentage within the hour * `max_cpu_utilization` (float8): Maximum CPU utilization percentage within the hour **Connection Utilization Metrics**: * `min_connection_utilization` (float8): Minimum connection utilization percentage * `avg_connection_utilization` (float8): Average connection utilization percentage * `max_connection_utilization` (float8): Maximum connection utilization percentage **I/O Performance Metrics**: * `min_iops` (float8): Minimum total I/O operations per second * `avg_iops` (float8): Average total I/O operations per second * `max_iops` (float8): Maximum total I/O operations per second * `avg_read_iops` (float8): Average read operations per second * `avg_write_iops` (float8): Average write operations per second **Storage**: Likely TimescaleDB hypertable * Time dimension: hour (chunk interval: likely weeks or months) * Secondary dimension: db\_identifier * Retention policy: Likely years for long-term trend analysis **ETL Process**: * Derived from the database\_metric table * Aggregated hourly to reduce data volume and improve query performance * Calculates min/avg/max statistics for key metrics within each hour * Used to optimize dashboard performance for long time ranges **Common Analysis Queries**: ```sql theme={null} -- Instance resize identification (underutilized instances) SELECT db_identifier, instance_class, engine_version, MAX(max_cpu_utilization) AS max_cpu FROM hourly_database_metrics WHERE hour > NOW() - INTERVAL '30 days' GROUP BY db_identifier, instance_class, engine_version HAVING MAX(max_cpu_utilization) < 40 ORDER BY MAX(max_cpu_utilization); -- High CPU utilization identification (potential performance issues) SELECT db_identifier, instance_class, engine_version, MAX(max_cpu) AS max_cpu FROM hourly_database_metric WHERE hour >= DATETIME('now', '-30 day') GROUP BY db_identifier, instance_class, engine_version HAVING MAX(max_cpu) > 80; ``` **Query Patterns**: * Dashboard queries selecting top N instances by resource utilization * Time-series visualizations covering 12+ hour periods * Performance trend analysis across multiple instances * Hourly comparison of resource utilization patterns **Key Benefits**: * Dramatically improves dashboard query performance * Reduces data scanning for common visualization patterns * Enables efficient long-term trend analysis * Supports comparison of performance across multiple instances * Facilitates capacity planning and cost optimization # null Source: https://docs.rapydo.io/settings/aws-accounts # AWS Accounts Connect AWS accounts to enable Rapydo to discover and monitor RDS instances. ## Overview AWS Accounts The AWS Accounts tab allows you to configure cross-account access so Rapydo can discover and monitor RDS instances across your AWS infrastructure. Rapydo uses IAM role assumption to securely access your AWS resources. ## Prerequisites Before adding an AWS account, ensure you have: Create an IAM role in the target AWS account Configure the trust policy to allow Rapydo to assume the role Attach the necessary permissions for RDS and CloudWatch access ## Adding an AWS Account Click **Add AWS Account Configuration** to open the configuration form. ### Configuration Fields A friendly name to identify this AWS account in Rapydo (e.g., "Production", "Staging", "Analytics"). Your 12-digit AWS account number. You can find this in the AWS Console under your account settings. The Amazon Resource Name (ARN) of the IAM role that Rapydo will assume to access your account. This role must have permissions to describe RDS instances and access CloudWatch metrics. Example format: `arn:aws:iam::123456789012:role/RapydoMonitoringRole` ### Actions * **Save** — Add the AWS account configuration * **Cancel** — Discard changes and close the form ## Managing AWS Accounts The AWS accounts list displays all configured accounts with: | Column | Description | | ---------------- | ----------------------------- | | AWS Account Name | Friendly name for the account | | AWS Account ID | 12-digit AWS account number | | Role ARN | IAM role ARN for access | Use the action menu on each row to delete existing account configurations. **Table Settings** — Customize which columns are displayed in the list. # null Source: https://docs.rapydo.io/settings/license # License View and manage your Rapydo license. ## Overview License The License tab displays your current license status and allows you to activate or renew your Rapydo license. ## License Status The License Status section displays a table with three columns: | Column | What It Shows | | ------------------- | -------------------------------------------------------------------------------------------------------------------------------------- | | **Current License** | License type (e.g., Enterprise License, Trial License), status message, EC2 Instance ID, AWS Account, Features tier, and Max Instances | | **Days Remaining** | Number of days until expiration and the expiration date (shown as "Expires \[date]") | | **Actions** | **Activate** button to renew or apply a new license | ## License Renewal License Renewal Click **Activate** to renew an expiring license or apply a new license key. ### Instance Information The renewal form displays your instance details for easy reference when contacting sales: Your 12-digit AWS account number. Click **Copy** to copy it to your clipboard. The instance ID tied to your license. Click **Copy** to copy it to your clipboard. Provide these details to the sales team when requesting a license renewal. ### Applying a License Paste your complete license key or trial token in the text area. License keys can be very long. Click **Renew Now** to apply the license or **Cancel** to close. ## Getting Help For immediate renewal, contact the sales team with your instance details (AWS Account ID and EC2 Instance ID). For urgent license assistance, email **[support@rapydo.io](mailto:support@rapydo.io)** # Logging Source: https://docs.rapydo.io/settings/logging Configure log levels for Rapydo system components ## Overview The **Logging** settings page lets you control the verbosity of log output for specific Rapydo system components. By adding logger configurations, you can increase detail for targeted modules during debugging or reduce noise in production. Access this page from **Settings → Logging**. *** ## Logger List The main view shows a table of all configured loggers: | Column | Description | | --------------- | ------------------------------------------------------------------------------------------------------------------------ | | **Logger Name** | The system component being configured. Displays as **Root Logger** when no name is specified (applies to all components) | | **Log Level** | The minimum severity level of messages that will be logged | | **Actions** | Delete the logger configuration | *** ## Log Levels Log levels control the minimum severity of messages that are written to logs. Only messages at or above the configured level are recorded. | Level | Description | | ------------ | ----------------------------------------------------------------------- | | **NOTSET** | Inherits the level from the parent logger (or root) | | **DEBUG** | Detailed diagnostic information for debugging | | **INFO** | General operational messages confirming things are working as expected | | **WARNING** | Indicates something unexpected happened but the system is still working | | **ERROR** | A serious problem that prevented a function from completing | | **CRITICAL** | A severe error that may cause the system to stop functioning | **Default level:** INFO *** ## Adding a Logger Click **Add Logger Configuration** to open the configuration panel. | Field | Required | Description | | --------------- | -------- | -------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Logger Name** | No | The name of the system component to configure. Leave empty to configure the **root logger**, which applies to all components not specifically configured | | **Log Level** | Yes | Select the minimum log level for this component | Click **Save** to apply the configuration. *** ## Root Logger Leaving the **Logger Name** field empty creates a **Root Logger** configuration that sets the default log level for all system components. This is the global floor — individual component loggers can override it by specifying a more specific logger name. **Example:** * Root Logger: WARNING — suppresses most output by default * `rapydo.queries`: DEBUG — enables detailed logging for the queries module only *** ## Deleting a Logger Click **Delete** in the row actions menu to remove a logger configuration. The component will revert to the default log level. *** ## Common Use Cases **Enable debug logging for a specific module during an investigation:** ``` Logger Name: rapydo.queries Log Level: DEBUG ``` **Suppress verbose output in production:** ``` Logger Name: (empty — Root Logger) Log Level: WARNING ``` **Track only critical errors from all components:** ``` Logger Name: (empty — Root Logger) Log Level: ERROR ``` # null Source: https://docs.rapydo.io/settings/mail-configuration # Mail Configuration Set up email notifications for alerts, reports, and system notifications. ## Overview Mail Configuration The Mail Configuration tab allows you to configure SMTP settings for sending email notifications from Rapydo. This enables alerts for database issues, scheduled reports, and system notifications. ## Adding a Mail Configuration Add Mail Configuration Click **Add New Mail Configuration** to open the configuration form. ### Configuration Fields SMTP username or AWS SES access key ID. SMTP password or AWS SES secret access key. The sender email address that will appear on outgoing notifications. SMTP server hostname. For AWS SES, use the regional endpoint (e.g., `email-smtp.us-east-1.amazonaws.com`). Server port number. Common values: * **Port 25** — Default SMTP (often blocked) * **Port 587** — SMTP with STARTTLS (recommended) * **Port 465** — SMTP over SSL Select the encryption mode: * **None** — No encryption * **Start TLS** — Upgrade to TLS after connecting * **Use TLS** — TLS from the start Comma-separated list of email addresses that will receive system notifications. ### Actions * **Test** — Send a test email to verify the configuration works * **Save** — Add the mail configuration * **Cancel** — Discard changes and close the form ## Managing Mail Configurations The mail configuration list displays all configured SMTP settings with key details: | Column | Description | | ----------------------- | --------------------------- | | User Account | SMTP username or access key | | From Email | Sender email address | | SMTP Service | SMTP server hostname | | SMTP Port | Server port | | Start TLS | TLS on connect setting | | Use TLS | Full TLS setting | | Notification Recipients | Configured recipients | Use the action menu (⋮) on each row to edit or delete existing configurations. **Table Settings** — Customize which columns are displayed in the list. # null Source: https://docs.rapydo.io/settings/overview # Settings Configure Rapydo's system-wide settings including email notifications, webhook endpoints, AWS integrations, licensing, Slack integration, user management, and logging. Access Settings from the bottom of the left navigation panel by clicking **Settings**. ## Settings Sections * **Users** — Create and manage users directly from the UI * **Mail Configuration** — Set up SMTP for email notifications * **Webhook** — Configure webhook endpoints for alert notifications * **AWS Accounts** — Connect AWS accounts for RDS monitoring * **License** — View and manage your Rapydo license * **Slack Integration** — Connect Slack for notifications and Scout AI access * **Logging** — Configure log levels for system components # null Source: https://docs.rapydo.io/settings/slack-integration # Slack Integration Connect Rapydo to Slack to receive database monitoring notifications and interact with Scout AI directly from Slack. ## Overview Slack Integration The Slack Integration allows you to: * Receive real-time database alerts and notifications in Slack channels * Interact with Scout AI directly from Slack using natural language * Keep your team informed about database health without leaving Slack ## Setup Wizard The Slack Integration Setup guides you through four steps to connect your workspace. Configure your Slack workspace domain and configuration token. Your Slack workspace domain. This is the subdomain of your Slack URL. For example, if your Slack URL is `mycompany.slack.com`, enter `mycompany`. Your Slack configuration token obtained from Slack app settings. Starts with `xoxe.xoxp-...` Your tokens are securely transmitted and stored. They will never be displayed in logs or error messages. Click **Create App & Authorize** to proceed. Follow the Slack authorization flow to grant Rapydo access to your workspace. You'll be redirected to Slack to approve the permissions. Configure the app token for Socket Mode support. This enables real-time communication between Rapydo and Slack. Review your configuration and finalize the integration. ## Connection Status Once setup is complete, the Current Status section displays your progress through four stages: | Stage | Description | | ---------------- | ------------------------------------------ | | Not Configured | Initial state, no connection established | | Bot Token Set | Bot token has been configured | | Connected | Successfully connected to Slack | | Fully Configured | All features enabled including Socket Mode | A fully configured integration shows: * **Workspace** — The name of your connected Slack workspace * **Connection Status** — Shows "Connected" when the integration is active * **Mode** — Displays "Socket Mode" when real-time features are enabled ## Managing the Integration ### Set Up Integration If no integration is configured, click **Set Up Integration** to launch the setup wizard. ### Reconfigure Integration Once the integration is configured, click **Reconfigure Integration** to update your Slack connection settings. Use this if you need to: * Update tokens * Reconnect after a disconnection * Change workspace settings ### Delete Integration Deleting the integration will disconnect Rapydo from your Slack workspace, stop all notifications, and disable Scout AI access from Slack. Click **Delete Integration** to completely remove the Slack connection. You can set up the integration again at any time by going through the setup wizard. # null Source: https://docs.rapydo.io/settings/user-management # Users Create and manage Rapydo users directly from the Users settings page. ## Overview The **Users** settings page provides a full interface for managing user accounts. Administrators can add users, reset passwords, change roles, and delete users without needing API access. ## Users Table The Users table lists all existing users with the following columns: | Column | Description | | ------- | ------------------------------------------- | | Name | The user's username | | Role | The user's role: **Admin** or **Read Only** | | Actions | Per-row action menu | ### Row Actions Use the action menu on each row to: * **Reset Password** — Set a new password for the user * **Change Role** — Switch the user's role between Admin and Read Only (disabled for your own account) * **Delete** — Remove the user (disabled for your own account) ## Adding a User Click **Add User** to open the user creation panel. ### Fields The login username for the new user. The initial password for the new user. Select the user's access level: * **Admin** — Full access to all features and settings * **Read Only** — View-only access to dashboards and monitoring When enabled, the user will be prompted to set a new password on their first login. ### Actions * **Save** — Create the user * **Cancel** — Discard and close the panel ## User Roles | Role | Description | | ------------- | --------------------------------------------------------------------------------------------------------------------------------- | | **Admin** | Full access to all features and settings including Query Analysis, Execute Query, Scout Rules, Alerts, and Settings configuration | | **Read Only** | View-only access to dashboards and monitoring. Cannot modify settings or configurations | ## API Reference Users can also be created via API. This is useful for scripted provisioning or environments without UI access. ### Prerequisites * SSH access to the Rapydo machine * Admin credentials * `curl` and `jq` installed on the machine ### Step 1: SSH into the Machine ```bash theme={null} ssh user@your-rapydo-instance ``` ### Step 2: Obtain an Authentication Token ```bash theme={null} TOKEN=$(curl -sS --location 'http://127.0.0.1:8000/v1/users/login' \ --form 'username="admin"' \ --form 'password="admin"' | jq -r .access_token) ``` Adjust the username and password to match your environment. ### Step 3: Create the User ```bash theme={null} curl --location 'http://127.0.0.1:8000/v1/users/' \ --header 'Content-Type: application/json' \ --header "Authorization: Bearer $TOKEN" \ --data '{ "username": "user", "password": "user", "role": "read_only" }' ``` Change `username`, `password`, and `role` as needed. Valid `role` values: `admin`, `read_only`. ### Login Endpoint ``` POST http://127.0.0.1:8000/v1/users/login Content-Type: multipart/form-data Parameters: - username (string): User's username - password (string): User's password Response: { "access_token": "eyJ...", "token_type": "bearer" } ``` ### Create User Endpoint ``` POST http://127.0.0.1:8000/v1/users/ Content-Type: application/json Authorization: Bearer Body: { "username": "string", "password": "string", "role": "admin" | "read_only" } ``` # Webhook Configuration Source: https://docs.rapydo.io/settings/webhook Configure webhook endpoints for alert and rule notifications ## Overview The **Webhook Configuration** settings page lets you define HTTP endpoints that Rapydo will call when Alert Rules or Scout Rules trigger notifications. Each webhook can be configured with authentication, retry behavior, and SSL verification. Access this page from **Settings → Webhook**. *** ## Webhook List The main view shows a table of all configured webhooks with the following columns: | Column | Description | | ------------- | ------------------------------------------------------ | | **Name** | Friendly name for the webhook | | **URL** | The target endpoint URL | | **Method** | HTTP method (POST, PUT, or PATCH) | | **Type** | Slack or Generic | | **Auth Type** | Authentication method (None, Bearer, Basic, or Custom) | | **Active** | Whether the webhook is enabled | | **Actions** | Edit or Delete the webhook | *** ## Adding a Webhook Click **Add New Webhook Configuration** to open the configuration panel. Fields are grouped into three sections. ### Basic Configuration | Field | Required | Description | | ---------------- | -------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | **Name** | Yes | A descriptive name for the webhook (max 255 characters) | | **URL** | Yes | The endpoint URL. Must use `http://` or `https://` | | **HTTP Method** | Yes | How to call the endpoint: **POST**, **PUT**, or **PATCH** (default: POST) | | **Webhook Type** | Yes | **Slack** — sends rich Block Kit formatted messages. **Generic** — sends a clean JSON payload suitable for PagerDuty, custom alerting systems, or any HTTP endpoint | ### Authentication | Field | Description | | -------------- | ------------------------------------------------------------------------------------------------------------ | | **Auth Type** | Choose the authentication method: **None**, **Bearer**, **Basic**, or **Custom** (default: None) | | **Auth Token** | Shown when Auth Type is not None. Enter the token or credentials. Hidden by default with a show/hide toggle. | > **Security note:** Auth tokens are write-only. After saving, the token is never returned by the API. When editing an existing webhook, leave the token field empty to keep the current token unchanged. ### Advanced Settings | Field | Default | Range | Description | | -------------------------- | ---------- | ----- | -------------------------------------------------------------------------------------------------------- | | **Timeout** | 30 seconds | 1–300 | How long to wait for a response before considering the request failed | | **Retry Count** | 3 | 0–10 | Number of retry attempts if the request fails | | **Retry Delay** | 5 seconds | 1–60 | Delay between retry attempts | | **Verify SSL Certificate** | Enabled | — | Validates the SSL certificate of the target URL. Disable only when testing with self-signed certificates | *** ## Testing a Webhook Before saving, click **Test** to send a test request to the configured URL. The button shows a loading spinner while the request is in progress. Use this to confirm the endpoint is reachable and authentication is correct. *** ## Editing a Webhook Click **Edit** in the row actions menu to open the configuration panel pre-filled with the current settings. Make your changes and click **Save**. *** ## Deleting a Webhook Click **Delete** in the row actions menu to remove the webhook. Any Alert Rules or Scout Rules using this webhook will need to be updated to use a different notification destination. *** ## Using Webhooks in Rules Once configured, webhooks are available as notification destinations when creating: * **Alert Rules** — select the webhook under the Webhook notification option * **Scout Rules** — enter a webhook URL in the Notification section See [Alert Rules Reference](/automation/alerts_triggers_and_actions) and [Scout Rules Reference](/automation/scout_triggers_and_actions) for details.