All Products
Search

This Product

    PolarDB:Data models and data storage schemas

    Document Center

    PolarDB:Data models and data storage schemas

    Last Updated:Mar 22, 2023

    This topic describes data models, data storage schemas, and their data details.

    Data models and data storage schemas

    To allow data to be stored in PolarDB for PostgreSQL(Compatible with Oracle) databases and Time Series Database (TSDB), Alibaba Cloud abstracts monitoring data models. Similar to data warehouses, separate tables are created for each subject data. These tables are called data models. These tables have three column types: metric column, dimension column, and time column. The metric column contains collected metrics and is of the numeric type. The dimension column contains the descriptions of metrics and generally is of the string type. In TSDB, the dimension column is a tag or label field.

    In real-world implementation, all monitoring data is stored in the polar_gawr_collection schema in the postgres database:

    • Tables whose names start with fact_ are data models. Dimension columns, metric columns, and time columns are in the following relationships:

      • Columns whose names start with tag are dimension columns and of the TEXT type. If they are of the INTEGER type, they are associated with the corresponding dimension table.

      • Columns whose names start with value_ are metric columns and of the INT8 type.

      • The time column stores Unix timestamps. You can use the to_timestamp function to restore timestamps as readable time values.

    • Tables whose names start with dim_ are dimension tables and can be associated with the corresponding column in fact tables. The dim_ins_info dimension table is the table that is used to identify collection metadata, and is associated with the tag_ins_info column of each fact table.

    • Tables whose names start with agg_ are aggregate tables of fact tables. The end of the tables indicates the aggregation granularity in seconds.

    • Tables whose names start with view_ are views that join fact_ tables with dim_ tables. You can only perform SELECT operations on views.

    Examples

    polar_stat_process is used in the following example. The fact_polar_stat_process table is in the polar_gawr_collection schema of the postgres database. 

    select * from polar_gawr_collection.fact_polar_stat_process limit 1;

    The following output is displayed:

    -[ RECORD 1 ]-----------------+-----------
tag_backend_type               2
tag_ins_info                   3
time                           1645010019
value_backend_num              1
value_cpu_sys                  0.1
value_cpu_user                 0.1
value_local_read_ps            0
value_local_read_throughput    0
value_local_write_ps           0
value_local_write_throughput   0
value_rss                      2
value_shared_read_ps           0
value_shared_read_throughput   0
value_shared_write_ps          0
value_shared_write_throughput  0

    Columns whose names start with value_ are metric columns. Columns whose names start with tag_ are dimension columns. A dimension column of the INTEGER type can be associated with the ID column of the dim_polar_stat_process_backend_type table:

    select * from polar_gawr_collection_db_lq$pg$2.dim_polar_stat_process_backend_type where id = 2;

    The following output is displayed:

    -[ RECORD 1 ]+---------------------------
id            2
backend_type  background logindex writer

    You can view the associated results in the view_fact_polar_stat_process table. 

    select * from polar_gawr_collection.view_fact_polar_stat_process limit 1;

    The following output is displayed:

    -[ RECORD 1 ]-----------+----------------------------------------------
