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@fajar.net>
On Thu, Jan 12, 2012 at 6:36 PM, Alexander Kosykh <avkosykh@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