How to measure IOPS consumed by Vertica cluster

Yogi_Baba

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

 

colin_loghin

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

Adrian_Oprea_1

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

 

Yogi_Baba

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

Adrian_Oprea_1

 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