« Return to Blog

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.

Thanks!
-Brandon

Comments for this post:

Great!
Good to see the site back up. I've got an issue, though. I try to back up my transactions once a month by exporting to CSV. I just tried it and can no longer do it. It gives me the following error:

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
by twim7693 on Jun 20, 2013
twim,

Please email us and let us know what search criteria you're using to export your transactions.
by Brandon on Jun 20, 2013
Thank you!
Thanks for the update and thanks for such a great site/app. I suggest it to a lot of my friends and some clients who are wanting to learn how to better manage their finances. I tried quite a few before finding CCB and I am very happy with this one. I have used it for about 4 years now.
by Tadascott on Jun 20, 2013
Thanks!
Keep up the good work, nothing compares to this site. I have tried a lot of sites but this site rocks. I use this site multiple times a day and it's always been there for me. I have used this site for more then 5 years now. Thanks again!
by gmeyer8 on Jun 20, 2013
Thought
What if you were to simply flag a transaction as deleted VS actually deleting it from the database (e.g. a simple bit field named "is_deleted")? This would fix two issues:

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.
by jcowher on Jun 25, 2013

Archives

» 2018
» 2017
» 2016
» 2015
» 2014
» 2013
» 2012
» 2011
» 2010
» 2009
» 2008
» 2007
» 2006

Categories

Contact ClearCheckbook

If you have a question about the site, please check the Support Forums, Tutorials and Help section to see if it's already answered there.

Your message has been sent.

Thank you for reaching out to us. We work hard to respond as quickly as possible to our emails. You'll receive a response soon.