Home page is awfully slow

Question

We have a decent of OCS clients worldwide mostly linux.

Even with all the DB tweaks to get a better performance, we are still experiencing a very long time to connect to the OCS homepage. After some debug, it was the number of softwares that appears in the dashboard that was stuk for 280 seconds. So I commented this code from require/console/Console.php :

        //$sql = "SELECT s.ID, count(CONCAT(s.NAME_ID,'_',s.VERSION_ID)) FROM `software` s LEFT JOIN accountinfo a ON a.HARDWARE_ID = s.HARDWARE_ID";

        //if (is_defined($_SESSION['OCS']["mesmachines"])) {

        //  $sql .= " WHERE " . $_SESSION['OCS']["mesmachines"];

        //}

        //$sql .= " GROUP BY CONCAT(s.NAME_ID,'_', s.VERSION_ID)";

        //$result = mysql2_query_secure($sql, $_SESSION['OCS']["readServer"]);

        //$softs = "<a style='font-size:32px; font-weight:bold;' href='index.php?function=visu_all_soft'>".mysqli_num_rows($result)."</a>";

        $softs = "<a style='font-size:32px; font-weight:bold;' href='index.php?function=visu_all_soft'>TOO_MANY</a>";

This way, the OCS server is behaving normally and maybe it could be a good enhancement for the future.

For information, we have more than 8 000 OCS clients contacting everyday and 80 000 in DB. The softwares DB has been recently scratched and is counting   around 160 000 softwares.

Hope this helps, Thomas.

Answer 1

what was the server version you had ?

my Console.php file seems to be a little different from yours ...

Answer 2

The table is 'software' and not 'softwares', so OCS >= 2.8.0.
In my case, we have ~7.000 hardware with a contact by day, ~12.000 hardware in table and ~9 M software. I confirm a delay between login and main page ... sometimes. If you leave and login again, it's immediate.

I suppose the delay is needed for this query and sometimes the data are in temp table (cache). I'm not a DBA (DataBase Administrator) but 3 (bad) ideas :

- a crontab with each hour a similar sql query search (without any usage) : could keep data in temp tables

- create a field in table 'software' and populate with 'concat(NAME_ID,'_',VERSION_ID)' with an index on this field, and modify the sql search : I think it could speed up the search

- count only for NAME_ID, means you doesn't count for each version of NAME_ID software

Answer 3

Upstream of all this, is there a way to modify the agent to NOT get all the software from the PCs... to prevent the software table from becoming too large?...