MySQL Tweaking & Optimization

As I mentioned in the interview with @enked on his website, I had serious problems with MySQL database optimization on the project. The current public stable version (beta-1) is using the MyISAM engine and it’s not holding much data – profiles, locations, geo points for the followers geography.

In the new version (currently dev-1 and hopefully beta-2 in a few days) I changed most of the old tables and added new ones, and using InnoDB this time. You see, it’s not very easy to scan through ~1,000,000 relations for the @mashable account ;) and I bumped into a ~10 second delay before the @mashable profile showed up at Slow query showed that one of the simplest queries caused that slow-mo – it took 6 seconds to execute! The guys at Stack Overflow helped me optimize the query and the two tables I was having problems with, so I came down to ~2 seconds for that query, neat!

Digging further I managed to tune the MySQL server up a little bit (caching, all sorts of buffers, etc – you should definitely take a look at MySQLTuner, it’s a perl script that helps you tune pretty much all the MySQL config) which decreased the query execution time to 1 s. The peeps at Stack Overflow said it’s pretty okay for that kind of query to execute for 3 seconds on over 2 million rows, so I thought that 1 second is final. Phew! :)

Now, think about the MySQL query cache. It doesn’t work in my situation, simply because I shoot UPDATEs and INSERTs at the relations table every five minutes or so (via a cron job), so there actually is a way to perform even higher. Thought of temporary tables, views and triggers (and even stored procedures). Nah.. Simply caching that query would be good, right? I mean if I cache the whole profile for an hour, why wouldn’t I cache the relations result set? Cache the query.. Aha, but I thought slightly further. Why not cache the whole page with memcached? I’ll keep you updated with the results. Gets Featured on Startups Live TV! is definitely one of the best shows I’ve seen around these days and guess what! I got there!

Startups Live is the non-biased platform for any startup to get live exposure, a captive audience, and valuable feedback from potential business partners, customers, members, and investors.

I sent a request to Startups Live asking them to feature the Twitter service I’ve created and bang! I got on the show! It was really amazing, I’ve never seen anything like it!

The girls are so wonderful! Charissa was the one interviewing me and Dannie took care of the promo and blog post. We had so much fun during the live recording, that we couldn’t stop reading the profiles after the show, so if you missed the live version, you’ll never get to see what happened beyond the recorded version at, sorry!

I was very nervous on the show, but Charissa and Dannie said that I never showed that (hope that this is true), although when I played back the recorded version and looked at myself, I kinda laughed. Do I really look that funny on TV? And the panda, oh that was weird, wasn’t it ;) but I couldn’t help it! Then, when Charissa asked me about the business model of, wow! I didn’t know what to say, cause I haven’t really thought about that (much).

The guests in the chatroom during the show were amazing too, especially when we started reading their profiles out loud, heh that was fun! And my brand-new-to-twitter brother @SoulSeekah got his one-minute celebrity spotlight on the show too, thanks for that girls!

Anyway, if you’ve missed the show, I feel terribly sorry for you! So go ahead and watch (at least) the recorded version on Startups Live. You won’t regret it.

Don’t forget to follow the girls on Twitter: Charissa Cowart and Dannie McClain. And definitely follow Startups Live on Twitter for the hottest news. Oh, I almost forgot, they have a Startups Live Facebook group. I suggest you join that too ;)

WordPress Junkies: Blogging via iPhone

I wrote about offline blogging with Windows Live Writer some time ago, and now I realized that it’s not that fun! Alright, I have two good news. The first one’s that I received approval for my second wordpress plugin (Related External Links) to be hosted at the official WordPress Plugin Directory. I commited the first beta a few minutes ago so go ahead and vote it up: Related External Links (thanks!).

The second news is… Y’know I’ve been dreaming about it since it was first announced. Yeah, I finally got myself an Apple iPhone!! Woot! It’s so sad that Moscow hasn’t got 3G internet yet (MTS announced it for the end of 2009) but I’ve got Wi-Fi at home and office plus EDGE everywhere else, so I guess I’ll survive. The first thing you definitely have to install on a brand new iPhone is TwitterFon. Get it for free from the iTunes Application Store (via iTunes on PC or App Store on your phone) and you’ll be tweeting 24/7, sending TweetPics along with your iPhone GPS coordinates. Jeez I’m so excited (BTW follow me!).

Anyways. Back to WordPress. I really appreciate that the WordPress team made this iPhone App. You can get it for free at the iTunes Application Store. It’s very user friendly and all the functionality you’d need is plugged into this little app. I’ve made some screenshots (thanks to @CMoz) to show you the whole process. Make sure you read the comments to each shot.

One more thing. If you’re into iPhone, you should definitely check out: Apps and Hats. It’s a brand new video blog about iPhone apps, which is launching 20th of March. Make sure you subscribe to their RSS feed and/or follow the Apps and Hats Twitter account. I think they’ll be using TwitterFeed to tweet their blog updates. Cheers!

I'm on Friend Connect!

I’ve finally found some time to setup a Google Friend Connect profile for my website, it was pretty easy by the way, so you should create one as well. And if you do, make sure you connect to my profile. Oh, and I tweeted out my ICQ number and e-mail address yesterday to public. It took me around 20 seconds to realize I did that, and around 5 seconds to remove it. Question – should I switch to a new e-mail address? :)

Websites That Suck!

A couple of weeks ago I made a new friend on TwitterAaron, a web developer from Severna Park, MD. He posted a link to his new blog post on his website about websites that suck. I loved the idea and subscribed to his RSS feed. Yes, there are billions of webs out there that really suck, and I’m glad that Aaron’s getting them all in once place. Aaron describes the things done bad, rather than just saying “oh my god, this sucks” and that’s probably the main reason why I followed.

According to Google, the most important one seems to be World’s Worst Website hosted on angelfire, which has frames, animated background, colour stuffing and all the other stuff you wouldn’t like to use when building a website.

Anyways, thanks to Aaron and his blog: Websites That SUCK!