[scream]

Добрый день!

Есть сервер на freebsd 8.2(x64), на нем установлен freeradius2 и mysql5.5 , к freeradius-у прикручен rlm_perl! Ниже конфиг radiusd.conf

prefix = /usr/local

exec_prefix = ${prefix}

sysconfdir = ${prefix}/etc

localstatedir = /var

sbindir = ${exec_prefix}/sbin

logdir = /var/log

raddbdir = ${sysconfdir}/raddb

radacctdir = ${logdir}/radacct

name = radiusd

confdir = ${raddbdir}

run_dir = ${localstatedir}/run/${name}

db_dir = ${raddbdir}

libdir = /usr/local/lib/freeradius-2.2.0

pidfile = ${run_dir}/${name}.pid

user = freeradius

group = freeradius

max_request_time = 30

delete_blocked_requests = no

cleanup_delay = 5

max_requests = 1024

 

listen {

type = auth

ipaddr = *

port = 0

}

 

#

listen {

ipaddr = *

port = 0

type = acct

}

 

hostname_lookups = no

allow_core_dumps = no

regular_expressions = yes

extended_expressions = yes

log {

destination = files

file = ${logdir}/radius.log

syslog_facility = daemon

stripped_names = no

auth = no

auth_badpass = no

auth_goodpass = no

}

 

checkrad = ${sbindir}/checkrad

 

security {

max_attributes = 200

reject_delay = 1

status_server = yes

}

proxy_requests = no

 

$INCLUDE clients.conf

 

 

thread pool {

start_servers = 5

max_servers = 32

min_spare_servers = 3

max_spare_servers = 10

max_requests_per_server = 0

}

 

modules {

perl {

module = ${confdir}/auth.pl

max_clones = 48

start_clones = 48

}

files {

usersfile = ${confdir}/users

compat = no

}

}

 

$INCLUDE sites-enabled/

 

Все прекрасно работало около 3-x месяцев, сегодня смотрю чё то база тормозит, и freeradius вылетел, в логи радиуса постоянно пишет следующее:

Mon Jan 21 14:56:09 2013 : Error: Request 162 has been waiting in the processing queue for 5 seconds. Check that all databases are running properly!

Mon Jan 21 14:56:27 2013 : Error: Received conflicting packet from client 10.0.1.1 port 1646 - ID: 195 due to unfinished request 146. Giving up on old request.

Mon Jan 21 14:56:29 2013 : Info: WARNING: Module rlm_perl became unblocked for request 145

Mon Jan 21 14:56:33 2013 : Info: WARNING: Child is hung for request 148 in component accounting module perl.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 354 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 307 in component accounting module perl.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 355 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 356 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 357 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 367 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 337 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 338 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 311 in component accounting module perl.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 312 in component accounting module perl.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 358 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 368 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 359 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 360 in component <core> module <queue>.

Mon Jan 21 14:57:08 2013 : Info: WARNING: Child is hung for request 369 in component <core> module <queue>

вот сам perl модуль

use DBI;

use POSIX;

use vars qw(%RAD_REQUEST %RAD_REPLY %RAD_CHECK);

use Data::Dumper;

use constant RLM_MODULE_REJECT=> 0; #

use constant RLM_MODULE_FAIL=> 1; #

use constant RLM_MODULE_OK=> 2; #

use constant RLM_MODULE_HANDLED=> 3; #

use constant RLM_MODULE_INVALID=> 4; # Code errors

use constant RLM_MODULE_USERLOCK=> 5; #

use constant RLM_MODULE_NOTFOUND=> 6; #

use constant RLM_MODULE_NOOP=> 7; #

use constant RLM_MODULE_UPDATED=> 8; #

use constant RLM_MODULE_NUMCODES=> 9; #

 

###################################################################################################################

# DB connection

sub db_connect {

$db = "isg";

$host = "localhost";

$user = "******";

$pass = "******";

$dbh = DBI->connect("DBI:mysql:$db:$host", $user, $pass);

}

####################################################################################################################

# Log burner

