SQLIPPool performance issue
Peter Nixon
listuser at peternixon.net
Thu Jul 26 08:16:24 CEST 2007
Hi Roy
Thats good news. I am going to change the defaults queries to the ones I sent
you. A few questions..
Are you running accounting with your tests or is it all auth? Just an auth
test will not be representative. Throw accounting into the mix and it will
be worse :-D
Depending on the size of your tables and the number of pools it may or may
not make sense to actually simplify the indexes. Try the following set
instead and see how it performs:
CREATE INDEX radippool_poolname ON radippool USING btree (pool_name);
CREATE INDEX radippool_framedipaddress ON radippool USING btree
(framedipaddress);
CREATE INDEX radippool_nasip_poolkey_ipaddress ON radippool USING btree
(nasipaddress, pool_key, framedipaddress);
ie.
DROP INDEX radippool_poolname_expire;
CREATE INDEX radippool_poolname ON radippool USING btree (pool_name);
This is because the expiry_time column gets updated (and therefore the index)
for EVERY accounting packet. This change will make the IP allocation a
little slower but speed up the subsequent accounting queries. Its a good
tradeoff I think in real life, but if you are purely benchmarking auth
requests you will actually see a negative hit.
Again, please get back to me with how it works out. I have both limited time
and hardware (and certainly not a huge sun server) for testing here, but am
more than happy to help you do so :-)
The other thing is, you have a monster Postgresql box, but is it tuned right.
Postgresql's default config is very very conservative so you can get orders
of magnitude greater performance out of the box simply by increasing cache
sizes etc (Both Postgresql and the OS need to be tuned for a box like this)
Regards
Peter
On Thu 26 Jul 2007, 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:
> freeradius-users-bounces+rwalker=sensorlogic.com at lists.freeradius.org 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:
> > 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
--
Peter Nixon
http://peternixon.net/
More information about the Freeradius-Users
mailing list