« Return to Blog

Transactions fully restored and more clarity about what happened

7/9/2015 in ClearCheckbook News

All of your transactions have been restored to your accounts and they will show up as they normally do on both the web and mobile apps.

What the heck happened?
Now, a little more explanation about what happened and what we're doing to future-proof the site. On the morning of Tuesday June 30th, the database started getting hung up on certain queries which caused a queue of queries to form. When this happens, the server tries its best to get through the queue as fast as possible, but in doing so, overloads and causes the queue to essentially stop in its tracks. This makes itself visible to you by seeing a bunch of errors and not being able to log into the site.

We were able to get things running again for the rest of the day but two days later on the morning of Thursday July 2nd the database had the same issue. We scrambled to get things running again but everything we'd done in the past simply wasn't working. We narrowed the problem down to a single table in our database, the Transactions table. This is what stores all of the transactions that ClearCheckbook users add to the site, all 75+ million of them. Whenever changes are made to this table (adding, editing or deleting transactions), indexes that help the database run more efficiently are updated. Normally this is a quick process but on 75M rows of data, this can take a long time.

To try and speed up the reading/writing of this table, we archived transactions for users who hadn't logged into the site for a few months. Unfortunately this didn't help too much and after a few hours we were back to the database overloading issues. By now it's Friday July 3rd and we're scrambling to get things figured out but it seems like nothing we do is helping at all. It's now that we make some calls and, fortunately, even on a Friday afternoon before Independence Day in the United States, we were able to reach some outside help that was able to meet up on Saturday July 4th and work with us.

The first thing we did was backup all transactions and force manual restoring when you logged back in. This worked fairly well and at least got the site operational. The problem was many people were reporting that not all of their transactions fully restored and were concerned that their data was lost.

What we're doing in the short term
This obviously wasn't a long term solution and the confusion that arose made us speed up plans to break up the giant Transactions table into about 30 smaller tables based on user id. This will be our mid-to-long term solution as far as the database schema goes. The downtime tonight was necessary so we could move all the transactions from the giant tables into the smaller ones. In the short term, this will help the site maintain uptime on our current hardware since the database won't have to search 75M transactions regularly.

What's next
The next step, and what we're working on now, is migrating to a cloud based solution. The outside help we mentioned earlier also excels at making these transitions and has done so with other companies in the past. We're working with them now to optimize our site code for its next home in the cloud. The cloud solution will help us expand and scale up as the site grows without having to maintain our own servers.

A heartfelt apology
I have to admit that this is the single most stressful situation I've ever been in before. When I took ClearCheckbook on as my full time job back in 2009, I knew there would be growing challenges along the way. I use the site daily myself for both personal and business accounts. When the site is having problems, I know how frustrating it can be.

What compounds the frustration is when I receive threatening, cursing and hate filled messages to my personal cell phone and email accounts and our social media outlets and the ClearCheckbook blog while we're working our hardest to resolve the problems. At the height of the issues, we were receiving several hundred emails an hour. There's no possible way we could respond to each one of your messages individually and still have time to work on the database problems.

I can't express how sorry I am that this happened the way it did. Please know that we do have a plan of action to prevent this kind of mess from happening in the future and I'll be posting more information about it as a plan and timeline solidifies.

If you're a premium member who contacted us to cancel/refund your membership, we tried to search through all the messages and fulfill your requests, but there are probably some that slipped through the cracks. If you haven't heard back from us and still wish to stop using the site, please contact us again through the Contact Us link at the bottom right side of the page.

Again, from the bottom of my heart, I'm so extremely sorry for the inconveniences the downtime caused you.

Brandon OBrien
Founder, ClearCheckbook

Comments for this post:

Hats off to you & Thank you
Brandon,

Hatsoff and I sincerely appreciate your diligent work in getting the site up and running. Thanks for restoring the data as it existed earlier. Keep up the good work.
by radkrishg3 on Jul 9, 2015
Excellent Summary
Dear Brendon,
It is unfortunate that you received negative mails, however, you did excellent in posting continuous updates. In the current world scenario where the human race has become extremely agitated and short tempered, i think you have done a wonderful job and hope that unwarranted situations do not affect your ongoing progress. Thanks once again and best regards.
by joyrodricks on Jul 9, 2015
Just wanted to say thanks for the detailed explanation and updates throughout the week. Being a developer myself it's always appreciated when someone takes the time to explain what actually is going on and I have to say you handled the entire thing way better than most companies would have. Hats off.
Keep up the good work!
by adara on Jul 9, 2015
Thank you
Again thank you, all is back as it should be. I have been thru what you describe with a very MS SQL database my self and my boss spent 8 hours on the phone with MS to get thing back up and running. While waiting for the long process of SQL chewing its way through the tables making repairs my boss suggest that we might want to work on our resume's. It is a painful process doing table repair, again thank you.

