I want to know ... UserTracking query


  • Developer

    Hi fogers,

    I am having problems with the performance of the database and my FOG server. The environment is:

    Server OS: RHEL 7
    Resources: 8 vCores and 16 GB RAM
    Client number: > 8200 PC
    FOGversion: 1.5.5

    I am experiencing a mistical situation with the database. I know that the necessary resources for the version 1.X are huge for very big scenarios because the FOG client asks every X seconds to the server. This does that the server needs a lot of resources to MariaDB and Apache. Actually the server supports 700-800 select queries per second and MariaDB needs circa 3 vCores to do his job. All goes well until “someone” does a query.

    # Time: 190220 16:42:57
    # User@Host: 
    # Thread_id: 3812272  Schema: fog  QC_hit: No
    # Query_time: 57.802350  Lock_time: 0.448168  Rows_sent: 573616  Rows_examined: 1790716
    # Rows_affected: 0  Bytes_sent: 791750865
    SET timestamp=1550677377;
    SELECT * FROM `userTracking`  LEFT OUTER JOIN `hosts` ON `hosts`.`hostID`=`userTracking`.`utHostID`  LEFT OUTER JOIN `hostMAC` ON `hostMAC`.`hmHostID`=`hosts`.`hostID`  LEFT OUTER JOIN `images` ON `images`.`imageID`=`hosts`.`hostImage`  LEFT OUTER JOIN `os` ON `os`.`osID`=`images`.`imageOSID`  LEFT OUTER JOIN `imagePartitionTypes` ON `imagePartitionTypes`.`imagePartitionTypeID`=`images`.`imagePartitionTypeID`  LEFT OUTER JOIN `imageTypes` ON `imageTypes`.`imageTypeID`=`images`.`imageTypeID`  LEFT OUTER JOIN `hostScreenSettings` ON `hostScreenSettings`.`hssHostID`=`hosts`.`hostID`  LEFT OUTER JOIN `hostAutoLogOut` ON `hostAutoLogOut`.`haloHostID`=`hosts`.`hostID`  LEFT OUTER JOIN `inventory` ON `inventory`.`iHostID`=`hosts`.`hostID`    WHERE `hostMAC`.`hmPrimary` = '1'  ORDER BY `userTracking`.`utID` ASC;
    

    This query freezes the server literally. I know that one of the recomendations in the wiki to improve the performance is truncate the data of UserTracking table, but we want to this data to a possible auditory.

    Then, the question is … who does this query?


  • Developer

    @Fernando-Gietz Seems like we have introduced some PHP warning when adding that fix to dev-branch. See the PHP Warning: Illegal offset type in /var/www/html/fog/lib/fog/fogmanagercontroller.class.php on line 296 messages here: https://forums.fogproject.org/topic/13602/errors-in-apache-log-and-www-error-log

    I won’t revert your fix as I think you are totally right. It shouldn’t query the full userTracking table if it only wants the information for one particular host! But the problem seems to be the action array send with the query. I’ll have a closer look at this now.

    Edit: Nevermind, just pushed out a fix to dev-branch.


  • Developer

    @Fernando-Gietz Just pushed to dev-branch. Thanks again! Should we mark this solved?


  • Developer

    @Fernando-Gietz Thanks a lot for figuring this out! Looks good from my point of view. I will push the fix to the dev-branch in the next hours.


  • Developer

    Hi @Sebastian-Roth and @Tom-Elliott ,

    I have fixed this bug in my server making a little change in hostlogins function.

    File: hostmanagementpage.class.php
    Line: 3767

    Route::listem('UserTracking');
    

    To:

    Route::listem('UserTracking',
                array(
                    'hostID' => $this->obj->get('id'),
                    'action' => array('', 0, 1)
                )
            );
    

    I can push the changes but, maybe is better that Sebastian or Tom do them.


  • Developer

    Hi @Tom-Elliott again,

    We found who does the query XD accidentaly. If you access to the host edit page, the query is made. For example:

    /fog/management/index.php?node=host&sub=edit&id=614

    Then, every time a technician accesses the page of a host, my server is freezed XD. The problem is the hostLoginHistory() function which is called by edit() function in line 3257 from hostmanagementpage.class.php.


  • Developer

    Anyway, I think there must be someone else doing the query.


  • Developer

    @Tom-Elliott I try the select with the WHERE hosts.hostID=’<SOMEHOSTIDHERE>’ clausure and goes better XD from 50 seconds downs to 0.044 seconds. If is possible, please, implement this little improvement.


  • Senior Developer

    Maybe another user has the page open to this report?

    I could see if maybe the host page were being accessed the same “mostly” query would be used except adding a WHERE clause to limit the results to that of that particular host.( WHERE hosts.hostID='<SOMEHOSTIDHERE>')

    I don’t know who’s doing the query. Maybe look at the access log and search for the particular report?

    Sorry I’m not more helpful.


  • Developer

    Hi @Tom-Elliott,

    Yes, I think the same but is very strange, because is very frequent. For example, today 20 times??


  • Senior Developer

    This looks like the query performed by the User Tracking report.


Log in to reply
 

389
Online

6.0k
Users

13.4k
Topics

126.2k
Posts