We will just briefly cover the features available in gxadmin, there are lots of queries that may or may not be useful for your Galaxy instance and you will have to read the documentation before using them.

It started life as a small shell script that Helena wrote because she couldn’t remember what Gravity was called or where it could be found. Some of the functions needed for things like swapping zerglings are still included in gxadmin but are highly specific to UseGalaxy.eu and not generally useful.

Since then it became the home for “all of the SQL queries we [galaxy admins] run regularly.” Helena Rasche and Nate Coraor often shared SQL queries with each other in private chats, but this wasn’t helpful to the admin community at large, so they decided to put them all in gxadmin and make it as easy to install as possible. We are continually trying to make this tool more generic and generally useful, if you notice something that’s missing or broken, or have a new query you want to run, just let us know.

Agenda

1. Installing gxadmin
2. Configuration
3. Overview
4. Admin Favourite Queries
5. gxadmin for Monitoring
6. Summary

Comment: Galaxy Admin Training Path

The yearly Galaxy Admin Training follows a specific ordering of tutorials. Use this timeline to help keep track of where you are in Galaxy Admin Training.

1. Step 1
   ansible-galaxy
2. Step 2
   backup-cleanup
3. Step 3
   customization
4. Step 4
   tus
5. Step 5
   cvmfs
6. Step 6
   apptainer
7. Step 7
   tool-management
8. Step 8
   reference-genomes
9. Step 9
   data-library
10. Step 10
    dev/bioblend-api
11. Step 11
    connect-to-compute-cluster
12. Step 12
    job-destinations
13. Step 13
    pulsar
14. Step 14
    celery
15. Step 15
    gxadmin
16. Step 16
    reports
17. Step 17
    monitoring
18. Step 18
    tiaas
19. Step 19
    sentry
20. Step 20
    ftp
21. Step 21
    beacon

Installing gxadmin

It’s simple to install gxadmin. Here’s how you do it, if you haven’t done it already.

Hands-on: Installing gxadmin with Ansible

1. Edit your requirements.yml and add the following:

   --- a/requirements.yml
   +++ b/requirements.yml
   @@ -28,3 +28,5 @@
      version: 6.1.0
    - name: usegalaxy_eu.rabbitmqserver
      version: 1.4.1
   +- src: galaxyproject.gxadmin
   +  version: 0.0.12
      
   

   Tip: How to read a Diff

   If you haven’t worked with diffs before, this can be something quite new or different.

   If we have two files, let’s say a grocery list, in two files. We’ll call them ‘a’ and ‘b’.

   Input: Old

   $ cat old
   🍎
   🍐
   🍊
   🍋
   🍒
   🥑
   

   Output: New

   $ cat new
   🍎
   🍐
   🍊
   🍋
   🍍
   🥑
   

   We can see that they have some different entries. We’ve removed 🍒 because they’re awful, and replaced them with an 🍍

   Diff lets us compare these files

   $ diff old new
   5c5
   < 🍒
   ---
   > 🍍
   

   Here we see that 🍒 is only in a, and 🍍 is only in b. But otherwise the files are identical.

   There are a couple different formats to diffs, one is the ‘unified diff’

   $ diff -U2 old new
   --- old	2022-02-16 14:06:19.697132568 +0100
   +++ new	2022-02-16 14:06:36.340962616 +0100
   @@ -3,4 +3,4 @@
    🍊
    🍋
   -🍒
   +🍍
    🥑
   

   This is basically what you see in the training materials which gives you a lot of context about the changes:

   • --- old is the ‘old’ file in our view
   • +++ new is the ‘new’ file
   • @@ these lines tell us where the change occurs and how many lines are added or removed.
   • Lines starting with a - are removed from our ‘new’ file
   • Lines with a + have been added.

   So when you go to apply these diffs to your files in the training:

   1. Ignore the header
   2. Remove lines starting with - from your file
   3. Add lines starting with + to your file

   The other lines (🍊/🍋 and 🥑) above just provide “context”, they help you know where a change belongs in a file, but should not be edited when you’re making the above change. Given the above diff, you would find a line with a 🍒, and replace it with a 🍍

   Added & Removed Lines

   Removals are very easy to spot, we just have removed lines

   --- old	2022-02-16 14:06:19.697132568 +0100
   +++ new	2022-02-16 14:10:14.370722802 +0100
   @@ -4,3 +4,2 @@
    🍋
    🍒
   -🥑
   

   And additions likewise are very easy, just add a new line, between the other lines in your file.

   --- old	2022-02-16 14:06:19.697132568 +0100
   +++ new	2022-02-16 14:11:11.422135393 +0100
   @@ -1,3 +1,4 @@
    🍎
   +🍍
    🍐
    🍊
   

   Completely new files

   Completely new files look a bit different, there the “old” file is /dev/null, the empty file in a Linux machine.

   $ diff -U2 /dev/null old
   --- /dev/null	2022-02-15 11:47:16.100000270 +0100
   +++ old	2022-02-16 14:06:19.697132568 +0100
   @@ -0,0 +1,6 @@
   +🍎
   +🍐
   +🍊
   +🍋
   +🍒
   +🥑
   

   And removed files are similar, except with the new file being /dev/null

   --- old	2022-02-16 14:06:19.697132568 +0100
   +++ /dev/null	2022-02-15 11:47:16.100000270 +0100
   @@ -1,6 +0,0 @@
   -🍎
   -🍐
   -🍊
   -🍋
   -🍒
   -🥑
   

