• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Monitor records in Mysql

 
Harish Shivaraj
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all.

Im quite new to the concept of databases. As a matter of fact my first ever project. Need to learn quick. And this is my first hurdle. Any pointers to possible solution would be great.

I have a table with the set of records which potentially will increase in time to 100 of 1000's. I need to monitor these records every second. That is each records within the table has an expiry time and when timer expires the record must be deleted.
Is there a watch dog process or something similar exists. Ofcourse i could query the entire table and parse through every record, but i dont think that is efficient?

Thanks guys!

 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Harish,
Why do you have to delete them every second? This seems like an inefficient way of dealing with many problems.

For example, if the reason is to only query records before the expiration time, you could use a view that takes that into account and do a nightly purge of the underlying table's data.
 
Harish Shivaraj
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There are a few reasons, consider a scenario where you have a record and record should exists in table for 2 hrs. The time it passes the 2hrs; I may either have to update the record or delete the record of the table.

I suppose I could have a column

timestamp - this would be the time when the record was inserted
expiry time - this would be the time when the record will expire (timestamp +2hrs)

And I could query every 5secs to check if any record.expirytime <= now. And delete them all.

does that sound any sensible at all ?
and also is query database often is an inefficient?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 35279
384
Eclipse IDE Java VI Editor
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That's not a business reason though.

You could have a stored procedure run every second. That's wasteful though and I suspect a poor design. That's why I ask what you are trying to accomplish. For example, "I need users to never see expired data"
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Harish Shivaraj wrote:There are a few reasons, consider a scenario where you have a record and record should exists in table for 2 hrs.


As Jeanne says, you need to explain why the records should exist for only 2 hours.

If, as suggested, it's so the users cannot see them then that's a query thing. It's used all the time. Databases are very good at "windowing" over date/time ranges.
If the data is then not really needed you can simply run a purge every night to reduce the number of rows.
 
Harish Shivaraj
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The application is this. Im trying to simulate a car parking charges. In which the every car gets two hour free parking and after the two hour the clock starts to tick. This is the application.

So I have one table called parking which has all the information about when the car first entered the car park. That would be the first time-stamp and after 2hrs and I need to notify the user and start charging for the car park.

This was the reason why I wanted to find out any record in the parking table with the expired time should get all the relevant information and notify the user.

Is there an elegant way solve the issue I have?

Thanks for very suggestions so far.


 
Roel De Nijs
Sheriff
Posts: 10666
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Harish Shivaraj wrote:does that sound any sensible at all ?
and also is query database often is an inefficient?

To be honest: with the given details it's impossible to tell if this implementation makes sense or not.

Let's say I have a database table with last minute holiday deals. Each deal is valid for a given period (2 hours, 14 hours, 1 day, 1 week,...). Therefore the table has a column expiry_datetime with a datetime value when each deal will expire. When I want to list all available last minute holiday deals to the user, I select only the records with expiry_datetime > now. Using this query all records will be in the table (even the expired deals) but the user will only see the available ones. No need to delete records from this table.

Also please note that a database can handle tables containing millions of records just fine. The performance of your queries will depend on much more than row count: e.g. number of joins, how your indexes are set up, the system properties of database server (how much ram, number of CPUs, type and speed of hard drives,...), network latency,... So there's actually no reason to delete rows just to keep row count as little as possible, because a database is used to handle large amounts of rows (data).
And if you really want to delete rows, it makes much more sense to run some job at a given time interval (each night, week, month,...) to remove (archive) all records which have expired more than e.g. 6 months ago (as Dave already suggested).

So both your posts describe how you want to implement a business requirement. But before we can give you any advice about this implementation, we need to know what this business requirement is all about. It's impossible for us to give you good advice, if you don't tell us what you are trying to achieve as Jeanne (and Dave) already mentioned as well. So please spill the beans and TellTheDetails.

Hope it helps!
Kind regards,
Roel

[edit] Oops, while I was typing this reply you have already spilled the beans and explained the details...
 
Roel De Nijs
Sheriff
Posts: 10666
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Harish Shivaraj wrote:So I have one table called parking which has all the information about when the car first entered the car park. That would be the first time-stamp and after 2hrs and I need to notify the user and start charging for the car park.

That's a good description of what you are trying to implement

Harish Shivaraj wrote:Is there an elegant way solve the issue I have?

I think you have several alternatives to implement this behavior. And one might be more elegant than the other, another one might be easier to implement but has a bit more overhead,... I'll provide one alternative, maybe others will provide a different solution.

Assume the following columns in table parking:
  • parking_id: unique id of a parking record
  • parking_start: datetime value when car enters the parking
  • parking_start_charge: datetime value when free parking has ended (automatically calculated: parking_start + 2 hours)
  • parking_end: datetime value when car leaves the parking
  • notification_sent: flag to indicate if a notification was sent to inform the user free parking has ended
  • payment_sent: flag to indicate if a payment was sent to charge the user for parking his car


  • You have one task (thread) which takes care of sending notifications. You query the table parking and get all records where notification_sent is false and parking_start_charge >= now. For each record returned by this query, you perform the following business logic:
    a) parking_end is null: the user hasn't left the parking yet, so you send a notification to the user and set notification_sent to true (using an UPDATE statement).
    b) parking_end is not null: the user has already left the parking during his free parking time, so you don't need to send a notification and the user doesn't need to pay. So you need only to set both notification_sent and payment_sent to true.

    Another task (thread) is responsible for charging the user for parking his car. You query the table parking and get all records where payment_sent is false and parking_end is not null. For each record returned by this query, you calculate the parking fee for this user (using parking_start_charge and parking_end), sent him a payment request and set payment_sent to true.

    Because you know have two tasks, you can schedule each task at a different time interval: e.g. sending notifications every 5 seconds whereas sending payment requests happens every 15 minutes.

    Hope it helps!
    Kind regards,
    Roel

    PS. As you can see I didn't mention a DELETE statement in the above explanation, because in my opinion it's not required for this use case
     
    Dave Tolls
    Ranch Hand
    Posts: 2112
    16
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Harish Shivaraj wrote:The application is this. Im trying to simulate a car parking charges. In which the every car gets two hour free parking and after the two hour the clock starts to tick. This is the application.

    So I have one table called parking which has all the information about when the car first entered the car park. That would be the first time-stamp and after 2hrs and I need to notify the user and start charging for the car park.

    This was the reason why I wanted to find out any record in the parking table with the expired time should get all the relevant information and notify the user.


    Off the top of my head, I'd want to hold onto the data. Aren't the rows useful anyway, since you need to know when they entered the car park to know how much to charge when they leave?

    Mark them as "notified" or some such (a simple boolean column if the db has them) once they've been dealt with, so you can avoid querying them.
    Poll the table every how often you think makes sense (select where not notified and entry time > 2 hours ago).

    Archive off any rows each night that have their "time of exit" filled in.

    Again, just a quick brain dump...feel free to pick it apart!
     
    Ron McLeod
    Bartender
    Pie
    Posts: 1050
    65
    Android Eclipse IDE Java Linux MySQL Database Redhat
    • Likes 1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    In addition to Dave's comments, you might want to keep the expired data around for a while so that it can be used for other purposes such as generate business statistics, help resolve customer disputes (when did they actually enter the lot and start their free parking), aid in system troubleshooting, etc.
     
    Harish Shivaraj
    Ranch Hand
    Posts: 50
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thanks very much all.

    Really appreciate it. I shall try the suggestions

     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic