34. The AMPS Statistics Database¶

AMPS provides the ability to record the statistics gathered from the AMPS instance and the host machine. This appendix describes working with the AMPS statistics database.

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.

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>
    <Name>AMPS-Sqlite</Name>
    <Admin>
        <InetAddr>localhost:9090</InetAddr>
        <FileName>./stats.db</FileName>
        <Interval>5s</Interval>
    </Admin>

    <!-- [snip] -->
</AMPSConfig>

In the example listed above, the AMPS administration interface is set to collect statistics every 5 seconds as indicated by the <Interval> tag. In the example, 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 get started in 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.

The sqlite3 tools

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: .schema <table name>, 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:

Parameter	Description
database	The sqlite3 database file to query.
query	The query to run. Notice that the query must be enclosed in quotes.

Table 34.1: Parameters for amps-sqlite3

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 convienience functions that can be included in the query. These functions are evaluated before the query is presentd to the sqlite3 database engine.

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.

Table 34.2: Convenience functions in amps-sqlite3

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 converts 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:

Take the AMPS instance offline.
(Optional, but recommended) Make a backup copy of the AMPS statistics database on another device.
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:

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

sqlite3 stats.db

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.

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

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.

Table Of Contents

Related Topics