(RADIATOR) Suggestions for FindQuery and AllocateQuery in AddressAllocatorSQL
Mike McCauley
mikem at open.com.au
Sun Aug 22 17:27:54 CDT 2004
Hello Andy,
Thanks for your suggestions. We have implemented both of them, and a new
version of AddressAllocatorSQL.pm is now available in the Radiator 3.9
patches area.
Cheers.
On Friday 20 August 2004 02:05, Andy M wrote:
> Hi,
>
> We are running Radiator 3.8 on a Linux server, using AuthBy DYNADDRESS and
> AddressAllocator SQL in order to allocate IP addresses from the RADPOOL
> table held in an Oracle 9i DB.
>
> The current logic of the address allocation process seems to be:
>
> 1. Select an address (using FindQuery)
> 2. Update it (using AllocateQuery)
> 3. If the update failed due to another process getting there first, goto #1
> (maximum of 20 times)
>
> We are seeing an increasing number of cases of repeated lookups - to the
> point where it is impacting performance; to get around this we've written a
> stored PL/SQL function, get_ip(), which will both choose an IP address and
> update it (in such a way that it isn't kept waiting for locks from other
> updates to be released). It is called by means of a FindQuery like:
>
> select get_ip(pool) from dual;
>
> The problems here are:
>
> 1. We need to define an AllocateQuery function, even though we don't want
> to run anything. We could run something like the following (which does
> nothing), but it is still an unnecessary DB round trip:
> AllocateQuery DECLARE p_ret_allocate integer; BEGIN p_ret_allocate
> := 1; END;
> 2. At the stage that FindQuery is run, the expiry time (which is required
> by the update statement) is not known and so isn't accessible as a bind
> variable
>
> ... so could I make a couple of suggestions:
>
> 1. Don't run AllocateQuery if it is defined as aen empty statement
> 2. The expiry time is calculated once, before the main loop, and made
> accessible as a bind variable to FindQuery
>
> Thanks in advance,
>
> Andy
>
> --
> Archive at http://www.open.com.au/archives/radiator/
> Announcements on radiator-announce at open.com.au
> To unsubscribe, email 'majordomo at open.com.au' with
> 'unsubscribe radiator' in the body of the message.
>
>
> ** ACCEPT: CRM114 PASS Markovian Matcher **
> CLASSIFY succeeds; success probability: 1.0000 pR: 15.5302
> Best match to file #0 (nonspam.css) prob: 1.0000 pR: 15.5302
> Total features in input file: 9104
> #0 (nonspam.css): features: 1445968, hits: 404221, prob: 1.00e-00, pR:
> 15.53 #1 (spam.css): features: 1860048, hits: 375885, prob: 2.95e-16, pR:
> -15.53
>
>
> -=-Extra Stuff-=-
>
> From seb Thu Aug 19 18:30:15 2004
> Return-path: <owner-radiator at open.com.au>
> Delivery-date: Thu, 19 Aug 2004 18:28:46 +0200
> Received: from pop.easynet.fr [212.180.1.61]
> by localhost with POP3 (fetchmail-5.9.0)
> for seb at localhost (single-drop); Thu, 19 Aug 2004 18:30:15 +0200 (CEST)
> Received: from [10.0.1.57] (helo=eric.easynet.fr)
> by mailhub1.mail.easynet.fr with esmtp (Exim 4.34)
> id 1BxpmQ-00010o-J4; Thu, 19 Aug 2004 18:28:46 +0200
> Received: from server1.open.com.au ([209.61.182.19])
> by eric.easynet.fr with esmtp (Exim 4.34)
> id 1BxpmK-0004ct-Rs; Thu, 19 Aug 2004 18:28:46 +0200
> Received: (from majordomo at localhost)
> by server1.open.com.au (8.11.6/8.11.6) id i7JG3IZ13590
> for radiatorzz-list; Thu, 19 Aug 2004 11:03:18 -0500
> X-Authentication-Warning: server1.open.com.au: majordomo set sender to
> owner-radiator at open.com.au using -f Received: from mail.o2.co.uk
> (holly.london.ongenie.net [193.113.160.41]) by server1.open.com.au
> (8.11.6/8.11.6) with ESMTP id i7JG36t13581 for <radiator at open.com.au>; Thu,
> 19 Aug 2004 11:03:06 -0500
> Received: from po211611 (10.144.21.82) by mail.o2.co.uk (7.0.028)
> id 40EC1F6B007A1D04; Thu, 19 Aug 2004 17:00:45 +0100
> Message-ID: <000d01c48606$5083b0b0$5215900a at po211611>
> From: "Andy M" <oscr at megevand.net>
> To: <radiator at open.com.au>
> Subject: (RADIATOR) Suggestions for FindQuery and AllocateQuery in
> AddressAllocatorSQL Date: Thu, 19 Aug 2004 17:05:14 +0100
> MIME-Version: 1.0
> Content-Type: text/plain;
> charset="iso-8859-1"
> Content-Transfer-Encoding: 7bit
> X-Priority: 3
> X-MSMail-Priority: Normal
> X-Mailer: Microsoft Outlook Express 6.00.2800.1437
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
> X-Spam-Checker-Version: SpamAssassin 2.60 (1.212-2003-09-23-exp) on
> server1.open.com.au
> X-Spam-Status: No, hits=0.0 required=5.0 tests=none autolearn=no
> version=2.60 X-Spam-Level:
> Sender: owner-radiator at open.com.au
> Precedence: bulk
> List-Id: <radiator.list-id.open.com.au>
> X-Spam-Score: -4.9
> X-Spam-Report: SpamAssassin 2.64 (2004-01-11) on c3po.easynet.fr
> Content analysis details: (-4.9 points, 10.0 required)
> pts rule name description
> ---- ---------------------- -------------------------------------------
> -4.9 BAYES_00 BODY: Bayesian spam probability is 0 to 1%
> [score: 0.0000]
> X-Spam-Level: ----
> Delivered-To: barbereau at easynet.fr
>
> Hi,
>
> We are running Radiator 3.8 on a Linux server, using AuthBy DYNADDRESS and
> AddressAllocator SQL in order to allocate IP addresses from the RADPOOL
> table held in an Oracle 9i DB.
>
> The current logic of the address allocation process seems to be:
>
> 1. Select an address (using FindQuery)
> 2. Update it (using AllocateQuery)
> 3. If the update failed due to another process getting there first, goto #1
> (maximum of 20 times)
>
> We are seeing an increasing number of cases of repeated lookups - to the
> point where it is impacting performance; to get around this we've written a
> stored PL/SQL function, get_ip(), which will both choose an IP address and
> update it (in such a way that it isn't kept waiting for locks from other
> updates to be released). It is called by means of a FindQuery like:
>
> select get_ip(pool) from dual;
>
> The problems here are:
>
> 1. We need to define an AllocateQuery function, even though we don't want
> to run anything. We could run something like the following (which does
> nothing), but it is still an unnecessary DB round trip:
> AllocateQuery DECLARE p_ret_allocate integer; BEGIN p_ret_allocate
> := 1; END;
> 2. At the stage that FindQuery is run, the expiry time (which is required
> by the update statement) is not known and so isn't accessible as a bind
> variable
>
> ... so could I make a couple of suggestions:
>
> 1. Don't run AllocateQuery if it is defined as aen empty statement
> 2. The expiry time is calculated once, before the main loop, and made
> accessible as a bind variable to FindQuery
>
> Thanks in advance,
>
> Andy
>
> --
> Archive at http://www.open.com.au/archives/radiator/
> Announcements on radiator-announce at open.com.au
> To unsubscribe, email 'majordomo at open.com.au' with
> 'unsubscribe radiator' in the body of the message.
>
>
>
>
> -0-0-0-
--
Mike McCauley mikem at open.com.au
Open System Consultants Pty. Ltd Unix, Perl, Motif, C++, WWW
9 Bulbul Place Currumbin Waters QLD 4223 Australia http://www.open.com.au
Phone +61 7 5598-7474 Fax +61 7 5598-7070
Radiator: the most portable, flexible and configurable RADIUS server
anywhere. SQL, proxy, DBM, files, LDAP, NIS+, password, NT, Emerald,
Platypus, Freeside, TACACS+, PAM, external, Active Directory, EAP, TLS,
TTLS, PEAP etc on Unix, Windows, MacOS etc.
--
Archive at http://www.open.com.au/archives/radiator/
Announcements on radiator-announce at open.com.au
To unsubscribe, email 'majordomo at open.com.au' with
'unsubscribe radiator' in the body of the message.
More information about the radiator
mailing list