Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an application (ProcessMonitor) that monitor some constantly running process at fixed interval (like 30 seconds, but can be changed by user) and store the process status to a database table, the table looks like this:

| id         | int             | NO   | PRI | NULL    | auto_increment |
| pid        | int             | YES  |     | NULL    |                |
| pstatus    | int             | YES  |     | NULL    |                |
| timestamp  | datetime        | YES  |     | NULL    |                |

Obviously, this table grows very fast because for every single process, there will be 2880 new records inserted into table per day if the interval is 30 seconds. Given the fact that the process's status is not changed very often may keep one state for several hours), I decide change the logic of ProcessMonitor to write to database only when the status of process changes using the same table design. There are two problems for the new approach:

  1. There is no way to track when and how long ProcessMonitor is stopped.
  2. Unable to keep track of the change of monitoring interval.

I am feeling that I may need to change the design of the table, but I've not found a good solution, could anyone suggest me a better way to store the process status?

share|improve this question

migrated from stackoverflow.com Apr 26 '13 at 9:32

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers

You'll be averaging about a million records a year, which shouldn't be a problem given the table is fairly narrow. I don't see anything wrong with your design (other than a NULLable primary key?). You should be able to easily track when and how long each process takes by comparing timestamps. This is accomplished using a self join.

share|improve this answer
    
Thanks for your reply, I may have over 30 processes to monitor, and the interval could be shorter then 30 second, so the table could be much bigger. Another reason is that this table will be queried very frequently, I think to keep it small will help the performance. –  eric2323223 Apr 26 '13 at 3:18
    
What do you mean it will be queried frequently? As in there will be records inserted or there will be selects hitting the table? –  Kermit Apr 26 '13 at 3:21
    
It will be read operation. –  eric2323223 Apr 26 '13 at 3:23
    
You can also do replication. –  Kermit Apr 26 '13 at 4:34
add comment

You could probably add another field like stopTimestamp to record the date and time when a processed is stopped. And by the way is the timeStamp field is the date and time that the process start? If yes then you could probably renamed it to startTimeStamp to contrast it with stopTimeStamp if ever you are going to implement it.

And with that then you could calculate the interval between start and stop of a particular process and you could even calculate the interval of all processes for a given date.

And like @FreshPrinceOfSO the primary key id should not be a NULL therefore NOT NULL would be the label for that given it is also by the way AUTO INCREMENT obviously it would not be NULL.

Edit:

You have the right point there @FreshPrinceOfSO

Then if that's the case you could probably add another field like Status. So, for if a process started then it has the 'Start' status and if it stopped it has the 'Stopped' status. So, you won't have to search but simply insert which is much quicker as you said. However, this would also mean by the way that you have double the number of records? But again you could calculate intervals based on process ID and date.

share|improve this answer
    
My only reservation about a stopTimestamp is being able to a) find that record, b) inserts are quicker –  Kermit Apr 26 '13 at 3:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.