Anomaly detection in a database

Question

We have a production database. The load on the database varies at different times. I want to identify anomalies; for e.g, the number of database processes responding to user queries at 9 am is 100 for a given day. If the number is 200, then it's an anomaly and as the DBA, we need to check the DB immediately. The goal is to identify a pattern and alert when there's an event outside this pattern.

Day  time  processcount label
Mon  09:00 100          Normal
Mon  09:05 150          Normal
Tue  09:00 200          Abnormal

I'm using pandas to collect the data but I'm not sure how to identify the pattern and report anomalies. The closest i could get is this thread How do I approach grouped anomaly detection?

Answer 1

There are some packages for anomaly detection such as Linkedin's Luminol (https://github.com/linkedin/luminol) or Microsoft's TagAnomaly (https://github.com/microsoft/TagAnomaly). Also you can use clustering algorithms for your features and detect outlier clusters. Or you can tag your previous anomalies to train your data (however its not possible most of the cases)

So my suggestion is prepare your data with features such that 5_min_window_processcount_mean, 5_min_window_processcount_std, 5_min_window_processcount_std and 5_min_lag_processcount_std etc.(get lagged and windowed features and then calculate their mean,std,median etc)

After that try clustering and check it if it can find anomalies or not. If not try to label your data with those features and try classification algorithms. Meantime you can use above packages with your features. (time based lag/window features are very important).

Answer 2

Anomaly detection is often treated as an unsupervised problem (no labels used for training). This can be clustering, density estimation, or one-class classification.

For time-series there exists dedicated methods for anomaly detection, such as autoencoders on sliding analysis windows. But it is always smart to try a non-time-series method first, as they are much simpler and usually more familiar.

With time-as-context modelling then you can use one of the standard anomaly/outlier models in scikit-learn. Split the time into its components, for example:

time_of_day, weekday, weeknumber

Depending on model you may want to one-hot-encode the weekday, as it can be seen as an ordinal feature. Alternatively you can split into just is_workday/not (and include holidays also).

The choice of time-interval to compute the features on can be quite important to good performance. If there is considerable natural variation, then 5 minutes might be too often. I would consider every 60, 30 or 15 minutes. You can then compute some summary statistics of the measurement points (5 mins or lower), and use those as the features.

queries_mean, queries_std, queries_min, queries_max 

Run one of the above scikit-learn models on these features from historical data get the anomaly scores. Plot the scores as a histogram, and set a threshold on the value to become your decision function for anomaly-or-not.

You should also plot the anomaly scores as a time-series, together with the input features and threshold, and see if known anomalies in the past were picked up OK.

Aside: It is highly desirable to have/build up a set of labeled anomalies for a validation set and testing set. To do hyper-parameter optimization (like selecting threshold) and estimating