AMPS Statistics

AMPS provides the ability to record the statistics gathered from the AMPS instance and the host machine.

The AMPS statistics database is stored in sqlite3 format and can be used with any of the standard sqlite3 tools. This section assumes that you are using the standard sqlite3 package installed on your local computer. While you may be able to run the SQL examples in this guide using other packages, this guide will assume that all SQL commands will be executed with sqlite3.

Notice that the statistics subsystem is independent of the other subsystems in AMPS, and is the only part of AMPS that uses the sqlite3 format. You cannot use sqlite3 tools with SOW files, journal files or .ack files: these files use formats specifically designed for high performance messaging.

Working with the AMPS statistics database is described in more detail in the following sections.

Configuring AMPS to Persist Statistics

By default, AMPS maintains statistics in memory. To configure AMPS to record the statistics to a file, the following configuration options are available in the AMPS configuration file to update the location and frequency of the statistics database file.

<AMPSConfig>

    ...

    <Admin>
        <InetAddr>localhost:9090</InetAddr>
        <FileName>./stats.db</FileName>
        <Interval>5s</Interval>
    </Admin>

    ...

</AMPSConfig>

In the example above, the AMPS administration interface is set to collect statistics every 5 seconds as indicated by the <Interval> tag. The AMPS administration interface is additionally configured to save the statistics in the stats.db file, which will be created in the directory where AMPS was started.

AMPS does not require that statistics are persisted. Persisting statistics enables information about instance performance, capacity, usage, and so on to be analyzed offline (rather than by using RESTful operations against a running instance). This also enables statistical information about the instance to be persisted when AMPS restarts.

Introduction to SQLite3

This section is a quick reference to sqlite3. It is intended to help in getting started with examining the statistics provided by AMPS. While this guide will be sufficient to execute the examples listed, a more comprehensive guide of the sqlite3 command line tool is available at http://www.sqlite.org/sqlite.html.

Starting SQLite3

To start sqlite3 with the stats.db file simply type:

$> sqlite3 ./stats.db

This will create a command prompt that looks like the following:

$> sqlite3 ./stats.db
SQLite version 3.7.3
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite>

To exit the sqlite3 prompt at any time, use the Ctrl+d sequence.

Simple SQLite3 Commands

Tables

To get a listing of all available tables in the sqlite database type the .table command.

sqlite> .table
HCPUS_DYNAMIC                   IMEMORY_CACHES_DYNAMIC
HCPUS_STATIC                    IMEMORY_CACHES_STATIC
HDISKS_DYNAMIC                  IMEMORY_DYNAMIC
HDISKS_STATIC                   IMEMORY_STATIC
HMEMORY_DYNAMIC                 IPROCESSORS_DYNAMIC
HMEMORY_STATIC                  IPROCESSORS_STATIC
HNET_DYNAMIC                    IQUEUES_DYNAMIC
HNET_STATIC                     IQUEUES_STATIC
ICLIENTS_DYNAMIC                IREPLICATIONS_DYNAMIC
ICLIENTS_STATIC                 IREPLICATIONS_STATIC
ICONFLATEDTOPICS_DYNAMIC        ISOW_DYNAMIC
ICONFLATEDTOPICS_STATIC         ISOW_STATIC
ICONSOLE_LOGGERS_DYNAMIC        ISTATISTICS_DYNAMIC
ICONSOLE_LOGGERS_STATIC         ISTATISTICS_STATIC
ICPUS_DYNAMIC                   ISUBSCRIPTIONS_DYNAMIC
ICPUS_STATIC                    ISUBSCRIPTIONS_STATIC
IFILE_LOGGERS_DYNAMIC           ISYSLOG_LOGGERS_DYNAMIC
IFILE_LOGGERS_STATIC            ISYSLOG_LOGGERS_STATIC
IGLOBALS_DYNAMIC                ITRANSPORTS_DYNAMIC
IGLOBALS_STATIC                 ITRANSPORTS_STATIC
ILIFETIMES_DYNAMIC              IVIEWS_DYNAMIC
ILIFETIMES_STATIC               IVIEWS_STATIC

Schema

To view the schema for any table, type the .schema <table name> command where <table name> is the name of the table to inspect.

sqlite> .schema IFILE_LOGGERS_DYNAMIC

CREATE TABLE IFILE_LOGGERS_DYNAMIC( timestamp integer,
static_id integer, bytes_written integer, PRIMARY
KEY( timestamp, static_id ) );

Statistics Table Design

This section describes the philosophy of how the AMPS tables are designed within the statistics database. This chapter also includes some examples of some useful queries which can give an administrator more information than just the raw data would normally give them. Such information can be a powerful tool in diagnosing perceived problems in AMPS.