2. Install the role with:

   Input: Bash

   ansible-galaxy install -p roles -r requirements.yml
   

3. Add the role to your playbook:

   --- a/galaxy.yml
   +++ b/galaxy.yml
   @@ -34,3 +34,4 @@
        - galaxyproject.nginx
        - galaxyproject.tusd
        - galaxyproject.cvmfs
   +    - galaxyproject.gxadmin
      
   

4. Run the playbook

   Input: Bash

   ansible-playbook galaxy.yml
   

With that, gxadmin should be installed! Now, test it out:

Hands-on: Test out gxadmin

1. Run gxadmin as the galaxy user and list recently registered users:

   Input: Bash

   sudo -u galaxy gxadmin query latest-users
   

   Input: Output

    id |          create_time          | disk_usage | username |       email        | groups | active
   ----+-------------------------------+------------+----------+--------------------+--------+--------
     1 | 2021-06-09 12:25:59.299651+00 | 218 kB     | admin    | admin@example.org  |        | f
   (1 rows)
   

1.sh

Configuration

If psql runs without any additional arguments, and permits you to access your galaxy database then you do not need to do any more configuration for gxadmin. Otherwise, you may need to set some of the PostgreSQL environment variables

Overview

gxadmin has several categories of commands, each with different focuses. This is not a technically meaningful separation, it is just done to make the interface easier for end users.

Admin Favourite Queries

Simon Gladman ‘s favourite: gxadmin query old-histories. He contributed this function to find old histories, as their instance has a 90 day limit on histories, anything older than that might be automatically removed. This helps their group identify any histories that can be purged in order to save space. Running this on UseGalaxy.eu, we have some truly ancient histories, and maybe could benefit from a similar policy.

Input

gxadmin query old-histories

Output

id	update-time	user-id	email	name	published	deleted	purged	hid-counter
361	2013-02-24 16:27:29.197572	xxx	xxxx	Unnamed history	f	f	f	6
362	2013-02-24 15:31:05.804747	xxx	xxxx	Unnamed history	f	f	f	1
347	2013-02-22 15:59:12.044169	xxx	xxxx	Unnamed history	f	f	f	19
324	2013-02-22 15:57:54.500637	xxx	xxxx	Exercise 5	f	f	f	64
315	2013-02-22 15:50:51.398894	xxx	xxxx	day5 practical	f	f	f	90
314	2013-02-22 15:45:47.75967	xxx	xxxx	5. Tag Galaxy-Kurs	f	f	f	78

Nate Coraor ‘s favourite: gxadmin query job-inputs. He contributed this function which helps him debug jobs which are not running and should be.

Input

gxadmin query job-inputs 5 # Or another job ID

Björn Grüning ‘s favourite: gxadmin query latest-users let’s us see who has recently joined our server. We sometimes notice that people are running a training on our infrastructure and they haven’t registered for training infrastructure as a service which helps us coordinate infrastructure for them so they don’t have bad experiences.

Input

gxadmin query latest-users

