-
Notifications
You must be signed in to change notification settings - Fork 47
Database
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.
MTT requires PostgreSQL 8.2 or later. We test with 8.2.4.
- Install PostgreSQL http://www.postgresql.org/
- Set up the database to restart on boot. Start the postgresql service.
systemctl enable postgresql
systemctl start postgresql
- Login to the postgres instance as the 'postgres' user
su - postgres
psql
- Create the 'mtt' user - for creating tables
postgres=# CREATE USER mtt WITH LOGIN PASSWORD '******' CREATEDB CREATEROLE;
- 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.
1. 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.
1. 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. cd to `$MTT_TRUNK/server/sql/`.
1. Load in the base tables `schemas-v3.sql`:
Don't worry about the "table xx does not exist, skipping" errors, they are normal. 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
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
2. cd to `$MTT_TRUNK/server/sql/summary`
1. Load the summary tables.
shell$ psql -U mtt mtt -f summary_tables.sql
2. Load the summary triggers.
shell$ psql -U mtt mtt -f summary_trigger.sql
3. cd to `$MTT_TRUNK/server/sql/support
1. 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.
1. 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:
```sh
$ 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
-
Create the yearly partition tables for
test_buildby following the same instructions as withmpi_install, but using thecreate-partitions-test-build.plscript. -
Create the partition tables for
test_runby following the same instructions as withmpi_install, but using thecreate-partitions-test-run.plscript. -
Create the partition tables for
indexesby following the same instructions as withmpi_install, but using thecreate-partitions-indexes.plscript. -
cd to
$MTT_TRUNK/server/sql/summary. Create the partition tables fortriggersby following the same instructions as withmpi_install, but using thecreate-partitions-trigger.plscript. -
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
(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)
Some documentation on the MTT database schema design is located in the MTT docs directory.