optimize sqlippool scheme

[Alexander Kosykh]

One more question.

Where can I take nas-type value to use it in user authorization? Radius
take it from mysql nasinfo table at startup. I take it from DB every time
subscriber try to authorize.

Regards,
Alexander.


2012/1/12 Fajar A. Nugraha <list at fajar.net>

> 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
> -
> List info/subscribe/unsubscribe? See
> http://www.freeradius.org/list/users.html
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20120112/5083c05d/attachment.html>