Find the Busiest Hour in the Day for I/O

Posted August 2, 2019 by Jim Knicely, Vertica Principal Solution Architect

Busy traffic jam
The IO_USAGE system table provides disk I/O bandwidth usage history for the system. You can query it to find out interesting IO statistics like which hour of the day has the most IO usage on your Vertica cluster!

Example: dbadmin=> SELECT * dbadmin-> FROM (SELECT "HOUR", "Total IO (GB)", "Read IO (GB)", "Write IO (GB)" dbadmin(> FROM (SELECT MIN(TRUNC(start_time)) start_time, dbadmin(> DATE_TRUNC('hour', start_time) as "HOUR", dbadmin(> (SUM(read_kbytes_per_sec) + SUM(written_kbytes_per_sec)) * 60/1024/1024 as "Total IO (GB)", dbadmin(> SUM(read_kbytes_per_sec) * 60/1024/1024 as "Read IO (GB)", dbadmin(> SUM(written_kbytes_per_sec) * 60/1024/1024 as "Write IO (GB)" dbadmin(> FROM v_monitor.io_usage dbadmin(> GROUP BY 2) foo dbadmin(> WHERE start_time >= TRUNC(SYSDATE) - 5 -- LAST 5 DAYS! dbadmin(> LIMIT 1 OVER (PARTITION BY start_time ORDER BY start_time, "Total IO (GB)" DESC)) foo2 dbadmin-> ORDER BY "HOUR" DESC; HOUR | Total IO (GB) | Read IO (GB) | Write IO (GB) ---------------------+------------------+---------------------+------------------ 2019-08-02 10:00:00 | 7.31542510986328 | 1.28464279174805 | 6.03078231811523 2019-08-01 23:00:00 | 13.6195484161377 | 10.5460218429565 | 3.07352657318115 2019-07-31 23:00:00 | 13.6010026931763 | 10.5195688247681 | 3.0814338684082 2019-07-30 12:00:00 | 104.669023132324 | 6.8077428817749 | 97.8612802505493 2019-07-29 14:00:00 | 1.92557315826416 | 0.00724811553955078 | 1.91832504272461 (5 rows) Helpful Link: https://www.vertica.com/docs/latest/HTML/Content/Authoring/SQLReferenceManual/SystemTables/MONITOR/IO_USAGE.htm

Have fun!