-
Notifications
You must be signed in to change notification settings - Fork 0
Pgantenna Database Reference
no0p edited this page Oct 19, 2014
·
7 revisions
The pgantenna application stores data in a Postgresql database.
This database can be queried for generating stats, plots, alert conditions, or data sets for analysis. This document describes the structure of that database so that it can be queried effectively.
| Data Source | Antenna Table |
|---|---|
| pg_stat_activity | stat_activity |
| pg_stat_database | stat_database |
| pg_stat_bgwriter | stat_bgwriter |
| pg_stat_user_tables | stat_user_tables |
| pg_stat_user_indexes | stat_user_indexes |
| pg_statio_user_indexes | statio_user_indexes |
| pg_statio_user_tables | statio_user_tables |
| pg_stat_user_functions | stat_user_functions |
| pg_stat_replication | stat_replication |
| pg_stat_statements | stat_statements (only if installed) |
| pg_stat_statements | statements (only if installed) |
| pg_locks | stat_lock |
| pg_class | stat_class |
| pg_stats | stat_column |
| Filesystem Data | stat_filesystems |
| System Data | stat_systems |
| GUCs | restart_gucs |
| GUCs | transient_gucs |
Table "public.heartbeats"
Column | Type | Modifiers
---------------+--------------------------+---------------------------------------------------------
id | integer | not null default nextval('heartbeats_id_seq'::regclass)
current_tx_id | integer | not null
measured_at | timestamp with time zone | not null
Table "public.stat_activity"
Column | Type | Modifiers
------------------+--------------------------+------------------------------------------------------------
id | integer | not null default nextval('stat_activity_id_seq'::regclass)
database_id | integer | not null
pid | integer | not null
user | text | not null
application_name | text |
client_addr | text | not null
state | text | not null
query | text |
backend_start | timestamp with time zone |
query_start | timestamp with time zone |
state_change | timestamp with time zone |
measured_at | timestamp with time zone | not null
Table "public.stat_database"
Column | Type | Modifiers
----------------+--------------------------+------------------------------------------------------------
id | integer | not null default nextval('stat_database_id_seq'::regclass)
database_id | integer | not null
numbackends | integer | not null
xact_commit | bigint | not null
xact_rollback | bigint | not null
blks_read | bigint | not null
blks_hit | bigint | not null
tup_returned | bigint | not null
tup_fetched | bigint | not null
tup_inserted | bigint | not null
tup_updated | bigint | not null
tup_deleted | bigint | not null
conflicts | bigint | not null
temp_files | bigint | not null
temp_bytes | bigint | not null
deadlocks | bigint | not null
blk_read_time | double precision | not null
blk_write_time | double precision | not null
stats_reset | timestamp with time zone |
measured_at | timestamp with time zone | not null
created_at | timestamp with time zone | not null default now()
Table "public.stat_bgwriter"
Column | Type | Modifiers
-----------------------+--------------------------+------------------------------------------------------------
id | integer | not null default nextval('stat_bgwriter_id_seq'::regclass)
checkpoints_timed | bigint | not null
checkpoints_req | bigint | not null
checkpoint_write_time | numeric | not null
checkpoint_sync_time | numeric | not null
buffers_checkpoint | bigint | not null
buffers_clean | bigint | not null
maxwritten_clean | bigint | not null
buffers_backend | bigint | not null
buffers_backend_fsync | bigint | not null
buffers_alloc | bigint | not null
stats_reset | timestamp with time zone | not null
measured_at | timestamp with time zone | not null
Table "public.stat_user_tables"
Column | Type | Modifiers
-------------------+--------------------------+---------------------------------------------------------------
id | integer | not null default nextval('stat_user_tables_id_seq'::regclass)
table_id | integer | not null
seq_scan | bigint | not null
seq_tup_read | bigint | not null
idx_scan | bigint | not null
idx_tup_fetch | bigint | not null
n_tup_ins | bigint | not null
n_tup_upd | bigint | not null
n_tup_del | bigint | not null
n_tup_hot_upd | bigint | not null
n_live_tup | bigint | not null
n_dead_tup | bigint | not null
vacuum_count | bigint | not null
autovacuum_count | bigint | not null
analyze_count | bigint | not null
autoanalyze_count | bigint | not null
last_vacuum | timestamp with time zone |
last_autovacuum | timestamp with time zone |
last_analyze | timestamp with time zone |
last_autoanalyze | timestamp with time zone |
measured_at | timestamp with time zone | not null
Table "public.stat_user_indexes"
Column | Type | Modifiers
---------------+--------------------------+----------------------------------------------------------------
id | integer | not null default nextval('stat_user_indexes_id_seq'::regclass)
index_id | integer | not null
idx_scan | bigint | not null
idx_tup_read | bigint | not null
idx_tup_fetch | bigint | not null
measured_at | timestamp with time zone | not null
Table "public.statio_user_indexes"
Column | Type | Modifiers
---------------+--------------------------+------------------------------------------------------------------
id | integer | not null default nextval('statio_user_indexes_id_seq'::regclass)
index_id | integer | not null
idx_blks_read | bigint | not null
idx_blks_hit | bigint | not null
measured_at | timestamp with time zone | not null
Table "public.statio_user_tables"
Column | Type | Modifiers
-----------------+--------------------------+-----------------------------------------------------------------
id | integer | not null default nextval('statio_user_tables_id_seq'::regclass)
table_id | integer | not null
heap_blks_read | bigint | not null
heap_blks_hit | bigint | not null
idx_blks_read | bigint | not null
idx_blks_hit | bigint | not null
toast_blks_read | bigint | not null
toast_blks_hit | bigint | not null
tidx_blks_read | bigint | not null
tidx_blks_hit | bigint | not null
measured_at | timestamp with time zone | not null
Table "public.stat_user_functions"
Column | Type | Modifiers
-------------+--------------------------+------------------------------------------------------------------
id | integer | not null default nextval('stat_user_functions_id_seq'::regclass)
function_id | integer | not null
calls | bigint | not null
total_time | numeric | not null
self_time | numeric | not null
measured_at | timestamp with time zone | not null
Table "public.stat_replication"
Column | Type | Modifiers
------------------+--------------------------+---------------------------------------------------------------
id | integer | not null default nextval('stat_replication_id_seq'::regclass)
usename | text | not null
application_name | text |
client_addr | text | not null
client_hostname | text |
null | text |
client_port | text | not null
backend_start | timestamp with time zone | not null
state | text | not null
sent_location | text | not null
write_location | text | not null
flush_location | text | not null
replay_location | text | not null
sync_priority | text | not null
sync_state | text | not null
current_xlog | text | not null
measured_at | timestamp with time zone | not null
Table "public.stat_statements"
Column | Type | Modifiers
---------------------+--------------------------+--------------------------------------------------------------
id | integer | not null default nextval('stat_statements_id_seq'::regclass)
statement_id | integer | not null
role_oid | integer | not null
db_oid | integer | not null
calls | bigint | not null
total_time | numeric | not null
rows | bigint | not null
shared_blks_hit | bigint | not null
shared_blks_read | bigint | not null
shared_blks_dirtied | bigint | not null
shared_blks_written | bigint | not null
local_blks_hit | bigint | not null
local_blks_read | bigint | not null
local_blks_dirtied | bigint | not null
local_blks_written | bigint | not null
temp_blks_read | bigint | not null
temp_blks_written | bigint | not null
blk_read_time | numeric | not null
blk_write_time | numeric | not null
measured_at | timestamp with time zone | not null
created_at | timestamp with time zone | not null default now()
Table "public.statements"
Column | Type | Modifiers
------------+--------------------------+---------------------------------------------------------
id | integer | not null default nextval('statements_id_seq'::regclass)
q | text | not null
query_hash | text | not null
created_at | timestamp with time zone | default now()
Table "public.stat_lock"
Column | Type | Modifiers
------------------+--------------------------+--------------------------------------------------------
id | integer | not null default nextval('stat_lock_id_seq'::regclass)
database_id | integer | not null
stat_activity_id | integer | not null
granted | boolean | not null
holder_vxid | text | not null
lock_type | text | not null
lock_mode | text | not null
table_id | integer |
relation_oid | integer |
page | integer |
tuple | integer |
transaction_id | integer |
vtransaction_id | text |
measured_at | timestamp with time zone | not null
Table "public.stat_class"
Column | Type | Modifiers
------------------------+--------------------------+---------------------------------------------------------
id | integer | not null default nextval('stat_class_id_seq'::regclass)
table_id | integer |
index_id | integer |
relpages | integer |
reltuples | integer |
relallvisible | integer |
rel_size_bytes | integer |
rel_indexes_size_bytes | integer |
measured_at | timestamp with time zone | not null
Table "public.stat_column"
Column | Type | Modifiers
---------------------+--------------------------+----------------------------------------------------------
id | integer | not null default nextval('stat_column_id_seq'::regclass)
column_id | integer | not null
null_fraction | numeric |
average_width_bytes | integer |
distinct_non_nulls | numeric |
most_common_vals | text |
most_common_freqs | text |
correlation | numeric |
measured_at | timestamp with time zone | not null
Table "public.stat_filesystems"
Column | Type | Modifiers
---------------+--------------------------+---------------------------------------------------------------
id | integer | not null default nextval('stat_filesystems_id_seq'::regclass)
filesystem_id | integer | not null
blks_in_frags | bigint | not null
blk_free | bigint | not null
blk_avail | bigint | not null
measured_at | timestamp with time zone | not null default now()
Table "public.filesystems"
Column | Type | Modifiers
------------+--------------------------+----------------------------------------------------------
id | integer | not null default nextval('filesystems_id_seq'::regclass)
name | text | not null
mnt_dir | text | not null
mnt_type | text | not null
mnt_opts | text | not null
blksize | integer | not null
fragsize | integer | not null
created_at | timestamp with time zone | default now()
updated_at | timestamp with time zone |
Table "public.stat_systems"
Column | Type | Modifiers
------------------+--------------------------+-----------------------------------------------------------
id | integer | not null default nextval('stat_systems_id_seq'::regclass)
cpu_count | integer | not null
page_size | integer | not null
pages | bigint | not null
pages_available | bigint | not null
one_min_load_avg | numeric | not null
swap_total | bigint | not null
swap_free | bigint | not null
swap_cached | bigint | not null
page_cache | bigint | not null
buffer_cache | bigint | not null
measured_at | timestamp with time zone | not null default now()
Table "public.restart_gucs"
Column | Type | Modifiers
-------------------------------------+--------------------------+-----------------------------------------------------------
id | integer | not null default nextval('restart_gucs_id_seq'::regclass)
shared_buffers | numeric |
max_connections | integer |
blk_size | integer |
listen_addresses | text |
wal_level | text |
wal_buffers | integer |
max_wal_senders | smallint |
autovacuum_max_workers | smallint |
autovacuum_freeze_max_age | numeric |
autovacuum_multixact_freeze_max_age | numeric |
max_locks_per_transaction | smallint |
max_pred_locks_per_transaction | smallint |
wal_segment_size | integer |
data_directory | text |
created_at | timestamp with time zone | not null default now()
Table "public.transient_gucs"
Column | Type | Modifiers
---------------------------------+--------------------------+-------------------------------------------------------------
id | integer | not null default nextval('transient_gucs_id_seq'::regclass)
checkpoint_segments | smallint |
checkpoint_timeout | integer |
checkpoint_completion_target | numeric |
work_mem | integer |
temp_buffers | integer |
maintenance_work_mem | integer |
seq_page_cost | numeric |
random_page_cost | numeric |
cpu_tuple_cost | numeric |
cpu_operator_cost | numeric |
effective_cache_size | integer |
vacuum_cost_delay | integer |
vacuum_cost_page_hit | integer |
vacuum_cost_page_miss | integer |
vacuum_cost_page_dirty | integer |
vacuum_cost_limit | integer |
bgwriter_delay | text |
bgwriter_lru_maxpages | smallint |
bgwriter_lru_multiplier | numeric |
effective_io_concurrency | smallint |
synchronous_commit | text |
wal_writer_delay | text |
commit_delay | integer |
commit_siblings | smallint |
wal_keep_segments | smallint |
geqo | text |
geqo_threshold | smallint |
geqo_effort | smallint |
geqo_pool_size | integer |
geqo_generations | integer |
geqo_selection_bias | numeric |
geqo_seed | numeric |
default_statistics_target | smallint |
constraint_exclusion | text |
cursor_tuple_fraction | numeric |
from_collapse_limit | smallint |
join_collapse_limit | smallint |
autovacuum | text |
autovacuum_vacuum_threshold | integer |
autovacuum_analyze_threshold | integer |
autovacuum_scale_factor | numeric |
autovacuum_analyze_scale_factor | numeric |
autovacuum_vacuum_cost_delay | text |
deadlock_timeout | text |
created_at | timestamp with time zone | not null default now()
To understand where this data comes from it and what it means, it is helpful to refer to the pgsampler documentation and source.