Roy, It sounds like you may need to adjust the DB parameters. The defaults, even in 8.2, are still fairly conservative. Would you post your current settings for things like:
max_connections shared_buffers work_mem maintenance_work_mem max_fsm_pages vacuum_cost_* bgwriter_* wal_buffers commit_delay commit_siblings checkpoint_segments checkpoint_timeout random_page_cost effective_cache_size autovacuum autovacuum_* Basically, anything you have changed from the default configuration file. Proper choices for these parameters can make a huge difference in baseline performance. Ken On Wed, Jul 25, 2007 at 11:27:53PM -0500, Roy Walker wrote: > Ok chaning the indexes definately made some difference. The database load > still went off the charts, but the radius logs were much better with DB > errors connect errors. This still seems horribly slow. > > I can take it down to 2 simultaneous connections on the radclient test and > will still get some IP Allocation FAILED (although way less than I was) > messages in the radius logs. With only 2 simultaneous connections the DB > load hovers around 1 so that seems fine. > > Here is the command I am using to test: /radclient -p 2 -d > /usr/src/freeradius-server-snapshot-20070725/share -f /tmp/radclient-test > 1.1.1.10 auth testing123 > Where the radclient-test file has 5000 client requests seperated by the > necessary blank lines. > > I guess I will spend some time tomorrow and enable postgres query logging. I > already have an idea of what I am going to find, there is just an insane > number of queries running per auth request and the subsequent IP allocation... > > Peter: If you can share any query changes you have, I would be most > appreciative. > > Roy > > > ________________________________ > > From: [EMAIL PROTECTED] on behalf of Peter Nixon > Sent: Wed 7/25/2007 6:30 PM > To: FreeRadius users mailing list > Subject: Re: SQLIPPool performance issue > > > > 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 > > > - > List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
