I want to know ... UserTracking query

[Fernando Gietz]

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?

[Tom Elliott]

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

[Fernando Gietz]

Hi @Tom-Elliott,

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

[Tom Elliott]

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.

[Fernando Gietz]

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

[Fernando Gietz]

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

[Fernando Gietz]

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.

[Fernando Gietz]

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.

[Sebastian Roth]

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

[Sebastian Roth]

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

[Sebastian Roth]

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

[Fernando Gietz]

I returned XD thanks for all @Sebastian-Roth