-1

I have a rather challenging requirement that I think I have designed correctly but am unsure as it violates a data normalization edict.

I am acquiring data from sensors at with a frequency of about 2 mins per sensor but there are many sensors (thousands). Most of the time the user wants to see how the system performs over the last hour or so but when there are issues they want to see historical data.

The challenge is that on one hand the immediate data is useful but then it becomes aggregated to max min and average values for the historical data.

After the hour the old data can be discarded as it is already ingested in another file/log that is archived.

My solution therefore is to put together a column based equivalent of a FIFO log where each time i update the hourly snap shot data I shift the oldest data out of the table.

It works really well and makes for really cool looking sparkgraphs and makes the fetches for all the sensors hourly data fairly fast as I can select and process most of the rows in a few fetches and display them on the client.

The issue is that I have 60 columns (30 for the data and 30 for the timestamp) and a total of 70 columns in all.

When i try to join this with any other table it takes forever. So I am forced to add replicated data (sensor name, location name, computed values) from other tables and include it in to this hourly snapshot table.

I truly cannot think of any other way to implement this solution and ensure that the system will perform well.

When and if you respond please refrain from patronizing lectures about X normal form etc. I know the rules, I just don't know if breaking them in this situation is the best way forward.

I have a trigger which both shows the fields and how it is dynamically updated from the data ingestion table

INSERT INTO  now
 ( id    , contname , tempname
 , tempvalu , huminame, humivalu , pm25name , pm25valu ,
 data  , createdate,
 d00   , t00  ,
 d01   , t01  ,
 d02   , t02  ,
 d03   , t03  ,
 d04   , t04  ,
 d05   , t05  ,
 d06   , t06  ,
 d07   , t07  ,
 d08   , t08  ,
 d09   , t09  ,
 d10   , t10  ,
 d11   , t11  ,
 d12   , t12  ,
 d13   , t13  ,
 d14   , t14  ,
 d15   , t15  ,
 d16   , t16  ,
 d17   , t17  ,
 d18   , t18  ,
 d19   , t19  ,
 d20   , t20  ,
 d21   , t21  ,
 d22   , t22  ,
 d23   , t23  ,
 d24   , t24  ,
 d25   , t25  ,
 d26   , t26  ,
 d27   , t27  ,
 d28   , t28  ,
 d29   , t29  ) VALUES (  

  SUBSTRING(
    @NEWdata,LOCATE('data',@NEWdata,20) + 8 ,8)  , @NEWcontname , @NEWtempname 
   @NEWtempvalu , @NEWhuminame , @NEWhumivalu , @NEWpm25name , @NEWpm25valu ,
    @NEWdata ,  @NEWcreatedate,
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate, 
    @NEWdata ,  @NEWcreatedate)    ON DUPLICATE KEY UPDATE 
    now.d29 =  now.d28  ,  now.t29 =   now.t28  , 
    now.d28 =  now.d27  ,  now.t28 =   now.t27  ,
    now.d27 =  now.d26  ,  now.t27 =   now.t26  ,
    now.d26 =  now.d25  ,  now.t26 =   now.t25  ,
    now.d25 =  now.d24  ,  now.t25 =   now.t24  ,
    now.d24 =  now.d23  ,  now.t24 =   now.t23  ,
    now.d23 =  now.d22  ,  now.t23 =   now.t22  ,
    now.d22 =  now.d21  ,  now.t22 =   now.t21  ,
    now.d21 =  now.d20  ,  now.t21 =   now.t20  ,
    now.d20 =  now.d19  ,  now.t20 =   now.t19  ,
    now.d19 =  now.d18  ,  now.t19 =   now.t18  ,
    now.d18 =  now.d17  ,  now.t18 =   now.t17  ,
    now.d17 =  now.d16  ,  now.t17 =   now.t16  ,
    now.d16 =  now.d15  ,  now.t16 =   now.t15  ,
    now.d15 =  now.d14  ,  now.t15 =   now.t14  ,
    now.d14 =  now.d13  ,  now.t14 =   now.t13  ,
    now.d13 =  now.d12  ,  now.t13 =   now.t12  ,
    now.d12 =  now.d11  ,  now.t12 =   now.t11  ,
    now.d11 =  now.d10  ,  now.t11 =   now.t10  ,
    now.d10 =  now.d09  ,  now.t10 =   now.t09  ,
    now.d09 =  now.d08  ,  now.t09 =   now.t08  ,
    now.d08 =  now.d07  ,  now.t08 =   now.t07  ,
    now.d07 =  now.d06  ,  now.t07 =   now.t06  ,
    now.d06 =  now.d05  ,  now.t06 =   now.t05  ,
    now.d05 =  now.d04  ,  now.t05 =   now.t04  ,
    now.d04 =  now.d03  ,  now.t04 =   now.t03  ,
    now.d03 =  now.d02  ,  now.t03 =   now.t02  ,
    now.d02 =  now.d01  ,  now.t02 =   now.t01  ,
    now.d01 =  now.d00  ,  now.t01 =   now.t00  ,
    now.d00 =  now.data ,  now.t00 =  now.createdate,
    now.data = @NEWdata            , now.createdate =  @NEWcreatedate ,  contname = @NEWcontname ,
    now.tempname= @NEWtempname     , now.tempvalu= @NEWtempvalu ,  
    now.huminame= @NEWhuminame     , now.humivalu= @NEWhumivalu ,  
    now.pm25name= @NEWpm25name     , now.pm25valu= @NEWpm25valu ; 

