optimize sqlippool scheme

[Phil Mayers]

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.