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). Running freeradius-server-snapshot-20070705 on a P4 2.8GHZ machine Database is Postgres 8.1.9 running on a monstrous Sun Opteron machine. When radius starts taking requests (it's a lot of requests, about 500/sec), the load on the radius server spikes to 100 and it eventually stops taking requests completely. The database is never above 0.05 load. I see messages like these in the radius logs: Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696841693 port 0 user 14696841693) Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 19723023688 port 0 user 19723023688) Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696845996 port 0 user 14696845996) Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696846177 port 0 user 14696846177) Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696825390 port 0 user 14696825390) Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14694415538 port 0 user 14694415538) Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696883664 port 0 user 14696883664) Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 12149915071 port 0 user 12149915071) Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 19728908614 port 0 user 19728908614) Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 19723023948 port 0 user 19723023948) Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 102 due to unfinished request 3988 Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 62 due to unfinished request 3987 Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 116 due to unfinished request 3989 Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 121 due to unfinished request 3990 Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 26 due to unfinished request 3991 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 105 due to unfinished request 3992 Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 74 due to unfinished request 3993 Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 29 due to unfinished request 3994 Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Wed Jul 25 10:29:15 2007 : Auth: Login OK: [12146063348] (from client nas01 port 0 cli 12146063348) I have tried playing with the numbers of threads (I used the current radius servers setting to start) and the results are the same. Anyone have any ideas? Thanks, Roy
Mr. Walker, It sounds as if you need to tune your postgresql installation. By the way, postgresql 8.2.4 will out perform 8.1.9. You should probably turn on query logging and see what the queries are and if they can be optimized. Maybe you are missing an index or two, although you may just have too little I/O capacity. Good luck. Ken On Wed, Jul 25, 2007 at 01:19:04PM -0500, 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).
Running freeradius-server-snapshot-20070705 on a P4 2.8GHZ machine
Database is Postgres 8.1.9 running on a monstrous Sun Opteron machine.
When radius starts taking requests (it's a lot of requests, about 500/sec), the load on the radius server spikes to 100 and it eventually stops taking requests completely. The database is never above 0.05 load.
I see messages like these in the radius logs:
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696841693 port 0 user 14696841693)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 19723023688 port 0 user 19723023688)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696845996 port 0 user 14696845996)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696846177 port 0 user 14696846177)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696825390 port 0 user 14696825390)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14694415538 port 0 user 14694415538)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696883664 port 0 user 14696883664)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 12149915071 port 0 user 12149915071)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 19728908614 port 0 user 19728908614)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 19723023948 port 0 user 19723023948)
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 102 due to unfinished request 3988
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 62 due to unfinished request 3987
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 116 due to unfinished request 3989
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 121 due to unfinished request 3990
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 26 due to unfinished request 3991
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 105 due to unfinished request 3992
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 74 due to unfinished request 3993
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 29 due to unfinished request 3994
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Auth: Login OK: [12146063348] (from client nas01 port 0 cli 12146063348)
I have tried playing with the numbers of threads (I used the current radius servers setting to start) and the results are the same.
Anyone have any ideas?
Thanks,
Roy
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
I upgraded to 8.2.4 and the performance does not seem to be any different. I guess my next step is to try looking at the query log and see which is the worst. -----Original Message----- From: freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradiu s.org] On Behalf Of Kenneth Marshall Sent: Wednesday, July 25, 2007 1:30 PM To: FreeRadius users mailing list Subject: Re: SQLIPPool performance issue Mr. Walker, It sounds as if you need to tune your postgresql installation. By the way, postgresql 8.2.4 will out perform 8.1.9. You should probably turn on query logging and see what the queries are and if they can be optimized. Maybe you are missing an index or two, although you may just have too little I/O capacity. Good luck. Ken On Wed, Jul 25, 2007 at 01:19:04PM -0500, 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).
Running freeradius-server-snapshot-20070705 on a P4 2.8GHZ machine
Database is Postgres 8.1.9 running on a monstrous Sun Opteron machine.
When radius starts taking requests (it's a lot of requests, about 500/sec), the load on the radius server spikes to 100 and it eventually stops taking requests completely. The database is never above 0.05 load.
I see messages like these in the radius logs:
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696841693 port 0 user 14696841693)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 19723023688 port 0 user 19723023688)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696845996 port 0 user 14696845996)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696846177 port 0 user 14696846177)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696825390 port 0 user 14696825390)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14694415538 port 0 user 14694415538)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 14696883664 port 0 user 14696883664)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 12149915071 port 0 user 12149915071)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 19728908614 port 0 user 19728908614)
Wed Jul 25 10:29:15 2007 : Info: IP Allocation FAILED from general_pool (did slogic.t-mobile.com cli 19723023948 port 0 user 19723023948)
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 102 due to unfinished request 3988
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 62 due to unfinished request 3987
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 116 due to unfinished request 3989
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 121 due to unfinished request 3990
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 26 due to unfinished request 3991
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 105 due to unfinished request 3992
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 74 due to unfinished request 3993
Wed Jul 25 10:29:15 2007 : Error: Discarding duplicate request from client nas01 port 1812 - ID: 29 due to unfinished request 3994
Wed Jul 25 10:29:15 2007 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0
Wed Jul 25 10:29:15 2007 : Auth: Login OK: [12146063348] (from client nas01 port 0 cli 12146063348)
I have tried playing with the numbers of threads (I used the current radius servers setting to start) and the results are the same.
Anyone have any ideas?
Thanks,
Roy
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
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/
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@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@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
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@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@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/
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@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@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@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
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@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@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@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/
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: freeradius-users-bounces+rwalker=sensorlogic.com@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@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@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
- List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
On Thu 26 Jul 2007, Kenneth Marshall wrote:
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.
Yep. My guess is, on that box, if he is running a default Postgresql config he should get 10-100 times greater performance after tuning it correctly for the ram and cpu setup.. Cheers -- Peter Nixon http://peternixon.net/
Here is the config lines: max_connections = 100 shared_buffers = 400MB temp_buffers = 32MB work_mem = 1MB maintenance_work_mem = 128MB max_fsm_pages = 204800 Didn't change any of these as for my testing I don't have autovacuum enabled. #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 0-10000 credits #bgwriter_delay = 200ms # 10-10000ms between rounds #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round #wal_buffers = 64kB #commit_delay = 0 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000 checkpoint_segments = 32 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h #random_page_cost = 4.0 autovacuum = off # enable autovacuum subprocess? # 'on' requires stats_start_collector # and stats_row_level to also be on #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 500 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold = 250 # min # of tuple updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before # vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before # analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovacuum, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit -----Original Message----- From: freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradiu s.org] On Behalf Of Peter Nixon Sent: Thursday, July 26, 2007 9:53 AM To: FreeRadius users mailing list Subject: Re: SQLIPPool performance issue On Thu 26 Jul 2007, Kenneth Marshall wrote:
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.
Yep. My guess is, on that box, if he is running a default Postgresql config he should get 10-100 times greater performance after tuning it correctly for the ram and cpu setup.. Cheers -- Peter Nixon http://peternixon.net/ - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Well, I have a pretty small, single core SunFire x2100 with 2GB ram and SATA disks as my DB server. Your 8 Core box with 8g of ram and hardware RAID should therefore at least 4 times faster, possibly up to 10 times faster. The major differences I have are: max_connections = 400 shared_buffers = 65536 # Should be at > max_connections*2. 8KB each. # Recommend 25% of RAM work_mem = 10000 # min 64, size in KB Your shared_buffers are WAY to low for a box with 8GB ram. The Postgresql tuning guide clearly recommends 25% of ram so you need to make that 2GB instead of 400MB. I guess you will see significant performance gains. I would also enable autovacuum as the radippool and radacct tables are constantly changing.. Let me know how it goes.. Cheers Peter On Thu 26 Jul 2007, Roy Walker wrote:
Here is the config lines:
max_connections = 100 shared_buffers = 400MB temp_buffers = 32MB work_mem = 1MB maintenance_work_mem = 128MB max_fsm_pages = 204800
Didn't change any of these as for my testing I don't have autovacuum enabled. #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 0-10000 credits
#bgwriter_delay = 200ms # 10-10000ms between rounds #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#wal_buffers = 64kB
#commit_delay = 0 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h
#random_page_cost = 4.0
autovacuum = off # enable autovacuum subprocess? # 'on' requires stats_start_collector # and stats_row_level to also be on #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 500 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold = 250 # min # of tuple updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before # vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before # analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovacuum, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit
-----Original Message----- From: freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradiu s.org] On Behalf Of Peter Nixon Sent: Thursday, July 26, 2007 9:53 AM To: FreeRadius users mailing list Subject: Re: SQLIPPool performance issue
On Thu 26 Jul 2007, Kenneth Marshall wrote:
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.
Yep. My guess is, on that box, if he is running a default Postgresql config he should get 10-100 times greater performance after tuning it correctly for the ram and cpu setup..
Cheers
-- Peter Nixon http://peternixon.net/
Oh. I forgot to add: effective_cache_size = 196608 # 3x shared_buffers Cheers Peter On Thu 26 Jul 2007, Peter Nixon wrote:
Well, I have a pretty small, single core SunFire x2100 with 2GB ram and SATA disks as my DB server. Your 8 Core box with 8g of ram and hardware RAID should therefore at least 4 times faster, possibly up to 10 times faster.
The major differences I have are:
max_connections = 400 shared_buffers = 65536 # Should be at > max_connections*2. 8KB each. # Recommend 25% of RAM work_mem = 10000 # min 64, size in KB
Your shared_buffers are WAY to low for a box with 8GB ram. The Postgresql tuning guide clearly recommends 25% of ram so you need to make that 2GB instead of 400MB. I guess you will see significant performance gains.
I would also enable autovacuum as the radippool and radacct tables are constantly changing..
Let me know how it goes..
Cheers
Peter
On Thu 26 Jul 2007, Roy Walker wrote:
Here is the config lines:
max_connections = 100 shared_buffers = 400MB temp_buffers = 32MB work_mem = 1MB maintenance_work_mem = 128MB max_fsm_pages = 204800
Didn't change any of these as for my testing I don't have autovacuum enabled. #vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 0-10000 credits
#bgwriter_delay = 200ms # 10-10000ms between rounds #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#wal_buffers = 64kB
#commit_delay = 0 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h
#random_page_cost = 4.0
autovacuum = off # enable autovacuum subprocess? # 'on' requires stats_start_collector # and stats_row_level to also be on #autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 500 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold = 250 # min # of tuple updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before # vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before # analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovacuum, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit
-----Original Message----- From: freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradiu s.org] On Behalf Of Peter Nixon Sent: Thursday, July 26, 2007 9:53 AM To: FreeRadius users mailing list Subject: Re: SQLIPPool performance issue
On Thu 26 Jul 2007, Kenneth Marshall wrote:
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.
Yep. My guess is, on that box, if he is running a default Postgresql config he should get 10-100 times greater performance after tuning it correctly for the ram and cpu setup..
Cheers
-- Peter Nixon http://peternixon.net/
Roy, The obvious really bad ones I have noted below. Ken On Thu, Jul 26, 2007 at 12:57:15PM -0500, Roy Walker wrote:
Here is the config lines:
max_connections = 100 shared_buffers = 400MB
Could be as much as 25% of RAM or 2GB.
temp_buffers = 32MB work_mem = 1MB
Running EXPLAIN ANALYZE for the logged queries will let you know if this value is too small. In particular, it is used to evaluate whether or not a hash/merge join can be used. You may need to raise it depending on what your query analysis shows.
maintenance_work_mem = 128MB
Bump this up to 256MB or 512MB or more. Otherwise maintaenance actions can become disk I/O bound.
max_fsm_pages = 204800
This needs to be large enough to handle the size of your DB.
Didn't change any of these as for my testing I don't have autovacuum enabled.
You definitely need to enable autovacuum. Poor plans due to poor statistics can hamstring your performance.
#vacuum_cost_delay = 0 # 0-1000 milliseconds #vacuum_cost_page_hit = 1 # 0-10000 credits #vacuum_cost_page_miss = 10 # 0-10000 credits #vacuum_cost_page_dirty = 20 # 0-10000 credits #vacuum_cost_limit = 200 # 0-10000 credits
You may need the bgwriter to smooth out checkpoint I/O. Check to see if you are getting checkpoint errors in your logs.
#bgwriter_delay = 200ms # 10-10000ms between rounds #bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round #bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round #bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round #bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#wal_buffers = 64kB
Bump this to 256kB.
#commit_delay = 0 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000
checkpoint_segments = 32 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h
#random_page_cost = 4.0
autovacuum = off # enable autovacuum subprocess?
Should be on.
# 'on' requires stats_start_collector # and stats_row_level to also be on
On, and stats_row_level should be on too.
#autovacuum_naptime = 1min # time between autovacuum runs #autovacuum_vacuum_threshold = 500 # min # of tuple updates before # vacuum #autovacuum_analyze_threshold = 250 # min # of tuple updates before # analyze #autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before # vacuum #autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before # analyze #autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum # (change requires restart) #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for # autovacuum, -1 means use # vacuum_cost_delay #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for # autovacuum, -1 means use # vacuum_cost_limit
-----Original Message----- From: freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradius.org [mailto:freeradius-users-bounces+rwalker=sensorlogic.com@lists.freeradiu s.org] On Behalf Of Peter Nixon Sent: Thursday, July 26, 2007 9:53 AM To: FreeRadius users mailing list Subject: Re: SQLIPPool performance issue
On Thu 26 Jul 2007, Kenneth Marshall wrote:
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.
Yep. My guess is, on that box, if he is running a default Postgresql config he should get 10-100 times greater performance after tuning it correctly for the ram and cpu setup..
Cheers
--
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
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.
The problem is that RADIUS servers take less time to do things than an SQL server needs. So when you hammer the RADIUS server with requests, the SQL server is getting 5-10x the load.
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.
FreeRADIUS should really be a little smarter about loading the SQL server. But it's a very hard problem to solve in a good way. i.e. "if SQL server is busy, stop processing the current request, BUT remember to wake up later to keep processing it." The only real solution is to get a bigger machine to handle the database, OR slow down on the RADIUS traffic. Alan DeKok.
On Thu 26 Jul 2007, Alan DeKok wrote:
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.
The problem is that RADIUS servers take less time to do things than an SQL server needs. So when you hammer the RADIUS server with requests, the SQL server is getting 5-10x the load.
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.
FreeRADIUS should really be a little smarter about loading the SQL server. But it's a very hard problem to solve in a good way.
i.e. "if SQL server is busy, stop processing the current request, BUT remember to wake up later to keep processing it."
The only real solution is to get a bigger machine to handle the database, OR slow down on the RADIUS traffic.
Yep. there are a few things you can do to limit disastrous situations though. * Turn off any SQL query that you don't need... * Run Accounting and Auth queries through different sql module instances so a flood of auth doesn't kill accounting and visa versa. SQLIPPool can be on a 3rd instance... * Give the radippool table a dedicated disk spindle, or even a dedicated server.. * Likewise for radacct vs all the auth tables.. Auth is read intensive, acct is write intensive.. (You can use different types of RAID even...) * Use radrelay for accounting if possible. Queueing is good for your health.. Given that as far as I can tell you are ONLY testing Auth performance at present, most of these wont help you :-( Cheers -- Peter Nixon http://peternixon.net/
participants (4)
-
Alan DeKok -
Kenneth Marshall -
Peter Nixon -
Roy Walker