You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@cloudstack.apache.org by "daviftorres (via GitHub)" <gi...@apache.org> on 2026/03/26 18:44:29 UTC
Re: [D] [MySQL] Pruning / Off-loading Database [cloudstack]
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 users@cloudstack.apache.org.
To unsubscribe, please send an email to: users-unsubscribe@cloudstack.apache.org