Various bits of MySQL performance improvement
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!