On Thu, Oct 27, 2011 at 12:13 AM, Daniel Menezes <listas@dmnzs.com.br> wrote:

Yes, there is a large number of rows in the radacct and radposauth tables. The attribute 'Acct-Interim-Interval' works very well but makes many records.

Interim update aren't suppose to add records, they simply update existing ones. They DO make the db busier though, since the number of accounting request increased (depending on your environment, the difference can be over a magnitude).

I rotate these tables to archive old records, I think I'll do this every month.

Of course, the script wouldn't solve all my problems, but it was very useful. Maybe I really need some customization to the backend, I'll think about it.

Another thing to consider, IF: - you're pretty sure that your setup is optimized-enough - you already have someone with enough knowledge to look at the system and determine that the bottleneck is in disk I/O (due to frequent random db disk access) - you have limited budget then you might want to try spending your budget to replace the disk with SSD. Get sandforce-based SSD (or any other MLC SSD that have good garbage collection and wear-leveling). Usually they can give you instant performance boost (can be over 10x, depending on your current situation) due to increased available IOPS. -- Fajar

Fajar, I had radutmp and SQL commented out in account {}. I don't know why, a possible mistake. After mark radutmp and restart freeradius I don't see new errors in log. In the NAS (MikroTik) statistics sometimes have a few resends and timeouts, it's normal? Sds, --- Daniel Menezes -----Mensagem original----- De: freeradius-users-bounces+listas=dmnzs.com.br@lists.freeradius.org [mailto:freeradius-users-bounces+listas=dmnzs.com.br@lists.freeradius.org] Em nome de Fajar A. Nugraha Enviada em: quarta-feira, 26 de outubro de 2011 13:19 Para: FreeRadius users mailing list Assunto: Re: FreeRadius + MySQL | radacct: Errors and Warnings

Another thing to try, are you using radutmp? If no (e.g. session/simultaneous use check is using sql), just mark all instance of radutmp from sites-available/default (and whatever other virtual server you use).

Fajar,

So you mean radutmp was the root cause of your problem?

I don't know, but it's better now. =)

What does FR log says? Does it say it recives duplicate or conflicting packets? If yes, then the db is still slow. You still need to fix it. If not, then the problem might be somewhere else (e.g. congested network causing dropped packets)

This is strange! When starting radius in debug mode I don't see any error, in normal mode duplicate or conflicting packages have disappeared. Always the statistics in MikroTik shows 2, 4 resends and timeouts .. a few. I'll try other ways, first, change the DB engine. Tomorrow i'll write about. Thanks. Sds, --- Daniel Menezes

Hi Tim,

1. User the InnoDB Engine in MySQL. I read about it and don't know if it's the best way. Why InnoDB engine? MySAM engine is more fast.

2. Increase the number of SQL sockets in sql.conf (num_sql_socks). The default is 5, try 25. Ok.

3. Increase the number of connections (max_connections) in my.cnf to match the number of SQL sockets in sql.conf. Ok.

4. Enable the MySQL slow query log (slow_query_log) in my.cnf. 5. Check the MySQL slow query log file for problems. I've enable the slow query and set up to 2 sec. The log don't show any slow query .. It's much time? I've tested whith mtop[1] too, no slow queries.

Thanks! Sds, --- Daniel Menezes Links: [1] http://mtop.sourceforge.net/

On 27 Oct 2011, at 16:16, Alan DeKok wrote:

Daniel Menezes wrote:

...

...

1. User the InnoDB Engine in MySQL. I read about it and don't know if it's the best way. Why InnoDB engine? MySAM engine is more fast.

It doesn't support row level locking for one. Which absolutely cripples selects against the radacct/postauth table when there are high levels of inserts/updates. MyISAM should *NOT* be used for the postauth and radacct. Version 3 schema has been updated to use INNODB for these tables. https://github.com/alandekok/freeradius-server/blob/master/raddb/sql/mysql/s... -Arran Arran Cudbard-Bell a.cudbardb@freeradius.org Betelwiki, Betelwiki, Betelwiki.... http://wiki.freeradius.org/ !

Hi Alan,

If you know better than the RADIUS experts, why are you asking questions on this list?

I don't know better than anyone, I'm simply asking to understand where I'm lost. Sorry if you feel bad with my questions .. Sds, --- Daniel Menezes