you can setupAll statistics are saved in a file called “statistics.data” which uses an internal data format for optimizing reading and saving of the file (specifically *not* optimized for file size).

The PP Admin GUI can be used to view statistics stored in the file, but if your applications add hundreds or even thousands of entries to the statistics, then it can start getting hard to find and view the relevant entries.

If you wish to use a database for storing the statistics, you can set up a statistics listener that acts as a kind of filter in the statistics server so it gets called whenever statistics are collected from any of the other components in the applications, which by default means every 30 seconds (can be adjusted with the statistics.poll.interval parameter).

Usage

The StatToDB statistics-to-database addon saves statistics in a database with just 5 tables where 3 of them have the same columns – they contain statistics for different time periods and intervals – minutes, hours and days.

e.g. in the table ID_STAT_MIN you have statistics stored for the last

Here is a sample DDL to create the database table shown without indexes for simplicity.

CREATE TABLE ID_STAT_SERVERS (
	SERVER_ID	INTEGER GENERATED ALWAYS AS IDENTITY,
	S_NAME		CHAR(128),
	PRIMARY KEY (SERVER_ID)
);
CREATE TABLE ID_STAT_FUNCTIONS (
	SERVER_ID	INTEGER NOT NULL,
	FUNCTION_ID	INTEGER GENERATED ALWAYS AS IDENTITY,
	F_NAME		CHAR(256),
	PRIMARY KEY (FUNCTION_ID),
	FOREIGN KEY (SERVER_ID) REFERENCES ID_STAT_SERVERS ON DELETE RESTRICT
);
CREATE TABLE ID_STAT_MIN (
	FUNCTION_ID	INTEGER NOT NULL,
	SLOT_ID		INTEGER NOT NULL,
	FROM_DATE	TIMESTAMP NOT NULL,
	TO_DATE		TIMESTAMP NOT NULL,
	HITS		INTEGER NOT NULL,
	TOTAL		FLOAT NOT NULL,
	SQRT		FLOAT NOT NULL,
	MINVAL		FLOAT NOT NULL,
	MAXVAL		FLOAT NOT NULL,
	FOREIGN KEY (FUNCTION_ID) REFERENCES ID_STAT_FUNCTIONS
);
CREATE TABLE ID_STAT_HOUR (
	FUNCTION_ID	INTEGER NOT NULL,
	SLOT_ID		INTEGER NOT NULL,
	FROM_DATE	TIMESTAMP NOT NULL,
	TO_DATE		TIMESTAMP NOT NULL,
	HITS		INTEGER NOT NULL,
	TOTAL		FLOAT NOT NULL,
	SQRT		FLOAT NOT NULL,
	MINVAL		FLOAT NOT NULL,
	MAXVAL		FLOAT NOT NULL,
	FOREIGN KEY (FUNCTION_ID) REFERENCES ID_STAT_FUNCTIONS
);
CREATE TABLE ID_STAT_DAY (
	FUNCTION_ID	INTEGER NOT NULL,
	SLOT_ID		INTEGER NOT NULL,
	FROM_DATE	TIMESTAMP NOT NULL,
	TO_DATE		TIMESTAMP NOT NULL,
	HITS		INTEGER NOT NULL,
	TOTAL		FLOAT NOT NULL,
	SQRT		FLOAT NOT NULL,
	MINVAL		FLOAT NOT NULL,
	MAXVAL		FLOAT NOT NULL,
	FOREIGN KEY (FUNCTION_ID) REFERENCES ID_STAT_FUNCTIONS
);

Statistics stored in the per-minute table are deleted after 24 hours (meaning entries older than 24 hours are purged), the one stored in the per-hour table are deleted after 60 days, and rows in the per-day table are not deleted automatically.

Using any standard report generator tool, you can point it at the database and generate queries that will give you reports customized to what you need to display.

Note that the slot_id column is the one you should do your searches on – it contains the number of minutes/hours/days since 1/1 1970 depending on which table you search in. If you e.g. want to view statistics for a week starting a specific date, calculate the number of days from 1/1 1970 and the date you wish to begin with – and search for slot_id’s between that number and the number+7.

Do the same with the minute and hour tables.

Configuration

Add the class name dk.itp.statistics.addon.StatToDB to the statistics.listeners configuration property for the statistics server (see the configuration parameters section for details).

The file stat_statements.properties must exist in the classpath – it contains all the SQL statements executed when adding statistics to the database, and things like table names or column names can be adjusted in there if needed – just make sure to keep the order of parameters intact.

The new addon will read these new parameters, listed here with their default values:

statdb.poolsize=1
statdb.connectionurl
statdb.username
statdb.credentials
statdb.drivername
statdb.debug=false
statdb.maxconnectionlife=0
statdb.maxconnectionusage=0
statdb.commitonrelease=false
statdb.getconnectiontimeout=5000
statdb.testonreserve=false
statdb.testtable=
statdb.disableminutestats=false
statdb.minuteinterval=1
statdb.tablequalifier=
statdb.enabled=true
statdb.serverprefix=
statdb.cleanupinterval=240

Details on the individual parameters are in the configuration parameters section in this document.

The important ones are these:

• statdb.connectionurl must point to a database, e.g. statdb.connectionurl=jdbc:cloudscape:/db/ppstatistics
• statdb.drivername must contain the name of the JDBC driver to use, e.g. com.ihost.cs.jdbc.CloudscapeDriver or COM.ibm.db2.jdbc.app.DB2Driver.
• If load on the database is high, and applications have an extreme number of measurement points, then it might make sense to either disable the highest resolution statistics by setting statdb.disableminutestats=true – this will significantly reduce the amount of database updates being done at the cost of losing some of the details – the statistics will still be present in the hour and day tables, but you will no longer be able to see resolution beyond one hour.
• Instead of disabling the per-minute statistics, you can also elect to increase the interval – e.g. from 1 minute to 5 minutes by setting statdb.minuteinterval=5 – this will change the resolution to 5-minutes instead of the default 1 minute.
• statdb.cleanupinterval specifies the number of minutes between each cleanup of the database. A cleanup will delete entries in the minutes table older than 24 hours, and entries in the hours table older than 60 days.

Important Performance Considerations

In a large system with many servers and agents attached there can be a lot of records going to the database – e.g. a system with 2000 different measurement points and measurements being done on all of them at least once per minute (which is very unlikely, but theoretically possible) will end up with 2000*3 = 6000 updates per minute = 360.000 per hour, and 60*2000 + 2000 = 122.000 rows in the database.

If you disable use of the per-minute table, *and* change the statistics.poll.interval to 15 minutes, this will drop to 2000 * 4 * 2 = 16.000 database accesses per hour worst-case, assuming full load on all servers and all measuring points being hit at least every minute.

In general in the real world scenario it will more than likely be enough to increase the statdb.minuteinterval parameter if you have performance problems with the database. You can also check the statistics generated by the StatToDB addon for the statistics server to view the load on the database.