FabulousEntertainment

There’s a fascinating article over at TechCrunch about Google OpenSocial. The basic idea seems to be that Google will provide a standard API for social networks so that developers only need to support one API.

This is fantastic news for us, a site like Fab Swingers is obviously much too small to attract a development community but through OpenSocial all we’ll need to do is implement a standard API and then our users will access to a huge range of products.

Overall I suspect that OpenSocial will be a big win for the smaller social network sites.

I think that I’ve more or less reached the limit of my MySQL optimisation, there’s always ways that I can speed up queries here and there but I’m not going to get a 10x speed up through this path. So I am now looking at reducing the number of databases queries through a cache of some sort.

As suggested by a comment on this blog I checked out memcached. I have to say I was very impressed. I love the fact that it’s used by Slashdot (see their write up) and LiveJournal amongst other. It’s also great that it’s so simple and doesn’t even have a configuration file!

I had been thinking about doing the cache at the level of my Python application. However the problem with that is that I run multiple processes for the application and in the future I may well want to run the application on more than one physical server.

Memcache comes with a pure Python interface however I shall be using the C Python interface which is reportedly 3 times faster — because I’m in this for the speed!

I’ve not decided how to use memcached though, I could cache data structures (Python dictionaries probably) or I could cache whole pages. Following my recent MySQL optimisations/removal of awstats I’ve bought myself quite a bit of time so I won’t need to roll out memcache until traffic doubles which should give me another couple of months.

PS: I think that I may disable the MySQL query cache, I’m seeing a hit rate of 500,000 verses 14,000,000 inserts so I suspect that the cache is making performance worse. The problem is that my member table changes so frequently that the cache probably ends up being invalidated before it can be used.

There’s good news and bad news. The good news is that I’ve successfully generated quite reasonable traffic to the site form the US campaign. The bad news is that the traffic isn’t really converting; the conversion rate is just a bit over 10% but for a free site with compelling content this is pretty poor and it’s a fraction of the virtually 50% conversion rate we get for UK traffic. So, I’m going to put the traffic generation on hold while I fix the conversion rate.

Looking at the site I think it’s pretty obvious what the problem is; the homepage makes Fab Swingers look like a UK swingers site.

I’m actually quite inclined to change the homepage completely anyway and rather than making it just the recent online profiles I think it should be more of a discussion about the site with some sexy photos and lots of search-engine friendly text. Making it a bit more static would also help performance too.

Unfortunately I’m flat out with other web projects right now so I think this will have to get paused for a week or so. However I’m in no big rush and UK membership continues to rocket.

I’ve kicked off the US promotional campaign today. First off, I made a few changes to the homepage to make it a bit more welcoming to Californians by making the USA mentions a bit more prominent.

Secondly, I changed the Swingers Online Now page so that it only shows people in your country rather than everyone who happens to be online. I also posted a message to Swingers in California explaining that the site is just beginning so it doesn’t have many American members yet. The site will need some other changes too but it’s fine for now.

We now have 95 members in California and I’ve began the email campaign.

In some ways it feels like being right back to day one, however I think that this should hopefully work faster than the UK campaign because:

• The site is technically much better. When we launched in the UK we had terrible forums, a terrible chatroom and lots of missing features. However for this launch we have a site that is technically one of the best.
• I think that we know much more about what works and doesn’t for member recruitment.
• We now have some budget so I don’t mind spending some money to do this. The original launch had to be done with virtually nothing.

So. Farewell
Then
AWStats.

I knew you well
Over many years
As you documented
My traffic hopes and fears.

It was love at first sight —
I knew it was fate
Even though your reports
Were never very accurate.

But now your CPU utilisation
Has knocked me for six
So I’ll just have to use
Google Analytics.

We’re over the 5 million pageviews
Of the free version
Google don’t seem to have noticed
Unless it’s just their inertion.

E. J. Thribb (17½)

PS. At the end of our first year we are now at exactly 0.5% of FaceBook’s monthly visitors (Nielsen Online). Sadly we’re not at 0.5% of their $100m revenue or 0.5% of their valuation!

I’ve had a good chat with my collaborator today and we now feel that the time has come to begin to target the USA market.

The UK side of the site is now on an almost exponential growth path and the main challenges that we have are to technically cope with the increase in traffic and to ensure our community is regulating itself. Almost none of the new members have anything to do with our efforts anyway, it is virtually all word of mouth.

It would be good to try again on the SEO and maybe also do a little bit of offline activity but I think that basically we’ve established the critical mass and it’s now there.

