Looking for Real Time Solution : Efficient DB interaction

On the container one java pplication name as sbs is deployed.On the top of it 3 other java 开发者_高级运维applications are deployed let say A, B, and C.

Now Application A, B, and C hits one method name as Sing(id) of application sbs which is used to play a message.

sing(id) method of application sbs, hits around 200 times per second and play a message for all.

My Problem :

DB Table :

Play ID : Id of play

Count : Total number of request for this play id

Last Date Time : last date and time when its played

Play ID : 1

Count : Total 66

Last Date Time : 10/8/2011 231145 (something)

now update the table

Play ID : 1

Count : Total 92

Last Date Time : 10/8/2011 231344 (something)

I have to update this table and as i mentioned earlier this field changes 200 times per second.Aim is to know how many times this play id has been played at this time.

Ho do i do that ?

It is not possible to hit database 200 times per second.So i can hit the datebase in every 4-5 seconds(periodicaly) and update the count status of the play id.How do i do that application wise.Need to increment the count 200 times in every second and store value somewhere and update the database...something like this....whats the best efficient approach....

Let me know if you have any confusion

Looking for your valuable inputs. Thanks in advance.

There is something rather fishy about the problem as you have stated it:

• If it is acceptable to only update the table every 4 or 5 seconds, then you will inevitably be "missing" updates, and the value of count and last_date_time will be out of date if you queried it.

• Worse still (maybe) if your system crashes 4.9 seconds after the last update, then your system will entirely lose a few count ticks.

If these anomalies matter, then you've got no choice but to synchronously persist the values of count and last_date_time on each sing(...) request.

But if they don't matter, why are keeping this information in the database at all? Why don't you just keep it all in memory and accept that the counts reset when the server crashes and restarts? Or if you need to keep the counts for accounting purposes, why don't you write the "play(id)" events to a special logfile and generate the accounts by an offline scan of the logs.

By the way, 200 updates per second is not unreasonable, especially if the table is small and you do some tuning.

I can afford 4-5 seconds delay in the updation of records. .... If server crashes where are application is deployed then and we can put some logic ..something like....if count value is zero for the particular play id...fetch the last count from DB and then increment it to one.

There is a significant flaw in this approach. In the interval between the last update and the application crashing, there could be zero "plays", or one "play" or many "play"s for any given song. When the application comes back, it simply won't know what those "plays" were ... and it can't know unless they were logged somewhere.

We are keeping this information in the database becuase by these records we need to fetch records like how many play id played for this time stamp ( something ).

The big question ... which you haven't answered ... is whether it matters if you fail to count some "plays" occasionally.

• If it doesn't matter, then caching and doing an update every few seconds is OK.
• If it really does matter, then that solution is unacceptable, because it will lose some plays under certain circumstances.

The alternative is (as I said before) is to tune the database; e.g. choose the most appropriate database engine / table types / indexes, and tune the SQL or stored procedure used to do the update. 200 updates per second is not excessively expensive if you tune the database properly.

If i come across with the solution in which my performance does not impact much then obeviously not.

If that is (really) the case, then just go with the "save every 5 seconds" approach. Or if you want better performance "5 minutes" or "5 hours".

Actually don't think you really understand what I'm asking ... because it is NOT obvious (to someone who doesn't understand your business) that it doesn't matter, and performance really shouldn't be relevant to the answer at all. It either matters, or it doesn't.

Let me put the question another way:

"Would it hurt your business if the counts were inaccurate"?

Well you can hit your db 200 times per second or more, but in your scenario here that's not needed. Instead make a in memory synchronized counter (like a singleton class with an atomic integer), that gets incremented each time the song is played, and then updates the count value in the db every like 30 seconds or something. I've actually done something similar to this, it works great. Just ensure that you have one counter class instance per countable entity (song i guess here), and that incrementing the counter value is thread safe (atomic integer should take care of that)