Skip to content

Database

Jump to bottom
DebRez edited this page Jul 25, 2019 · 21 revisions

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
  1. Login to the postgres instance as the 'postgres' user
su - postgres
psql
  1. Create a database user named mtt.
postgres=# CREATE USER mtt WITH LOGIN PASSWORD '******' CREATEDB CREATEROLE;
  1. 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
  1. Outside of these options, the defaults should be fine for most users.
  2. 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. cd to $MTT_TRUNK/server/sql/.
  2. Load in the base tables schemas-v3.sql: Don't worry about the "table xx does not exist, skipping" errors, they are normal.
shell$ psql -U mtt mtt
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 and then exit:
mtt=> \i schemas-indexes.sql
mtt=> \q
  1. Create the yearly partition tables:
  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. Also, see the README file in $MTT_TRUNK/server/sql/support for more information if needed.
  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. 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.

  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. Load the yearly partition tables:


Schema Documentation
-----

Some documentation on the MTT database schema design is located in the
MTT [docs](https://svn.open-mpi.org/trac/mtt/browser/trunk/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)

Clone this wiki locally