Performance revisited
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.