On 01/12/2012 11:59 AM, Fajar A. Nugraha wrote:Agreed, this is the key. SQL optimisation is a specialist task, and if you lack the specialist skills, you need to acquire them.
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.
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?- use postgres
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)
;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.