Helena Rasche ‘s favourite gxadmin report job-info. This command gives more information than you probably need on the execution of a specific job, formatted as markdown for easy sharing with fellow administrators.

Input

gxadmin report job-info 1

Output

# Galaxy Job 5132146

Property      | Value
------------- | -----
         Tool | toolshed.g2.bx.psu.edu/repos/bgruening/canu/canu/1.7
        State | running
      Handler | handler_main_2
      Created | 2019-04-20 11:04:40.854975+02 (3 days 05:49:30.451719 ago)
Job Runner/ID | condor / 568537
        Owner | e08d6c893f5

## Destination Parameters

Key                     |   Value
---                     |   ---
description             |   `canu`
priority                |   `-128`
request_cpus            |   `20`
request_memory          |   `64G`
requirements            |   `GalaxyGroup == "compute"`
tmp_dir                 |   `True`

## Dependencies

Name   |   Version   |   Dependency Type   |   Cacheable   |   Exact   |   Environment Path                          |   Model Class
---    |   ---       |   ---               |   ---         |   ---     |   ---                                       |   ---
canu   |   1.7       |   conda             |   false       |   true    |   /usr/local/tools/_conda/envs/__canu@1.7   |   MergedCondaDependency

## Tool Parameters

Name                 |   Settings
---------            |   ------------------------------------
minOverlapLength     |   500
chromInfo            |   /opt/galaxy/tool-data/shared/ucsc/chrom/?.len
stage                |   all
contigFilter         |   {lowCovDepth: 5, lowCovSpan: 0.5, minLength: 0, minReads: 2, singleReadSpan: 1.0}
s                    |   null
mode                 |   -nanopore-raw
dbkey                |   ?
genomeSize           |   300000
corOutCoverage       |   40
rawErrorRate         |
minReadLength        |   1000
correctedErrorRate   |

## Inputs

Job ID    |   Name                |   Extension     |   hda-id    |   hda-state   |   hda-deleted   |   hda-purged   |   ds-id     |   ds-state   |   ds-deleted   |   ds-purged   |   Size
----      |   ----                |   ----          |   ----      |   ----        |   ----          |   ----         |   ----      |   ----       |   ----         |   ----        |   ----
4975404   |   Osur_record.fastq   |   fastqsanger   |   9517188   |               |   t             |   f            |   9015329   |   ok         |   f            |   f           |   3272 MB
4975404   |   Osur_record.fastq   |   fastqsanger   |   9517188   |               |   t             |   f            |   9015329   |   ok         |   f            |   f           |   3272 MB

## Outputs

Name                                          |   Extension   |   hda-id    |   hda-state   |   hda-deleted   |   hda-purged   |   ds-id     |   ds-state   |   ds-deleted   |   ds-purged   |   Size
----                                          |   ----        |   ----      |   ----        |   ----          |   ----         |   ----      |   ----       |   ----         |   ----        |   ----
Canu assembler on data 41 (trimmed reads)     |   fasta.gz    |   9520369   |               |   f             |   f            |   9018510   |   running    |   f            |   f           |
Canu assembler on data 41 (corrected reads)   |   fasta.gz    |   9520368   |               |   f             |   f            |   9018509   |   running    |   f            |   f           |
Canu assembler on data 41 (unitigs)           |   fasta       |   9520367   |               |   f             |   f            |   9018508   |   running    |   f            |   f           |
Canu assembler on data 41 (unassembled)       |   fasta       |   9520366   |               |   f             |   f            |   9018507   |   running    |   f            |   f           |
Canu assembler on data 41 (contigs)           |   fasta       |   9520365   |               |   f             |   f            |   9018506   |   running    |   f            |   f           |

Catherine Bromhead contributed the ‘jobs’ query: gxadmin query jobs lets you list jobs that have been run on your Galaxy. It’s a lot more flexible than queue-overview and we suggest using it instead, in most places. E.g. to find circos jobs that were recently run:

Input

gxadmin query jobs --limit 2  --tool circos

or to see recent jobs from a specific user (e.g. to help answer their email queries when they just send you a screenshot rather than a proper bug report)

Input

gxadmin query jobs --limit 2 --user helena-rasche --terminal

gxadmin for Monitoring

gxadmin already supported query, csvquery, and tsvquery for requesting data from the Galaxy database in tables, CSV, or TSV formats, but we recently implemented influx queries which output data in a format that Telegraf can consume.

