My MOVEit Transfer server is responding slowly and timing out when processing HTTP, SFTP, and FTPS requests. The task manager on the server shows the 'mysqld.exe' process consuming high amount of CPU.

If your MOVEit Transfer server is using MySQL as the database engine, you could experience issues if your database size exceeds 2GB (MySQL - Database Size )  or if your system is too busy and
requires a more robust database engine such as Microsoft SQL Server (How do I convert my MOVEit Transfer (DMZ) database from MySQL to Microsoft SQL Server? ) to properly handle the production load.

Check for Long Running Queries
You can find out if your system load is too high by reviewing All Debug DMZ_WEB logs and checking for long running queries using Notepad++ with the search term 'OK in ....+ms' with the 'regular expression' option enabled.

Results will appear if queries take longer than 1000 milliseconds to complete.


Review Currently Running Process
You can review what the MySQL service is currently processing by running the 'processlist' command. The below command will create a text file containing that information (This must be run from the MySQL bin folder).

• C:\MySQL\Bin>mysql -u root -p -e "show full processlist" > C:/processlist.txt

 

Perform Row Count on Database Tables
Use the command below to extract a row count for all tables in your MySQL instance. Here we can check for excessively large tables that can potentially point to an underlying problem. You should run the MySQL analyze command first to ensure the information_schema table is up-to-date.

• C:\MySQL\Bin>mysqlcheck.exe -a -uroot -p --databases <database_name>
• mysql>SELECT table_name, table_rows FROM information_schema.tables WHERE table_schema = '<database_name>' ORDER BY table_rows desc;

Check Table Lock Contention
You can analyze table lock contention on your system by checking the 'Table_locks_immediate' and 'Table_locks_waited' status variables, which indicate the number of times that requests for table locks could be granted immediately and the number that had to wait, respectively.(1).

• mysql>SHOW STATUS LIKE 'Table%';

A large or growing Table_locks_waited value is the sign of a serious concurrency bottleneck. Any long running select will block an insert or update from happening. That will in turn prevent any further selects from occurring until after the queued insert or update is complete. Your computer will suddenly begin working in serial with respect to MySQL. It doesn't matter how many CPUs you throw at it, only one will be used.(2).

High MySQL CPU usage can potentially occur if a Brute Force login attempt is taking place, especially if the change is very sudden. Before proceeding, check the system for unusual login attempts using the built in security reports such as 'Suspicious Usernames - Many Attempts' or 'Suspicious Usernames - Many IPs'. If suspicious activity is found, offending IP addresses must be blocked at the firewall level so the requests do not reach the MOVEit Transfer server.

Other causes may include:

• Poor system maintenance (Transfer - Database Maintenance & Archiving) 
• Under-resourced application server (RAM, CPU, HD, etc.)
• Database engine no longer able to sustain workload - MySQL, MSSQL (MOVEit - Database Engine Recommendations ) 
• System architecture no longer able to sustain workload (Single Node Instance, Multi-Node Webfarm)
• Outdated application version (Upgrade and/or Migration Guide for MOVEit Automation and MOVEit Transfer ) 

*Please note, truncating database tables will result in permanent data loss.

Follow the steps below to backup and purge the MOVEit Transfer MySQL database.
 

1. Take a virtual machine snapshot of MOVEit Transfer server.
2. Create a backup file using the dmzbackup utility (Transfer - Backup Utility ).
   1. C:\MOVEitTransfer\Scheduler>dmzbackup.exe --output=C:\backups\Transfer_Backup_[YYYY][MM][DD].zip
3. Create a copy of the C:\MySQL folder and store it in a secure location.
4.  (Optional) Run a mysqldump from the MySQL bin folder. You will be prompted for the root password (If you receive an error regarding ''secure-file-priv' variable, see article MySQL - Export Query Results to a CSV File). The file path (C:/backups/mysqldump) must exist or you will receive an error.
   1. C:\MySQL\Bin>mysqldump -u root -p -t -T C:/backups/mysqldump <database_name> --fields-terminated-by=,
5. Turn off all services besides mysql.
6. Connect to MySQL (see article MOVEit - Connect to MySQL Using Command Prompt ) and truncate the following tables: (folderperms, folderperminfo, sysstats, log, any large tables).
   1. mysql>TRUNCATE TABLE <table_name>;
7. Run a mysql check from the MySQL bin folder. You will be prompted for the root password.
   1. C:\MySQL\Bin>mysqlcheck.exe -c -uroot -p --databases <database_name>
8. Run a mysql optimize from the MySQL bin folder. You will be prompted for the root password.
   1. C:\MySQL\Bin>mysqlcheck.exe -o -uroot -p --databases <database_name>
9. Stop the MySQL service.
10. Start all services.
11. Check system functionality.
12. Process complete.

References to other Documentation:
-(1) 8.11.1 Internal Locking Methods
-(2) Lock Contention: To MyISAM or InnoDB, Everita Ltd
MyISAM documentation:
https://dev.mysql.com/doc/refman/8.0/en/myisam-storage-engine.html

InnoDB documentation
https://dev.mysql.com/doc/refman/8.0/en/innodb-storage-engine.html

Everita, LTD:
https://www.everita.com/