optimize sqlippool scheme

[Alexander Kosykh]

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 trying to add indexes to this table. It make fast selects, but slow
updates.

[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]

Next I tried to make table from the begin. Here it is

[code]
CREATE TABLE `radippool_new` (
`id` int(4) unsigned NOT NULL AUTO_INCREMENT,
`pool_name` tinyint(1) unsigned NOT NULL,
`framedipaddress` int(4) unsigned NOT NULL,
`nasipaddress` int(4) unsigned DEFAULT NULL,
`calledstationid` bigint(8) unsigned NOT NULL,
`callingstationid` bigint(8) unsigned NOT NULL,
`expiry_time` timestamp NULL DEFAULT NULL,
`username` varchar(64) NOT NULL,
`pool_key` int(5) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `allocate-find` (`pool_name`,`expiry_time`,`callingstationid`) USING
BTREE
) ENGINE=InnoDB AUTO_INCREMENT=34817 DEFAULT CHARSET=utf8
ROW_FORMAT=COMPACT COMMENT='Ip pool for customers'

CREATE TABLE `radippool_names` (
`id` tinyint(1) unsigned NOT NULL AUTO_INCREMENT,
`pool_name` varchar(64) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8
[/code]

It's working two time faster, than default with updates queries. But two
time slower with selects queries.

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

*Regards,*
Alexander
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freeradius.org/pipermail/freeradius-users/attachments/20120112/96fed1d9/attachment.html>