Our ERP inventory module has been experiencing severe performance degradation over the past two weeks, but we’re flying blind because slow query logs aren’t capturing the problematic queries. The RDS MySQL instance is configured with slow query logging enabled, but queries that we know are taking 15-20 seconds to complete aren’t showing up in the slow query log.
Current configuration shows:
SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
The slow_query_log is ON, and long_query_time is set to 10 seconds, yet our inventory stock level queries that timeout at the application level (20-second timeout) never appear in the logs. We’ve verified log retention configuration in the RDS console, and it shows 7 days retention.
The impact is critical - we can’t identify which inventory queries need optimization, our warehouse operations are delayed by slow stock lookups, and we’re getting customer complaints about unavailable items that are actually in stock. Without visibility into these slow queries, we’re stuck guessing at index strategies.
Let me address all three focus areas systematically to solve your slow query log visibility problem:
Slow Query Log Settings - Complete Configuration:
Your current configuration is partially correct, but missing critical parameters. Here’s what you need:
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 5;
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL log_output = 'FILE,TABLE';
The key issue is likely the missing log_queries_not_using_indexes parameter. ERP inventory queries often perform table scans on large stock tables, and these queries may complete in under 10 seconds individually but cause cumulative performance problems. This parameter captures them regardless of execution time.
Query Time Threshold - Optimal Settings:
Your 10-second threshold is too high for effective performance tuning. Here’s the recommended approach:
- Set
long_query_time = 5 as a starting point for capturing problematic queries without log overflow
- Monitor for 24-48 hours and analyze the captured queries
- If log volume is manageable (<1000 queries/day), reduce to 3 seconds
- For inventory-specific tuning, focus on queries hitting your stock_levels, inventory_transactions, and product_warehouse tables
Important: RDS measures query time in seconds with microsecond precision. A value of 5 means 5.000000 seconds. Queries completing at 4.999999 seconds won’t be logged, so consider setting it slightly lower (4.5) if you’re seeing borderline cases.
Log Retention Configuration - Access and Analysis:
Your 7-day retention is configured correctly in the RDS console, but you need to ensure you’re accessing logs properly:
-
Console Access: RDS Console → Instance Details → Logs → Slow Query Logs. Download logs for offline analysis if they’re not visible in the console viewer.
-
Table-Based Access: If you enabled log_output=‘TABLE’, query directly:
SELECT * FROM mysql.slow_log
WHERE db = 'erp_inventory'
AND query_time > 5
ORDER BY start_time DESC LIMIT 100;
- Performance Insights: Enable RDS Performance Insights in the console. This provides real-time query analysis independent of slow query logs and will show you those 15-20 second queries even if they’re not being logged.
Root Cause Analysis for Your Missing Queries:
Based on your description, the most likely reasons queries aren’t appearing:
-
Application-Level Timeout: Your application’s 20-second timeout is killing queries before they complete, so RDS never logs them as “completed slow queries.” Check your connection pool settings (maxWait, socketTimeout in JDBC URLs).
-
Lock Wait Time Exclusion: RDS only counts active execution time, not lock wait time. If queries wait 15 seconds for locks then execute in 3 seconds, they won’t appear in slow query logs. Check:
SHOW ENGINE INNODB STATUS;
Look for “LOCK WAIT” sections and high lock wait times.
- Query Killed by Resource Limits: If RDS is hitting memory or connection limits, queries may be terminated without completing, thus never logged. Check CloudMonitor for memory usage and connection count metrics.
Immediate Action Plan:
- Execute the configuration changes above (adjust parameters)
- Enable Performance Insights for real-time visibility (independent of slow query logs)
- Set up CloudMonitor alerts for query execution time and lock wait time
- After 24 hours, download and analyze slow query logs to identify top offenders
- Focus optimization efforts on queries without proper indexes (flagged by log_queries_not_using_indexes)
This comprehensive approach will give you full visibility into your inventory module’s performance issues and enable data-driven optimization decisions.
Be careful with lowering long_query_time too aggressively. Setting it to something like 1-2 seconds in a busy ERP system will generate massive log volumes and impact performance. I’d recommend starting at 5 seconds and monitoring for a day or two. Also, enable log_queries_not_using_indexes parameter - many slow inventory queries are probably missing indexes entirely, and this will catch them regardless of execution time.
First thing to check - are those queries actually reaching the database, or are they timing out at the application connection pool level? If your application has a 20-second timeout but the connection pool timeout is set to 15 seconds, the query gets killed before RDS even sees it complete. Check your application’s database connection settings and JDBC pool configuration.
Another possibility is query time calculation. RDS measures query execution time from when it starts processing the query until it finishes, but doesn’t include time spent waiting for locks or row-level locks held by other transactions. If your inventory queries are waiting on locks from concurrent update operations, that wait time isn’t counted toward the slow query threshold. You might want to check your InnoDB lock wait statistics and see if lock contention is the real issue, not query performance.
Don’t forget about the log_output parameter. If it’s set to FILE but your RDS version has table logging available, switching to TABLE (mysql.slow_log table) can sometimes reveal queries that weren’t showing up in file-based logs due to buffering issues. The table format also makes it easier to query and analyze slow query patterns using SQL rather than parsing log files.