You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@paimon.apache.org by "yangyongyongyong (via GitHub)" <gi...@apache.org> on 2024/05/30 07:27:06 UTC

[I] [Feature] lookup join performance is particularly poor in the case of cache penetration [paimon]

yangyongyongyong opened a new issue, #3440:
URL: https://github.com/apache/paimon/issues/3440

   ### Search before asking
   
   - [X] I searched in the [issues](https://github.com/apache/paimon/issues) and found nothing similar.
   
   
   ### Motivation
   
   ```
   CALL sys.query_service('default.tb_fixed_bucket_primary', 1);
   
     -- 维表join测试效果
       use catalog default_catalog;
       CREATE TABLE orders (
               product_id bigint,
               msg string,
               proc_time as PROCTIME()
           ) WITH (
             'connector' = 'datagen'
             ,'number-of-rows' = '300000000'
             ,'rows-per-second' = '300000'
             ,'fields.product_id.min' = '0'
             ,'fields.product_id.max' = '20'
           );
       CREATE TABLE blackhole_table (
             product_id bigint,
             msg string,
             price DOUBLE
           ) WITH (
             'connector' = 'blackhole'
           );
       insert into blackhole_table select l.product_id, l.msg, r.price from orders l join `my_catalog`.`default`.`tb_fixed_bucket_primary`  FOR SYSTEM_TIME AS OF l.proc_time AS  r on l.product_id = r.product_id;
       -- 注意 `catalog名`.`database或namespace名`.`表名`    这里my_catalog就是paimon的catalog
       
       效果总结:
           如果 'fields.product_id.max' = '20'  则维表join速度: 9000c/s
           如果 'fields.product_id.max' = '2000000'  则维表join速度: 8c/s    -- 说明join不到的数据,lookup join service 没缓存.
           
       dim table only 10 line:
           Flink SQL> select * from `my_catalog`.`default`.`tb_fixed_bucket_primary`;
               +------------+-------+-------+
               | product_id | price | sales |
               +------------+-------+-------+
               |          2 |   2.0 |     2 |
               |          6 |   6.0 |     6 |
               |          8 |   8.0 |     8 |
               |         10 |  10.0 |    10 |
               |          1 |   1.0 |     1 |
               |          9 |   9.0 |     9 |
               |          3 |   3.0 |     3 |
               |          7 |   7.0 |     7 |
               |          4 |   4.0 |     4 |
               |          5 |   5.0 |     5 |
               +------------+-------+-------+
   ```
   issue:
       - left table id large than right paimon dim table id number, eg: 2000000 vs 10 ,then lookup join is slowly 8c/s,eg: enum 20 vs 10,it is fast,9000c/s. 
       - if cannot find k in paimon dim table, if query service cache (k,null)
       - if dim table is small, if paimon service load all data in memory or rocksdb.
       - last:   why Cache Penetration make look up speed only 8c/s
   
   
   
   
   
   
   
   
   ### Solution
   
   If the paimon dimension table is small, it is recommended to support full load and periodic synchronization, so that cache penetration does not affect performance
   
   ### Anything else?
   
   _No response_
   
   ### Are you willing to submit a PR?
   
   - [ ] I'm willing to submit a PR!


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@paimon.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] [Feature] lookup join performance is particularly poor in the case of cache penetration [paimon]

Posted by "yangyongyongyong (via GitHub)" <gi...@apache.org>.
yangyongyongyong closed issue #3440: [Feature] lookup join performance is particularly poor in the case of cache penetration
URL: https://github.com/apache/paimon/issues/3440


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@paimon.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] [Feature] lookup join performance is particularly poor in the case of cache penetration [paimon]

Posted by "yangyongyongyong (via GitHub)" <gi...@apache.org>.
yangyongyongyong commented on issue #3440:
URL: https://github.com/apache/paimon/issues/3440#issuecomment-2138903915

     load table into memory and update memory data via changlog ?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@paimon.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] [Feature] lookup join performance is particularly poor in the case of cache penetration [paimon]

Posted by "WencongLiu (via GitHub)" <gi...@apache.org>.
WencongLiu commented on issue #3440:
URL: https://github.com/apache/paimon/issues/3440#issuecomment-2139077942

   Hi @yangyongyongyong , thanks for the feedback! Based on your description, are you saying that the lookup join is extremely slow when the range of the `product_id` field in the `orders` table is much larger than the range of the `product_id` field in the `tb_fixed_bucket_primary` table? Could you please share the DDL statement defining the `tb_fixed_bucket_primary` table? We will reproduce and try to find the root cause.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@paimon.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] [Feature] lookup join performance is particularly poor in the case of cache penetration [paimon]

Posted by "yangyongyongyong (via GitHub)" <gi...@apache.org>.
yangyongyongyong commented on issue #3440:
URL: https://github.com/apache/paimon/issues/3440#issuecomment-2138858303

   The above code is flinksql


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: issues-unsubscribe@paimon.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org