As some of you have noticed about once a week it seems like the site really starts to slow down and get sluggish or not let you log in. This is caused by a few issues which I'll explain below. We're also working to fix these issues so the site continues to run quickly and efficiently in the future.
Issue #1: Complex Database Queries
ClearCheckbook has massive amounts of data we have to process each time you perform an action on the site. To give you a little perspective, we have over 39 MILLION transactions in our database. On an average day we're getting almost 50,000 new transactions added to the site.
This is a lot of data to handle and some of the reports or search functions require processing a lot of this information. The database queries to perform some of these actions are very complex and can take a while to complete. What we've noticed by looking at our logs after a site outage is that people get a little impatient and refresh the page or click the search button several times in a row which adds to the list of complex queries being run. It acts a bit like a snowball where the more of these complex queries that add up, the longer it takes the site to get to other less intense queries. If enough of these build up, it causes the slowdowns you experience.
We're usually around to catch these pretty quickly but sometimes they happen early in the morning before we're at our computers.
Issue #2: Site Growth
This is something we've had to deal with since we launched the site almost 7 years ago. ClearCheckbook is very popular and always seems to be growing. We're not complaining about that at all, but it seems like with each growth spurt we discover new issues that arise due to large amount of traffic hitting the site.
When you combine both of those issues, it means our site is getting used at all times by people all over the world and there's constant database activity. As more people use the site, more of those complex queries are getting regularly run.
What We're Doing:
Each time the site goes down it's because of the database being overloaded. When this happens we can look at the longest running queries to figure out what started the so called "snowball" mentioned above. Whenever this happens, we look at the queries in question and work on optimizing them and the database to prevent this from happening again.
We've fixed countless queries and performed more optimizations to the site than we'd care to admit and we believe we're getting close to optimizing all of these complex queries that take a little longer to run.
I built ClearCheckbook because I wanted a way to easily manage my money. When I made this my full time job in September 2009 it became my main focus and all I wanted was to see the site succeed and help people with their finances.
Whenever the site has these hiccups it may frustrate you, but believe me when I say that it's infinitely more frustrating to me. We have almost a quarter million registered users so whenever there's a site issue I feel like I'm personally letting everyone down.
All I ask is that you're patient with us while we're going through some of these growth spurts. We're constantly working to make the site better by providing updates, new features, quick bug fixes and more.