Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

tying requests to disk io usage

Trying to identify what might tie high disk io usage to individual requests.  Getting some very high disk io usage and trying to track down what requests might be doing that.  Metadata tables like v_monitor.io_usage and v_monitor.system_resource_usage show node level info but no ties to requests.    Meta tables like query_requests show memory_acquired_mb but not the disk usage data.

Comments

  • Hi sld, 

     

    One place to start looking is at query_events, and check for which queries the event_type='GROUP_BY_SPILLED' or other '%SPILLED% events since those queries will be hitting the disk. You can then try to correlate those queries to times of high disk usage. 

     

    You can also try looking at v_monitor.execution_engine_profiles:

    select * from execution_engine_profiles where counter_name like '%disk%' to see where queries used the disk in their execution. This may not be as helpful, however, because the data in this view tends to roll out quickly for large workloads. But if you do suspect a query, you can profile it and check how much bytes the query is actually reading/writing from disk. 

     

    Hope that helps,

    Chris

  • Hi ,

     Try to use ths information from this query:

     -this query can be written better but is doing the job.

     It will find sessions that spilled, track the used operator and list user and the sql he is running. Ideal would be to have labels on your queries so tracking/pointing to your worst query would be easyer. 

     

      One thing i noticed related with high IO is the use of temporary table and queryes on top of heavy encoded projections. 

     But the biggest vilen of them all is the limited memeory and the enabled spill option, this will limit memory for a user/pool but will kick your arese on the IO use and CPU. (it`s a trade of i guess), unless you opt not to enable spill. 

     

    WITH
    spilled AS
    (
    SELECT
    session_id,
    transaction_id,
    statement_id,
    event_type,
    event_description
    FROM
    v_monitor.query_events
    WHERE
    event_type IN ('JOIN_SPILLED',
    'GROUP_BY_SPILLED')
    )
    ,
    engineprof AS
    (
    SELECT
    session_id,
    transaction_id,
    statement_id,
    operator_name
    FROM
    execution_engine_profiles
    WHERE
    (
    session_id,transaction_id,statement_id) IN
    (
    SELECT
    session_id,
    transaction_id,
    statement_id
    FROM
    spilled)
    ORDER BY
    counter_value DESC
    )
    ,
    mix AS
    (
    SELECT
    sp.event_type,
    sp.event_description,
    ep.*
    FROM
    spilled sp
    JOIN
    engineprof ep
    ON
    sp.session_id=ep.session_id
    )
    SELECT
    m.*,
    qr.user_name,
    qr.request_duration_ms,
    qr.request
    FROM
    mix m
    JOIN
    query_requests qr
    ON
    (
    m.session_id,m.transaction_id,m.statement_id)=(qr.session_id,qr.transaction_id,
    qr.statement_id)

    Try to see if you can correlate your high IO after running this script, and if you can make it better make sure oyu share back :) 

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.