So running gxadmin query queue-overview normally shows something like:

Input

gxadmin query queue-overview

The gxadmin iquery queue-overview is run by our Telegraf monitor on a regular basis, allowing us to consume the data:

Input

gxadmin iquery queue-overview

Output

queue-overview,tool_id=toolshed.g2.bx.psu.edu/repos/iuc/unicycler/unicycler/0.4.6.0,tool_version=0.4.6.0,state=running,handler=handler_main_4,destination_id=12cores_180G_special,job_runner_name=condor count=1
queue-overview,tool_id=toolshed.g2.bx.psu.edu/repos/iuc/unicycler/unicycler/0.4.6.0,tool_version=0.4.6.0,state=running,handler=handler_main_5,destination_id=12cores_180G_special,job_runner_name=condor count=1
queue-overview,tool_id=toolshed.g2.bx.psu.edu/repos/devteam/freebayes/freebayes/1.1.0.46-0,tool_version=1.1.0.46-0,state=running,handler=handler_main_3,destination_id=12cores_12G,job_runner_name=condor count=1
queue-overview,tool_id=toolshed.g2.bx.psu.edu/repos/devteam/vcffilter/vcffilter2/1.0.0_rc1+galaxy1,tool_version=1.0.0_rc1+galaxy1,state=queued,handler=handler_main_11,destination_id=4G_memory,job_runner_name=condor count=1
queue-overview,tool_id=toolshed.g2.bx.psu.edu/repos/iuc/hisat2/hisat2/2.1.0+galaxy3,tool_version=2.1.0+galaxy3,state=running,handler=handler_main_11,destination_id=8cores_20G,job_runner_name=condor count=1
queue-overview,tool_id=toolshed.g2.bx.psu.edu/repos/devteam/fastqc/fastqc/0.72,tool_version=0.72,state=running,handler=handler_main_11,destination_id=20G_memory,job_runner_name=condor count=4
queue-overview,tool_id=ebi_sra_main,tool_version=1.0.1,state=running,handler=handler_main_3,destination_id=4G_memory,job_runner_name=condor count=2
queue-overview,tool_id=ebi_sra_main,tool_version=1.0.1,state=running,handler=handler_main_4,destination_id=4G_memory,job_runner_name=condor count=3

And produce some nice graphs from it.

You can use an influx configuration like:

[[inputs.exec]]
    commands = ["/usr/bin/galaxy-queue-size"]
    timeout = "10s"
    data_format = "influx"
    interval = "1m"

This often requires a wrapper script, because you’ll need to pass environment variables to the gxadmin invocation, e.g.:

#!/bin/bash
export PGUSER=galaxy
export PGHOST=dbhost
gxadmin iquery queue-overview --short-tool-id
gxadmin iquery workflow-invocation-status

Tip: Which queries support iquery?

This data is not currently exposed, so, just try the queries. But it’s easy to add influx support when missing! Here is an example, we set the variables in a function:

fields="count=1"
tags="tool_id=0"

This means: column 0 is a tag named tool_id, and column 1 is a field (real value) named count. Here is an example that has multiple fields that are stored.

Summary

There are a lot of queries, all tailored to specific use cases, some of these may be interesting for you, some may not. These are all documented with example inputs and outputs in the gxadmin readme, and help is likewise available from the command line.

Hands-on: Time to git commit

It’s time to commit your work! Check the status with

git status

Add your changed files with

git add ... # any files you see that are changed

And then commit it!

git commit -m 'Finished Galaxy Monitoring with gxadmin'

Comment: Got lost along the way?

If you missed any steps, you can compare against the reference files, or see what changed since the previous tutorial.

If you’re using git to track your progress, remember to add your changes and commit with a good commit message!

Comment: Galaxy Admin Training Path

The yearly Galaxy Admin Training follows a specific ordering of tutorials. Use this timeline to help keep track of where you are in Galaxy Admin Training.

1. Step 1
   ansible-galaxy
2. Step 2
   backup-cleanup
3. Step 3
   customization
4. Step 4
   tus
5. Step 5
   cvmfs
6. Step 6
   apptainer
7. Step 7
   tool-management
8. Step 8
   reference-genomes
9. Step 9
   data-library
