View markdown source on GitHub

Galactic Database

Contributors

last_modification Published: Jan 28, 2019
last_modification Last Updated: Jun 16, 2022

Galactic Database

.left[Galaxy uses a database for:]

.left[Galaxy does not use a database for:]

Speaker Notes


Defaults

Speaker Notes


Choices

Speaker Notes


Sizing

Galaxy rarely deletes from the database, most objects are marked deleted.

Allocate at least 20 GB of disk to start, 50+ GB if expanding would be difficult.

8-16 GB memory should be sufficient.

Recommended: Run PostgreSQL on a different server for resource isolation.

Speaker Notes


Configuration

database_connection is specified as a database URL in galaxy.yml

Speaker Notes


New Database

On first startup with an empty database, Galaxy creates its schema

Speaker Notes


Migrations

Changes in the Galaxy DB model (when upgrading Galaxy) are captured incrementally in the form of atomic scripts.

Each script can both upgrade and downgrade a DB.

$ ./manage_db.sh upgrade
$ ./manage_db.sh downgrade --version=132

Speaker Notes


Tuning - Pool

If the server logs errors about not having enough database pool connections.

Galaxy config option default value usegalaxy.org value
database_engine_option_pool_size 5 10
database_engine_option_max_overflow 10 20

Values for usegalaxy.org

Speaker Notes


Tuning - Server-side cursors

If large database query results are causing memory or response time issues in the Galaxy process, leave it on server (PostgreSQL only, recommended).

Galaxy config option default value usegalaxy.org value
database_engine_option_server_side_cursors false true

Speaker Notes


Tuning - Slow query logging

Queries slower than this threshold (in s) will be logged at debug level.

Galaxy config option default value usegalaxy.org value
slow_query_log_threshold 0 2

Speaker Notes


Tuning - TS install database

Galaxy can track Tool Shed data in a separate DB.

Galaxy config option default value usegalaxy.org value
install_database_connection value of database_connection SQLite DB in CVMFS

All other database config options but prefixed with install_ are also available.

.left[This allows:]

Speaker Notes


Access through model

Python script to access Galaxy’s database layer via the Galaxy model.

(venv)$ python -i scripts/db_shell.py --config config/galaxy.yml
>>> new_user = User('foo@example.org', 'secret')
>>> new_user.username = 'foo'
>>> sa_session.add(new_user)
>>> sa_session.flush()
>>> sa_session.query(User).all()

Speaker Notes


Useful queries

Captured in gxadmin (tutorial)

Speaker Notes


Thank you!

This material is the result of a collaborative work. Thanks to the Galaxy Training Network and all the contributors! page logo Tutorial Content is licensed under Creative Commons Attribution 4.0 International License.