Thanks Alan. I am using INNODB and default indexes (default schema from mods-config). I put 65k ip’s in the pool so that I could let the test run for a bit but that’s definitely contributing to the performance bottleneck. If I drop the number to 5k I can do a bit more (5/sec). As a next step, I added an index to include the pool_name but unfortunately the results are the same. ---- from “show create table radius.radippool” ---- radippool | 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 `radippool_poolname_expire` (`pool_name`,`expiry_time`), KEY `framedipaddress` (`framedipaddress`), KEY `radippool_nasip_poolkey_ipaddress` (`nasipaddress`,`pool_key`,`framedipaddress`), KEY `radippool_nasip_poolkey_ipaddress_pool_name` (`nasipaddress`,`pool_key`,`framedipaddress`,`pool_name`) ) ENGINE=InnoDB AUTO_INCREMENT=152091 DEFAULT CHARSET=latin1 | Even with some improvements to MySQL it sounds like Redis is a much better option so I’ll explore that instead. Thanks again for your feedback. -Ryan On 1/28/16, 9:34 AM, "Freeradius-Users on behalf of Alan DeKok" <freeradius-users-bounces+ryan.oconnell=viasat.com@lists.freeradius.org on behalf of aland@deployingradius.com> wrote:
On Jan 28, 2016, at 12:24 PM, O'Connell, Ryan <ryan.oconnell@viasat.com> wrote:
Curious if any has ballpark figures for any testing they have done with a "stand alone” FreeRadius DHCP server (3.0.10). I have setup a host with a local instance of MySQL (FR & MySQL on same Cent7 host) as well as FR pointing to a “physical” MySQL server with a massive amount of resources and write capability via a SAN. Irrespective of this, I seem to be topping out at 3 req/sec which seems strange.
How is MySQL configured? Which DB back-end are you using? i.e. MyISAM, etc. How many IPs are in the pool? Is the pool indexed?
When the server starts to fail, I start seeing errors like:
Sat Jan 23 12:19:51 2016 : ERROR: (60870) dhcp_sqlippool: ERROR: rlm_sql_mysql: ERROR 1205 (Lock wait timeout exceeded; try restarting transaction): HY000 Sat Jan 23 12:19:51 2016 : WARNING: (60870) WARNING: Module rlm_sqlippool became unblocked for request 60870
Because the SQL server is taking too long to respond.
Consistent with the errors in the radius log, the MySQL log has errors similar to this.
2016-01-23 12:19:52 7270 [ERROR] /usr/sbin/mysqld: Sort aborted: Lock wait timeout exceeded; try restarting transaction 2016-01-23 12:19:52 7270 [ERROR] /usr/sbin/mysqld: Lock wait timeout exceeded; try restarting transaction
Which probably means that the query is locking the entire table, and later queries get delayed / blocked.
I’ve tried adjusting the number of servers up and down from tens to hundreds (I know this isn’t a solution per se but just commenting that this has been done) while also adjusting my.cnf and sysctl (open files / processes) but the behavior is the same. I have also tried moving the MySQL data to a ramdisk since this is just a test but irrespective of whether a high I/) SAN or ramdisk is used, the performance is the same. Has anyone achieved better performance than this? I found comments from the past talking about the mac2ip performance being really high but I can’t seem to figure out what I’m missing to make dhcp_sqlippool go faster. I’m using “dhcperf” as the test but have also tried other simulators. Here’s the server startup. There is no selection logic so just a single pool.
The issue is in MySQL. For whatever reason, it can't handle the load. How to fix that is largely up to MySQL...
We've tested the v3.1.x HEAD with Redis as an IP pool back-end. It's benchmarked at 10K IPs per second per redis node. i.e. a cluster with 4 nodes can do 40K IP assignments per second.
I'm not aware of many *commercial* DHCP servers which can do that.
Alan DeKok.
- List info/subscribe/unsubscribe? See https://urldefense.proofpoint.com/v2/url?u=http-3A__www.freeradius.org_list_...