On Tue, Jun 7, 2011 at 10:54 AM, OzSpots - Carl Sawers <c...@ozspots.com.au> wrote: > > SELECT radacct.UserName AS UserName > , radcheck.Value AS Value > > FROM radacct > > left outer join radlookupnas > ON radlookupnas.user = radacct.UserName > AND radlookupnas.macauth = '0' > AND radacct.CallingStationId = '60-33-4B-20-1F-5F' > > left outer join radcheck > ON radcheck.username = radacct.username > AND radcheck.attribute='User-Password' > WHERE radcheck.username = radlookupnas.user > > LIMIT 1;
(sigh) This is obviously a custom query. Who wrote that? You're joining three tables (radcheck, radacct, radlookupnas. While possible, it means that as the radacct gets bigger the query will be slower. Does the person who wrote that understands the consequences? If yes, did that person setup the necessary measures to keep performance acceptable (e.g. using indexes)? If not, get a dba, have them fix it. > ------------------------------------- > > In radius.log I can see that a lot of the entrys are double ups, the > same authentication request repeated a few times over 2 or 3 seconds: > AND the occasional Error like this: No need to repeat this info over and over and over again. As previously noted, this is side effect to db being slow. It won't go away if you don't fix the db. > PHPmyadmin runtime info states that Select_full_join is 49 and that > if this value is not 0, you should carefully check the indexes of your > tables. Good suggestion. Did you do it? > > If the radius Mysql tables need to be indexed which ones? That's why I repeatedly say get a dba. A dba will know what to do. If you insist on doing it yourself anyway, here's a hint: http://dev.mysql.com/doc/refman/5.1/en/using-explain.html Use that on whatever slow query you have to get an idea of why it's so slow. > radacct has > 3500 records and is 1.1mb, radcheck has 9000 and is .5mb and aradacct > has 27000 and is 8mb. Are either of these that big? It's very small. I have systems with radacct as big as several million entries per month, with tens of GB data. And it works just fine. We have a certified MySQL DBA helping design the database structure and queries. > I output this mysqlreport and tried to find where issues are but it's Here's another hint: don't expect phpmyadmin or mysqlreport to magically show you which option to change in order to get performance boost. So in summary: - use EXPLAIN - fix non-optimum indexes - fix server settings (hint: convert your tables to innodb, use reasonable innodb_buffer_pool_size) - if you have no idea what I'm talking about, get a dba -- Fajar - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html