SQLIPPool performance issue

[Peter Nixon]

Hi Roy

The default indexes are:

CREATE INDEX radippool_poolname_ipaadr ON radippool USING btree (pool_name, 
framedipaddress);
CREATE INDEX radippool_poolname_expire ON radippool USING btree (pool_name, 
expiry_time);
CREATE INDEX radippool_nasipaddr_poolkey ON radippool USING btree 
(nasipaddress, pool_key);
CREATE INDEX radippool_nasipaddr_calling ON radippool USING btree 
(nasipaddress, callingstationid);

After reading though them, I think they need some work... (My production 
queries are a little different and so are my indexes)

I think a better index set would be:

CREATE INDEX radippool_poolname_expire ON radippool USING btree (pool_name, 
expiry_time);
CREATE INDEX radippool_framedipaddress ON radippool USING btree 
(framedipaddress);
CREATE INDEX radippool_nasip_poolkey_ipaddress ON radippool USING btree 
(nasipaddress, pool_key, framedipaddress);

Therefore, please run to fullowing on your postgresql database, and report 
back to me what difference it makes:

DROP INDEX radippool_poolname_ipaadr;
DROP INDEX radippool_nasipaddr_poolkey;
DROP INDEX radippool_nasipaddr_calling;
CREATE INDEX radippool_nasip_poolkey_ipaddress ON radippool USING btree 
(nasipaddress, pool_key, framedipaddress);
CREATE INDEX radippool_framedipaddress ON radippool USING btree 
(framedipaddress);

Cheers

Peter

On Thu 26 Jul 2007, Roy Walker wrote:
> 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/