FabulousEntertainment

I’ve just done a quick analysis of page views verses MySQL select queries:

• 16.5 select queries per second
• 7.3 page views per second

The caveat is that the page views are from Google Analytics and will be slightly under-reporting the actual page views as clients which don’t support JavaScript will not be counted. However, it is reasonable to say that we are averaging more than 2 select queries per page view.

This strikes me as quite a high number. I would like to target a ratio of 1:1 or less for selects to page views which would half the total number of SQL queries and would be a great step to increasing the performance of the site.

I think I am also going to refactor some of my code so that the cache logic is shifted out of the main application into my Python SQL methods. This will simplify the application and make it easier for me to put memcache in front of virtually every query.

PS. I also notice that the query cache hit rate is absolutely terrible! I really need to either fix this (by putting updates into a batch and doing them less frequently) or just switch the query cache off.

Recently the MySQL backup has been killing the (already loaded) server. I was doing backup using mysqldump and then piping it into bzip2. Both commands put a heavy load on the processor and in fact made the site virtually unusable in the very early morning.

As I am currently using MyISAM I can instead take advantage of mysqlhotcopy which is doing the backup in less than a hundredth of the time with virtually no load!

It is an important reminder that when you are looking at optimising the performance of a server it’s not just about the application code, it’s also about sysadmin scripts.

Reminder to me: If the site every changes to InnoDB then the backup will need to be revisited.

I have had a look at the Google App Engine and I guess my first thought was that it uses almost exactly the same architecture as Fab Swingers! The language and middleware is the same so from a technical point of view it would be a very easy thing to use for the site and would be a great way to get past the scaling problems I’ve got.

Unfortunately it’s a no go because the Program Policies would prevent me using it for Fab Swingers.

So, it’s back to look at new servers again.