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: > [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] > 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