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