RE: There are no DB handles to use! skipped 0, tried to connect 0
hi, thanks for the great feedback, now all tables are InnoDB everything was working fine, till I upgraded to the latest CVS src of 16-Dec-2003, and ran ground just to check now the radius keeps on dying on me.. have not been able to check it out but some of the interesting thing that I found in radiusd.log Wed Dec 17 01:29:50 2003 : Error: Dropping conflicting packet from client X:52730 - ID: 234 due to unfinished request 76121 above message repeating about 40-50 times and then the radiusd died now I have downgraded to 0.9.3 and trying to find out if the problem still exists no changes were done in any configuration files Ripunjay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Kostas Kalevras Sent: Tuesday, December 16, 2003 1:53 AM To: [EMAIL PROTECTED] Subject: RE: There are no DB handles to use! skipped 0, tried to connect 0 On Tue, 16 Dec 2003, Ripunjay Bararia wrote: thanks Alan, for the comment, My SQL server and FR are running on the same box, will separating them be a good idea, I need to do AAA for about 1500 concurrent users what kind of a machine would I need for FR and how much load will it put on the MySQL server so that I can scale both of the machines accordingly currently both are running on P-IV 2.6 Intel 856 based board 512MB DDR 266Mhz 9.1GB X 2 SCSI disks The hardware is more than adequate. And there's no need to separate them. Read doc/tuning_guide and especially the section on the sql module. In general for mysql EXPLAIN SELECT is your friend. Run all the SELECT queries (and also transform all the UPDATE queries to corresponding SELECT queries) through an EXPLAIN SELECT statement to see how many candidate rows are there. Example outputs: mysql explain select * from radacct where acctstoptime is null; +-+--+---+--+-+--- +--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--- +--+-+ | radacct | ref | AcctStopTime | AcctStopTime | 8 | const | 315 | Using ^ where | +-+--+---+--+-+--- +--+-+ 1 row in set (0.02 sec) mysql explain select * from radacct where acctstoptime = '2003-12-15 21:00:00'; +-+--+---+--+-+--- +--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--- +--+-+ | radacct | ref | AcctStopTime | AcctStopTime | 8 | const |1 | Using ^ where | +-+--+---+--+-+--- +--+-+ The rows and possible_keys columns are important. If you see that the candidate rows are more than a few, or that an index is never used (for example: mysql explain select * from radacct where acctterminatecause = 'User-Request'; +-+--+---+--+-+--+ +-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--+ +-+ | radacct | ALL | NULL | NULL |NULL | NULL | 971518 | Using where | +-+--+---+--+-+--+ +-+ 1 row in set (0.00 sec) then you should either rearrange your queries to use a proper index (like using the acctuniqueid column in the accounting_stop query) or add a corresponding index. If you are using MySQL 3.X maybe you should think of moving to 4.X and to the InnoDB tables (instead of MyISAM which have global instead of per row locking). Hope the above was helpful. thanks Ripunjay Bararia -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Alan DeKok Sent: Monday, December 15, 2003 10:19 PM To: [EMAIL PROTECTED] Subject: Re: There are no DB handles to use! skipped 0, tried to connect 0 Ripunjay Bararia [EMAIL PROTECTED] wrote: --- radius.log begin --- Mon Dec 15 12:30:23 2003 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Find out why your SQL database is slow. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Kostas Kalevras Network Operations Center [EMAIL PROTECTED] National Technical University of Athens, Greece Work Phone: +30 210 7721861 'Go back to the shadow' Gandalf - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info
Re: There are no DB handles to use! skipped 0, tried to connect 0
Ripunjay Bararia [EMAIL PROTECTED] wrote: --- radius.log begin --- Mon Dec 15 12:30:23 2003 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Find out why your SQL database is slow. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: There are no DB handles to use! skipped 0, tried to connect 0
thanks Alan, for the comment, My SQL server and FR are running on the same box, will separating them be a good idea, I need to do AAA for about 1500 concurrent users what kind of a machine would I need for FR and how much load will it put on the MySQL server so that I can scale both of the machines accordingly currently both are running on P-IV 2.6 Intel 856 based board 512MB DDR 266Mhz 9.1GB X 2 SCSI disks thanks Ripunjay Bararia -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Alan DeKok Sent: Monday, December 15, 2003 10:19 PM To: [EMAIL PROTECTED] Subject: Re: There are no DB handles to use! skipped 0, tried to connect 0 Ripunjay Bararia [EMAIL PROTECTED] wrote: --- radius.log begin --- Mon Dec 15 12:30:23 2003 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Find out why your SQL database is slow. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: There are no DB handles to use! skipped 0, tried to connect 0
Ripunjay Bararia [EMAIL PROTECTED] wrote: My SQL server and FR are running on the same box, will separating them be a good idea, It shouldn't matter. I need to do AAA for about 1500 concurrent users what kind of a machine would I need for FR Almost any machine available today will do this easily. and how much load will it put on the MySQL server Almost no load. Something in your SQL database is taking a long time, and preventing the server from working properly. Find out what that is, and the server will be OK. I don't know much about SQL, so I can't help you there, sorry. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: There are no DB handles to use! skipped 0, tried to connect 0
Thanks Alan I will try to see what can be done about the MySQL interface thanks Ripunjay -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Alan DeKok Sent: Tuesday, December 16, 2003 1:08 AM To: [EMAIL PROTECTED] Subject: Re: There are no DB handles to use! skipped 0, tried to connect 0 Ripunjay Bararia [EMAIL PROTECTED] wrote: My SQL server and FR are running on the same box, will separating them be a good idea, It shouldn't matter. I need to do AAA for about 1500 concurrent users what kind of a machine would I need for FR Almost any machine available today will do this easily. and how much load will it put on the MySQL server Almost no load. Something in your SQL database is taking a long time, and preventing the server from working properly. Find out what that is, and the server will be OK. I don't know much about SQL, so I can't help you there, sorry. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
RE: There are no DB handles to use! skipped 0, tried to connect 0
On Tue, 16 Dec 2003, Ripunjay Bararia wrote: thanks Alan, for the comment, My SQL server and FR are running on the same box, will separating them be a good idea, I need to do AAA for about 1500 concurrent users what kind of a machine would I need for FR and how much load will it put on the MySQL server so that I can scale both of the machines accordingly currently both are running on P-IV 2.6 Intel 856 based board 512MB DDR 266Mhz 9.1GB X 2 SCSI disks The hardware is more than adequate. And there's no need to separate them. Read doc/tuning_guide and especially the section on the sql module. In general for mysql EXPLAIN SELECT is your friend. Run all the SELECT queries (and also transform all the UPDATE queries to corresponding SELECT queries) through an EXPLAIN SELECT statement to see how many candidate rows are there. Example outputs: mysql explain select * from radacct where acctstoptime is null; +-+--+---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+--+-+ | radacct | ref | AcctStopTime | AcctStopTime | 8 | const | 315 | Using ^ where | +-+--+---+--+-+---+--+-+ 1 row in set (0.02 sec) mysql explain select * from radacct where acctstoptime = '2003-12-15 21:00:00'; +-+--+---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+---+--+-+ | radacct | ref | AcctStopTime | AcctStopTime | 8 | const |1 | Using ^ where | +-+--+---+--+-+---+--+-+ The rows and possible_keys columns are important. If you see that the candidate rows are more than a few, or that an index is never used (for example: mysql explain select * from radacct where acctterminatecause = 'User-Request'; +-+--+---+--+-+--++-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--+---+--+-+--++-+ | radacct | ALL | NULL | NULL |NULL | NULL | 971518 | Using where | +-+--+---+--+-+--++-+ 1 row in set (0.00 sec) then you should either rearrange your queries to use a proper index (like using the acctuniqueid column in the accounting_stop query) or add a corresponding index. If you are using MySQL 3.X maybe you should think of moving to 4.X and to the InnoDB tables (instead of MyISAM which have global instead of per row locking). Hope the above was helpful. thanks Ripunjay Bararia -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Alan DeKok Sent: Monday, December 15, 2003 10:19 PM To: [EMAIL PROTECTED] Subject: Re: There are no DB handles to use! skipped 0, tried to connect 0 Ripunjay Bararia [EMAIL PROTECTED] wrote: --- radius.log begin --- Mon Dec 15 12:30:23 2003 : Info: rlm_sql (sql): There are no DB handles to use! skipped 0, tried to connect 0 Find out why your SQL database is slow. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html -- Kostas Kalevras Network Operations Center [EMAIL PROTECTED] National Technical University of Athens, Greece Work Phone: +30 210 7721861 'Go back to the shadow' Gandalf - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html
Re: There are no DB handles to use! skipped 0, tried to connect 0
I had this very same error several times. This answer may be way off base, but I found that MySQL was putting mysql.sock in /tmp. I changed /etc/my.conf to socket = /var/lib/mysql/mysql.sock and it fixed it. Again, I am no expert in MySQL or FreeRADIUS but I am learning. - Original Message - From: Alan DeKok [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 15, 2003 1:37 PM Subject: Re: There are no DB handles to use! skipped 0, tried to connect 0 Ripunjay Bararia [EMAIL PROTECTED] wrote: My SQL server and FR are running on the same box, will separating them be a good idea, It shouldn't matter. I need to do AAA for about 1500 concurrent users what kind of a machine would I need for FR Almost any machine available today will do this easily. and how much load will it put on the MySQL server Almost no load. Something in your SQL database is taking a long time, and preventing the server from working properly. Find out what that is, and the server will be OK. I don't know much about SQL, so I can't help you there, sorry. Alan DeKok. - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html