sub logger {

$log_query = $dbh->do("INSERT INTO radius(date,login,errors)

VALUES ('$_[0]','$_[1]','$_[2]')");

$dbh->disconnect();

}

####################################################################################################################

# Authorization

sub authorize {

if ($RAD_REQUEST{'User-Name'}=="SERVICE_L4R")

{

return 2;

}

if (($RAD_REQUEST{'User-Name'}=="U100") || ($RAD_REQUEST{'User-Name'}=="W1") || ($RAD_REQUEST{'User-Name'}=="W2") || ($RAD_REQUEST{'User-Name'}=="W3") || ($RAD_REQUEST{'User-Name'}=="W5") || ($RAD_REQUEST{'User-Name'}=="W10") || ($RAD_REQUEST{'User-Name'}=="W20") || ($RAD_REQUEST{'User-Name'}=="W30") || ($RAD_REQUEST{'User-Name'}=="W50"))

{

return 2;

}

 

db_connect();

$auth_query = $dbh->prepare("SELECT c.login,i.mac,i.active,i.packet_id,i.packet_id_ua,i.tp_count FROM (internet i, clients c)

WHERE i.mac='$RAD_REQUEST{'Calling-Station-Id'}' AND i.client_id=c.id;");

$auth_query->execute();

$auth_row=$auth_query->fetchrow_hashref();

if ($auth_row > 0 ) {

$username = $auth_row->{'login'};

$mac = $auth_row->{'mac'};

$status = $auth_row->{'active'};

$tp_count = $auth_row->{'tp_count'};

$packet = $auth_row->{'packet_id'};

$packetua = $auth_row->{'packet_id_ua'};

$curdate = strftime "%Y%m%d-%H:%M:%S", localtime;

$auth_query->finish();

}

 

else {

$curdate = strftime "%Y%m%d-%H:%M:%S", localtime;

#logger($curdate,$username,"MAC: $RAD_REQUEST{'Calling-Station-Id'} not found![$RAD_REQUEST{'User-Name'}]");

&radiusd::radlog(4,"MAC: $RAD_REQUEST{'Calling-Station-Id'} not found![$RAD_REQUEST{'User-Name'}]");

return 0;

}

if ($status ne "1") {

logger($curdate,$username,"User: $username disable!");

#&radiusd::radlog(4,"User: $username disable!");

return 0;

}

if ($tp_count == 1 ) {

$tg_query = $dbh->prepare("SELECT tg.speed_down,tg.speed_up FROM (packets tg, internet ig)

WHERE tg.id='$packet';");

$tg_query->execute();

$tg_row=$tg_query->fetchrow_hashref();

$speed_in = $tg_row->{'speed_up'};

$speed_out = $tg_row->{'speed_down'};

$tg_query->finish();

$RAD_REPLY{'Cisco-Service-Info'} = "QU;".($speed_in *1000).";".((($speed_in *1000)*1.5)/8).";".((($speed_in *1000)*3)/8).";"."D;".($speed_out *1000).";".((($speed_out *1000)*1.5)/8).";".((($speed_out *1000)*3)/8);

}

 

if ($tp_count == 2 ) {

$tu_query = $dbh->prepare("SELECT speed_world, speed_ua FROM packets_ua WHERE id='$packetua';");

$tu_query->execute();

$tu_row=$tu_query->fetchrow_hashref();

$speed_world = $tu_row->{'speed_world'};

$speed_ua = $tu_row->{'speed_ua'};

$tu_query->finish();

push @{ $RAD_REPLY{'Cisco-Account-Info'} }, ("A$speed_world");

push @{ $RAD_REPLY{'Cisco-Account-Info'} }, ("A$speed_ua");

}

push @{ $RAD_REPLY{'Cisco-AVPair'}}, ("subscriber:accounting-list=ISG-AUTH-1");

$RAD_REPLY{'Session-Timeout'} = "86400";

$RAD_REPLY{'Idle-Timeout'} = "200";

$RAD_REPLY{'User-Name'} = "$username";

logger($curdate,$username,"Login OK: $username [($RAD_REQUEST{'User-Name'}/$mac)]");

#&radiusd::radlog(2,"Login OK: $username [($RAD_REQUEST{'User-Name'}/$mac)]");

return 2;

$dbh->disconnect;

}

####################################################################################################################

# Accounting

sub accounting {

$curdate = strftime "%Y%m%d-%H:%M:%S", localtime;

db_connect();

$ipaddr = $RAD_REQUEST{'Framed-IP-Address'};

$user = $RAD_REQUEST{'User-Name'};

$sessid = $RAD_REQUEST{'Acct-Session-Id'};

$in = $RAD_REQUEST{'Acct-Input-Octets'};

$out = $RAD_REQUEST{'Acct-Output-Octets'};

$time = $RAD_REQUEST{'Acct-Session-Time'};

$cid = $RAD_REQUEST{'Calling-Station-Id'};

$error = $RAD_REQUEST{'Acct-Terminate-Cause'};

 

if ($RAD_REQUEST{'Acct-Status-Type'} eq "Start") {

$start_query = $dbh->do("INSERT INTO acct(username,mac,ip,session_id,date_start)

VALUES ('$user','$cid','$ipaddr','$sessid','$curdate')");

$dbh->disconnect();

}

if ($RAD_REQUEST{'Acct-Status-Type'} eq "Interim-Update") {

$update_query = $dbh->do("UPDATE acct SET upload='$in', download='$out',

session_time='$time' WHERE session_id='$sessid'");

$dbh->disconnect();

}

if ($RAD_REQUEST{'Acct-Status-Type'} eq "Stop") {

$stop_query = $dbh->do("UPDATE acct SET upload='$in', download='$out', error='$error',

session_time='$time', date_stop='$curdate' WHERE session_id='$sessid'"); # If recived "STOP"

$dbh->disconnect();

}

$dbh->disconnect();

return 2;

}

 

Абонентов около 200, в mysql значение max_connections=500 !

Изменено 21 января, 2013 пользователем scream

[scream]

только что заметил что когда коментирую в perl модуле accounting то ошибки пропадают, может я не правильно sub accounting описал ???

[agr]

скорее всего разрослась таблица acct и операции с ней не укладываются в положенное время. Сколько в ней строк? Для session_id есть индекс? Сделайте analyze над таблицей, не повредит в любом случае.

[scream]

mysql> SELECT COUNT(*) FROM acct;

+----------+

| COUNT(*) |

+----------+

| 193239 |

+----------+

1 row in set (3 min 20.57 sec)

 

mysql> ANALYZE TABLE acct;

+----------+---------+----------+----------+

| Table | Op | Msg_type | Msg_text |

+----------+---------+----------+----------+

| isg.acct | analyze | status | OK |

+----------+---------+----------+----------+

1 row in set (43.48 sec)

Изменено 21 января, 2013 пользователем scream

[agr]

у вас сервер слабый или загружен чрезмерно или еще какая проблема с базой. Для 200К строк в таблице 3 минуты на подсчет их количества - это запредельно много.

 

После analyze не стало легче? Всетаки есть индекс для session_id ?

[scream]

После analyze не стало легче, а вот индекса не было. Создал индекс для для session_id, и еще для username , и все начало летать ))

Большое спасибо, а то я целый день крутил радиус, а оказалось что проблема с базой ))