GitHub user daviftorres added a comment to the discussion: [MySQL] Pruning / Off-loading Database
As I mentioned before, I’m concerned about the long-term health of the database. The main issue is that data is never pruned or moved out of the hot/live database. For context, here are some real numbers from a production environment that isn’t even 11 months old, based on Percona’s Query Analytics over the last 24 hours. Regarding the `usage_event` table: - Its load is 0.04 over 0.27, about 15% of the total DB load (only for the most frequent query). - Average query time is ~45 seconds (and getting progressively worse). - It accounts for 75% of all rows examined and returned (very inefficient). - Key points: - Some events date back to `2025-04-30 02:44:00`. - Queries like `SELECT SQL_NO_CACHE * FROM usage_event` fetch all columns (likely more than needed). - Consider whether `SQL_NO_CACHE` is necessary, since most data is stale. - Adding a WHERE clause to fetch only the last 30 or 90 days can provide meaningful information, better than all-times. <img width="1471" height="1176" alt="image" src="https://github.com/user-attachments/assets/a14c9041-1f9f-4384-8573-0e3a26d99944"; /> Regarding the Usage Server: - It is responsible for most of the load mentioned above. - Its daily processing already takes about 8 hours (roughly 8 PM to 4 AM). - This suggests it may be processing all historical data each time, rather than just the incremental changes since the last run. - I may be mistaken, but it’s hard to believe our daily data volume alone would justify this duration. - The `cloud_usage` database is already 15 times larger than the `cloud` database. <img width="1132" height="449" alt="image" src="https://github.com/user-attachments/assets/78effad0-1732-4ced-9f71-33225162a3a0"; /> I could go into lock times or wait events, but they seem minor compared to the Usage Server activity and the load of the `usage_event` table. Please let me know if my understanding is incorrect and why. I’d rather be proven wrong and better understand what’s happening. GitHub link: https://github.com/apache/cloudstack/discussions/12159#discussioncomment-16331675 ---- This is an automatically sent email for [email protected]. To unsubscribe, please send an email to: [email protected]
