Re: Optimize multiple writes on the same row
OK, first of all, the operations are not all inserts, there's a lot of updates too. The table will grow and then stabilize to about 5M rows, after that, almost all queries are updates. It's a statistic table, where every user has a row, so for every new user, there's an insert. Then, when an existing user sends new stats, that will only update the existing row.
About the 1000 queries per second, that's not an average, but we have to support that many queries per second and even more.
Of course, I'm not updating the sum and average every time a row is updated or inserted. That's just impossible. I do it periodically, every 60 seconds for instance. But that's why I thought about using a running sum/average algorithm, using only the previous sum/avg and the new data. That would keep my sum/avg up to date in real time, without having to scan every rows.
The problem with this solution is that every insert/update queries are trying to write the new sum/avg at the same place in the database and they can't be executed simultaneously anymore. When we have peeks of 1000+ queries/sec, that stalls DB server.
The current solution with a periodic update works well, but if it was possible to use a running algorithm, that would be a lot less CPU intensive and it would be real time.