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 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
 

508
Online

5.8k
Users

13.1k
Topics

123.2k
Posts