• Recent
  • Unsolved
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Register
  • Login
  • Recent
  • Unsolved
  • Tags
  • Popular
  • Users
  • Groups
  • Search
  • Register
  • Login

I want to know ... UserTracking query

Scheduled Pinned Locked Moved Solved
Bug Reports
3
12
1.2k
Loading More Posts
  • Oldest to Newest
  • Newest to Oldest
  • Most Votes
Reply
  • Reply as topic
Log in to reply
This topic has been deleted. Only users with topic management privileges can see it.
  • F
    Fernando Gietz Developer
    last edited by Feb 20, 2019, 4:06 PM

    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?

    1 Reply Last reply Reply Quote 0
    • T
      Tom Elliott
      last edited by Feb 20, 2019, 4:15 PM

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

      Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG! Get in contact with me (chat bubble in the top right corner) if you want to join in.

      Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

      Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

      1 Reply Last reply Reply Quote 0
      • F
        Fernando Gietz Developer
        last edited by Feb 20, 2019, 4:18 PM

        Hi @Tom-Elliott,

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

        1 Reply Last reply Reply Quote 0
        • T
          Tom Elliott
          last edited by Feb 20, 2019, 4:22 PM

          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.

          Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG! Get in contact with me (chat bubble in the top right corner) if you want to join in.

          Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

          Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

          F 1 Reply Last reply Feb 20, 2019, 4:34 PM Reply Quote 0
          • F
            Fernando Gietz Developer @Tom Elliott
            last edited by Feb 20, 2019, 4:34 PM

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

            1 Reply Last reply Reply Quote 0
            • F
              Fernando Gietz Developer
              last edited by Feb 20, 2019, 4:40 PM

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

              1 Reply Last reply Reply Quote 0
              • F
                Fernando Gietz Developer
                last edited by Feb 20, 2019, 5:23 PM

                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.

                1 Reply Last reply Reply Quote 1
                • F
                  Fernando Gietz Developer
                  last edited by Fernando Gietz Mar 5, 2019, 9:47 AM Mar 5, 2019, 3:46 PM

                  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.

                  1 Reply Last reply Reply Quote 1
                  • S
                    Sebastian Roth Moderator
                    last edited by Mar 5, 2019, 6:10 PM

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

                    Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                    Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                    1 Reply Last reply Reply Quote 0
                    • S
                      Sebastian Roth Moderator
                      last edited by Mar 6, 2019, 5:40 PM

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

                      Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                      Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                      1 Reply Last reply Reply Quote 0
                      • S
                        Sebastian Roth Moderator
                        last edited by Sebastian Roth Aug 18, 2019, 4:42 PM Aug 18, 2019, 9:25 PM

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

                        Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                        Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                        1 Reply Last reply Reply Quote 0
                        • F
                          Fernando Gietz Developer
                          last edited by Aug 20, 2019, 1:08 PM

                          I returned XD thanks for all @Sebastian-Roth

                          1 Reply Last reply Reply Quote 0
                          • 1 / 1
                          • First post
                            Last post

                          229

                          Online

                          12.0k

                          Users

                          17.3k

                          Topics

                          155.2k

                          Posts
                          Copyright © 2012-2024 FOG Project