Skip navigation links

MySQL Forums :: Performance :: Optimize multiple writes on the same row


Advanced Search

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


Subject Views Written By Posted
Optimize multiple writes on the same row 1292 Francis Page 03/14/2012 02:16PM
Re: Optimize multiple writes on the same row 485 Rick James 03/15/2012 10:41AM
Re: Optimize multiple writes on the same row 597 Francis Page 03/16/2012 05:31AM
Re: Optimize multiple writes on the same row 447 Rick James 03/18/2012 10:06AM
Re: Optimize multiple writes on the same row 566 Francis Page 03/19/2012 08:33AM
Re: Optimize multiple writes on the same row 551 Rick James 03/20/2012 11:32AM
Re: Optimize multiple writes on the same row 434 Francis Page 03/22/2012 11:49AM
Re: Optimize multiple writes on the same row 645 Rick James 03/23/2012 11:35AM


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.