$30 off During Our Annual Pro Sale. View Details »

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

okumin
November 29, 2022

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

okumin

November 29, 2022
Tweet

More Decks by okumin

Other Decks in Programming

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