# 35. The AMPS Statistics Database

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 appendix 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.

## 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 Script

The AMPS distribution includes a convenience script, `amps-sqlite3`, for easily running queries against a statistics database. This script requires a Python 2.6 or 2.7 interpreter that includes the sqlite3 module. Most Linux distributions meet this requirement in the default installation.

The script takes two parameters, as shown below:

amps-sqlite3 parameters
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` script 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` wrapper also provides a set of convenience functions that can be included in the query. These functions are evaluated before the query is presented to the sqlite3 database engine.

amps-sqlite3 convenience functions
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 the `amps-sqlite3` script, 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

2. 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.

3. 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

4. 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.