How to shrink the size of the embedded Mongo database

[CristianoJ]

How To: Shrink the size of Alteryx Server Embedded MongoDB

It is common to see cases where the customer says that they are running out of space on their database.

The customer goes into his Alteryx System Settings > Controller > Persistence > Persistence Options, decreases the number of days he wants to hold on to workflow results, completed schedules, or uploaded files in their database, they then notice that the workflow results, completed schedules, or uploaded files have less records, however the size of his database remains the same.

When you change the persistence options by changing the number of days you want to delete workflow results, completed schedules, or uploaded files from the database, you are indeed getting rid of those files, however the space where those files were, are now empty spaces in the database. In order to get rid of those empty spaces you need to perform a database backup and restore, which will then eliminate all of those empty spaces and shrink the size of the database. This is explained in the MongoDB documentation, here, scrolling down to the section named "How do I reclaim disk space in WiredTiger?".

Prerequisites

• Product - Alteryx Server
• Product - Embedded MongoDB

Example

Keep in mind that this procedure below is based on an existing sample I created and the description is based on the sample.

1. Verification

• Verify Size of Embedded MongoDB by navigating to the database folder in a File Explorer > right click > select properties > verify size.
  - Notice on the screenshot below thatin my case the MongoDB has a size of 913 MB.
  - To find out where your database folder open Alteryx System Settings > Controller > Persistence > Data Folder
• Open Alteryx Designer in the server and go to Options > View Schedules > Make sure that the Controller field is set to "My Computer" > go into the Results tab > verify the number of results in the bottom left of the View Schedules screen
  - Notice on the screenshot bellow that my scheduler contains 174 results

2. Implementation

• Open Alteryx System Settings > Controller > Persistence > Persistence Options
  If you do not have any of the Persistence Options Enabled then enable them by selecting the checkboxes and set it to the desired number of days.
  The Persistence options are: - Delete queue and results after (days) - Delete completed schedules after (days) - Delete uploaded files after (days)
• When you enable Persistence Options, which are pretty much self explanatory, queue and results, completed schedules, and uploaded files will be deleted after the number of days set by the user. In my example the Alteryx System Settings had the Persistence Options "Delete queue and results after (days)" and "Delete completed schedules after (days)" set to delete my results that are over 120 days old. I changed those settings to 30 days and then I kept clicking next through all the screens until it finished and the service automatically restarted.

3- After Implementation

• Back into my scheduler you can see that the total number of results went down from 174 (previously checked in the verification section above) to only 71 workflows.
• Also notice that the size of my database did not change. It is still 913 MB.

The procedure above did delete multiple documents from the database, however (as mentioned on the introduction of this article above) those documents are now empty spaces and the size occupied by the database remains the same.

Procedure

In order to get rid of the empty spaces in the database it is necessary to do a database backup and restore.

1. Backup

• Create an empty folder to store the backup in. I named it MongoDB_backup.

• Open command prompt as administrator
• Navigate to path “Program Files\Alteryx\bin”
• Stop the service by using command "AlteryxService.exe stop"
• Then enter the emongodump command as seen below (AlteryxService.exe emongodump=Z:\Path\MongoDB_backup):

After the backup finishes it is time to do a restore.

5. Restore

• As you can see on my screenshot below, first I checked if the service was still stopped.
• Rename the current database folder (i.e. MongoDB_4_0.old).
• I then created a new empty folder with the exact same name as what my Mongo database had: MongoDB_4_0
• Then I ran the emongorestore command - (alteryxservice emongorestore=,)
• Once the restore finished I started the service. (AlteryxService.exe start)
• Then I verified the status of service to make sure it is running.

6- Verify if the size of the database has decreased

• In Windows File Explorer right-click over the MongoDB_4_0 folder and select Properties
• Notice that the size is now 693 MB, rather then 913 MB as it initially had.

Additional Resources

• Alteryx Server Backup and Recovery Part 1: Best Practices
• Alteryx Server Backup and Recovery Part 2: Procedures