More database updates to ClearCheckbook
9/15/2011 in ClearCheckbook Updates
I just wanted to give a little more clarification on what's going on behind the scenes and why you experienced some extreme slow downs this morning.
ClearCheckbook has over 20 million transactions stored in its database for over 180,000 users. In extremely simple terms, whenever you load your ClearCheckbook account, the site has to dig through those 20 million+ transactions to grab yours. As time goes on, this takes longer and longer as more transactions get added to the site. We want to keep the site running as quickly as possible so we came up with a couple solutions, the best of which was to archive transactions for people who hadn't signed on in a while.
Last night we started a script that would take some of those 20 million transactions for people who haven't logged into the site in a while and move them into a different table, one that doesn't need to be called when we're pulling up your transactions. (Don't worry though if you log back in and you haven't been on in a while, we have it set up to automatically restate all the archived transactions for your account if they exist).
Some time during the night, our database has a command that runs periodically to rebuild indexes and maintain associations. This is something the database does on its own to keep its own speed up. When that started running in conjunction with our archive script, it really started to bog down the database because tens of thousands of transactions were getting archived at a time and the script was trying to continually re-index all the tables at the same time.
We didn't catch what was going on until a little while ago, so we killed both our archiving script and the "autovaccuum" script the database was running and things have returned more or less to normal.
We're sorry for the disruptions this has caused. Rest assured your data is safe as we always perform a full backup before running any updates like this. Our overall goal is to keep this site running as quick as possible to let everyone get instant access to their finances. We're sorry we dropped the ball on this one. We won't let the same thing happen again.
CEO / Founder of ClearCheckbook
Last night and continuing for the next few nights, we will be running some database updates that are aimed at speeding up site performance. These updates revolve around archiving transactions for members who haven't logged into the site for a while. This will help speed up the site because there won't be so many unused transactions taking up space in the database.
The script from last night ran a little long and if you tried visiting the site early this morning you may have noticed some slow loading pages. We have fixed this so it won't happen the next time we run the script.