MySQL configuration to connect to multiple local applications

Question

I've installed FreeRADIUS, MySQL and FreeRADIUS management (a web-based app) on the same machine, with both FreeRADIUS and FreeRADIUS management using same user to connect MySQL. My problem is when I make a report from FreeRADIUS managment, it makes MySQL busy and FreeRADIUS can not respond to the request (out of service) until my report query has finished. My question is: How both applications (FreeRADIUS & FreeRADIUS management) run queries on MySQL at same time.

I'm using MyISAM and below is the result of SHOW PROCESSLIST;

These are the queries run during SHOW PROCESSLIST;

SELECT count( DISTINCT (username) ) , `nasipaddress` FROM `radacct` 
WHERE (
   acctstarttime < 'First period' 
   AND acctstoptime > 'First period'
   AND acctstoptime <'Second period'
   )
OR (
    acctstarttime > 'First period'
    AND acctstoptime < 'Second period'
   )
OR (
  acctstarttime < 'First period'
  AND acctstoptime > 'Second period'
 )
OR (
  acctstarttime > 'First period'
  AND acctstarttime < 'Second period'
  AND acctstoptime > 'Second period'
 )
OR (
    acctstarttime < 'First period'
    AND acctstoptime IS NULL
    )
OR (
  acctstarttime > 'First period'
  AND acctstarttime <'Second period'
  AND acctstoptime IS NULL
 )
GROUP BY `nasipaddress`

-----------------------------------------
SELECT SUM(AcctSessionTime), SUM(AcctOutputOctets), SUM(AcctInputOctets) 
FROM radacct
WHERE username='xxx'
-----------------------------------------

UPDATE radacct
      SET 
     nasipaddress = '192.168.1.2', 
         framedipaddress = '10.10.10.2', 
         acctsessiontime     = '200', 
         acctinputoctets     = '8263'
         acctoutputoctets    = '310'
      WHERE acctsessionid = 'FD324A12' 
      AND username        = 'yyy' 
      AND nasipaddress    = '192.168.1.2'"

Answer 1

Not really an answer, but too big for a comment. This is quite a specialised question and would probably (IMHO) be better off asked on the FreeRadius mailing lists.

I looked here and found this

FreeRADIUS is a high-performance and highly configurable RADIUS server. It supports many database back-ends such as flat-text files, SQL, LDAP, Perl, Python, etc.

The problem with many systems which support multiple back-ends is that they end up not being optimised for any of them - it's possible that something in their code conflicts with MySQL's way of doing things. And I'm not even sure what a "Perl, Python, etc." back-end is - drivers?

From here, it appears to me that the default database is PostgreSQL and it's possible that scenarios which work with PostgreSQL either don't work or work poorly with MySQL. So, my second suggestion is to try that RDBMS (which, in any case, is superior to MySQL IMHO).

[EDIT] in response to the OP's queries.

Just out of curiosity - what are the response times of those queries when run separately? Second, the issue appears to be a lock on the radacct table. This is a problem with MyISAM tables which your system uses.

Could you take a dump of your schema using mysqldump, change ENGINE=MyISAM to ENGINE=InnoDB,

sed -i 's/ENGINE=MyISAM/ENGINE=InnoDB/g' your_schema_dump.sql

load the edited dump file and rerun the system to the point where you are having the problem? There is very little to be said these days for running a system using MyISAM tables.