Slow Mysql Queries

[Fajar A. Nugraha]

On Sat, Jun 4, 2011 at 5:47 PM, OzSpots - Carl Sawers
<carl at ozspots.com.au> wrote:
> Hi All, I have a freeradius server which has some slow mysql queries and
> some errors like the below happen now and again(daily).
>
> We have actually just upgraded to this server and have imported all the
> MYsql data into the new server (it was working fine on the older quite
> similar server). One thing I have noticed; the server boots with around
> 280mb of ram used but after an hour or two is closer to 400mb used and
> stays around this point until rebooted again.
>
> My research says that the tables need to be indexed but it seems I would
> have to manually do this and I don't know which ones would need it
> anyway... and why would this be the case when it wasn't needed
> previously.
>

There are lots of things that can cause mysql to be slow. Also lots of
things that can be done to fix them.

Judging from what you wrote I HIGHLY suggest you get a qualified dba.
Here's why:
- Your statement about index indicates you don't even know whether
index can help you or not. Not good.
Index can do wonders to speed up read queries. However, too many index
can greatly slow down write queries. Finding the right amount and
right columns to index can be hard for a non-dba.
- No matter what database you use, performance can degrade quickly if
keep all data indefinitely and don't purge old data. But deleting
several thousand records of old data can kill the database
performance-wise during the process. This is where partitioning can
come in handy. Again, it can be hard for a non-dba.
- Your statement seem to imply upgrading the hardware can solve
everything. It doesn't. Even if it does, you need to know what to
upgrade to get the most effect. Busy databases are usually disk I/O
bound. In this case upgrading from (example) a server with Core 2 Duo
to a dual 10-core Xeon E7 will not do much to solve your problem if
you keep on using 7200-rpm HDDs. On the other hand, changing the disks
to Sandforce or Intel-based SSDs can do wonders.
- There are various tunables in MySQL. One of the most important is
what storage engine to use. The other is memory-related settings.
Again, it can be hard for a non-dba to determine optimum settings.

> The other thing that I have noticed is what appear to be doubled up
> entries in radius.log, (check the times) Anyone know what this could be?

Most likely the side effect of MySQL being slow.

If you can't get a dba's help, then most likely your best option is to
keep a copy of current radacct table somewhere safe (using something
like mysqldump), delete the table, and recreate it (see
raddb/sql/mysql/schema.sql for a schema example). You'll lose old
accounting records that way, so be very careful if you choose to go
down this path.

-- 
Fajar