10. Step 10
    dev/bioblend-api
11. Step 11
    connect-to-compute-cluster
12. Step 12
    job-destinations
13. Step 13
    pulsar
14. Step 14
    celery
15. Step 15
    gxadmin
16. Step 16
    reports
17. Step 17
    monitoring
18. Step 18
    tiaas
19. Step 19
    sentry
20. Step 20
    ftp
21. Step 21
    beacon

You've Finished the Tutorial

Please also consider filling out the Feedback Form as well!

Key points

• gxadmin is a tool to run common database queries useful for Galaxy admins

• new queries are welcome and easy to contribute

Frequently Asked Questions

Feedback

Did you use this material as an instructor? Feel free to give us feedback on how it went.
Did you use this material as a learner or student? Click the form below to leave feedback.

Citing this Tutorial

1. Helena Rasche, Galaxy Monitoring with gxadmin (Galaxy Training Materials). https://training.galaxyproject.org/training-material/topics/admin/tutorials/gxadmin/tutorial.html Online; accessed TODAY
2. Hiltemann, Saskia, Rasche, Helena et al., 2023 Galaxy Training: A Powerful Framework for Teaching! PLOS Computational Biology 10.1371/journal.pcbi.1010752
3. Batut et al., 2018 Community-Driven Data Analysis Training for Biology Cell Systems 10.1016/j.cels.2018.05.012

BibTeX

@misc{admin-gxadmin,
author = "Helena Rasche",
	title = "Galaxy Monitoring with gxadmin (Galaxy Training Materials)",
	year = "",
	month = "",
	day = ""
	url = "\url{https://training.galaxyproject.org/training-material/topics/admin/tutorials/gxadmin/tutorial.html}",
	note = "[Online; accessed TODAY]"
}
@article{Hiltemann_2023,
	doi = {10.1371/journal.pcbi.1010752},
	url = {https://doi.org/10.1371%2Fjournal.pcbi.1010752},
	year = 2023,
	month = {jan},
	publisher = {Public Library of Science ({PLoS})},
	volume = {19},
	number = {1},
	pages = {e1010752},
	author = {Saskia Hiltemann and Helena Rasche and Simon Gladman and Hans-Rudolf Hotz and Delphine Larivi{\`{e}}re and Daniel Blankenberg and Pratik D. Jagtap and Thomas Wollmann and Anthony Bretaudeau and Nadia Gou{\'{e}} and Timothy J. Griffin and Coline Royaux and Yvan Le Bras and Subina Mehta and Anna Syme and Frederik Coppens and Bert Droesbeke and Nicola Soranzo and Wendi Bacon and Fotis Psomopoulos and Crist{\'{o}}bal Gallardo-Alba and John Davis and Melanie Christine Föll and Matthias Fahrner and Maria A. Doyle and Beatriz Serrano-Solano and Anne Claire Fouilloux and Peter van Heusden and Wolfgang Maier and Dave Clements and Florian Heyl and Björn Grüning and B{\'{e}}r{\'{e}}nice Batut and},
	editor = {Francis Ouellette},
	title = {Galaxy Training: A powerful framework for teaching!},
	journal = {PLoS Comput Biol} Computational Biology}
}

                   

Galaxy Administrators: Install the missing tools

You can use Ephemeris's shed-tools install command to install the tools used in this tutorial.

shed-tools install [-g GALAXY] [-a API_KEY] -t <(curl https://training.galaxyproject.org/training-material/api/topics/admin/tutorials/gxadmin/tutorial.json | jq .admin_install_yaml -r)

Alternatively you can copy and paste the following YAML

---
install_tool_dependencies: true
install_repository_dependencies: true
install_resolver_dependencies: true
tools: []

Feedback

5 stars	2
4 stars	1

March 2022

• 5 stars: Liked: Is really cool to see lots of different admins pooling their knowledge and effort in this way Disliked: From a voice control perspective, it would be useful if the hands-on gxadmin commands were in a copiable code block like they are in other tutorials – it really helps to be able to copy them with the single click. Also, the 'admin favourites' section could include the arguments for the functions listed, e.g. , for a bit more clarity

July 2021

• 5 stars: Liked: Simple and clear Disliked: The Hands-on: Adding a query did not work as the user ubuntu but had to be done as the user galaxy