TreasureData Tech Talk 2022 Hive Distributed Profiling System in Treasure Data @okumin(Shohei Okumiya) English Version

© 2022 Treasure Data, Inc. Public 2 Agenda - TD’s Query Engine Service - Overview of HDPS(Hive Distributed Profiling System) - Use cases of HDPS - How to implement HDPS - Future work

© 2022 Treasure Data, Inc. Public 3 TD’s Query Engine Service

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public TD’s Query Engine Service 4 Web UI CDP BI tool SQL etc. Plazma (TD’s Storage) Managed by Query Engine team Data

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public - Customers can submit interactive queries from our Web UI 5 Web UI for Analysts

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public - Treasure Data Workflow, a fully manage Digdag, is available 6 Workflow Engine for Data Engineers

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public - CDP allows customers to analyze their data without writing SQLs 7 Compile CDP(Customer Data Platform) for Marketers

© 2022 Treasure Data, Inc. Public 8 Overview of HDPS(Hive Distributed Profiling System)

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public - CPU/Method Profiling - Which methods or lines dominate CPU time? 9 What’s Profiling?

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public - HDPS = Hive Distributed Profiling System - HDPS is designed as a highly scalable profiling tool for Hive on Tez - We can store a massive amount of stack traces - We can analyze massive stack traces in a flexible and scalable manner Hive Distributed Profiling System(HDPS) 10 Hadoop Metrics DB (Plazma) Hive Query Hive Query Hive Query Hive Query Stack Traces Aggregation Visualization Query Simulator HDPS UI

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public - HDPS enables us to analyze any faces of Hive on Tez queries 11 HDPS UI - Flexible Parameters

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public - HDPS generates a ranking of hot spots 12 HDPS UI - Bottleneck Report

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public - HDPS visualizes bottlenecks with a flame graph 13 HDPS UI - Flame Graph

© 2022 Treasure Data, Inc. Public 14 Use cases of HDPS

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public Use cases 15 - Investigation - We use HDPS for troubleshooting - Investment - We use HDPS for decision-making

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public Troubleshooting: HIVE-26184 16 - A customer reported a certain query was strangely slow - We located the root cause(COLLECT_SET) at a glance thanks to HDPS

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public 17 - The query spent 98% CPU time for java.util.HashSet#clear - The time complexity of COLLECT_SET was O(N^2) - We sent a Pull Request to Apache Hive and the query got 20x faster Troubleshooting: HIVE-26184

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public 18 - HDPS allows us profile Hive tasks across queries Analyze queries submitted by CDP Find areas for potential improvement

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public 19 - We can find which areas we can work on - It is tough but has a very positive impact -> We can make an initiative - We find Quick Wins -> We will try them in the next sprint - It is tough and not so promising -> We don’t invest our time for that Find areas for potential improvement