We are getting a bit of traffic from the US but we have the chicken-and-egg problem that the site is not of much interest because there aren’t that many members. On the positive side though the site is technically good, when we launched in the UK we had some pretty weak parts of the site (such as the chatroom).

I think it’s also pretty important for us not to view the US as a market anyway, we need to think about it in terms of states or even smaller areas. California is the largest state by population and also has the reputation for being the most liberal so that is where we are going to start.

The target is 10,000 new members in California over the next year. As soon as we hit this target we’ll start on another state. I think the free love message will go down well!

I’ve now deployed with the new table structure. It turns out that one of the big things that was slugging performance was tables with ‘text’ columns. I’ve read around the subject and the general consensus seems to be that text and blob columns will both cause severe loss of performance.

As an example doing a count(*) on the message table has moved from 400 ms to 0 ms when I removed the text column. I’ve now shifted the text column into a separate message_detail table and am only querying on it when absolutely necessary.

Here’s what ’show status’ was showing before I made the changes:

Created_tmp_disk_tables 1839073
Created_tmp_tables 3880113

And here’s what the same command is showing now:

Created_tmp_disk_tables 1231
Created_tmp_tables 303910

So there has been an absolutely huge reduction in the proportion of temporary tables which are now being written to disk.

This has been a good start to the MySQL optimisation but there’s still more to do.

Today I installed YSlow, a pretty cool tool for spotting speed problems with webpages, it’s based on the 14 Rules for High Performance Websites.

Anyway, the results showed that I need to get the cache expire headers running on lighttpd and that also on lighttpd I wasn’t gzipping my content. Then I went onto the Swinging Forums and it popped up with a warning about a JavaScript error. It turns out that I hadn’t installed the Google Analytics code properly.

I’ve been a bit surprised by how poorly the forum has been performing, it seems fairly busy but it never shows up on the stats so of course now I know that it’s because it was being ignored! I’ve probably been underestimating my traffic by a fairly significant amount.

UPDATE: Just noticed that I’ve also missed the Google Analytics code from the chatroom. Some people spend hours and hours in the chatroom so I suspect that we’ll see a huge increase in the reported length of time people are staying on the site.

The good news is that traffic to Fab Swingers has continued to rocket. Following my recent work the Python side is fixed and the bottleneck has now moved to MySQL.

Today I improved the performance of the homepage. The homepage shows the 10 most recent couples, 5 most recent women and 5 most recent men. I was just using a LIMIT but I’ve now improved the WHERE clause so that the limit is performed on far fewer records which has sped everything up pretty nicely.

However the more I think about it, the more I think that the main issue I have is the structure of some of the tables. For example the main member table should be split vertically so that a lot of the detail (particularly the profile text column) get shifted into a detail table. There are always going to be some scans and sorts that won’t use indexes so if the rows in the table are much narrower (and in MyISAM’s static format) then that would be much faster.

I also need to revisit my indexing strategy, much more sorting needs to be done through the indexes. It is a challenge on the search page because there are so many possibilities however I’m sure the 80/20 rule applies so I should be able to knock off the 80%.

Finally, I think that I need to do a bit of caching in the application. Profiles and the homepage are all moderately static so don’t really need to be created dynamically for every view.

I’m not beating myself up over the performance; I’m managing 15 hits a second to CherryPy/MySQL on a low power server which is also handling many more hits to the static content. I think that with a bit more work I can extend the life of this hardware by another a few months but it’s clear that I will need a hardware investment this calendar year (probably buying a second cheap server rather than upgrading the current server to something more powerful).

Looking at the recent number I think that the challenge for year two is not going to be marketing but in getting the technical infrastructure to keep pace with the traffic growth whilst keeping within the financial constraints of the advertising-funded model.

Here’s a brief unscientific benchmark: I’ve just logged into a few of the major UK swingers sites and I’m just checking how many members are online. Now, each site has probably got a different metric for working out whether a member is online so it’s not particularly accurate.

On this measure we presently have 6.5% of Swinging Heaven’s traffic, 20% of Adult Hub and 23% of Local Swingers.

It also shows how relatively inactive Swinging Heaven’s members are with 0.4% of their members online but 2% of our members being online. This isn’t surprising as Swinging Heaven is a much older site so some of the claimed members may well have not signed in for years and also many members of the site will signup and then discover that they can’t do anything without paying. While Swinging Heaven are presently claiming 780,428 members I would discount this down to 156,000 active members.

So basically we need to increase our membership by 15 times in order to snatch the leading British site from Swinging Heaven.