I do have one very minor item for you list. With the Android app when you enter a transaction and there is not connection the the server it drops the transaction. It would be nice if it would hold the transaction until a connection can be made. Just a suggestion since you are re-coding.

Again thank you.
by bburgess on Jul 9, 2015
Thanks
Thanks :-D
by garito on Jul 9, 2015
Thank you for the consistent communication!
Luckily being on social media, you did a great job of constantly keeping us in the loop. The workarounds were helpful. I do realize how much I rely on Clearcheckbook and now I'm saving up for a membership.
by natnpaul on Jul 9, 2015
Thank you
Just want to say Thank you! I love this site....and I was looking for another but told my husband I just can't find one that compares to CCB. I love it! Thank you for working so hard to resolve the issue. I was doing a happy dance this morning.
God Bless you
by stephens on Jul 9, 2015
Great Job!!
Thank you for your hard work and effort to get things restored. I logged in this morning and everything came up as expected. I now plan on getting the premium membership, as I want to stand behind you and this product. As an IT professional myself, I understand how challenging hardware failures can be, and sometimes they ultimately cause data loss. In this case, you not only retained the data, you kept us informed. All the best!!
by illdoitagain on Jul 9, 2015
Clear Checkbook Site
I am amazed that while the site had problems over the past week the people who sent "threatening, cursing and hate filled messages to my personal cell phone and email accounts and our social media outlets" felt this was the answer to the problem. I have been using this site for four years, maybe longer and this is the first problem in that time. I wish my cell phone, cable, ISP, car etc.... had that track record. It is just another example of the amount of idiots we have in our world. Brandon cannot say anything because it is his company but I can, while I do not wish harm to anyone, the cowardliness of sending threats over the internet is mind boggling to me. I sincerely hope none of the people who did this, have or will have the chance in the future to reproduce. Maybe our world will be better if they cannot.

Mike Calhoun
by mcalhoun823 on Jul 9, 2015
Thanks for sticking with it.
I am a software developer who does SQL as well, and I empathized with the pain of fixing things on a major three-day weekend. The site down time was quite long by modern standards, but I don't depend on the site for day-to-day activities even though it is really nice to have. I hope your cloud solutiion is also going to include some geographic redundancy and load balancing. If you have 75 million transactions now (it was 50 million when I joined two years ago), you are going to need it in the near future.
by rambotech on Jul 9, 2015
AWESOME
Thanks for all of the work you put in to get the site fully functional I feel this is an exceptional free service and am really appreciative of everything that is offered
by djmoore250 on Jul 9, 2015
Thank you!
This last week has made me realize how much I rely on this site. I just became a premium member, I figure for something this useful I should probably be contributing towards it's support.

Not an easy thing to implement I'm sure, but maybe you could eventually include a way to archive old data? I certainly don't need every transaction since I started using the site to be active. I'd be perfectly happy to archive every year, (or even delete the old stuff since I have downloaded a copy for my records.) That might be a way to keep the database from getting too far out of hand.

Anyway, sorry people are jerks, and please know that your hard work is greatly appreciated. I love this site!

~Christy
by heathengirl on Jul 9, 2015
So Happy Things Are Back
Thanks so much for saving our data and getting us up and running. I've always been a free customer, but will not be becoming a paid one because I can see how you really do value us.
Thanks again.
by Imfletch on Jul 9, 2015
Thank you
Sorry you had to incur the negativity. Some people need to learn about a virtue known as patience. Thanks for getting things back up and running. I knew you would. Love the app.
by kernalcrak on Jul 9, 2015
Glad It's Working Again
I've been a premium member for a long time and love this product. So glad it's working again. My only complaint is I wish the company had posted updates on Twitter because I went for a couple days totally in the dark wondering what was going on until I realized you were updating on Facebook (I hardly ever use FB). It made me worry since the last tweet was in April. Thanks!
by bettythorne on Jul 10, 2015
Thank you, always.
I have been a premium member since 2010 and have no plans to change. Thanks again for all you do, Brandon! The few times I've contacted you, you've always been responsive and accommodating to my requests and concerns. I have full faith in your dedication to this site and hope to be a member for a very long time.
by twim7693 on Jul 10, 2015
Thank You Bandwagon
Add me to the list of positive feedback. I commented earlier, but after hearing about the haters it bears repeating. I agree with other happy folks here, I think you did a great job. I'm also in IT, been through this on your side, and I understand the issues - and you have done a much better job than anyone else I've seen in this situation at communicating, explaining, and doing what you can to get back on your feet. I felt like I was "in the loop" the whole way. So while being without my checkbook for a week was an inconvenience, it was not the end of the world. Keep your head up and be proud of how you handled this - obviously, the best you could!
by jdemmerich on Jul 10, 2015
Thank you
Thank you for bringing the site and app back to normal operation. I had no idea why the app wasn't loading and why transactions were missing on the website. Thank you for posting updates about the problem. I look forward to my year of premium free as a student.
by avery1 on Jul 11, 2015
Very Professional!
Mr. OBrien,

