Table Reference
This section lists the statistics database tables that are related to each performance metric.
The table below lists the base name for the tables that contain each type of metric. In the AMPS statistics database, static properties (such as the client name of a connection) are stored in a STATIC table, while statistics that are captured at each interval are stored in a DYNAMIC table.
The amps-sqlite3 script automatically handles the join from STATIC tables to DYNAMIC tables to allow you to query using the base table name directly.
For queries that use other tools, include a join between the corresponding STATIC and DYNAMIC table static_id fields. For example, to query information for clients, join ICLIENTS_STATIC and ICLIENTS_DYNAMIC on ICLIENTS_STATIC.static_id = ICLIENTS_DYNAMIC.static_id.
Host Metrics
| Metric Category | Base Table Names |
|---|---|
| cpus (host level) | HCPUS (instance info in ICPUS) |
| disk capacity and activity | HDISKS |
| memory (host level) | HMEMORY (instance info in IMEMORY) |
| network activity | HNET |
Instance Metrics
| Metric Category | Base Table Names |
|---|---|
| api (embedded client) | IGLOBALS |
| clients | ICLIENTS |
| conflated topics | ICONFLATEDTOPICS (info also in ISOW) |
| cpu (instance) | ICPUS (host level info in HCPUS) |
| logging | ICONSOLE_LOGGERS, IFILE_LOGGERS, ISYSLOG_LOGGERS |
| memory (instance) | IMEMORY, IMEMORY_CACHES, IPAGINATIONS |
| message processors | IPROCESSORS |
| queries | IGLOBALS |
| queues | IQUEUES (info also in ISOW) |
| replication | IREPLICATIONS |
| sow (including information on views, conflated topics, queues) | ISOW |
| statistics | ISTATISTICS |
| subscriptions | ISUBSCRIPTIONS |
| timestamp | IGLOBALS |
| transaction log | ITRANSACTION_LOG, ITRANSACTION_LOG_WRITE_LATENCY, ITRANSACTION_LOG_WRITE_SIZE |
| transports | ITRANSPORTS |
| views | IVIEWS |
Computed Metrics
Some metrics available through the administrative interface are calculated when queried and are not stored as columns in the physical SQLite tables. When querying those tables directly, calculate the values from the stored columns using the expressions below.
| Admin metric | Source tables | SQL expression |
|---|---|---|
/amps/host/memory/in_use | HMEMORY_STATIC joined to HMEMORY_DYNAMIC on static_id | IFNULL(total - free - buffers - cached, 0) |
/amps/instance/cpu/total_percent | ICPUS_DYNAMIC | IFNULL(user_percent + system_percent, 0) |
/amps/instance/statistics/time_per_sample | ISTATISTICS_DYNAMIC | 1.0 * total_time / total_samples |
/amps/instance/statistics/disk_per_sample | ISTATISTICS_DYNAMIC | 1.0 * file_size / total_samples |
For example, to calculate time_per_sample directly from the statistics database:
SELECT timestamp, 1.0 * total_time / total_samples AS time_per_sample
FROM ISTATISTICS_DYNAMIC
ORDER BY timestamp;