MySQL - Time/Date/String for datatype?

[Zxian]

I'm trying to setup some simple monitoring software here, and I figured that the easiest way of keeping track of all the various servers we have would be to store their system status in a MySQL database.

I'm trying to figure out the easiest way of storing the individual machine uptimes. The TIME datatype seems ideal, until I read about the restriction of only storing 34 days in the day portion of it. DATE or TIMESTAMP aren't really much better, since I want something in the form of days-hours-minutes-seconds (roughly).

I'm starting to think that simply storing the uptime as a string is easiest...

[CoffeeFiend]

I'm trying to setup some simple monitoring software here, and I figured that the easiest way of keeping track of all the various servers we have would be to store their system status in a MySQL database.

If you want something simple (and great), then try Nagios (or munin, or cacti). Some people even just use very simple SNMP (or WMI polling) graphing using MRTG/RRDTool. Or there's always the MS-way: buy licenses for MOM...

But if you really want to keep track of uptime only, then you can indeed write some scripts to poll computers and dumping the data somewhere (including a database). I'm not sure I'd use MySQL for something so trivial either (just keeping a computer name/uptime "pair") -- SQLite is a good fit for such things.

As for how I'd store it in a database, I'd probably convert the uptime in numeric format first before storing it (much like the unix time format). Keeping it as a string is an option too for sure, if you want something very simple and don't want the trouble of parsing/converting the string to a number first.

[Zxian]

Thanks for the suggestions.

I don't think any of the pre-built solutions are really viable for me, since I'm coupling our scheduling software into this web-app. For the record, there are about 30 Linux servers (all running CentOS), and I want/need to monitor more than just uptime data. I'm keeping track of the amount of memory that a user has requested on a particular server, along with the actual memory usage (same goes for swap), system load, kernel versions, disk usage, etc etc. All of this information is currently pushed to the MySQL server on our management node every 15 minutes.

The uptime program in Linux has the following kind of output:

[root@schroedinger scripts]# uptime
 16:49:24 up 93 days,  7:55,  9 users,  load average: 0.06, 0.05, 0.06

After I've cut it down as I want it, it looks something like this:

[root@schroedinger scripts]# uptime | awk '{print $3" "$4,$5}' | cut -d"," -f1,2
93 days, 7:56

My main problem was that the TIME datatype in MySQL can't handle days above 34. After working with this for a while, I don't think there's going to be much use for me to store it in a date format, since I probably won't be doing any manipulation of the data afterwards.

[CoffeeFiend]

I don't think any of the pre-built solutions are really viable for me, since I'm coupling our scheduling software into this web-app.

Ah, I see. A lot of fun

My main problem was that the TIME datatype in MySQL can't handle days above 34. After working with this for a while, I don't think there's going to be much use for me to store it in a date format, since I probably won't be doing any manipulation of the data afterwards.

Well, what I was trying to suggest WRT that, was converting days hh:ss in a equivalent number (i.e. just seconds -- lots of them), which is a lot easier to store. It's also quite easy to transform it back in any format you please, using any language/tools i.e. "93 days, 7:56" being 134396 minutes, or 8063760 seconds. That should be easy to store in any database, including MySQL (a plain old INT would work).

[Zxian]

Just figured I'd update people on this. I managed to finish things (at least for the major setup). jcarle helped me with making it pretty.

The cluster we've got uses Cluster Resources Maui scheduler. Part of the information on the page comes from the scheduler (letting us know what resources have been requested), and part of it comes from the individual nodes (updated via cron every 5 minutes or so). All the colors are dynamically chosen to help make the quick overview of status a lot easier. If any nodes are down (determined both by the scheduler and if they haven't been updated in more than 10 minutes), the entire row goes red.

I ended up taking your suggestion in the end and simply storing the uptime in seconds (taken from /proc/uptime) instead of relying on the uptime command. The formatting of uptime changes based on whether or not the system has been up for more than a day... which makes things ugly.

If anyone wants the PHP code, just ask.

(IP is hidden for obvious purposes...)

schroedinger.htm