#41 Bad performance with large amount of users

all
open
nobody
5
2013-04-27
2011-12-07
Anonymous
No

I'm experiencing that daloradius can not handle service provider environment at this time.
At the moment we have ~60.000 "PIN-code" users in DB, and with daloradius, because of the suboptimal sql queries we face seriuos performance problems (practically unusable).
As I took a look into the code I found some problems:

For example you get the number of rows with a query, which practically queries everything, and then counts the results with php:

$sql = "SELECT distinct(".$configValues['CONFIG_DB_TBL_RADCHECK'].".username),".$configValues['CONFIG_DB_TBL_RADCHECK'].".value,
".$configValues['CONFIG_DB_TBL_RADCHECK'].".id,".$configValues['CONFIG_DB_TBL_RADUSERGROUP'].".groupname as groupname, attribute FROM
".$configValues['CONFIG_DB_TBL_RADCHECK']." LEFT JOIN ".$configValues['CONFIG_DB_TBL_RADUSERGROUP']." ON
".$configValues['CONFIG_DB_TBL_RADCHECK'].".username=".$configValues['CONFIG_DB_TBL_RADUSERGROUP'].".username
WHERE (Attribute='Auth-Type') or (Attribute LIKE '%-Password') GROUP BY UserName";
$res = $dbSocket->query($sql);
$numrows = $res->numRows();

There is no need to joint to groups and then again group it just for a number of users, and mysql can directly query number of rows, there is noo need to feed a huge array to php and count it.

For a very quick workaround I changed the query to: SELECT count(distinct(".$configValues['CONFIG_DB_TBL_RADCHECK'].".username)) from ".$configValues['CONFIG_DB_TBL_RADCHECK']." WHERE (Attribute='Auth-Type') or (Attribute LIKE '%-Password')

Displaying users is mutch quicker (before: 10-30seconds, after: 0-4 seconds)

Another big performance clog is, that you select the Firstname and Lastname for every single record in all views, which produces extremely slow queries. A possible optimal solution would be not to join Firstname and Lastname every time on every view.

Now I'm testing with a reduced amount of userdb (~30.000) and with this the mng-rad-usergroup-list.php produces a dead mysql for some reason. (Not debugged yet, update will follow)

For accounting to diplay, we face also seriuos problems (radacct is relative small ~40.000 records (uing InnoDB)

Discussion

  • Mirek Rozewicz

    Mirek Rozewicz - 2012-05-18

    I think you should try to add indexes to all sql tables becouse one of problems whit dalo performance is lack of indexes in tables. After entering the indexes to the tables in the database got fivefold performance improvement of all queries to the database.

     
  • Liran Tal

    Liran Tal - 2012-06-24

    Obviously if you remove fields from the original query you'll get better performance due to losing the joins and what not. If that works for you - fine, otherwise if you're able to provide an optimized query which yields the same results then please do.

    @fuggii - can you please share your indexes SQL so we can apply them too by default?

     
  • Anonymous - 2013-04-22

    We also have huge performance problems with listing our 700.000 entries. It takes about 2.5Minutes on a 24GiG-MySQL-Server / 8Core Xeon-CPU.

    Any improvements so far for the specific querys? :)

     
  • Liran Tal

    Liran Tal - 2013-04-27

    Many improvements can be done. This is something that we might have skipped through during the development so I can see the problem being valid.

    Once you find out the sql query that takes long to run (it's easy if you simply turn on slow log queries with MySQL), you can re-run it after prefixing it with the EXPLAIN keyword (such as: EXPLAIN SELECT ... FROM ...) and paste the explain output here so we can debug it. I am betting many indexes are missing.

     


Anonymous

Cancel  Add attachments