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

MTT requires PostgreSQL 8.2 or later. We test with 8.2.4.

1. Install PostgreSQL http://www.postgresql.org/
2. Set up the database to restart on boot. Start the postgresql service.

systemctl enable postgresql
systemctl start postgresql

3. Login to the postgres instance as the 'postgres' user

su - postgres
psql

4. Create a database user named mtt.

postgres=# CREATE USER mtt WITH LOGIN PASSWORD '******' CREATEDB CREATEROLE;

5. Create a database named mtt and assign the owner to mtt. Then exit postgres.

postgres=# CREATE DATABASE mtt WITH OWNER mtt;
postgres=# \q

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 in /var/lib/pgsql/data/pg_hba.conf and adjusting the listen_address option in the /var/lib/pgsql/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

3. Outside of these options, the defaults should be fine for most users.
4. Restart postgresql after editing configuration files.

systemctl restart postgresql

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. Find the sql scripts in $MTT_TRUNK/server/sql/.
2. 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.

---

Yearly Maintainance

Verify that the tables are not already there (look for the year in the table names)

  shell$ psql-ompi
  mtt=> \dt
  mtt=> \di

 (0) Log in as the mpiteam user
 (1) cd /mnt/data/mtt.open-mpi.org/mtt/server/sql/support
 (2) less README
 (3) EDIT create-partitions-test-run.pl to remove references to:  bios_id, firmware_id, provision_id, harasser_id
       -- OMPI DB does not have these fields so the Test Run tables will fail to load if these references are in there.
 (4) ./yearly-table-update.pl 2019
 (5) Insert those tables using the commands displayed to the console
     instead of "psql mtt -U mtt" use the wrapper "psql-ompi" like the following:
psql-ompi -f tmp/2019-mpi-install.sql
psql-ompi -f tmp/2019-test-build.sql
psql-ompi -f tmp/2019-test-run.sql
psql-ompi -f tmp/2019-indexes.sql
psql-ompi -f tmp/2019-triggers.sql
 (6) Verify the tables were inserted (see above)