About the problem of real-time analysis and processing of 1 million device data！

[databodys]

I have a case where I need to monitor the current, voltage of 1 million devices, each device sends data approximately every 1 minute, I want to know the average voltage, average current, maximum voltage,maximum current and other indicators of each device in the 5-30 minute time window .
The data structure of the device is:

   create table dev_data  (
     dev_id int  #Device ID
     v double，           #Voltage value
     i double，            #Current value
    create_at timestamp #Timestamp
    )

I'm doing it now in the following steps:

1. one million device data per minute is written to the kafka cluster through the Internet.
2. Then create a materialized source in risingwave to establish a data link with kafka

create materialized source mydata(
        dev_id int, 
        v double,
        i double,
       create_at timestamp
 ) 
with ( 
        'connector' = 'kafka', 
        'kafka.topic' = 'mydata', 
        'kafka.brokers' = 'master:9092', 
        'kafka.scan.startup.mode'='latest' 
) 
row format json;

4. Create a time window materialized view:

create materialized view myview as 
         select  dev_id,avg(v) as v_avg, avg(i) as i_avg,max(v) as v_max,max(i) as i_max 
         from tumble(mydata , created_at, interval '5' minute) 
         group by dev_id ;

6. Query the statistical data of the specified device number or multiple device numbers.
   select * from myview where dev_id=100;
   The current running situation is that when I am checking the statistics of a certain device, the query speed is very slow, sometimes reaching nearly 10 minutes. I would like to ask if you have a better solution. Welcome to discuss together, very grateful!

[liurenjie1024]

Thanks for you interest, currently we are working on speeding up point query like yours, and hope to release in a few weeks.

[liurenjie1024]

I took a look at the query plan and it's corret, so the query performance is expected to improve. I'll do some experiments today to verify the result query performance today.

[liurenjie1024]

I did an experiment and it's quite fast to retrieve by id.

I think you may have some wrong setting as described in you question:

create materialized view myview as
select dev_id,avg(v) as v_avg, avg(i) as i_avg,max(v) as v_max,max(i) as i_max
from tumble(mydata , created_at, interval '5' minute)
group by dev_id , window_start;

select * from dev_id where dev_id = 100;

Please help to confirm. IIUC, in the above case we can't filter out ids and can only table scan, if the result set is huge, it's expected behavior.

[liurenjie1024]

cc @databodys

[databodys]

Thank you very much for your attention and reply. There is indeed a small problem. It is that 'window_start' is missed when copying. I create an index by id, and a single query is faster, but if there are tens of millions of records in the materialized view , query records of a certain period, or use "limit 100 offset 1000000", the query is still very slow, I would like to ask, whether this application scenario is supported by you, or there are other solutions, thank you!

[liurenjie1024]

Currently our system is designed for point query, so large scale scan is not supported efficiently now. If you do have strong requirements, please describe it and I would like to talk to our product team to work on it together.

cc @neverchanje @fuyufjh @TennyZhuang @hzxa21

[databodys]

cc @liurenjie1024