On 01/12/2012 11:59 AM, Fajar A. Nugraha wrote:
That's why having a dba is important. If you can't do it yourself, hire one. Or learn to be one. Depending on your deployment scale, the cost is justifiable. Seriously.
Agreed, this is the key. SQL optimisation is a specialist task, and if you lack the specialist skills, you need to acquire them. However, I'm quite surprised that you're having problems with 25k subscribers; that's not a large table. What is the query rate? Do you have very low interim accounting values perhaps, meaning you're extending the IP "lease" times too frequently?
So in short, for now: - revert your changes - try changing the engine to innodb - if it's still too slow, hire a dba, and/or be prepared to implement mysql cluster (or something like clustrix)
- use postgres ;o) In all seriousness, It's worth noting that postgres does have the advantage that "select ... for update" uses row-level locking, not table level. So, you can allocate IPs without fear of duplication, transactionally.