Notice that the values cascade from 00 to 29 then out.

I also have a sensor definition table that includes sensor name, id and location, as well as a users table that stores privileges to these sensors on a per location basis.

My typical slow query is as follows

SELECT *
from now
WHERE now.id IN (
  SELECT   defs.id
  FROM    defs
  WHERE EXISTS (
    SELECT   users.name
    FROM  users  WHERE  defs.name  =  users.name   AND users.uid   > 0
  )
  order by defs.name
)
limit 0, 20

These queries are generated from MySQL node.js, so they are sent (I assume) as plain text strings to the MySQL server.

(I have no execution plans right now but will try to get some tomorrow.)

  • 4
    Can you show us how the SELECT that JOINs the tables look like? And how your tables and indexes are defined? The execution plans for the slow queries will also be helpful? – joanolo Jun 17 '17 at 16:43
1

In one hour, there will be 30 readings for each of 'thousands' of sensors? Then you summarize the data and toss it?

Two tables:

  • The raw data for the hour
  • Summaries of older data

The raw data has (sensor, time, value), so 30 x thousands of rows. Not columns. Normalization is optional at this point.

Suggest two copies of the first table -- one for gathering, one for summarizing and tossing. This way, the two tasks do not collide. See RENAME TABLE for swapping.

When summarizing into the archive table, normalization may be important because of the bulk. It is unclear why there might be a problem with JOINs. Aren't you fetching one sensor at a time? Or are you fetching 'thousands' at a time?

My discussion of high speed ingestion talks about ping-ponging the first table. The discussion is aimed at far higher speed ingestion than you have, but the ideas seem to apply.

The summarization query would be something like

INSERT INTO Archive
    (hr, sensor, min, max, avg, cnt)
    SELECT
        LEFT(timestamp, 13) as hr,
        sensor,
        MIN(value),
        MAX(value),
        AVG(value),
        COUNT(*)
    FROM Gather
    GROUP BY 1;

(There are issues of dealing with spilling from one hour to the next; left as an exercise to the reader. Also keyword issues (min vs MIN).)

  • For the ingestion process I am intrigued by your high speed ingestion link. I need to study it further to see if i can map it to my existing config. Re: queries. Typically I am requesting between 10 and 100 records out of many thousands which need to be filtered based upon user authorization requirements, search filters and a special requirement of name to Id mapping. For the time being the elimination of all table joins has dramatically improved performance . For some reason subqueries are much more performant. – Lost In Space Jul 2 '17 at 5:28
  • There is no "single answer" when dealing with joins versus subqueries. Some cases work much better with join; some with derived table; some with subqueries in the select. – Rick James Jul 2 '17 at 12:51
  • I will be trying another modification of the table structure to see how it performs. Instead of 30 data columns and 30 date columns I will prepend (or append depending upon performance) the date to the string value of each of those data columns. The advantage of this new structure is that it will store 60 actual data/date value pairs in one table row. This effectively doubles the 'now' timeframe and simplifies both triggers and decomposing in the client. Will let you know how it performs. – Lost In Space Jul 5 '17 at 20:40
  • Has anyone else seen a record-based rolling log-type function used anywhere else? Intuitively it seems that this type of rolling column/field based row/record would be useful for many time-based apps. – Lost In Space Jul 5 '17 at 20:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.