ID | IEP-27 |
Author | Iurii Gerzhedovich |
Sponsor | Vladimir Ozerov Ozerov |
Created | 26 Sep 2018 |
Status | DRAFT |
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 :
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.
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.
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.
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.
// Describe project risks, such as API or binary compatibility issues, major protocol changes, etc.
// Links to various reference documents, if applicable.
// Links or report with relevant JIRA tickets.