I apologize in advance if this is a repeat. I'm not really sure how to properly ask for what I'm running into.
I have a large InnoDB table set up. I have 3 sources that all INSERT concurrently at a moderately high volume (300-500 INSERT/s).
I have a PERL script running on a cron job every 5 minutes that aggregates data from the last time it ran. It keeps track of the last record it processed by storing the auto_increment value of the last row in a metadata table.
The aggregates are always off. But not by much. This has been so frustrating because it's just plain simple math (SELECT account_id,sum(sold) GROUP BY account_id). I have a suspicion that it has something to do with the transaction isolation (repeatable-read).
I recently found FlexViews which looks very cool and might address my problem. But I was wondering if anyone could:
- Tell me what is going on here. Why is this happening?
- What can be done to produce 100% accurate aggregates
I'm like 95% sure the auto_increment thing along with transaction commit ordering is hosing me up, but I don't understand enough about DBs to really ask the right question.
Oh, one thing to note, I've already checked over the field types. This issues isn't the result of rounding.