Table Naming Scheme

Tables in the database use the following naming scheme:

<I|H><STAT>_<STATIC|DYNAMIC>

Where:
I = AMPS instance statistics
H = Host statistics
STAT = The statistics that are collected (MEMORY, CPUs,
SUBSCRIPTIONS, etc)
STATIC = Attributes that rarely change for an object
(such as client name, CPU #)
DYNAMIC = Stats that are expected to change on every
sample (rates, counters, and so on)

Example Queries

To view which clients have fallen behind at one time, run:

sqlite> SELECT s.client_name, MAX(d.queue_max_latency),
MAX(queued_bytes_out) FROM ICLIENTS_DYNAMIC d
JOIN ICLIENTS_STATIC s ON (s.static_id=d.static_id)
GROUP BY s.client_name;

To view clients that are behind in the latest sample:

sqlite> SELECT s.client_name, d.queue_max_latency,
queued_bytes_out FROM ICLIENTS_DYNAMIC d
JOIN ICLIENTS_STATIC s ON (s.static_id=d.static_id)
WHERE d.timestamp = (SELECT MAX(d.timestamp)
FROM ICLIENTS_DYNAMIC d) AND d.queue_max_latency > 0;

Using the amps-sqlite3 Utility

The AMPS distribution includes a convenience utility, amps-sqlite3, for easily running queries against a statistics database.

The utility takes two parameters, as shown below:

Parameter	Description
database	The sqlite3 database file to query.
query	The query to run. Notice that the query must be enclosed in quotes, since this is a command-line program run by the Linux shell.

The amps-sqlite3 utility joins the STATIC and DYNAMIC tables together, making a single table that is easier to query on. For example, the script joins the ICLIENTS_DYNAMIC and ICLIENTS_STATIC tables together into a single ICLIENTS table.

The amps-sqlite3 utility also provides a set of convenience functions that can be included in the query.

Option	Description
ISO8601(timestamp)	Convert timestamp to an ISO8601 format string.
ISO8601_local(timestamp)	Convert timestamp to an ISO8601 format string in the local timezone.
timestamp(string)	Convert the provided ISO8601 format string to a timestamp.

To use amps-sqlite3, simply provide the file name of the database to query and the query to run. For example, the following query returns the set of samples AMPS has recorded for the system_percent consumed on each CPU while the instance has been running:

$ amps-sqlite3 stats.db "select iso8601(timestamp),system_percent from hcpus order by timestamp"

SQLite Tips and Troubleshooting

This section includes information on SQLite tasks that may not be immediately obvious and troubleshooting information on SQLite.

Converting AMPS Statistics Time to an ISO8601 Datetime

This Python function shows how to convert an AMPS timestamp to an ISO8601 datetime. You can use the equivalent in your language of choice to convert between the timestamps recorded in the statistics database and ISO8601 timestamps.

def iso8601_time(amps_time):
"""
Converts AMPS stats time into an ISO8601 datetime.
"""
pt = float(amps_time)/1000 - 210866803200 # subtract the unix epoch
it = int(pt)
ft = pt-it
return time.strftime("%Y%m%dT%H%M%S",time.localtime(it)) + ("%.6f" % ft)[1:]

Shrinking an AMPS Statistics Database

If the retention policy for an AMPS statistics database has changed such that there is unused space in the file at the maximum retention size, it may be helpful to shrink the size of the statistics database.

Running this procedure will only reduce the size of the database if the database has been truncated.

To do this:

1. Take the AMPS instance offline.

2. (Optional, but recommended) Make a backup copy of the AMPS statistics database on another device.

3. Run the sqlite VACUUM command to shrink the database:

   sqlite3 stats.db 'VACUUM'

   This operation may require free disk space equal to the current size of the statistics database. If the operation fails, the vacuum rolls back without changing the database.

A database should not be vacuumed while AMPS is running.

Troubleshooting "Database Disk Image is Malformed"

To repair this error, you need to extract the data from the SQLite datastore and create a new datastore. To do this:

1. Open the sqlite datastore. For example, if the database store is named stats.db, the command would be:

sqlite3 stats.db

1. Dump the data into a SQL script.

.mode insert
.output stats_data.sql
.dump
.exit

This creates a series of SQL commands that recreate the data in the database.

1. Make sure that the script commits updates (depending on the version of sqlite3 and the state of the database, the script may roll back the updates rather than committing them without this step).

sed -i 's/^ROLLBACK;/COMMIT;/ig' stats_data.sql

1. Now create a new database file using the SQL commands.

sqlite3 good.db < stats_data.sql

Finally, adjust the configuration of the Admin server to use the new database (in this example, good.db) or copy the new database over the old database.