optimize sqlippool scheme

[Fajar A. Nugraha]

On Thu, Jan 12, 2012 at 6:36 PM, Alexander Kosykh <avkosykh at gmail.com> wrote:
> hi
> Is anyone have a success story of optimizing ippool sql table to make it
> work faster?
> The default scheme is very slow, and then you have about 25000 subscribers
> it's not work.
>

I have over 1 million subscribers. Then again, I'm using mysql cluster :)

> I trying to add indexes to this table. It make fast selects, but slow
> updates.

Duh! :D
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.

>
> [code]
> CREATE TABLE `radippool` (
> `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
> `pool_name` varchar(30) NOT NULL,
> `framedipaddress` varchar(15) NOT NULL DEFAULT '',
> `nasipaddress` varchar(15) NOT NULL DEFAULT '',
> `calledstationid` varchar(30) NOT NULL,
> `callingstationid` varchar(30) NOT NULL,
> `expiry_time` datetime DEFAULT NULL,
> `username` varchar(64) NOT NULL DEFAULT '',
> `pool_key` varchar(30) NOT NULL,
> PRIMARY KEY (`id`),
> KEY `allocate-find` (`pool_name`,`expiry_time`,`callingstationid`)
> ) ENGINE=MyISAM AUTO_INCREMENT=34817 DEFAULT CHARSET=utf8
> [/code]

You really shouldn't use myisam for heavy-write tables.

> Maybe some one have a good modification of ippool table or another changes
> of default scheme, and could share it?

It's kinda complicated. I've been meaning to submit a patch, but it's
just not that easy.

For starters, about mysql storage engines:
- myisam -> good for majority of reads, terrible for writes. Plus it
lacks transactional support, which usually means that with the default
setup you can either hand out duplicate IP to clients, or stuck with
slow table locks.
- innodb -> somewhat balanced for both read-write, and have
transactional support, but mostly disk-bound. especially if you
enforce cosistency by flushing to disk on every transaction.
- memory -> good-enough for writes (i.e. not disk-bound, obviously),
but still suffer from table locks
- NDB (i.e. mysql cluster) -> good for lots of parallel writes,
usually not disk-bound

Now performance-wise using NDB or memory would be best, but it won't
be the best choice for common mysql setups. You MIGHT be able to gain
some improvements by using innodb (without changing anything else).
Maybe.

Then there's the problem with queries. The default sqlippool query
would basically lock the table during IP assignment (due to SELECT ...
FOR UPDATE). I opted to change the query to NOT use table locks (using
randomization instead), at the expense of possible duplicate ip
assignment. In my case when a client gets duplicate IP, the NAS will
reject the user, so the user will dial again, and (hopefully) get a
unique free ip address this time. It's an acceptable workaround for
me, but it won't be implementable in "normal" setups. Add to that the
fact that I had to implement it using stored procedures, and you can
see how it gets pretty complicated.

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)

-- 
Fajar