Faster Data Reduction and Smoothing for Analysis & Archival in MySQL

We will discuss the technique of data smoothing in MySQL using group averaging and possibly running/moving averages. Data smoothing is the act of translating high-resolution data into lower resolution data for lower frequency trend analysis over a long period, and more importantly, to reduce unneeded resolution for data archival storage. Topics coverered in this session will include business needs and use cases, techniques for different normalization needs and different data sets, and a benchmark comparison between scripted/programmed solutions verses database-side, SQL query-side solution.

Any application that performs frequent polling for data and archives this data for long (or infinite duration) has run into the situation of ‘too much data’. Tables become huge and unsearchable, performance goes down, and over time – the resolution of this data becomes worthless compared to the cost to store it.

We will discuss the simple ‘gauge data’ smoothing process, the more complicated ‘counter data’ smoothing process, and the ‘waterfall smoothing’ technique for multiple levels of archival and smoothing. We will also discuss optimal indexing for efficient grouping. If time permits, we will show examples of stored procedures for the very tough methods, and also look at solving moving averages in SQL.

Many times developers fall back on programming and scripting languages such as php, perl, or python to solve this problem, as it can be hard to do strictly in SQL, but here we show some comparative run times that show magnitudes of performance gain.

If time permits, we will also go into using partitioning for maintenance and quick deletion of archived data, and using stored procedures for more complex smoothing applications.