Friends,
Thank you for your patience while we were trying to diagnose/resolve the issue with our database’s erratic “glacial” performance. After running several tests, the problem was identified to be due to table locking.
Quoting our web page guru: “While someone was making an update or insert to a record, MySQL would lock the entire table or tables from being used by anyone else until the operation finished. This was very noticeable with the huge observations table. To help with this we changed the table backend to one that does row level locking instead. Technically there is a bit more overhead resource wise but the overall user experience is better. Now that this first database has been converted and it seems to be working well, we would like to do all the databases and associated tables on the server. The other changes that we have made were to the MySQL configuration. We increased buffers and caches to keep more things in memory (very fast) instead of having it hit the hard drive (slower).”
As our databases are evolving and growing in size and complexity, we are working towards improving their performance and we need to make sure that, moving forward, we will not be encountering such issues. We spend all day yesterday making the necessary modifications, and we hope that was a good solution to the problem. Please do let us know if you see any improvement while uploading data to our AID through WebObs. We do appreciate your feedback.
Best wishes – clear skies,
Stella.
Great job. Big thanks to the team working through this problem.
Douglas