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