We have used quite some time during the last month on analysing the use and speed of different operations in the user interface on MyRapidi.com
Some of our customers run many transfers and run them very often. This generates a lot of entries in the log table in the corresponding configuration database. We already had a process in place that deletes old log entries that are not relevant anymore. For example all error messages are deleted after 3 months and all transfer progress messages are deleted after 7 days.
We also have a process in place that compresses statistics entries to one monthly entry per transfer and source/destination. This compression is currently done for statistics that are older than 1 year.
This of cause helps to keep the number of entries in the Log a bit down but we can still easily get 10 or 20 million entries in the log for some services.
We have done several things to optimise this area: We have tested and selected the best indexes to use on this table and we have optimised the querries to work as fast as possible with these indexes.
Here are a few examples of the speed optimisations that we have gained (the times are only relevant for very large log databases, but smaller ones will also have a relative gain in speed also):
- Opening the Runs page going from 60 seconds down to under 1 second.
- Opening the Statistics page to show 1 months statistics for the largest log table going from 9 minutes down to 1 minute (we plan further optimisations here).
In general the optimisations of these specific slow querries will have a very positive effect on the responsiveness of myrapidi.com as normal requests will no longer queue up waiting for these slow requests.
As always, comments are more than welcome !