Query (0x7f33df840020): EXPLAIN WITH yarn_memory AS (WITH yarn_memory_ndt_test AS (WITH event_yarn AS (select * from looker.event_yarn where date(start_time)>'2022-05-08' ) SELECT (TIMESTAMP(DATE(event_yarn2.start_time ))) AS `start_time_date`, (event_yarn2.start_time ) AS `start_time_time`, (event_yarn2.finished_time ) AS `end_time_time`, substring_index(substring_index(substring_index(substring_index(substring_index(event_yarn2.name,'2019',1),'@',1),'/',1),':W=',-1),':',1) AS `job_name`, FLOOR((UNIX_TIMESTAMP(event_yarn2.finished_time)+300)/300) - floor(UNIX_TIMESTAMP(event_yarn2.start_time)/300) AS `intervals`, (date_add(date(event_yarn2.start_time),interval floor((TIME_TO_SEC(event_yarn2.start_time))/300)*300 second) ) AS `lower_bucket`, (cast(event_yarn2.memory_seconds as decimal(20,7))/(event_yarn2.elapsed_time))/1024 AS `interval_memory`, event_yarn2.elapsed_time/1000 AS `elapsed_seconds`, event_yarn_intervals.seq AS `seq`, COALESCE((0E0 + ( SUM(DISTINCT (CAST(FLOOR(COALESCE( event_yarn2.memory_seconds ,0)*(1000000*1.0)) AS DECIMAL(65,0))) + (CAST(CONV(SUBSTR(MD5( event_yarn2.id ),1,16),16,10) AS DECIMAL(65)) *18446744073709551616 + CAST(CONV(SUBSTR(MD5( event_yarn2.id ), 17, 16), 16, 10) AS DECIMAL(65))) ) - SUM(DISTINCT (CAST(CONV(SUBSTR(MD5( event_yarn2.id ),1,16),16,10) AS DECIMAL(65)) *18446744073709551616 + CAST(CONV(SUBSTR(MD5( event_yarn2.id ), 17, 16), 16, 10) AS DECIMAL(65)))) ) ) / (0E0 + (1000000*1.0)), 0) AS `memory_seconds` FROM event_yarn AS event_yarn2 LEFT JOIN looker.event_yarn_intervals AS event_yarn_intervals ON (FLOOR((UNIX_TIMESTAMP(event_yarn2.finished_time)+300)/300) - floor(UNIX_TIMESTAMP(event_yarn2.start_time)/300))>event_yarn_intervals.seq WHERE ((( event_yarn2.start_time ) >= ((DATE_ADD(CURDATE(),INTERVAL -28 day))) AND ( event_yarn2.start_time ) < ((DATE_ADD(DATE_ADD(CURDATE(),INTERVAL -28 day),INTERVAL 29 day))))) AND (if((substring_index(substring_index(substring_index(substring_index(substring_index(event_yarn2.name,'2019',1),'@',1),'/',1),':W=',-1),':',1)) in (select * from event_yarn_jobs ),1,0) ) = 1 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9) SELECT (date_add(yarn_memory_ndt_test.lower_bucket,interval yarn_memory_ndt_test.seq*300 second) ) AS `lower_time_interval`, COALESCE(SUM(yarn_memory_ndt_test.interval_memory), 0) AS `sum_memory_used`, COALESCE(SUM(case when yarn_memory_ndt_test.job_name = 'BulkPoWriter' then yarn_memory_ndt_test.interval_memory else 0 end ), 0) AS `sum_BulkPoWriter`, COALESCE(SUM(case when yarn_memory_ndt_test.job_name = 'bid-post-process-for-bid-recommender-for-multiplier-adjusted-segment' then yarn_memory_ndt_test.interval_memory else 0 end ), 0) AS `sum_bid_post_process`, COALESCE(SUM(case when yarn_memory_ndt_test.job_name = 'marin-tracker-hourlysessions-oozie' then yarn_memory_ndt_test.interval_memory else 0 end ), 0) AS `sum_marin_tracker_hourlysessions_oozie`, COALESCE(SUM(CASE WHEN (yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_kw_bid' OR yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_kw' OR yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_pt_bid' OR yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_cr' OR yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_pt' OR yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_sp' OR yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_gp' OR yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_gp_bid' OR yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_dt' OR yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_sl' OR yarn_memory_ndt_test.job_name LIKE 'test-marin-hybrid-odc-oozie-dim_dt_bid' OR yarn_memory_ndt_test.job_name LIKE 'BulkPoWriter_AD_CREATIVES' OR yarn_memory_ndt_test.job_name LIKE 'BulkPoWriter_TRACKING_VALUES' OR yarn_memory_ndt_test.job_name LIKE 'BulkPoWriter_KEYWORDS' OR yarn_memory_ndt_test.job_name IN (...) )) THEN yarn_memory_ndt_test.interval_memory ELSE NULL END), 0) AS `sum_other` FROM yarn_memory_ndt_test GROUP BY 1) SELECT (DATE(date((CAST(yarn_memory.lower_time_interval AS CHAR(19)))) )) AS `yarn_memory.date_key_date` FROM yarn_memory GROUP BY 1 ORDER BY (DATE(date((CAST(yarn_memory.lower_time_interval AS CHAR(19)))) )) DESC LIMIT 500
|
Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on
|