On Sat, Jun 4, 2011 at 5:47 PM, OzSpots - Carl Sawers <c...@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 - List info/subscribe/unsubscribe? See http://www.freeradius.org/list/users.html