- Freeradius-Devel - lists.freeradius.org

radsqlrelay performance issues with network latency
by Dr. Uwe Meyer-Gruhl 18 Dec '12

18 Dec '12

Hi, as probably most of you know, the rlm_sql_log/radsqlrelay pair of programs was invented to solve to problem of intermittent database downtimes. Furthermore, it is supposed to make FreeRADIUS answer faster because of the decoupling of the database operations. However, I found out that with latency of 25ms between a MySQL database server and the FreeRADIUS machine, there still is a performance problem: In our large-scale installation with > 100 log entries per seconds, we saw that the sqlrelay log file was constantly growing. It grew faster than radsqlrelay was able to shift the data to the database server. Another FreeRADIUS instance with < 1ms latency had no problem. The root cause is that each SQL statement is handled individually within radsqlrelay. So, with a network latency of 25ms, there can be at most 1000ms/25ms = 40 database operations per second, while with 1ms latency, there can be 1000 per second. Since the rlm_sql_log output consists mainly of INSERT statements to the same table and with the same structure, we can collect several INSERT statements into one with multiple rows. This can be done by the radsqlrelay Perl script very elegantly, since Perl was invented for such purposes. I have appended a modified version of radsqlrelay which collects up to 100 consecutive INSERT statements into one before doing the actual database submittal. I have only tested the script for MySQL. It cannot optimize in this way for Oracle databases, since Oracle does not conform to the SQL standard for multi-row INSERTs, but the script automatically reduces the maximum number of collected statements to 1 in that case. PostgreSQL databases should be able to cope with multi-row INSERTs as well and benefit likewise. The theoretical number of INSERT statements per second is lifted by a factor of 100 by this adapted script. In our own scenario, there will be no bottlenecks any more, even if the latency increased to 250ms. Maybe this improvement can be included in one of the next FreeRADIUS releases. Kind regards, Uwe Meyer-Gruhl --- #!/usr/bin/perl ## ## radsqlrelay.pl This program tails a SQL logfile and forwards ## the queries to a database server. Used to ## replicate accounting records to one (central) ## database, even if the database has extended ## downtime. ## ## Version: $Id$ ## ## Author: Nicolas Baradakis <nicolas.baradakis(a)cegetel.net> ## ## Copyright (C) 2005 Cegetel ## ## This program is free software; you can redistribute it and/or ## modify it under the terms of the GNU General Public License ## as published by the Free Software Foundation; either version 2 ## of the License, or (at your option) any later version. ## ## This program is distributed in the hope that it will be useful, ## but WITHOUT ANY WARRANTY; without even the implied warranty of ## MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the ## GNU General Public License for more details. ## ## You should have received a copy of the GNU General Public License ## along with this program; if not, write to the Free Software ## Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301, USA ## use DBI; use Fcntl; use Getopt::Std; use POSIX qw(:unistd_h :errno_h); use warnings; use strict; my $maxcollect = 100; # tunable, works for MySQL! my $lastinsert; my @values; my $need_exit = 0; sub got_signal() { $need_exit = 1; } # /!\ OS-dependent structure # Linux struct flock # short l_type; # short l_whence; # off_t l_start; # off_t l_len; # pid_t l_pid; # c2ph says: typedef='s2 l2 i', sizeof=16 my $FLOCK_STRUCT = 's2l2i'; sub setlock($;$$) { my ($fh, $start, $len) = @_; $start = 0 unless defined $start; $len = 0 unless defined $len; #type whence start till pid my $packed = pack($FLOCK_STRUCT, F_WRLCK, SEEK_SET, $start, $len, 0); if (fcntl($fh, F_SETLKW, $packed)) { return 1 } else { return 0 } } sub usage() { print STDERR <<HERE; usage: radsqlrelay [options] file_path options: -? Print this help message. -1 One-shot mode: push the file to database and exit. -b database Name of the database to use. -d sql_driver Driver to use: mysql, pg, oracle. -f file Read password from file, instead of command line. -h host Connect to host. -P port Port number to use for connection. -p passord Password to use when connecting to server. -u user User for login. -x Turn on debugging. HERE } sub connect_wait($) { my $dbinfo = shift; my $dbh; while (!$dbh) { $dbh = DBI->connect($dbinfo->{base}, $dbinfo->{user}, $dbinfo->{pass}, { RaiseError => 0, PrintError => 0, AutoCommit => 1 }); sleep (1) if !$dbh; exit if $need_exit; } $dbinfo->{handle} = $dbh; } sub process_file($$) { my ($dbinfo, $path) = @_; sub do_inserts($) { my $dbinfo = shift; if (scalar(@values) > 0) { my $query = $lastinsert . " "; $query .= join(" ), ( ",@values); $query .= " );"; do_query($dbinfo,$query); } @values = (); } sub do_query($$) { my ($dbinfo,$query) = @_; until ($dbinfo->{handle}->do($query)) { print $dbinfo->{handle}->errstr."\n"; if ($dbinfo->{handle}->ping) { sleep (1); } else { print "error: Lost connection to database\n"; $dbinfo->{handle}->disconnect; connect_wait($dbinfo); } } } unless (-e $path.'.work') { until (rename($path, $path.'.work')) { if ($! == ENOENT) { sleep(1); return if $need_exit; } else { print STDERR "error: Couldn't move $path to $path.work: $!\n"; exit 1; } } } open(FILE, "+< $path.work") or die "error: Couldn't open $path.work: $!\n"; setlock(\*FILE) or die "error: Couldn't lock $path.work: $!\n"; $lastinsert = ""; @values = (); while (<FILE>) { chomp (my $line = $_); if (!($line =~ /^\s*insert\s+into\s+`?\w+`?\s+(?:$.*?$\s+)? values\s*$.*$\s*;\s*$/ix)) { # This is no INSERT, so start new collection do_inserts($dbinfo); $lastinsert = ""; # must output this line do_query($dbinfo, "$line"); } else { # This is an INSERT, so collect it my $insert = $line; my $values = $line; $insert =~ s/^\s*(insert\s+into\s+`?\w+`?\s+(?:$.*?$\s+)? values\s*$).*$\s*;\s*$/$1/ix; $values =~ s/^\s*insert\s+into\s+`?\w+`?\s+(?:$.*?$\s+)? values\s*$(.*)$\s*;\s*$/$1/ix; if (($lastinsert ne "") && ($insert ne $lastinsert)) { # This is different from the last one do_inserts($dbinfo); } push(@values, $values); $lastinsert = $insert; # start new collection } # limit to $maxcollect collected lines if (scalar(@values) >= $maxcollect) { do_inserts($dbinfo); } } # Cleanup do_inserts($dbinfo); unlink($path.'.work'); close(FILE); # and unlock } # sub main() my %args = ( b => 'radius', d => 'mysql', h => 'localhost', p => 'radius', u => 'radius', ); my $ret = getopts("b:d:f:h:P:p:u:x1?", \%args); if (!$ret or @ARGV != 1) { usage(); exit 1; } if ($args{'?'}) { usage(); exit 0; } my $data_source; if (lc($args{d}) eq 'mysql') { $data_source = "DBI:mysql:database=$args{b};host=$args{h}"; } elsif (lc($args{d}) eq 'pg') { $data_source = "DBI:Pg:dbname=$args{b};host=$args{h}"; } elsif (lc($args{d}) eq 'oracle') { $data_source = "DBI:Oracle:$args{b}"; # Oracle does not conform to the SQL standard for multirow INSERTs $maxcollect = 1; } else { print STDERR "error: SQL driver not supported yet: $args{d}\n"; exit 1; } $data_source .= ";port=$args{P}" if $args{'P'}; my $pw; if($args{f}) { open(FILE, "< $args{f}") or die "error: Couldn't open $args{f}: $!\n"; $pw = <FILE>; chomp($pw); close(FILE); } else { # args{p} is always defined. $pw = $args{p}; } $SIG{INT} = \&got_signal; $SIG{TERM} = \&got_signal; my %dbinfo = ( base => $data_source, user => $args{u}, pass => $pw, ); connect_wait(\%dbinfo); my $path = shift @ARGV; until ($need_exit) { process_file(\%dbinfo, $path); last if ($args{1} || $need_exit); sleep(10); } $dbinfo{handle}->disconnect;

