risingwave: why source throughput drops down to zero after 5 mins? is this a bug or a performance tunning problem?

Describe the bug

image

Error message/log

No response

To Reproduce

CREATE MATERIALIZED VIEW  cleaned_meter_data AS
   select marked_data.*, 
           CASE
            WHEN marked_data.anomaly = 1 THEN 
                CASE
                    WHEN marked_data.prev_power IS NOT NULL AND marked_data.prev_power_2 IS NOT NULL AND marked_data.prev_power_3 IS NOT NULL THEN 
                        CASE 
                            WHEN marked_data.prev_power <= marked_data.run_cap * 5 AND marked_data.prev_power <= marked_data.rated_cap * 2 THEN marked_data.prev_power
                            WHEN marked_data.prev_power_2 <= marked_data.run_cap * 5 AND marked_data.prev_power_2 <= marked_data.rated_cap * 2 THEN marked_data.prev_power_2
                            WHEN marked_data.prev_power_3 <= marked_data.run_cap * 5 AND marked_data.prev_power_3 <= marked_data.rated_cap * 2 THEN marked_data.prev_power_3
                            ELSE NULL
                        END
                    ELSE NULL
                END
            WHEN marked_data.anomaly = 2 THEN 0
            WHEN marked_data.anomaly = 3 THEN 
                CASE
                    WHEN marked_data.prev_power IS NOT NULL THEN marked_data.prev_power
                    ELSE NULL
                END
            ELSE marked_data.power
         END AS fitted_power
         FROM
   (SELECT 
        da.cons_id,
        da.meter_id,
        md.datatime,
        md.data as power,
        da.run_cap,
        da.rated_cap,
        LAG(md.data) OVER (PARTITION BY md.meterId ORDER BY md.datatime) AS prev_power,
        LAG(md.data, 2) OVER (PARTITION BY md.meterId ORDER BY md.datatime) AS prev_power_2,
        LAG(md.data, 3) OVER (PARTITION BY md.meterId ORDER BY md.datatime) AS prev_power_3,
        CASE
            WHEN md.data IS NULL THEN 3 
            WHEN md.data < 0 THEN 2 
            WHEN md.data > da.run_cap  * 5 OR md.data > da.rated_cap  * 2 THEN 1 
            ELSE 0 
        END AS anomaly
    FROM device_accounts da 
    JOIN meter_data md ON md.meterId = da.meter_id
    where md.itemId = '2004_5'
   ) marked_data;

Expected behavior

No response

How did you deploy RisingWave?

No response

The version of RisingWave

No response

Additional context

No response

About this issue

  • Original URL
  • State: open
  • Created 2 years ago
  • Comments: 16 (7 by maintainers)

Most upvoted comments

     LAG(md.data) OVER (PARTITION BY md.meterId ORDER BY md.datatime) AS prev_power,
        LAG(md.data, 2) OVER (PARTITION BY md.meterId ORDER BY md.datatime) AS prev_power_2,
        LAG(md.data, 3) OVER (PARTITION BY md.meterId ORDER BY md.datatime) AS prev_power_3,

well, i found something about the window . does this means that the window size is unbounded? @lmatz

No. lags with constant offsets are not “unbounded”.

However, by default we do cache all entries of a partition when the partition key is cached. So basically there’re two levels of cache here: First, partition key -> partition (contains partition entries cache); second, inside partition entries cache order key + pk -> row. By default the second level contains all entries in cached partitions.

Two things worth trying here:

  1. Change the over window cache policy:

    set rw_streaming_over_window_cache_policy = recent; -- or recent_first_n or recent_last_n, depending on your workload pattern
    
  2. Create a base MV with order by <partition_key>, <order_key>, and create the desired MV on top of that. So that rows input to OverWindow operator should be ordered and can achieve better performance.

Hopefully these methods can help reduce the jaggies.