© 2022 Treasure Data, Inc. Public 20 How to implement HDPS

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public 21 Hadoop Metrics DB (Plazma) Hive Query Hive Query Hive Query Hive Query Stack Traces Aggregation Visualization Query Simulator HDPS UI Technical Challenges 1. Test any set of queries with any version or any parameters 2. Collect stack traces with their contexts for later analysis 3. Ingest a massive amount of stack traces 4. Summarize any set of stack traces in a flexible and scalable manner 5. Implement GUI

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public 22 Hadoop Metrics DB (Plazma) Hive Query Hive Query Hive Query Hive Query Stack Traces Aggregation Visualization Query Simulator HDPS UI Technical Challenges (1) 1. Test any set of queries with any version or any parameters 2. Collect stack traces with their contexts for later analysis 3. Ingest a massive amount of stack traces 4. Summarize any set of stack traces in a flexible and scalable manner 5. Implement GUI

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public query_fetcher: handler: com.treasudata.hadoop.hive.simulator.executor.fetch.SqlQueryFetcherImpl input_params: fetch_query_sql: | SELECT time, database, query, elapsed, job_id, success, ... FROM hive WHERE TD_TIME_RANGE(time, '2022-11-01', '2022-11-02') AND elapsed >= 600 query_executor: query_execution_params: - id: 1 hive_docker_version: worker8-hive:0.1.109-8478 hive_configurations: td.hive.jfr.enabled: "true" mapreduce.map.java.opts: "-XX:-Inline" Query Simulator 23 Queries to be tested TD Hive version Enable sampling We can try any parameters in an isolated environment - With Query Simulator, we collect stack traces with the given configurations - Query Simulator is a tool to replay real queries in a secure environment - Described in 『Journey of Migrating Millions of Queries on The Cloud』 - Our paper: https://arxiv.org/abs/2205.08664

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public 24 Hadoop Metrics DB (Plazma) Hive Query Hive Query Hive Query Hive Query Stack Traces Aggregation Visualization Query Simulator HDPS UI Technical Challenges (2) 1. Test any set of queries with any version or any parameters 2. Collect stack traces with their contexts for later analysis 3. Ingest a massive amount of stack traces 4. Summarize any set of stack traces in a flexible and scalable manner 5. Implement GUI

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public Java Flight Recorder 25 - We use Java Flight Recorder to collect stack trace samples - JFR is bundled with OpenJDK - Java APIs are convenient to integrate with Hive final Recording recording = new Recording(settings); recording.setToDisk(true); recording.setDestination(dumpFile); recording.start(); // You can measure anything ... recording.stop(); try (RecordingFile file = new RecordingFile(dumpFile)) { while (file.hasMoreEvents()) { final RecordedEvent event = file.readEvent(); final RecordedStackTrace stackTrace = event.getStackTrace(); stackTrace.getFrames().forEach(frame -> { final String methodName = frame.getMethod().getName(); final int lineNumber = frame.getLineNumber(); sendToAnywhere(methodName, lineNumber); }); } }

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public 26 - SQL or processed data affects a lot which classes or methods are used - Tez runs multiple kinds of tasks on a single JVM - We attach various IDs to each stack trace for later analysis - E.g. TD job id, YARN application id, Tez vertex id, Tez task attempt id ReduceSinkOperator TableScanOperator SelectOperator GroupByOperator GroupByOperator FileSinkOperator Map 1 Map 1 Reduce 2 Map 1 Reduce 2 Map 1 Plan Reduce 2 Plan Container(JVM) Container(JVM) Integration with Hive on Tez

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public 27 Hadoop Metrics DB (Plazma) Hive Query Hive Query Hive Query Hive Query Stack Traces Aggregation Visualization Query Simulator HDPS UI Technical Challenges (3) 1. Test any set of queries with any version or any parameters 2. Collect stack traces with their contexts for later analysis 3. Ingest a massive amount of stack traces 4. Summarize any set of stack traces in a flexible and scalable manner 5. Implement GUI

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public Storing Metrics on Plazma 28 - Plazma is a distributed storage developed by Treasure Data - Plazma and its streaming ingestion API are highly scalable - More than 200 trillion records are stored at this point - Plazma can accept a massive amount of stack traces - 10 samples/sec on 1k JVMs => 10k stack traces/sec <- No problem - Each stack trace contains 30+ method invocations <- No problem

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public 29 Hadoop Metrics DB (Plazma) Hive Query Hive Query Hive Query Hive Query Stack Traces Aggregation Visualization Query Simulator HDPS UI Technical Challenges (4) 1. Test any set of queries with any version or any parameters 2. Collect stack traces with their contexts for later analysis 3. Ingest a massive amount of stack traces 4. Summarize any set of stack traces in a flexible and scalable manner 5. Implement GUI

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public Making a summary with Hive 30 - HDPS has to read too many stack traces and join them with related metrics - 10k stack traces per sec => 36m stack traces per hour - HDPS UI compiles input parameters into SQLs and runs them on Hive - Hive’s scalability allows us to analyze any amount of data SELECT ... FROM tez_task_attempt_method_sampling AS t JOIN tez_vertex_finished AS v ON t.vertex_id = v.vertex_id JOIN tez_vertex_execution_plan AS p ON t.vertex_id = p.vertex_id JOIN hive AS h ON t.job_id = h.job_id WHERE TD_TIME_RANGE(t.time, '2022-11-21 00:00:00', '2022-11-22 00:00:00') AND TD_TIME_RANGE(v.time, '2022-11-21 00:00:00', '2022-11-22 00:00:00') AND TD_TIME_RANGE(p.time, '2022-11-21 00:00:00', '2022-11-22 00:00:00') AND TD_TIME_RANGE(h.time, '2022-11-21 00:00:00', '2022-11-22 00:00:00') AND t.thread = 'TezChild' AND v.vertex_name LIKE 'Reducer %' AND h.query LIKE '-- CDP: Audience:' AND p.plan LIKE '%Merge Join Operator%'

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public 31 Hadoop Metrics DB (Plazma) Hive Query Hive Query Hive Query Hive Query Stack Traces Aggregation Visualization Query Simulator HDPS UI 1. Test any set of queries with any version or any parameters 2. Collect stack traces with their contexts for later analysis 3. Ingest a massive amount of stack traces 4. Summarize any set of stack traces in a flexible and scalable manner 5. Implement GUI Technical Challenges (5)

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public HDPS UI 32 - Flame Graph is very strong visualization for profiliers - Implemented in TypeScript + React + d3-flame-graph

© 2022 Treasure Data, Inc. Public 33 Future work

© 2022 Treasure Data, Inc. Public © 2022 Treasure Data, Inc. Public Future work 34 - We would like to contribute to Apache Hive more - TD is an ideal environments to collect metrics of real-world queries - We would like to implement a similar system for Trino(PrestoSQL) in TD - Both query engines have similar problems in common

© 2022 Treasure Data, Inc. Public 35 Thank you