You are to be commended by us and proud of yourself for your professional handling of this crisis. My hat is off to you and so should we all be saluting your good work. I was amazed at how quickly and professionally you brought this under control and got us up and running again.

I'm sorry you were hit with nasty emails from people who strike out with no consideration of your efforts. I wasn't one of those this time, but I have been the angry one sometimes. Please know that anger is often a cover-up or a result of fear. When I feel threatened (fear of losing data, money, friends,etc.) I sometimes lash out in ways I regret later. This is no excuse for the expressions of anger. Again I think you spoke eloquently about these wildly inappropriate messages in a very professional and mature way.

I wish you all the best in your business,
Pat Klemme
by Patinphx on Jul 11, 2015
Awesome Communication!!
Thank you for all of your hard work and the communication you provided during this stressful time! To those who left hateful messages, you should be ashamed of yourselves. This has always been and wonderful site and continues to be so despite the snafu!

You continue to have a loyal member from me!
Thanks for all your efforts!

All the best,
Johnna
by dourdoufis on Jul 11, 2015
You do a great job and provide a fantastic product. I've been a customer for many years and have absolutely no plans to change. Every business goes through its trials and tribulations - your handling of the situation (although stressful) and outcome shows your commitment to your customers and demonstrates great character. Keep up the good work.
by charingrosario on Jul 12, 2015
Dont sweat it
Hey things break all the time, your car, tv etc. Dont give a minutes thought to the internet tough guys who send hate filled email, etc they would never have the balls to say or do anything in person. I think its a great site. Keep up the good work. P.S you said our data was safe and it was! You lived up to your word.
by nicktaormina on Jul 13, 2015
Thank you
It's a shame that people have to resort to negativity when things don't go their way. It wasn't a big deal in the grand scheme of things. I just want to thank you for keeping us updated as things were happening. As I always say, Sh*t happens, sometimes daily.

Thank you for providing us with such a wonderful app/website.
by Kjohnson0728 on Jul 14, 2015
Great job and thanks!
In today's world filled with demands of instant results, users of an application/software often don't realize all the behind-the-scenes goings-on that need to take place to address an issue when technology goes awry. I have been a loyal user of CCB since 2009 and love the interface and features. You all did a fantastic job keeping your users updated on status and for resolving the problem as promptly as possible.

Keep up the great work! :)
by silver0213 on Jul 19, 2015
Absolutely cannot complain
After reading and agreeing with all of the positive comments to date, there's nothing I can add other my sincere appreciation of your candor, responsibility and, really, pretty quick resolution. I'll happily continue to use and rely on CCB. Thanks again.
by dkingic on Jul 19, 2015
Thank you
I really appreciate your effects to get the site back to working. It is an excellent tool. I am sorry that some people were not more understanding. Hang in there and God bless!
by Sonja06 on Jul 22, 2015
No complaints!
Keep up the great work!! It's obvious you're doing your very best, and your best is excellent.
Now, I think it's time for Google calendar integration. Too soon? ;)
by nisperos on Jul 22, 2015
Thank You!
After trying other online financial record keeping sites, I found Clear Checkbook fits my budgeting style perfectly. I've been a premium member since 2011, and this was the first problem I've had. Since I use the site daily, I admit that I was frustrated without it. I now print my transactions regularly so I have a print record in addition to the online one. Keep up the good work!
by Sommer1 on Jul 26, 2015
It's working great
Just sending you some positive vibes, bro. The service is working really well.
by nisperos on Aug 10, 2015

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.