MySQL Forums
Forum List  »  Performance

Re: Optimize multiple writes on the same row
Posted by: Francis Page
Date: March 19, 2012 08:33AM

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.

Options: ReplyQuote

Written By
Re: Optimize multiple writes on the same row
March 19, 2012 08:33AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.