Database Issues Resolved - Site Loading Speeds Improved
6/19/2013 in ClearCheckbook News
If you've tried to access the site over the last couple of weeks you might have noticed some slow load times or even some site outages. We've been trying to figure out what the problem is and have been updating various aspects of the site, server and database to try to increase reliability.
We finally found the root of the issue and fixed it. There should be no unscheduled down-times or overloaded databases now.
Technical Wording Alert...
We knew the issue was database related. We were keeping track of long running queries that would start causing a snowball effect that would eventually cascade into a database overload. All of the queries were coming from the massive 40+million transactions table in our database.
The problem was there was no rhyme or reason to the long running queries behaving the way they did. They would perform fine on some accounts and not at all on others. We used this as a starting point and worked from there.
We initially thought it was a vacuuming issue (basically keeping the table in check for quick access) where the table was too large and the vacuum would die part way through. That's why we took the site down last night to run a full vacuum on the table. Unfortunately, as many of you noticed today, that didn't help. The issues persisted and the database continued to overload.
Fast forward through a lot of high blood pressure inducing brainstorming and we finally realized the transactions table was highly fragmented across our hard drives. All of the adding/deleting of transactions left data all over the place. This meant it took much longer than normal to access all the data quickly and efficiently.
We ended up dumping and reloading the data cleanly and sequentially on our hard disks and that fixed the problem. Everything appears to be fixed now with the transactions table.
Over the next week we'll be conducing the same action on our other large tables to help improve site performance even more.
We'd like to thank you all for dealing with these issues as we've encountered them. I launched ClearCheckbook in 2006 because I wanted everyone to be able to quickly and easily have access to their financial information. Whenever the site goes down I do my best to be transparent and let everyone know what's going on and work to fix the problems as quickly as possible.
ClearCheckbook has grown tremendously over the last few years (we just passed our 250,000th registered user) and we've had our technical hurdles from time to time. Each time we learn something new and work to make sure nothing like that happens again.
Please rest assured that ClearCheckbook is my #1 focus and we're doing our best to provide you with the best money management site and app suite available.
If you have any other questions, please let us know.
Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 372526 bytes) in /var/www/clearcheckbook/app/models/CCB/Export.php on line 187
Please email us and let us know what search criteria you're using to export your transactions.
a) oops... i didn't mean to delete that transaction and i want it back
b) data wouldn't get fragmented because you aren't actually deleting anything. you're simply changing an existing bit value that's already been allocated space on your disk.