FabulousEntertainment

I saw this quote on some swingers site. I like it a lot. It reminds me somewhat of Milan Kundera in The Unbearable Lightness of Being where he talks about there being two types of womaniser; first there is the man who is looking for the perfect woman but of course never finds her and secondly the man who just enjoys the infinite variety.

It is also something that is very true about marketing. Providing you have a basic proposition that people like then the key to successful promotion is just quantity; the more you do the more it works.

Fab Swingers has succeeded because of quantity. It’s not just quantity in marketing of course, but there is also a strong network effect because the more members the site has, the more valuable it is to the members. And the more likely they are to recommend it.

The only thing I’m feeling a bit negative about right now is that I’m mainly having to focus on just keeping the site going as the servers struggle to cope with the rocketing traffic. I’d be much happier if I could concentrate on marketing which is my skill.

However I’m sure that most social networking sites would be happy to have the problem of struggling to cope with traffic growth!

I took a look at the Lighttpd access log, which is actually more than 1 GB a day. This is completely useless, it’s taking lots of disk space and it’s far too big for me to ever run an analysis program on. Not only that but it’s only going to get worse!

As I’ve studied the performance of the server generally it’s pretty clear that it is very IO bound (although things can go a bit slow sometimes, the processors are always mostly idle). So writing a 1 GB log each day isn’t going to help.

I do want some stats so I enabled the statistics module which tells me that lighttpd is averaging about 60 hits per second (although right now it’s 75 hits per second).

Since I made the change I think that performance has improved a bit but it’s not been massively significant.

I am also considering moving Lighttpd off to another server completely, although it uses almost no CPU I think that it is using up IO bandwidth. I really need another server anyway but I’ll wait until the July promotions start at The Planet.

I use a login MyISAM table to record all logins to the site (IP address, user agent, username, etc) which is pretty handy for spotting scammers and people with multiple accounts.

Anyway, the problem is that this table gets very large, very quickly. My solution had simply been to run a delete every month to remove older entries. But this led to two problems:

1. The table was locked for ages while the delete happened.
2. The table was then left fragmented (which prevents concurrent inserts on my configuration)

My first thought was to use DELETE … LIMIT 100; in a loop so that the table wouldn’t be locked for too long. However that was still not an ideal solution as it was still using a lot of resource and fragmenting the table.

What I finally settled on was using a new login table for each month and then using a MyISAM merge table to join them all together. Then, I can simply drop the old tables as they go out of date and reconfigure the merge table.

I can now delete millions of rows instantly with no performance impact or fragmentation. Result!

It’s slightly embarrassing but I’ve actually slightly fallen for the old problem of going into optimizations without properly measuring. To be fair, part of the problem is that MySQL does not have the greatest reporting tools so it is not that as easy to spot the problems as it is with other databases.

Anyway, I identified that one of the problems was that some users just had too many messages so I’ve implemented some pruning so that users can have only 600 messages in their inbox/sent mail - this is all that the UI allowed them to see anyway so there will be no noticeable effect to them.

My improved logging also identified a missing index from my bounce table (which contains all bounced email) so I was able to speed up queries there very substantially.

I’ve also done a review of the MySQL status and it turned out I needed to increase the table cache a bit, but after boosting that from the default of 64 to 128 and then 192 I am now seeing that ‘opened_tables’ is sitting at a constant value.

The major problem that I am left with is the messaging system. It is full of joins, it gets a lot of write traffic, it’s a very large table and is the main contributor to the slow query log. Unfortunately I don’t really have a very clear idea of how to sort this out yet!

My latest project is to denormalize some of the heavily used tables. The core ‘person’ table which is used for displaying profiles is fine but the message, wink and friend invite tables usually need to be joined with the person table.

I’ve found that joins in MySQL are very expensive, the slow down is in the region of hundreds or thousands of times.

So I am going to amend the message directory so that instead of simply using a foreign key to reference the person who sent or received the message it will actually contain the basic details of the person. This will remove the need for the join.

It will give me a problem if people change their username, person type or names but presently only the latter can be changed through the web interface and if people do change their names then they will just have to accept that messages sent may have their previous names on them - which is just what happens with ordinary email anyway.

At the same time I have also decided to re-organize the photos directory. At the moment there is a directory for every member with uploaded photos in the top level which means I have a single directory containing 16,000 photo directories. To be honest the performance of this on ext3 isn’t bad but it’s clearly not going to scale. Under the new regime I have a maximum of 10,000 entries per directory.

I hope this works out as well as the summary tables has. Since I made that change the member home page has disappeared completely from the log of slow pages (slow = more than 400 ms).

I notice that Alexa have had quite a major change of their algorithms, instead of just using the Alexa Toolbar they are now using data from ISP’s which brings them into line with Hitwise, Compete.com and the other big players.

It’s great news for us because it’s really good to have more accurate rankings. The bad news though is that just after they introduced the change we took a dip! But overall it’s not really resulted in much of a change.

So our friends at Swinging Heaven continue to fall, The Adult Hub has also dropped slightly and Fab Swingers has risen slightly.

Meanwhile at the bottom of the table Dogging.co.uk has dropped quite a bit but the surprise has been Swing Fans which has dropped so far that it is heading for removal from the table.

I also had a quick look at members online earlier this evening, Fab Swingers and The Adult Hub were equal and Swinging Heaven had four times the number of members online (last time I did this check they were nearly seven times bigger).

The results from Google Analytics have also shown a pretty steady increase in members, page views and visits so overall I’m pretty happy with life!