System Tables
HeavyDB system tables provide a way to access information about database objects, database object permissions, and system resource (storage, CPU, and GPU memory) utilization. These system tables can be found in the information_schema database that is available by default on server startup. You can query system tables in the same way as regular tables, and you can use the SHOW CREATE TABLE command to view the table schemas.
Users
The users system table provides information about all database users and contains the following columns:
Column Name
Column Type
Description
user_id
INTEGER
ID of database user.
user_name
TEXT
Username of database user.
is_super_user
BOOLEAN
Indicates whether or not the database user is a super user.
default_db_id
INTEGER
ID of user’s default database on login.
default_db_name
TEXT
Name of user’s default database on login.
can_login
BOOLEAN
Indicates whether or not the database user account is activated and can log in.
Databases
The databases system table provides information about all created databases on the server and contains the following columns:
Column Name
Column Type
Description
database_id
INTEGER
ID of database.
database_name
TEXT
Name of database.
owner_id
INTEGER
User ID of database owner.
owner_user_name
TEXT
Username of database owner.
Permissions
The permissions system table provides information about all user/role permissions for all database objects and contains the following columns:
Column Name
Column Type
Description
role_name
TEXT
Username or role name associated with permission.
is_user_role
BOOLEAN
Boolean indicating whether or not the role_name column identifies a user or a role.
database_id
INTEGER
ID of database that contains the database object for which permission was granted.
database_name
TEXT
Name of database that contains the database object on which permission was granted.
object_name
TEXT
Name of database object on which permission was granted.
object_id
INTEGER
ID of database object on which permission was granted.
object_owner_id
INTEGER
User id of the owner of the database object on which permission was granted.
object_owner_user_name
TEXT
Username of the owner of the database object on which permission was granted.
object_permission_type
TEXT
Type of database object on which permission was granted.
object_permissions
TEXT[]
List of permissions that were granted on database object.
Roles
The roles system table lists all created database roles and contains the following columns:
Column Name
Column Type
Description
role_name
TEXT
Role name.
Tables
The tables system table provides information about all database tables and contains the following columns:
Column Name
Column Type
Description
database_id
INTEGER
ID of database that contains the table.
database_name
TEXT
Name of database that contains the table.
table_id
INTEGER
Table ID.
table_name
TEXT
Table name.
owner_id
INTEGER
User ID of table owner.
owner_user_name
TEXT
Username of table owner.
column_count
INTEGER
Number of table columns. Note that internal system columns are included in this count.
table_type
TEXT
Type of tables. Possible values are DEFAULT, VIEW, TEMPORARY , and FOREIGN.
view_sql
TEXT
For views, SQL statement used in the view.
max_fragment_size
INTEGER
Number of rows per fragment used by the table.
max_chunk_size
BIGINT
Maximum size (in bytes) of table chunks.
fragment_page_size
INTEGER
Size (in bytes) of table data pages.
max_rows
BIGINT
Maximum number of rows allowed by table.
max_rollback_epochs
INTEGER
Maximum number of epochs a table can be rolled back to.
shard_count
INTEGER
Number of shards that exists for table.
ddl_statement
TEXT
CREATE TABLE DDL statement for table.
Dashboards
The dashboards system table provides information about created dashboards (enterprise edition only) and contains the following columns:
Column Name
Column Type
Description
database_id
INTEGER
ID of database that contains the dashboard.
database_name
TEXT
Name of database that contains the dashboard.
dashboard_id
INTEGER
Dashboard ID.
dashboard_name
TEXT
Dashboard name.
owner_id
INTEGER
User ID of dashboard owner.
owner_user_name
TEXT
Username of dashboard owner.
last_updated_at
TIMESTAMP
Timestamp of last dashboard update.
data_sources
TEXT[]
List to data sources/tables used by dashboard.
Role Assignments
The role_assignments system table provides information about database roles that have been assigned to users and contains the following columns:
Column Name
Column Type
Description
role_name
TEXT
Name of assigned role.
user_name
TEXT
Username of user that was assigned the role.
Memory Summary
The memory_summary system table provides high level information about utilized memory across CPU and GPU devices and contains the following columns:
Column Name
Column Type
Description
node
TEXT
Node from which memory information is fetched.
device_id
INTEGER
Device ID.
device_type
TEXT
Type of device. Possible values are CPU and GPU.
max_page_count
BIGINT
Maximum number of memory pages that can be allocated on the device.
page_size
BIGINT
Size (in bytes) of a memory page on the device.
allocated_page_count
BIGINT
Number of allocated memory pages on the device.
used_page_count
BIGINT
Number of used allocated memory pages on the device.
free_page_count
BIGINT
Number of free allocated memory pages on the device.
Memory Details
The memory_details system table provides detailed information about allocated memory segments across CPU and GPU devices and contains the following columns:
Column Name
Column Type
Description
node
TEXT
Node from which memory information is fetched.
database_id
INTEGER
ID of database that contains the table that memory was allocated for.
database_name
TEXT
Name of database that contains the table that memory was allocated for.
table_id
INTEGER
ID of table that memory was allocated for.
table_name
TEXT
Name of table that memory was allocated for.
column_id
INTEGER
ID of column that memory was allocated for.
column_name
TEXT
Name of column that memory was allocated for.
chunk_key
INTEGER[]
ID of cached table chunk.
device_id
INTEGER
Device ID.
device_type
TEXT
Type of device. Possible values are CPU and GPU.
memory_status
TEXT
Memory segment use status. Possible values are FREE and USED.
page_count
BIGINT
Number pages in the segment.
page_size
BIGINT
Size (in bytes) of a memory page on the device.
slab_id
INTEGER
ID of slab containing memory segment.
start_page
BIGINT
Page number of the first memory page in the segment.
last_touched_epoch
BIGINT
Epoch at which the segment was last accessed.
Storage Details
The storage_details system table provides detailed information about utilized storage per table and contains the following columns:
Column Name
Column Type
Description
node
TEXT
Node from which storage information is fetched.
database_id
INTEGER
ID of database that contains the table.
database_name
TEXT
Name of database that contains the table.
table_id
INTEGER
Table ID.
table_name
TEXT
Table Name.
epoch
INTEGER
Current table epoch.
epoch_floor
INTEGER
Minimum epoch table can be rolled back to.
fragment_count
INTEGER
Number of table fragments.
shard_id
INTEGER
Table shard ID. This value is only set for sharded tables.
data_file_count
INTEGER
Number of data files created for table.
metadata_file_count
INTEGER
Number of metadata files created for table.
total_data_file_size
BIGINT
Total size (in bytes) of data files.
total_data_page_count
BIGINT
Total number of pages across all data files.
total_free_data_page_count
BIGINT
Total number of free pages across all data files.
total_metadata_file_size
BIGINT
Total size (in bytes) of metadata files.
total_metadata_page_count
BIGINT
Total number of pages across all metadata files.
total_free_metadata_page_count
BIGINT
Total number of free pages across all metadata files.
total_dictionary_data_file_size
BIGINT
Total size (in bytes) of string dictionary files.
Log-Based System Tables
Request Logs
The request_logs system table provides information about HeavyDB Thrift API requests and contains the following columns:
Column Name
Column Type
Description
log_timestamp
TIMESTAMP
Timestamp of log entry.
severity
TEXT
Severity level of log entry. Possible values are F (fatal), E (error), W (warning), and I (info).
process_id
INTEGER
Process ID of the HeavyDB instance that generated the log entry.
query_id
INTEGER
ID associated with a SQL query. A value of 0 indicates that either the log entry is unrelated to a SQL query or no query ID has been set for the log entry.
thread_id
INTEGER
ID of thread that generated the log entry.
file_location
TEXT
Source file name and line number where the log entry was generated.
api_name
TEXT
Name of Thrift API that the request was sent to.
request_duration_ms
BIGINT
Thrift API request duration in milliseconds.
database_name
TEXT
Request session database name.
user_name
TEXT
Request session username.
public_session_id
TEXT
Request session ID.
query_string
TEXT
Query string for SQL query requests.
client
TEXT
Protocol and IP address of client making the request.
dashboard_id
INTEGER
Dashboard ID for SQL query requests coming from Immerse dashboards.
dashboard_name
TEXT
Dashboard name for SQL query requests coming from Immerse dashboards.
chart_id
INTEGER
Chart ID for SQL query requests coming from Immerse dashboards.
execution_time_ms
BIGINT
Execution time in milliseconds for SQL query requests.
total_time_ms
BIGINT
Total execution time (execution_time_ms + serialization time) in milliseconds for SQL query requests.
Server Logs
The server_logs system table provides HeavyDB server logs in tabular form and contains the following columns:
Column Name
Column Type
Description
node
TEXT
Node containing logs.
log_timestamp
TIMESTAMP
Timestamp of log entry.
severity
TEXT
Severity level of log entry. Possible values are F (fatal), E (error), W (warning), and I (info).
process_id
INTEGER
Process ID of the HeavyDB instance that generated the log entry.
query_id
INTEGER
ID associated with a SQL query. A value of 0 indicates that either the log entry is unrelated to a SQL query or no query ID has been set for the log entry.
thread_id
INTEGER
ID of thread that generated the log entry.
file_location
TEXT
Source file name and line number where the log entry was generated.
message
TEXT
Log message.
Web Server Logs
The web_server_logs system table provides HEAVY.AI Web Server logs in tabular form and contains the following columns (Enterprise Edition only):
Column Name
Column Type
Description
log_timestamp
TIMESTAMP
Timestamp of log entry.
severity
TEXT
Severity level of log entry. Possible values are fatal, error, warning, and info.
message
TEXT
Log message.
Web Server Access Logs
The web_server_access_logs system table provides information about requests made to the HEAVY.AI Web Server. The table contains the following columns:
Column Name
Column Type
Description
ip_address
TEXT
IP address of client making the web server request.
log_timestamp
TIMESTAMP
Timestamp of log entry.
http_method
TEXT
HTTP request method.
endpoint
TEXT
Web server request endpoint.
http_status
SMALLINT
HTTP response status code.
response_size
BIGINT
Response payload size in bytes.
Refreshing Logs System Tables
The logs system tables must be refreshed manually to view new log entries. You can run the REFRESH FOREIGN TABLES SQL command (for example, REFRESH FOREIGN TABLES server_logs, request_logs; ), or click the Refresh Data Now button on the table’s Data Manager page in Heavy Immerse.
Request Logs and Monitoring System Dashboard
The Request Logs and Monitoring system dashboard is built on the log-based system tables and provides visualization of request counts, performance, and errors over time, along with the server logs.
System Dashboards
Preconfigured system dashboards are built on various system tables. Specifically, two dashboards named System Resources and User Roles and Permissions are available by default. The Request Logs and Monitoring system dashboard is considered beta functionality and disabled by default. These dashboards can be found in the information_schema database, along with the system tables that they use.
Cross-linking must be enabled to allow cross-filtering across charts that use different system tables. Enable cross-linking by adding "ui/enable_crosslink_panel": true to the feature_flags section of the servers.json file.
Last updated