4 3

Commit report for master branch
by announce＠freeradius.org 15 Dec '12

15 Dec '12

New activity for FreeRADIUS (the high performance and highly configurable RADIUS server) ====== Use async call so we can timeout on ldap modifications Arran Cudbard-Bell@2012-12-15T15:24:25Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/352ba46f44c70f7a1f33… ====== -- This commit summary was generated @2012-12-16T00:00:01Z by lgfeed version 0.00 (https://github.com/arr2036/lgfeed)

1 0

Commit report for v2.x.x branch
by announce＠freeradius.org 14 Dec '12

14 Dec '12

New activity for FreeRADIUS (the high performance and highly configurable RADIUS server) ====== Allow for multiple inserts in one transaction. Patch from Uwe Meyer-Gruhl Alan T. DeKok@2012-12-14T17:13:29Z Files modified: * scripts/radsqlrelay Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/825f728c59d964fc456a… ====== -- This commit summary was generated @2012-12-15T00:00:01Z by lgfeed version 0.00 (https://github.com/arr2036/lgfeed)

1 0

Commit report for master branch
by announce＠freeradius.org 14 Dec '12

14 Dec '12

New activity for FreeRADIUS (the high performance and highly configurable RADIUS server) ====== Fix condition Arran Cudbard-Bell@2012-12-14T19:55:40Z Files modified: * src/modules/rlm_rest/rest.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/96a160f7fd3618b2ecb3… ====== Fix typo Arran Cudbard-Bell@2012-12-14T19:36:05Z Files modified: * src/modules/rlm_rest/rlm_rest.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/e573f35418a58c679bf2… ====== Allow for multiple inserts in one transaction. Patch from Uwe Meyer-Gruhl Alan T. DeKok@2012-12-14T18:46:36Z Files modified: * scripts/radsqlrelay Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/ed45909fd02222fdc828… ====== New target to check for compiler warnings Alan T. DeKok@2012-12-14T14:03:23Z Files modified: * Makefile Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/69bcf9a6987fb7ba909c… ====== Fix compiler warnings Alan T. DeKok@2012-12-14T14:03:22Z Files modified: * src/modules/rlm_opendirectory/rlm_opendirectory.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/8cc6561eac67e92bda90… ====== Fix compiler warnings Alan T. DeKok@2012-12-14T14:03:22Z Files modified: * src/modules/rlm_mschap/opendir.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/17eb883c56ba273ec4dc… ====== Fix compiler warning Alan T. DeKok@2012-12-14T14:03:21Z Files modified: * src/modules/rlm_ruby/rlm_ruby.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/5e19c5b2e37a3e3ea613… ====== Move compile-time #warning to comment Alan T. DeKok@2012-12-14T14:03:21Z Files modified: * src/modules/rlm_ruby/rlm_ruby.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/a6d0b5b3e9f4560d110c… ====== -- This commit summary was generated @2012-12-15T00:00:01Z by lgfeed version 0.00 (https://github.com/arr2036/lgfeed)

1 0

Commit report for v2.x.x branch
by announce＠freeradius.org 13 Dec '12

13 Dec '12

New activity for FreeRADIUS (the high performance and highly configurable RADIUS server) ====== Include DHCP dictionary by default Arran Cudbard-Bell@2012-12-13T17:02:36Z Files modified: * share/dictionary Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/f81fd6302431472eebdc… ====== -- This commit summary was generated @2012-12-14T00:00:01Z by lgfeed version 0.00 (https://github.com/arr2036/lgfeed)

1 0

Commit report for master branch
by announce＠freeradius.org 13 Dec '12

13 Dec '12

New activity for FreeRADIUS (the high performance and highly configurable RADIUS server) ====== Move more messages outside of locks Arran Cudbard-Bell@2012-12-13T13:31:19Z Files modified: * src/main/connection.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/9fe208c66b0ee1e3fbbb… ====== -- This commit summary was generated @2012-12-14T00:00:01Z by lgfeed version 0.00 (https://github.com/arr2036/lgfeed)

1 0

Commit report for master branch
by announce＠freeradius.org 12 Dec '12

12 Dec '12

New activity for FreeRADIUS (the high performance and highly configurable RADIUS server) ====== Distinguish between idle and spare connections Track last throttled time and use it to limit error messages Add connection opened/failed messages Arran Cudbard-Bell@2012-12-12T15:14:15Z Files modified: * src/main/connection.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/f2f169fda7803a3ee8af… ====== Fix duplicate connection close messages Arran Cudbard-Bell@2012-12-12T12:18:20Z Files modified: * src/main/connection.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/135b5ae6d0ca5610b88d… ====== Add more debugging for when we bail out of spawning a new connection Arran Cudbard-Bell@2012-12-12T11:41:51Z Files modified: * src/main/connection.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/5ab93a1d65590b90f8b2… ====== -- This commit summary was generated @2012-12-13T00:00:01Z by lgfeed version 0.00 (https://github.com/arr2036/lgfeed)

1 0

LDAP Accounting
by Arran Cudbard-Bell 12 Dec '12

12 Dec '12

Just pushed up a few patches to add LDAP accounting. Not well tested, but managed to modify attributes in my test directory. https://github.com/FreeRADIUS/freeradius-server/commit/8a62cce4a224c03b5b5f… For the moment it doesn't support adding new LDAP objects, just modifying existing ones. If someone really really wants that I could take a look at adding it. Probably need to do something about the behaviour of post-auth now, suggestions welcome. Just drop : # # Modify user object on receiving Accounting-Request # # Useful for recording things like the last time the user logged # in, or the Acct-Session-ID for CoA/DM. # # LDAP modification items are in the format: # <ldap attr> <op> <value> # # Where: # <ldap attr>: The LDAP attribute to add modify or delete. # <op>: One of the assignment operators (:=, +=, -=). # Note: '=' is *not* supported. # <value>: The value to add modify or delete. # accounting { reference = "%{tolower:type.%{Acct-Status-Type}}" type { start { update { description := "Online at %S" } } interim-update { update { description := "Online at %S" } } stop { update { description := "Offline at %S" } } } } Into your existing config. -Arran

9 41

Commit report for master branch
by announce＠freeradius.org 11 Dec '12

11 Dec '12

New activity for FreeRADIUS (the high performance and highly configurable RADIUS server) ====== Ignore right operand for !* Arran Cudbard-Bell@2012-12-11T23:19:56Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/d62f6655286bcc5d6a7a… ====== Return noop if we did no work Arran Cudbard-Bell@2012-12-11T22:55:31Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/e78a4afdcf467ea79ebd… ====== Add missing module prefix to some log messages Arran Cudbard-Bell@2012-12-11T22:55:31Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c * src/modules/rlm_sql/rlm_sql.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/11b83b1855d341e38bfe… ====== Only allow ANY for !* Arran Cudbard-Bell@2012-12-11T17:27:14Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/59c0aa2975d1d4d9f88d… ====== Fix compiler warnings Alan T. DeKok@2012-12-11T16:23:09Z Files modified: * src/modules/rlm_sqlhpwippool/rlm_sqlhpwippool.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/82d67e1312a11231e252… ====== Pass msg_id into ldap_result Arran Cudbard-Bell@2012-12-11T15:08:06Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/482167ec52f5c6bfe402… ====== Revert changes to LDAP bind Arran Cudbard-Bell@2012-12-11T14:42:51Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/0e989d6d9819184a052b… ====== Already have an ldap_errno Arran Cudbard-Bell@2012-12-11T13:46:31Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/a3862a6bf613b637fc1c… ====== Add warning about replacing multiple attributes Use zero length array for wildcard deletes Arran Cudbard-Bell@2012-12-11T13:31:11Z Files modified: * raddb/mods-available/ldap * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/56d26918a6a1c543baea… ====== Missed & operator Arran Cudbard-Bell@2012-12-11T12:28:02Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/8c5dddd3e3f7fbda7dcc… ====== More prettyfying Arran Cudbard-Bell@2012-12-11T12:21:56Z Files modified: * src/modules/rlm_ippool/rlm_ippool_tool.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/7dbbb2f4bc15e077d2ba… ====== Change more return code checks to use != LDAP_SUCCESS Arran Cudbard-Bell@2012-12-11T12:15:27Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/9d66b4813be43eee12df… ====== Should allocate MAX_ATTRMAP + 1 for pointers Arran Cudbard-Bell@2012-12-11T00:44:43Z Files modified: * src/modules/rlm_ldap/rlm_ldap.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/d25de79d4eb6e31a5e41… ====== Fix build warnings in ippool tool Arran Cudbard-Bell@2012-12-11T00:22:28Z Files modified: * src/modules/rlm_ippool/rlm_ippool_tool.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/ec3dd7b9d2f30410049f… ====== Make code slightly more readable Arran Cudbard-Bell@2012-12-11T00:18:38Z Files modified: * src/modules/rlm_ippool/rlm_ippool_tool.c Commit diff: https://github.com/FreeRADIUS/freeradius-server/commit/c6f0775dfdc5ad8d09e7… ====== -- This commit summary was generated @2012-12-12T00:00:01Z by lgfeed version 0.00 (https://github.com/arr2036/lgfeed)

1 0

Mutating State value on every reply
by Phil Mayers 11 Dec '12

11 Dec '12

Why does the code do this i.e. xor-ing the eap ID etc. with bytes 3,4,5 of "State"? I'm sure there's a good reason, but I can't figure out what it is.

2 1