How to design database table to store process status?

Question

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?

Answer 1

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.

Answer 2

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.