Motivation

IO statistics is used by all database vendors to have ability monitoring and tuning of performance a queries and found bottle neck related to IO operations.

The proposed solution is to implement gathering IO statistics for logical (memory) read pages and physical (storage) read/write pages. The statistics can be used at least for :

1. Analyze IO performance of a query.
2. Have ability to understand why performance goes down in case it related to IO. For example on concrete node or cache.
3. Evaluate effectiveness of use indexes. Find unused indexes.
4. See most IO aggressive queries.

Competitive analysis

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

• • 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.

• 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 views summarize file I/O, grouped by host.
  • user_summary_by_file_io - These views summarize file I/O, grouped by user.
  • metrics - This view summarizes MySQL server metrics.

Risks and Assumptions

// Describe project risks, such as API or binary compatibility issues, major protocol changes, etc.

Reference Links

// Links to various reference documents, if applicable.

TBD

• Cluster wide statistics
• exposed interfaces: SQL, JMX, Visor, CMD, Web Console ....

Tickets

// Links or report with relevant JIRA tickets.