I've got a website that's grown well beyond what I ever expected of it, and I'm trying to optimise it as much as possible. I've already made the switch over to InnoDB from MyISAM tables to eliminate delays from table-locking, which I'm hoping balances out against the increased RAM requirement (which is partly what I'm hoping to improve) by allowing pages to load more quickly, which seems to be the case.
I'm curious though if anyone has much experience of persistent MySQL connections versus creating connections on-demand all the time. In my particular case I'd be using PHP's MySQLi interface, which in theory means I shouldn't need to change anything else in my code since it performs clean-up automatically (possibly both an advantage and disadvantage?)
What I'm wondering is whether or not it's worth making the change, bearing in mind that I have a site which has 200-300 concurrent users at peak times.
Anyway, I'm just looking to hear from anyone that's tried the changeover from standard to persistent connections, and if they think it's a worthwhile change or not for a standard Dreamhost site (not a dedicated/VPS host).
Actually, on the issue but what would be the best way to measure performance of MySQL connection requests, inserting code into my PHP? It might help to decide if I can get a sample of connection time during peak periods, though I'm also hoping to limit the number of simultaneous MySQL connections to avoid hitting the connection limit.