...

Industrial  RDBMS vendors (Oracle, Postgres, MySQL)  collect detailed IO statistics in dimensions such as queries, database objects (tables and indexes), files, sessions, users, event types etc. 

• Oracle
  

  
  

  • v$filestat - This view displays the number of physical reads and writes done and the total number of single-block and multiblock I/Os done at file level

  • v$segment_statistics -  displays information about segment-level statistics.

  • v$sqlarea -  lists statistics on shared SQL area and contains one row per SQL string. It provides statistics on SQL statements that are in memory, parsed, and ready for execution.

  • v$sysstat - displays system statistics. 

  • v$sesstat - displays user session statistics. 

  • AWR - historical statistics.

• PostgreSQL

  Have information about logical read and physical read/write page for indexes and table separately and for whole database. For the same time spent for physical operations. Also statistics available on SQL statements level.

• • pg_stat_database - Contain one row for each database in the cluster, showing database-wide statistics.

  • pg_statio_all_tables - Contain one row for each table in the current database (including TOAST tables), showing statistics about I/O on that specific table.

  • pg_statio_all_indexes -  Contain one row for each index in the current database, showing statistics about I/O on that specific index.

  • pg_stat_all_indexes - Contain one row for each index in the current database, showing statistics about accesses to that specific index. 

  • pg_stat_all_tables - Contain one row for each table in the current database, showing statistics about accesses to that specific table.

  • pg_stat_statements -  Tracking execution statistics of all SQL statements executed by a server.
    

    
    

• MySQL

  
  

  • table_io_waits_summary_by_table - Table aggregates all table I/O wait events. The grouping is by table.

  • table_io_waits_summary_by_index_usage - Table aggregates all table index I/O wait events. The grouping is by table index.

  • io_global_by_file_by_bytes - These views summarize global I/O consumers to display amount of I/O, grouped by file. 

  • io_global_by_wait_by_bytes -These views summarize global I/O consumers to display amount of I/O and time waiting for I/O, grouped by event. 

  • host_summary_by_file_io- These  These views summarize file I/O, grouped by host.

  • user_summary_by_file_io - These These views summarize file I/O, grouped by user.

  • metrics- This  This view summarizes MySQL server metrics.

Risks and Assumptions

...

Proposed Changes

1. Implement gathering local node statistics with two levels of detalization: fine grain by type of page types (about 35 such types) and aggregated statistics: INDEX, DATA, OTHER. Need to support 3 types of IO operations: logical read, physical read/write.
2. Implement collecting local node statistics with the following dimensions: cache, index, query.
3. implement exchange IO statistics by node to have ability expose cluster statistics (e.g. IO statistics from all node for distributed SQL). 
4. expose IO statistics interfaces for SQL, JMX, Visor, CMD, Web Console

Reference Links

// Links to various reference documents, if applicable.

TBD

• Cluster wide statistics, can lead performance issue
• which of interfaces should be exposed interfaces: SQL, JMX, Visor, CMD, Web Console ....
• reset of statistics. It can be by request, by time, some metrics value, ....
• .....

Tickets

// Links or report with relevant JIRA tickets.