Hive Distributed Profiling System in Treasure Data - English version #tdtechtalk

The English version of "Hive Distributed Profiling System in Treasure Data".

Japanese version -> https://speakerdeck.com/okumin/hive-distributed-profiling-system-in-treasure-data-japanese-version-number-tdtechtalk

I made a presentation in TreasureData Tech Talk 2022.
https://techplay.jp/event/879660

Transcript

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

   View Slide

2. © 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
   

   View Slide

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

   View Slide

4. © 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
   

   View Slide

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

   View Slide

6. © 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
   

   View Slide

7. © 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
   

   View Slide

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

   View Slide

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

   View Slide

10. © 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
    

    View Slide

11. © 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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

15. © 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
    

    View Slide

16. © 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
    

    View Slide

17. © 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
    

    View Slide

18. © 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
    

    View Slide

19. © 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
    

    View Slide

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

    View Slide

21. © 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
    

    View Slide

22. © 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
    

    View Slide

23. © 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
    

    View Slide

24. © 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
    

    View Slide

25. © 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);
    });
    }
    }
    

    View Slide

26. © 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
    

    View Slide

27. © 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
    

    View Slide

28. © 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
    

    View Slide

29. © 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
    

    View Slide

30. © 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%'
    

    View Slide

31. © 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)
    

    View Slide

32. © 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
    

    View Slide

33. © 2022 Treasure Data, Inc. Public
    33
    Future work
    

    View Slide

34. © 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
    

    View Slide

35. © 2022 Treasure Data, Inc. Public
    35
    Thank you
    

    View Slide