SQLIPPool performance issue

[Roy Walker]

Using freeradius-server-snapshot-20070705.

I have setup a test scenario where radclient is sending 500 simultaneous
requests to the radius server.  This drives the load on the radius and
postgres database to pretty much max.  The Postgres database is an 8
Core (4 dual cpu) Sun Opteron with 8g of ram and 3 x 15k SAS drives on
an LSI Megaraid controller.  So the database box is a decent machine.

Here is the indexes on the postgres database:
radius=# \di
                           List of relations
 Schema |            Name             | Type  | Owner  |     Table
--------+-----------------------------+-------+--------+---------------
 public | badusers_incidentdate_idx   | index | dialup | badusers
 public | badusers_pkey               | index | dialup | badusers
 public | badusers_username_idx       | index | dialup | badusers
 public | mtotacct_acctdate_idx       | index | dialup | mtotacct
 public | mtotacct_nasipaddress_idx   | index | dialup | mtotacct
 public | mtotacct_pkey               | index | dialup | mtotacct
 public | mtotacct_username_idx       | index | dialup | mtotacct
 public | mtotacct_userondate_idx     | index | dialup | mtotacct
 public | nas_nasname                 | index | dialup | nas
 public | nas_pkey                    | index | dialup | nas
 public | radacct_active_user_idx     | index | dialup | radacct
 public | radacct_pkey                | index | dialup | radacct
 public | radacct_start_user_idx      | index | dialup | radacct
 public | radcheck_pkey               | index | dialup | radcheck
 public | radcheck_username           | index | dialup | radcheck
 public | radgroupcheck_groupname     | index | dialup | radgroupcheck
 public | radgroupcheck_pkey          | index | dialup | radgroupcheck
 public | radgroupreply_groupname     | index | dialup | radgroupreply
 public | radgroupreply_pkey          | index | dialup | radgroupreply
 public | radippool_nasipaddr_calling | index | dialup | radippool
 public | radippool_nasipaddr_poolkey | index | dialup | radippool
 public | radippool_pkey              | index | dialup | radippool
 public | radippool_poolname_expire   | index | dialup | radippool
 public | radippool_poolname_ipaadr   | index | dialup | radippool
 public | radpostauth_pkey            | index | dialup | radpostauth
 public | radreply_pkey               | index | dialup | radreply
 public | radreply_username           | index | dialup | radreply
 public | radusergroup_username       | index | dialup | radusergroup
 public | totacct_acctdate_idx        | index | dialup | totacct
 public | totacct_nasipaddress_idx    | index | dialup | totacct
 public | totacct_nasondate_idx       | index | dialup | totacct
 public | totacct_pkey                | index | dialup | totacct
 public | totacct_username_idx        | index | dialup | totacct
 public | totacct_userondate_idx      | index | dialup | totacct
 public | userinfo_department_idx     | index | dialup | userinfo
 public | userinfo_pkey               | index | dialup | userinfo
 public | userinfo_username_idx       | index | dialup | userinfo
(37 rows)

This seems to be the recommended indexes from what I have seen.  I used
the latest schema from CVS.

I have not setup the database to look and see if one query is killing
the box, but I am going to guess it is just the amount that is doing it.

If anyone has another idea I would LOVE to hear it!

Thanks,
Roy

-----Original Message-----
From:
freeradius-users-bounces+rwalker=sensorlogic.com at lists.freeradius.org
[mailto:freeradius-users-bounces+rwalker=sensorlogic.com at lists.freeradiu
s.org] On Behalf Of Peter Nixon
Sent: Wednesday, July 25, 2007 5:21 PM
To: FreeRadius users mailing list
Subject: Re: SQLIPPool performance issue

On Wed 25 Jul 2007, Roy Walker wrote:
> I am having a problem with the SQLIPPOOL performance.  This is
migration
> of an existing radius server using flat user files (old server is
> running radius 1.1.0).

Hi Roy

You don't specify which version of FreeRADIUS you are using.. Which is
it?

What does you your radipool table and indexes look like?

Regards
-- 

Peter Nixon
http://peternixon.net/
- 
List info/subscribe/unsubscribe? See
http://www.freeradius.org/list/users.html