logical_ins_name         xxxxxx
physical_ins_name        xxxxxxxxxxx
host                     xxxxxxxxx
port                     xxxx
role                     Standby
version                  release date: 20211231, polar version: 1.1.20
backend_type             background logindex writer
time                     1645010019
shared_write_ps          0
shared_write_throughput  0
backend_num              1
cpu_sys                  0.1
local_write_ps           0
local_write_throughput   0
local_read_ps            0
shared_read_ps           0
local_read_throughput    0
rss                      2
shared_read_throughput   0
cpu_user                 0.1

    Data metrics

    The collected monitoring data is stored in database tables and includes metrics such as wait event statistics, database I/O and latency distribution, and resource statistics for each backend process. Each type of monitoring data is stored in a separate table. Each table contains dimension columns and metric columns.

    dbmetrics

    The dbmetrics table displays the collected information about database resource consumption and basic monitoring items. The following metrics are displayed:

    Metric

    Cycle

    Unit

    Description

    CPU

    cpu_total_usage

    5s

    %

    The total CPU utilization.

    cpu_user_usage

    5s

    %

    The CPU utilization in the user state.

    cpu_sys_usage

    5s

    %

    The CPU utilization in the system state.

    Memory

    mem_total_usage

    5s

    %

    The memory usage.

    mem_total_used

    5s

    MB

    The amount of memory used by the host.

    mem_rss

    5s

    MB

    The amount of memory used by resident set size (RSS).

    mem_cache

    5s

    MB

    The amount of memory used by the cache.

    mem_mapped_file

    5s

    MB

    The amount of memory used by mapped_file.

    mem_inactiverss

    5s

    MB

    The amount of memory used by inactive RSS.

    mem_inactivecache

    5s

    MB

    The amount of memory used by inactive cache.

    File system

    fs_size_total

    5s

    MB

    The total file system space.

    fs_size_used

    5s

    MB

    The used file system space.

    fs_size_usage

    5s

    %

    The file system usage.

    fs_inodes_total

    5s

    N/A

    The total number of inodes.

    fs_inodes_used

    5s

    N/A

    The number of used inodes.

    fs_inodes_usage

    5s

    %

    The inode usage.

    polar_base_dir_size

    5s

    MB

    The size of the data catalog.

    polar_wal_dir_size

    5s

    MB

    The size of the WAL catalog.

    Connection status

    active_connections

    5s

    N/A

    The number of connections in the active state.

    waiting_connections

    5s

    N/A

    The number of connections in the waiting state.

    Wait events

    client_waits

    5s

    N/A

    The number of processes waiting for clients.

    lwlock_waits

    5s

    N/A

    The number of processes waiting for lwlock.

    io_waits

    5s

    N/A

    The number of processes waiting for I/O.

    lock_waits

    5s

    N/A

    The number of processes waiting for locks.

    extension_waits

    5s

    N/A

    The number of processes waiting for plug-ins.

    ipc_waits

    5s

    N/A

    The number of processes that are communicating between processes.

    timeout_waits

    5s

    N/A

    The number of processes that have timed out.

    bufferpin_waits

    5s

    N/A

    The number of processes waiting for bufferpin.

    cpu_waits

    5s

    N/A

    The number of processes that use CPU.

    activity_waits

    5s

    N/A

    The number of processes that are idle and waiting to become active.

    Transactions

    commits_delta

    5s

    N/A

    The number of committed transactions.

    rollbacks_delta

    5s

    N/A

    The number of transactions that are rolled back.

    Transaction status

    active_transactions

    5s

    N/A

    The number of transactions in the active state.

    idle_transactions

    5s

    N/A

    The number of transactions in the idle state.

    waiting_transactions

    5s

    N/A

    The number of transactions in the waiting state.

    idle_connections

    5s

    N/A

    The number of connections in the idle state.

    two_pc_transactions

    5s

    N/A

    The number of distributed transactions.

    swell_time

    5s

    Seconds

    The execution duration of the longest transaction.

    SQL

    deadlocks_delta

    5s

    N/A

    The number of deadlocks.

    conflicts_delta

    5s

    N/A

    The number of queries that were canceled due to the resolution of replay conflicts of the secondary database.

    Rows

    tup_returned_delta

    5s

    N/A

    The number of scanned rows.

    tup_fetched_delta

    5s

    N/A

    The number of returned rows.

    tup_inserted_delta

    5s

    N/A

    The number of inserted rows.

    tup_updated_delta

    5s

    N/A

    The number of updated rows.

    tup_deleted_delta

    5s

    N/A

    The number of deleted rows.

    Temporary files

    temp_files_delta

    5s

    N/A

    The number of temporary files.

    temp_bytes_delta

    5s

    Bytes

    The size of temporary files.

    Database buffers

    blks_hit_delta

    5s

    N/A

    The number of blocks that hit the cache.

    blks_read_delta

    5s

    N/A

    The number of physical reads.

    buffers_backend_delta

    5s

    N/A

    The number of backend write buffers.

    buffers_alloc_delta

    5s

    N/A

    The amount of allocated buffers.

    buffers_backend_fsync_delta

    5s

    N/A

    The number of backend fsync buffers.

    buffers_checkpoint_delta

    5s

    N/A

    The number of checkpoint write buffers.

    buffers_clean_delta

    5s

    N/A

    The number of bgwriter buffers.

    polar_dirtypage_size

    5s

    N/A

    The number of dirty pages in the buffer.

    polar_copybuffer_used_size

    5s

    N/A

    The number of used copy buffers.

    polar_copybuffer_isfull

    5s

    N/A

    Indicates whether the copy buffer is full.

    checkpoint

    checkpoint_sync_time_delta

    5s

    Seconds

    The duration of the checkpoint sync task.

    checkpoints_timed_delta

    5s

    N/A

    The number of scheduled checkpoint tasks.

    checkpoint_write_time_delta

    5s

    Seconds

    The duration of the checkpoint write task.

    checkpoints_req_delta

    5s

    N/A

    The number of checkpoint requests.

    logindex_mem_tbl_size

    5s

    N/A

    The number of logindex tables.

    Database age

    db_age

    5s

    xid

    The age of the database.

    Copy

    replay_latency_in_mb

    5s

    MB

    The replay delay of the secondary database.

    send_latency_in_mb

    5s

    MB

    The send delay of the primary database.

    ap_cp_latency_mb

    5s

    MB

    The gap between the replay checkpoint and the consistency checkpoint.

    wp_ap_latency_mb

    5s

    MB

    The gap between the write checkpoint and the replay checkpoint.

    wp_cp_latency_mb

    5s

    MB

    The gap between the write checkpoint and the consistency checkpoint.

    polar_stat_process

    The polar_stat_process table displays the collected statistics on system resource consumption. The following dimension is included:

    Dimension

    Description

    process_type

    The type of the process.

    The following metrics of system resource consumption are displayed:

    Metric

    Cycle

    Unit

    Description

    cpu_user

    10s

    %

    The CPU utilization in the user state.

    cpu_sys

    10s

    %

    The CPU utilization in the system state.

    rss

    10s

    MB

    The amount of memory used by RSS.

    share_read_ps

    10s

    N/A

    The number of shared storage reads.

    share_read_throughput

    10s

    MB

    The throughput for shared storage reads.

    share_write_ps

    10s

    N/A

    The number of shared storage writes.

    share_write_throughput

    10s

    MB

    The throughput for shared storage writes.

    local_read_ps

    10s

    N/A

    The number of local reads.

    local_read_throughput

    10s

    MB

    The throughput for local reads.

    local_write_ps

    10s

    N/A

    The number of local writes.

    local_write_throughput

    10s

    MB

    The throughput for local writes.

    polar_stat_io_info

    The polar_stat_io_info table displays the collected information about database I/O calls, such as falloc, fsync, read, write, creat, seek, open, and close. The following dimensions are included:

    Dimension

    Description

    fileloc

    The location of the file. Valid values:

    • local

    • pfs

    filetype

    The type of the file. Valid values:

    • DATA: data file.

    • WAL: log file.

    The following metrics of database I/O calls are displayed:

    Metric

    Cycle

    Unit

    Description

    falloc_latency_us

    1s

    us

    The cumulative time of falloc calls within one second.

    read_throughput

    1s

    MB

    The throughput of read calls within one second.

    read_latency_us

    1s

    us

    The cumulative time of read calls within one second.

    creat_latency_us

    1s

    us

    The cumulative time of creat calls within one second.

    open_latency_us

    1s

    us

    The cumulative time of open calls within one second.

    write_latency_us

    1s

    us

    The cumulative time of write calls within one second.

    seek_count

    1s

    N/A

    The number of seek calls within one second.

    falloc_count

    1s

    N/A

    The number of falloc calls within one second.

    fsync_count

    1s

    N/A

    The number of fsync calls within one second.

    fsync_latency_us

    1s

    us

    The cumulative time of fsync calls within one second.

    close_count

    1s

    N/A

    The number of close calls within one second.

    read_count

    1s

    N/A

    The number of read calls within one second.

    write_count

    1s

    N/A

    The number of write calls within one second.

    creat_count

    1s

    N/A

    The number of creat calls within one second.

    open_count

    1s

    N/A

    The number of open calls within one second.

    write_throughput

    1s

    MB

    The throughput of write calls within one second.

    seek_latency_us

    1s

    us

    The cumulative time of seek calls within one second.

    polar_stat_io_latency

    The polar_stat_io_latency table displays the collected latency distribution for database I/O calls, such as fsync, read, write, seek, and open. The following dimension is included:

    Dimension

    Description

    latency

    The latency distribution.

    The following metrics of latency distribution for database I/O calls are displayed:

    Metric

    Cycle

    Unit

    Description

    seek

    10s

    N/A

    The number of seek calls within the latency interval.

    fsync

    10s

    N/A

    The number of fsync calls within the latency interval.

    read

    10s

    N/A

    The number of read calls within the latency interval.

    write

    10s

    N/A

    The number of write calls within the latency interval.

    open

    10s

    N/A

    The number of open calls within the latency interval.

    polar_stat_network

    The polar_stat_network table displays the collected network information. The following dimensions are included:

    Dimension

    Description

    client

    The IP addresses from which the specified users can access the specified databases.

    backend_type

    The type of the client.

    The following metrics of network statistics for data collection are displayed:

    Metric

    Cycle

    Unit

    Description

    client_count

    10s

    N/A

    The number of clients in the same dimension.

    send_mb

    10s

    MB

    The size of sent data.

    recv_mb

    10s

    MB

    The size of received data.

    retrans

    10s

    N/A

    The number of retransmissions.

    max_sendq

    10s

    N/A

    The maximum length of the sending queue.

    max_recvq

    10s

    N/A

    The maximum length of the receiving queue.

    max_rtt

    10s

    ms

    The network latency.

    max_cwnd

    10s

    N/A

    The maximum length of the sliding window.

    polar_aas_history

    The polar_aas_history table displays the collected wait events statistics. The following dimensions are included:

    Dimension

    Description

    wait_event_type

    The type of the wait event.

    wait_event

    The name of the wait event.

    queryid

    The unique ID of the SQL statement.

    The following metric of wait event statistics is displayed:

    Metric

    Cycle

    Unit

    Description

    wait_count

    1s

    N/A

    The number of sessions with the same queryid value and in the same wait event.

    polar_stat_slow_query

    The polar_stat_slow_query table displays the collected SQL statistics. The following dimensions are included:

    Dimension

    Description

    wait_event_type

    The type of the wait event.

    wait_event

    The name of the wait event.

    pid

    The ID of the process.

    dbname

    The name of the database.

    query

    The executed SQL statements.

    The following metric of SQL statistics is displayed:

    Metric

    Cycle

    Unit

    Description

    executing_time

    10s

    ms

    The execution duration of the SQL statement.

    polar_stat_max_memory_sql

    The polar_stat_max_memory_sql table displays the collected statistics on SQL statements that consume high memory. The following dimensions are included:

    Dimension

    Description

    dbname

    The name of the database.

    pid

    The ID of the process.

    query

    The executed SQL statements.

    The following metric of SQL statements that consume high memory is displayed.

    Metric

    Cycle

    Unit

    Description

    rss_mb

    10s

    MB

    The SQL statement that consumes the maximum memory and the consumed memory.

    polar_lock_contention_history

    The polar_lock_contention_history table displays the collected information about lock conflicts. The following dimensions are included:

    Dimension

    Description

    locktype

    The type of the lock.

    lockmode

    The level of the lock.

    datname

    The name of the database.

    relation

    The name of the table.

    query

    The executed SQL statements.

    The following metric of lock conflicts is displayed:

    Metric

    Cycle

    Unit

    Description

    total_num

    60s

    N/A

    The number of blocked sessions.

    polar_stat_top_sql

    The polar_stat_top_sql table displays the collected statistics on the most frequently executed SQL statements. The following dimensions are included:

    Dimension

    Description

    queryid

    The unique ID of the SQL statement.

    query

    The executed SQL statements.

    The following metrics of the most frequently executed SQL statements are displayed:

    Metric

    Cycle

    Unit

    Description

    logical_read

    600s

    N/A

    The number of logical reads.

    blks_read

    600s

    N/A

    The number of physical reads.

    logical_write

    600s

    N/A

    The number of logical writes.

    blks_written

    600s

    N/A

    The number of physical writes.

    blk_read_time

    600s

    ms

    The duration of the physical I/O read.

    blk_write_time

    600s

    ms

    The duration of the physical I/O write.

    calls

    600s

    N/A

    The number of executions of the SQL statement.

    total_time

    600s

    ms

    The total duration when the SQL statement is executed.

    rows

    600s

    N/A

    The number of rows that are accessed or modified.

    polar_stat_user_tables

    The polar_stat_user_tables table displays the collected information about the most frequently accessed tables. The following dimensions are included:

    Dimension

    Description

    dbname

    The name of the database.

    schemaname

    The name of the schema.

    relname

    The name of the table.

    The following metrics of the most frequently accessed tables are displayed:

    Metric

    Cycle

    Unit

    Description

    seq_scan

    3600s

    N/A

    The number of sequential scans.

    seq_tup_read

    3600s

    N/A

    The number of rows involved in sequential scans.

    idx_scan

    3600s

    N/A

    The number of index scans.

    idx_tup_fetch

    3600s

    N/A

    The number of rows involved in index scans.

    n_tup_ins

    3600s

    N/A

    The number of inserted rows.

    n_tup_upd

    3600s

    N/A

    The number of updated rows.

    n_tup_del

    3600s

    N/A

    The number of deleted rows.

    n_tup_hot_upd

    3600s

    N/A

    The number of rows involved in HOT updates.

    n_mod_since_analyze

    3600s

    N/A

    The number of rows updated after the last analyze operation.

    n_live_tup

    3600s

    N/A

    The number of live tuples.

    n_dead_tup

    3600s

    N/A

    The number of dead tuples.

    dead_tup_ratio

    3600s

    %

    The proportion of dead tuples.

    last_vacuum

    3600s

    s

    The time from the last vacuum operation.

    last_autovacuum

    3600s

    s

    The time from the last auto vacuum operation.

    last_analyze

    3600s

    s

    The time from the last analyze operation.

    last_autoanalyze

    3600s

    s

    The time from the last auto analyze operation.

    polar_statio_user_tables

    The polar_statio_user_tables table displays the collected information about the most frequently called table I/O operations. The following dimensions are included:

    Dimension

    Description

    dbname

    The name of the database.

    schemaname

    The name of the schema.

    relname

    The name of the table.

    The following metrics of the most frequently called table I/O operations are displayed:

    Metric

    Cycle

    Unit

    Description

    heap_logical_read

    3600s

    N/A

    The number of logical table reads.

    heap_blks_read

    3600s

    N/A

    The number of physical table reads.

    idx_logical_read

    3600s

    N/A

    The number of logical index reads.

    idx_blks_read

    3600s

    N/A

    The number of physical index reads.

    polar_stat_user_indexes

    The polar_stat_user_indexes table displays the collected information about the most frequently accessed indexes. The following dimensions are included:

    Dimension

    Description

    dbname

    The name of the database.

    schemaname

    The name of the schema.

    relname

    The name of the table.

    indexrelname

    The name of the index.

    The following metrics of the most frequently accessed indexes are displayed:

    Metric

    Cycle

    Unit

    Description

    idx_scan

    3600s

    N/A

    The number of index scans.

    idx_tup_read

    3600s

    N/A

    The number of rows involved in index reads.

    idx_tup_fetch

    3600s

    N/A

    The number of rows involved in index scans.

    polar_statio_user_indexes

    The polar_statio_user_indexes table displays the collected information about the most frequently called index I/O operations. The following dimensions are included:

    Dimension

    Description

    dbname

    The name of the database.

    schemaname

    The name of the schema.

    relname

    The name of the table.

    indexrelname

    The name of the index.

    The following metrics of the most frequently called index I/O operations are displayed:

    Metric

    Cycle

    Unit

    Description

    idx_blks_read

    3600s

    N/A

    The number of physical index reads.

    idx_logical_read

    3600s

    N/A

    The number of logical index reads.

    polar_stat_relation_size

    The polar_stat_relation_size table displays the collected information about the space consumed by top relations. The following dimensions are included:

    Dimension

    Description

    dbname

    The name of the database.

    schemaname

    The name of the schema.

    relname

    The name of the table.

    relkind

    The type of the table.

    The following metric of top relation-consumed space is displayed:

    Metric

    Cycle

    Unit

    Description

    relsize_total

    3600s

    MB

    The space consumed by the relation.

    polar_stat_table_age

    The polar_stat_table_age table displays the collected information about top relation age. The following dimensions are included:

    Dimension

    Description

    dbname

    The name of the database.

    schemaname

    The name of the schema.

    relname

    The name of the table.

    relkind

    The type of the table.

    The following metric of top relation age is displayed:

    Metric

    Cycle

    Unit

    Description

    table_age

    3600s

    xid

    The age of the relation.

    polar_statio_user_sequences

    The polar_statio_user_sequences table displays the collected information about the most frequently called sequence I/O operations. The following dimensions are included:

    Dimension

    Description

    dbname

    The name of the database.

    schemaname

    The name of the schema.

    relname

    The name of the table.

    indexrelname

    The name of the index.

    The following metrics of the most frequently called sequence I/O operations are displayed:

    Metric

    Cycle

    Unit

    Description

    blks_read

    3600s

    N/A

    The number of physical reads.

    logical_read

    3600s

    N/A

    The number of logical reads.

    polar_stat_user_functions

    The polar_stat_user_functions table displays the collected information about the most frequently executed functions. The following dimensions are included:

    Dimension

    Description

    dbname

    The name of the database.

    schemaname

    The name of the schema.

    funcname

    The name of the function.

    The following metrics of the most frequently executed functions are displayed:

    Metric

    Cycle

    Unit

    Description

    calls

    3600s

    N/A

    The cumulative number of executions of the function.

    total_time

    3600s

    ms

    The total duration when the function is executed.

    polar_settings

    The polar_settings table displays the configuration information of the database. The following dimensions are included:

    Dimension

    Description

    name

    The name of the configuration item.

    source

    The source of the configuration item.

    setting

    The value of the configuration item.