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. Most of the instructions in this wiki are designed for a generic database setup. Specific instructions for the official MTT Reporter database are at the end.

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 the 'mtt' user - for creating tables

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

5. Create the 'mtt_viewer' user - for the reporter

postgres=# CREATE USER mtt_viewer WITH LOGIN PASSWORD '******';

6. Create a database named mtt and assign the owner to mtt then exit.

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';

Initial Schema Setup

1. Load in the base tables schemas-v3.sql:

cd to $MTT_TRUNK/server/sql/.

Tables can be inserted into the database either by using the following command: psql mtt -U mtt -f file_to_insert.sql

Or from inside the database as follows:

shell$ psql -U mtt mtt
mtt=> \i schemas-v3.sql

Don't worry about the "table xx does not exist, skipping" errors, they are normal.

2. Load stats schemas-stats.sql, reporter schemas-reporter and indexes schemas-indexes.sql and exit the database:

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

3. Load the summary tables:

cd to $MTT_TRUNK/server/sql/summary

shell$ psql -U mtt mtt -f summary_tables.sql

4. Load the summary triggers.

shell$ psql -U mtt mtt -f summary_trigger.sql

5. Follow the directions below for Yearly Maintenance:

Each year new tables need to be set up. This helps keep the size of the database reasonable for searching.

Yearly Maintenance

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

  shell$ psql -U mtt mtt
  mtt=> \dt
  mtt=> \di
  mtt=> \q

1. Create partition tables for the current year:

Note: If it is necessary to create tables only for specific months and years - See the more detailed instructions below in 'Detailed Partition Table Maintenance'.

1. cd to $MTT_TRUNK/server/sql/support and run the yearly-table-update followed by the year. To create all the tables for 2019 you would enter the following command:

shell$ ./yearly-table-update.pl 2019

Follow the instructions to install the files. Here is an example of the output of yearly-table-update:

----------------------------------------------------------------------
Creating Tables for year: 2019
----------------------------------------------------------------------
	Creating file: tmp/2019-mpi-install.sql
	Creating file: tmp/2019-test-build.sql
	Creating file: tmp/2019-test-run.sql
	Creating file: tmp/2019-indexes.sql
	Creating file: tmp/2019-triggers.sql

----------------------------------------------------------------------
Now you are ready to insert these into the database

Check the database to make sure that the tables have not
already been added for that year
  psql mtt -U mtt
  mtt=> \dt
  mtt=> \di

When you are ready run the following commands: 
 (afterward you can delete the tmp directory)
----------------------------------------------------------------------
psql mtt -U mtt -f tmp/2019-mpi-install.sql
psql mtt -U mtt -f tmp/2019-test-build.sql
psql mtt -U mtt -f tmp/2019-test-run.sql
psql mtt -U mtt -f tmp/2019-indexes.sql
psql mtt -U mtt -f tmp/2019-triggers.sql

Detailed Partition Table Maintenance

1. 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. Also, see the README file in $MTT_TRUNK/server/sql/support for more information if needed.
2. 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

3. Create the yearly partition tables for test_build by following the same instructions as with mpi_install, but using the create-partitions-test-build.pl script.

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

5. Create the partition tables for indexes by following the same instructions as with mpi_install, but using the create-partitions-indexes.pl script.

6. cd to $MTT_TRUNK/server/sql/summary. Create the partition tables for triggers by following the same instructions as with mpi_install, but using the create-partitions-trigger.pl script.

7. Load the customized partition tables: Note: Order is critical!

shell psql -U mtt mtt -f mpi-install.sql
shell psql -U mtt mtt -f test-build.sql
shell psql -U mtt mtt -f test-run.sql
shell psql -U mtt mtt -f indexes.sql
shell psql -U mtt mtt -f triggers.sql

Official MTT-Reporter database site specific information:

1. Log in as the mpiteam user
2. cd /mnt/data/mtt.open-mpi.org/mtt/server/sql/support
3. less README
4. 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.
5. ./yearly-table-update.pl 2019
6. 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

7. Verify the tables were inserted (see above)

Schema Documentation

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