Slow Mysql Queries

[Fajar A. Nugraha]

On Tue, Jun 7, 2011 at 10:54 AM, OzSpots - Carl Sawers
<carl at 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