If you’ve been running your TestRail Server installation for a long time, the database may accumulate redundant test cases and/or tests. The redundant tests and cases are a result of inefficient code in TestRail that was optimized back in our TestRail 5.5 release.
The redundant rows were created for test cases that were not selected in a test run both at the time of the creation of those runs and when the runs were closed. The application worked just fine but would accumulate this redundant data over time and eventually the size of the cases and the tests table would cause performance degradation. Slowdowns and lock timeouts may occur as a result.
Depending on the age of the database and the activity, up to 90% or more of the rows could be redundant.
We’ve assisted customers running into performance issues as a result of this redundant data both in our cloud infrastructure and on-premise (Server) installations. In order to resolve the issues, we’ve utilized a couple of scripts to identify and remove redundant rows from the TestRail database. By popular demand, we’re making the scripts available for use by Server customers who wish to utilize them in order to improve the performance of their TestRail installation.
Because MySQL Innodb is not good at mass deleting rows, the scripts use a "copy, drop and rename" method which is much faster (days faster even with some customer databases). The script creates new versions of the tables and populates them with valid rows. They also create versions of the tables with the redundant rows. Effectively the scripts split the tables into good and bad tables. After creating those tables the old tables are deleted and the good tables are renamed to become the new versions of the tables.
The resulting tables are left in the database "just in case". After a reasonable amount of time to make sure that everything is OK, they can be dropped. This could potentially significantly increase the performance including the deletion of old closed test runs.
The scripts can be found below and should be executed using the following command:
php [mr_newtest.php, mr_newcase.php] -h {host} -u {user} -p{password or leave blank and it prompts you} -d {database}We recommend executing the scripts outside of office hours since they will lock the tables. Please create a backup of your database before executing the scripts!
Example output follows:
php mr_newtest.php -h <hostname> -u <user> -p -d <db>
03/04/2021 02:04:32 am: dropping new_tests table if it exists
03/04/2021 02:04:32 am: dropping deadwood_tests table if it exists
03/04/2021 02:04:32 am: dropping old_tests table if it exists
03/04/2021 02:04:32 am: creating new_tests table
03/04/2021 02:04:32 am: creating deadwood_tests table
03/04/2021 02:04:32 am: Locking all tables
03/04/2021 02:04:32 am: inserting is_selected = 1 tests into new_tests table
03/04/2021 02:04:32 am: insert into new_tests select * from tests where is_selected = 1
03/04/2021 02:48:56 am: inserting is_selected = 0 tests into deadwood_tests table
03/04/2021 02:48:56 am: insert into deadwood_tests select * from tests where is_selected = 0
03/04/2021 07:57:57 am: counting rows in new_tests table
03/04/2021 07:58:13 am: Number of rows in new_tests table: 17849816
03/04/2021 07:58:13 am: counting rows in deadwood_tests table
03/04/2021 08:01:42 am: Number of rows in deadwood_tests table: 796606952
03/04/2021 08:01:42 am: unlocking all tables
03/04/2021 08:01:42 am: Renaming tables
03/04/2021 08:01:42 am: dropping old_tests table
03/04/2021 08:01:49 am: Finished.Please note that after you run the script(s) you may not see a reduction in the overall size of your database footprint. This is due to the creation of the temporary tables with the redundant data copied to them. As mentioned above, once you’re happy that your TestRail instance is functioning correctly with the desired data, you can remove them with the associated reduction in overall database size.