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

How to measure IOPS consumed by Vertica cluster

Hello All, 

 

I am trying to figure out a way to measure IOPS / Throughput consumed by Vertica cluster. 

Please let me know if you have some idea about this subject.

 

regards,

Yogi

 

Comments

  • Is this what you need => iostat ? Install the sysstat package ( yum install sysstat  - on redhat ) and check 

     

    http://serverfault.com/questions/296552/measure-linux-iops-for-a-running-system

  • I sugest using iotop wiht the -u option to specifiy the user that is running the process and -o to see only the threads that are doing IO.

    Example :  

     

    iotop -u dbadmin -o 
    Total DISK READ: 125.10 M/s | Total DISK WRITE: 2.34 M/s
    TID PRIO USER DISK READ DISK WRITE SWAPIN IO> COMMAND
    56408 be/3 dbadmin 122.16 M/s 0.00 B/s 0.00 % 54.95 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    58426 be/0 dbadmin 399.82 K/s 0.00 B/s 0.00 % 7.42 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    57947 be/4 dbadmin 961.11 K/s 61.51 K/s 0.00 % 0.98 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    58427 be/0 dbadmin 407.51 K/s 0.00 B/s 0.00 % 0.00 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    58428 be/0 dbadmin 895.76 K/s 0.00 B/s 0.00 % 0.00 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    58429 be/0 dbadmin 123.02 K/s 0.00 B/s 0.00 % 0.00 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    56412 be/3 dbadmin 0.00 B/s 123.65 M/s 0.00 % 0.00 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    28561 be/4 dbadmin 0.00 B/s 2.63 M/s 0.00 % 0.00 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    47375 be/4 dbadmin 0.00 B/s 0.00 B/s 0.00 % 0.00 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    58430 be/0 dbadmin 0.00 B/s 0.00 B/s 0.00 % 0.00 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    49531 be/4 dbadmin 0.00 B/s 0.00 B/s 0.00 % 0.00 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803
    57756 be/4 dbadmin 0.00 B/s 0.00 B/s 0.00 % 0.00 % vertica -D /vertica/catalog/db/v~s_node0004 -h 1.1.1.111 -p 5433 -P 4803

     

  • Thanks All for the response.  

     

    I understand that Unix tools offer these metrics but I was looking for Vertica to provide this information. 

    It is very complicated task to run iostat on all nodes and do analysis.

     

    best regards,

    Yogi

  •  You can use the io_usage table from the v_monitor schema.

     

    I have done a side schema just for my workload analysis whee i am querying this table every hour or so and store it in a normal table that a run reports on it for time to time.

    - i have many queries that read metrics from the system tables and stores them so i can make an idea of how my database is doing

     Why do i do this:

      - well the data in the tables like dc_* is not stored in an optimized way so this makes it hard to query(to much resources), in my tables i make sure i have them segmented on the same key and order is done i nhte same way so i can make use of optimization stuff from the engine.

     

    Here is the sql that populates, the shell that gets run , and yes the pass & user are passed as variables from the  .profile file(so no text passwd would be passed).

     

     

    CREATE TABLE dba.io_usage
    (
    "time" timestamptz,
    node_name varchar(128),
    read_kbytes_per_sec float,
    written_kbytes_per_sec float
    );

    --SQL
    insert /* +direct*/ into dba.io_usage
    select start_time,node_name,read_kbytes_per_sec,written_kbytes_per_sec
    from io_usage where start_time >(select max(time) from dba.io_usage);
    -- run analyze(you don`t need to do this every execution)
    -- SELECT ANALYZE_STATISTICS('dba.io_usage');

    -- Shell script(put this in the crontab)
    #!/bin/bash
    #!/bin/bash

    . /home/dbadmin/.profile
    export VSQLDIR=/opt/vertica/bin
    export SQLDIR=/home/dbadmin/scripts/dba
    export data=`date`
    echo " Execution date -" $data >> /home/dbadmin/scripts/dba/log/io_usage.log
    echo "#################################################" >> /home/dbadmin/scripts/dba/log/io_usage.log
    $VSQLDIR/vsql -U $username -w $password -d analytics -f $SQLDIR/io_usage.sql >> /home/dbadmin/scripts/dba/log/io_usage.log
    echo "#################################################" >> /home/dbadmin/scripts/dba/log/io_usage.log

     

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.