Database

Database Setup

This page describes how to setup the MTT PostgreSQL database. PostgreSQL was chosen for a variety of reasons including performance, and advanced features such as partition tables and advanced optimizer techniques.

PostgreSQL Setup and Tuning

The MTT database schema can stress a PostgreSQL installation as a result there are a few configuration options that need to be tweaked in order for PostgreSQL to perform well in the common case.

1. Turn on remote access if your webserver is not the same machine as the database machine. Do this by editing the webserver information to data/pg_hba.conf and adjusting the listen_address option in the data/postgresql.conf file. See PostgreSQL documentation on how to properly set these values.
2. For VACUUM we need to increment the Free Space Map parameters to something like the following:

max_fsm_pages = 153600
max_fsm_relations = 2000

1. Outside of these options, the defaults should be fine for most users.

Some additional, per connection, options that are handy to know:

• constraint_exclusion: This will allow the optimizer to use the check constraints on a table to include or exclude it from the search list. This is handy when searching partition tables by start_timestamp since the check constraint on each partition table represents a slice of time that does not overlap with any other table. So this option will speedup searches on narrow time ranges.

mtt=> set constraint_exclusion = on;

• sort_mem: This is the amount of memory that PostgreSQL allows a connection for sorting results. Since MTT relies on many levels of aggregation increasing this value can drastically improve performance. Experimentally we have found 256 MB to be a good number.

mtt=> set sort_mem = '256MB';

Schema Setup

1. MTT requires PostgreSQL 8.2 or later. We test with 8.2.4
2. Create a database user named mtt. Consult PostgreSQL documentation for instructions.
3. Create a database named mtt. Consult PostgreSQL documentation for instructions.
4. Find the sql scripts in $MTT_TRUNK/server/sql/.
5. Load in the base tables schemas-v3.sql:

shell$ psql -d mtt
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

mtt=> \i schemas-v3.sql

1. Load stats schemas-stats.sql and reporter schemas-reporter:

mtt=> \i schemas-stats.sql
mtt=> \i schemas-reporter.sql

1. Load the indexes schemas-indexes.sql:

mtt=> \i schemas-indexes.sql

1. Create the partition tables for mpi_install:
2. In the directory $MTT_TRUNK/server/sql/support there exists a script called create-partitions-mpi-install.pl. This script takes two (2) arguments: YYYY MM. Where YYYY is the fully qualified year, and MM is the month (01 - 12) or XX for all 12 months. Specifying a single month will only generate the partition tables for that month.
3. Use this script to generate the mpi_install partition tables. The following generates partition tables for 11/2006, 12/2006, and all months in 2007:

$ cd $MTT_TRUNK/server/sql/support
$ ./create-partitions-mpi-install.pl 2006 11 >  mpi-install-part.sql
$ ./create-partitions-mpi-install.pl 2006 12 >> mpi-install-part.sql
$ ./create-partitions-mpi-install.pl 2007 XX >> mpi-install-part.sql

1. Load the partition tables into your database:

mtt=> \i mpi-install-part.sql

1. Create the partition tables for test_build by following the same instructions as with mpi_install, but using the create-partitions-test-build.pl script.
2. Create the partition tables for test_run by following the same instructions as with mpi_install, but using the create-partitions-test-run.pl script.
3. Now you should have the schemas in place for storing the MTT database.

Schema Documentation

Some documentation on the MTT database schema design is located in the MTT docs directory.