I want to know ... UserTracking query
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
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?
@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 296messages 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
@Fernando-Gietz Just pushed to
dev-branch. Thanks again! Should we mark this solved?
@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.
I have fixed this bug in my server making a little change in hostlogins function.
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.
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:
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.
Anyway, I think there must be someone else doing the query.
@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.
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.(
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.
Yes, I think the same but is very strange, because is very frequent. For example, today 20 times??
This looks like the query performed by the User Tracking report.