We have been having a database issue on our Production Galaxy.
Fortunately, it does not impact performance of the queries, but it is still there and would like your advice on how to address it.
# Background:
- We are running a very outdated version of Galaxy
- We run thousands of queries a day
- We run Galaxy under Docker
- Response time on the queries is good, no user complaints.
- We have not changed anything in Galaxy for a long while.
- Size of the USED database is 2.3 terabytes
# Description of the problem:
We run the cleanups, and cleanup “3” purge deleted datasets runs for MONTHS.
We suspect that when we migrated in the past from one file system to another, somehow the data from the old systems is still in the database as garbage. If so, how to clean?
The script to clean looks as follows:
#!/bin/bash
export GALAXY_VIRTUAL_ENV=/galaxy_venv
source $GALAXY_VIRTUAL_ENV/bin/activate
export script="/galaxy-central/scripts/cleanup_datasets/cleanup_datasets.py"
export config="/etc/galaxy/galaxy.ini"
export logdir="/export/galaxy-central/logs"
export days=15
export num=$1
nohup $script $config -d $days -$num -r -f >> $logdir/cleanup$num.log &
And we submit it as follows
./run_cleanup.sh 3
We run the cycle as follows, but it takes MONTHS to run
./run_cleanup.sh 1
./run_cleanup.sh 2
./run_cleanup.sh 4 ***** Note 4 comes before 3
./run_cleanup.sh 5 ***** Note 5 comes before 3
./run_cleanup.sh 3 *** This is the one that frees most of the space
./run_cleanup.sh 6
Looking at the log of cleanup 3 we see many occurrences like in the image enclosed
Looking at old logs - at the last 5 lines of each of the cleanups, we see:
.log@b2a72aa73839:/export/galaxy-central/logs/logs_DT2019_June# tail -5 cleanup1
Deleting history id 172245
Deleting history id 172003
Deleted 156774 histories
Elapsed time: 106138.901154
##########################################
.log@b2a72aa73839:/export/galaxy-central/logs/logs_DT2019_June# tail -5 cleanup2
Processing history id 46531 (Unnamed history)
Purging history id 46531
Purged 150295 histories.
Elapsed time: 2624113.57499
##########################################
.log@b2a72aa73839:/export/galaxy-central/logs/logs_DT2019_June# tail -5 cleanup3
Purging dataset id 1224582
Purged 1171574 datasets
Freed disk space: 2533726787392
Elapsed time: 6668142.44866
##########################################
.log@b2a72aa73839:/export/galaxy-central/logs/logs_DT2019_June# tail -5 cleanup4
Datasets will be removed from disk.
Purged 0 libraries .
Elapsed time: 0.0785779953003
##########################################
.log@b2a72aa73839:/export/galaxy-central/logs/logs_DT2019_June# tail -5 cleanup5
Datasets will be removed from disk.
Purged 0 folders.
Elapsed time: 0.053337097168
##########################################
.log@b2a72aa73839:/export/galaxy-central/logs/logs_DT2019_June# tail -5 cleanup6
Marking as deleted: HistoryDatasetAssociation id 1266634 (for dataset id 1241863)
Deleting dataset id 1241863
Examined 1659563 datasets, marked 1313117 datasets and 1327789 dataset instances (HDA) as deleted
Total elapsed time: 1973954.4967
##########################################
root@b2a72aa73839:/export/galaxy-central/logs/logs_DT2019_June#
The reason that we are looking into this is that we want to upgrade Galaxy to a more current release - we know that it will involve a step to upgrade the database and we are concerned that, if cleanups take months to complete, upgrade could too - making it impractical to upgrade.
![Screen Shot 2020-07-29 at 9.41.20 AM|690x202]
(upload://4yWJqWZGHa0E1Fmcm3nA3xSqgbf.png)