We’re continuing to test various solutions for the database scale issues we’ve run into. I’ve talked to a couple other developers in this space and they’ve been chuckling and nodding as they read through our previous post (having run into similar issues).
The good news is that we’ve got our new servers just about ready to install in the colo. Once we’ve got a solution we’re really happy with, we’ll be able to roll it out on more reasonable hardware pretty quickly.
For the last week or so, FeedLounge has been vacillating between “moderately slow” and “unusable”. After an excruciating investigation into the bowels of the application, database and server, I believe I have identified the root of the issues with the slowdown of the past week - and thought I would share it with everyone.
Disclaimer: This post might come across as my complete lack of understanding as to how MySQL operates, but let me assure you that I have used MySQL for five+ years, in some heavy enterprise situations.
For the last week, I have been digging into FeedLounge anywhere and everywhere to see if I could uncover the problem. Yesterday, in watching the server using top, vmstat, and mtop (mysqltop), I found the cause.
When designing the FeedLounge database I decided to use MyISAM tables, mainly for the capability of full-text search. I did understand that the MyISAM engine performs table level locking, but this never presented itself as a problem in any of our testing.
It seems that the MyISAM engine has a special condition whereby INSERTs do not lock the table, IF the table has never had a DELETE executed on it. When we moved to our new server, everything was fine until we did some data manintenance a couple weeks ago. The clean-up we did (deleting a few rows) has sent the application in a downward spiral performance-wise.
This morning I caught 40 queries locking on 2 INSERTs, and all the queries had crossed 300 seconds of real time. Obviously, we cannot expect any FL user to wait 5+ minutes for an item from a feed to load - but that is what was happening.
We are currently testing a switch to the InnoDB engine, which does row level locking and also keeps primary keys and data in order (so most queries should speed up for us). This is somewhat counter-intuitive, but FeedLounge needs the scalibility of row-level locking more than the performance of the MyISAM engine.
If you asked me when I started this, I would have told you that a web-based feedreader is just another web application, but that just is not be the case. Because of the input and output sides of a feed reader, the application does nearly as many writes to the database as it does reads from the database. This fundamentally changes the way I tackle problems at the database level.
How did I find this problem? First, *lots* persistence in looking for the problem (since Alex and I both agreed with the users about the slowness). Second, the world’s best MySQL book, High Performance MySQL, by Jeremy Zawodny and Derek Balling. These little caveats are the types of things that the book is loaded with. If you are dealing with a database that is larger than the RAM on your machine by a few orders of magnitude, this book is “The Book” you should be reading.
If our testing shows that we indeed have identified and fixed the problem, we’ll take FeedLounge offline tonight to make the needed changes to the database (it already takes more than an hour to re-load the database, and we’re only in alpha!).
We greatly appreciate the patience our alpha testers have displayed as we work through these growing pains.
The free version of FeedLounge will, like most free services, be supported by advertising. I’ve spent a couple of days trying to find a “perfect” ad provider for the service and have just about given up.
The current front runner is AdBrite, which has a bunch of great self-service tools, along with a system that allows people to bid to buy ads on your site. I was even able to call and talk to someone at AdBrite who knew what he was talking about and was quite helpful. A later follow-up e-mail to support was returned within 12 hours. Good job AdBrite!
The only drawback with AdBrite is the lack of a contextual ad offering. Initially, FeedLounge will definitely have a fairly focused user base, one that should certainly be appealing to various companies who might consider buying ads, however the best solution in my mind is to have contextual ads on a per user basis.
The way a contextual ad system like Google AdSense (or the upcoming Yahoo Publisher offering) works is by serving up ads that it deems to be relevant to the page they are being displayed on. AdSense uses the referring URL to know what page it’s being displayed on, and my guess is that Yahoo Publisher ads will work the same way.
For FeedLounge however, we want to be able to pass in the URL for ad relevance - using the URL of the item a user is reading. We could change the ads every 5-10 items, and pass in the URL of the current item as context. None of the systems I’ve found are able to support this type of usage (or at least they don’t mention it).
I was hoping that by getting through to a human at Google, I’d discover that AdSense had this capability tucked away somewhere. However after (finally) getting a reply, it was explained to me that AdSense can’t be placed in an application or service in the first place - so it’s pretty much out.
Anyone have recommendations?
Our first database server has arrived. 
