Friday, July 29, 2011

Mysql auto audit column

Thanks to my colleague Deepak, today I learnt a new thing. If you want a column in your table like lastModifiedTime that gets inserted when the row is created and also auto updated whenever someone updates the row then traditionally the only way to do this was to use a trigger. I have used oracle for 4-5 years so I thought this is not possible to do in DDL and the trigger is the only way. But Mysql has this magic. You can create a column like


And this column will have current datetime on insert and will get updated with current datetime anytime a mutation to that row occurs. Wow no more triggers and this column is self maintained so no more worry about some dba disabling the trigger and did the update to get data inserted quickly.

Scaling graphs in graphite monitoring tool

We use graphite for monitoring trends. One requirement is to spot an outlier from all nodes. So for e.g. we are monitoring apache threads at front apache and the Level2 apache. The default graph generated by graphite would show graphs but it would show all nodes in different scale. See below graph where open file handles on all nodes are shown differently

Every graph is showing data and just looking at it cant show the outliers as a graph with 2K as higher limit shows simliar to graph with 12K.

So passing yMin=0&yMax=75 to the graphite url did the trick. Here is an example of apache level2 threads.

From this clearly we can see the 3,4,7 are the outliers.