I was checking my database logs and noticed some really strange queries that are running super slow. Here’s what I found:
-- Execution time: 2023-08-15 14:22:35
-- Connection: webapp_user[webapp_user] @ [192.168.1.45] Session: 1247
-- Duration: 195.321456 Lock_duration: 0.000028 Records_returned: 7456123 Records_scanned: 7456123
USE main_app;
SET timestamp=1692108155;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `language_mappings`;
-- Execution time: 2023-08-15 14:27:12
-- Connection: webapp_user[webapp_user] @ [192.168.1.45] Session: 1247
-- Duration: 245.892341 Lock_duration: 0.000041 Records_returned: 2887456 Records_scanned: 2887456
SET timestamp=1692108432;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `notification_logs`;
These queries are taking way too much time to complete and I can’t figure out why they’re happening. My app uses Ruby on Rails so I’m wondering if maybe the ORM is doing some kind of background sync operation? Has anyone seen queries like this before? What could be causing them and how can I make them faster?
check for analytics or reporting jobs – those look like data export queries. rails doesn’t generate SQL_NO_CACHE automatically, so something else is doing this. could be a third-party integration pulling your language mappings and notification data. i’d also check what’s connecting from that 192.168.1.45 ip.
Those queries have the SQL_NO_CACHE hint - that’s usually from backup tools or monitoring systems, not Rails ORM. You’re seeing full table scans with millions of records, which screams mysqldump running in the background or some database monitoring tool doing health checks. I’ve seen this exact thing when our backup script ran during peak hours without proper scheduling. Check your cron jobs and any database monitoring tools you’ve got installed. Rails ActiveRecord normally generates specific queries with WHERE clauses, not these broad SELECT * statements. The high duration with zero lock time pretty much confirms these are maintenance operations competing with your app queries for resources.
Hmm, interesting - got any data sync services or ETL processes running? Those timestamps are only minutes apart, so I’m thinking there’s a scheduled job hitting your DB. Check your Rails logs around those same times. Also, what’s your language_mappings table used for? External services polling it for translation data maybe?