The list all hosts tab takes a long time
-
@Nicolas-Bricet DB maintenance is definitely worth a try. As well you might check Apache and PHP-FPM logs to see if there are any wanrings or errors when you try to load the page. See my signature on where to find the logs.
How many of those 800 host have the fog-client software installed?
-
Hello,
Thank you for your answers.
No we do not use the FOG client on our workstations.
I did the sparing with the proposed commands but that does not change the latency problem to list all the hosts.
Regarding the “Apache” log file, I have no error
Regarding the “PHP” log file here is the log file:[11-Feb-2021 06:32:09] NOTICE: [pool www] child 730309 exited with code 0 after 15368.558498 seconds from start [11-Feb-2021 06:32:09] NOTICE: [pool www] child 767564 started [11-Feb-2021 06:32:34] NOTICE: [pool www] child 730292 exited with code 0 after 15403.135179 seconds from start [11-Feb-2021 06:32:34] NOTICE: [pool www] child 767609 started [11-Feb-2021 06:33:13] NOTICE: [pool www] child 730378 exited with code 0 after 15391.122887 seconds from start [11-Feb-2021 06:33:13] NOTICE: [pool www] child 767663 started [11-Feb-2021 06:33:24] NOTICE: [pool www] child 730373 exited with code 0 after 15407.435764 seconds from start [11-Feb-2021 06:33:24] NOTICE: [pool www] child 767684 started [11-Feb-2021 06:33:33] NOTICE: [pool www] child 730287 exited with code 0 after 15468.350952 seconds from start [11-Feb-2021 06:33:33] NOTICE: [pool www] child 767697 started [11-Feb-2021 06:33:35] NOTICE: [pool www] child 730417 exited with code 0 after 15396.158264 seconds from start [11-Feb-2021 06:33:35] NOTICE: [pool www] child 767710 started [11-Feb-2021 06:33:47] NOTICE: [pool www] child 730334 exited with code 0 after 15455.534759 seconds from start [11-Feb-2021 06:33:47] NOTICE: [pool www] child 767727 started [11-Feb-2021 06:33:53] NOTICE: [pool www] child 730393 exited with code 0 after 15425.242136 seconds from start [11-Feb-2021 06:32:09] NOTICE: [pool www] child 767564 startedIt may not have anything to do with my problem !!
On the other hand I see a difference when I list all the hosts on Host Management and Task Management:
- Hosts : 20 seconds
- Task : 3 seconds
Thank you in advance for your help.
Have a good day.Nicolas.
-
@Nicolas-Bricet Which version of FOG do you use? Do you have storage nodes beside this Main FOG server? Do you use plugins like “locations”?
-
I am using version 1.5.9-RC2.9.
No storage node.
I don’t use the “location” plugin, only:
- accescontrol
- ldap
- Sites
But its problems appear even with a “local” account without restrictions.
Thank you.
Have a good day. -
@nicolas-bricet When your fog server is under normal load (typ 8a-5p local time) what does
topsay when sorted by cpu usagePIs the top process mysql with no extra activity as then when you call up the host table? (need 2 screen shots here)Of so log into mysql as root and run this command.
SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'fog' and ENGINE = 'MyISAM';post the results of both
topunder typical load (imaging adds very little load to the server, so that factor isn’t needed) as well as when you are accessing the host pages where you see the slowness. -
Here is the information requested:
Orders :
Normal charge :
Load list of hosts :
Thanks for your help
Nicolas. -
@nicolas-bricet So you have about 800 hosts with the fog client installed? Are you at typical load with all of these clients on? The reason why I ask is your non-host list load is a bit lower than I expected.
Also would you collect the output of this command from mysql
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session; -
No I have 800 hosts in the interface but we do not use the FOG client on our workstations, I may have expressed myself badly
Nevertheless here is the screenshot :
-
@nicolas-bricet Ah OK that explains what I see and what I expect.
If you want to make a few changes I think we can improve your query performance. These would be similar instructions if you had 800 computers with fog clients.
The root of the issue is the ISAM database engine that is default for Mysql doesn’t handle having too many entities accessing the database at one time. This isn’t your case here because at most you probably have 10 processes accessing the data. If you had 800 fog clients that would be 810 active processes. The fix we have is for large campuses with many fog clients, it will be interesting to see if just updating the data engine from ISAM to InnoDB solves the problem.
The first thing we need to do is change a few of the startup parameters for mysql. Will you provide me the text string of the last mysql command you ran. I need it in text form so I can give you a new string without having to manually retype that whole thing.
Also what linux distro is your FOG server running on?
-
@Nicolas-Bricet Did you have this long loading time for quite some time now? I mean, did it build up with adding the hosts? Or do you remember one point where it started to take long to load this list?
-
Hello,
Here is in text format :
mysql> SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session; +-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ | global | session | +-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)The distribution is:
“Ubuntu 20.04 LTS”Thank you !!
-
@nicolas-bricet said in The list all hosts tab takes a long time:
OK what we are going to do is two step process. The first step is to change (remote) some default startup values that will cause a conflict between mariadb, innodb, and a legacy date format that FOG uses.
The query you ran
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;shows us the current global settings of+-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------- ---+ | global | session | +-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------- ---+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTI ON | +-----------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------- ---+What we are going to do is remove these startup values
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,>> NO_ZERO_IN_DATE,NO_ZERO_DATE,<< ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONThat will allow the smooth transition between the ISAM data engine and the much faster innodb data engine. Understand this transition may not have a real impact on query times, but we need to rule out some of the deficiencies in the ISAM data engine. Future FOG databases will come native with the innodb format.
OK back on track.
We need to find the configuration file for the mariadb (or mysql) server. There is a configuration file in th e/etc directory. The location varies between distributions. I don’t have a current ubuntu server available to give you the exact location so we will need to search for it.Run this command as root or sudo
grep -R -e 'sql-mode' /etc/*We will look for a file that has an existingsql-modekey. Normally this query should return no files with this in it, but we check just to make sure. If it finds one then skip over the next paragraph.We need to find the config file that contans the
[mysqldstanza. So as root or an elevated sudogrep -R -e '\[mysqld\]' /etc/*We are looking for a file that has[mysqld]section. Your search might find two files the default is /etc/my.cnf there may be a file in /etc/my.cnf.d directory. If there is one on /etc/my.cnf.d directory edit that one. If none are found we will stop here because we will need to create a config file. Let me know if you hit this roadblock.Now that you have found the right config file lets edit it. Under the
[mysqld]section insert or edit the line to look like this.[mysqld] sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION"Save and exit the editor.
Now you need to stop and restart mysql
Once that is done log back into the mysql client program as root and rerun that query to ensure NO_ZERO_IN_DATE,NO_ZERO_DATE have been removed from the startup settings.
The final command will convert the hosts table from ISAM format to innodb format
Key this into the mysql client
use fog; ALTER TABLE hosts ENGINE=InnoDB;It should take a few seconds to run.
Now run the query
SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'fog' and ENGINE = 'MyISAM';You should see the hosts table in innodb format.
Now run your hosts management query in the web ui. What are the results.
While we are at it (after you do the above test) we probably should convert the rest of the tables in case there are any joins going on in the queries and also to have a consistent db format.
Run this query (copy and paste will be your friend here. You can paste the query in all in one go.
ALTER TABLE LDAPServers ENGINE=InnoDB; ALTER TABLE clientUpdates ENGINE=InnoDB; ALTER TABLE dirCleaner ENGINE=InnoDB; ALTER TABLE globalSettings ENGINE=InnoDB; ALTER TABLE greenFog ENGINE=InnoDB; ALTER TABLE groupMembers ENGINE=InnoDB; ALTER TABLE groups ENGINE=InnoDB; ALTER TABLE history ENGINE=InnoDB; ALTER TABLE hookEvents ENGINE=InnoDB; ALTER TABLE hostAutoLogOut ENGINE=InnoDB; ALTER TABLE hostMAC ENGINE=InnoDB; ALTER TABLE hostScreenSettings ENGINE=InnoDB; ALTER TABLE hosts ENGINE=InnoDB; ALTER TABLE imageGroupAssoc ENGINE=InnoDB; ALTER TABLE imagePartitionTypes ENGINE=InnoDB; ALTER TABLE imageTypes ENGINE=InnoDB; ALTER TABLE images ENGINE=InnoDB; ALTER TABLE imagingLog ENGINE=InnoDB; ALTER TABLE inventory ENGINE=InnoDB; ALTER TABLE ipxeTable ENGINE=InnoDB; ALTER TABLE keySequence ENGINE=InnoDB; ALTER TABLE location ENGINE=InnoDB; ALTER TABLE locationAssoc ENGINE=InnoDB; ALTER TABLE moduleStatusByHost ENGINE=InnoDB; ALTER TABLE modules ENGINE=InnoDB; ALTER TABLE multicastSessions ENGINE=InnoDB; ALTER TABLE multicastSessionsAssoc ENGINE=InnoDB; ALTER TABLE nfsFailures ENGINE=InnoDB; ALTER TABLE nfsGroupMembers ENGINE=InnoDB; ALTER TABLE nfsGroups ENGINE=InnoDB; ALTER TABLE notifyEvents ENGINE=InnoDB; ALTER TABLE os ENGINE=InnoDB; ALTER TABLE oui ENGINE=InnoDB; ALTER TABLE plugins ENGINE=InnoDB; ALTER TABLE powerManagement ENGINE=InnoDB; ALTER TABLE printerAssoc ENGINE=InnoDB; ALTER TABLE printers ENGINE=InnoDB; ALTER TABLE pxeMenu ENGINE=InnoDB; ALTER TABLE scheduledTasks ENGINE=InnoDB; ALTER TABLE schemaVersion ENGINE=InnoDB; ALTER TABLE snapinAssoc ENGINE=InnoDB; ALTER TABLE snapinGroupAssoc ENGINE=InnoDB; ALTER TABLE snapinJobs ENGINE=InnoDB; ALTER TABLE snapinTasks ENGINE=InnoDB; ALTER TABLE snapins ENGINE=InnoDB; ALTER TABLE supportedOS ENGINE=InnoDB; ALTER TABLE taskLog ENGINE=InnoDB; ALTER TABLE taskStates ENGINE=InnoDB; ALTER TABLE taskTypes ENGINE=InnoDB; ALTER TABLE tasks ENGINE=InnoDB; ALTER TABLE userCleanup ENGINE=InnoDB; ALTER TABLE userTracking ENGINE=InnoDB; ALTER TABLE users ENGINE=InnoDB; ALTER TABLE virus ENGINE=InnoDB; ALTER TABLE wolbroadcast ENGINE=InnoDB;Now run the query again to see if I missed any tables.
SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'fog' and ENGINE = 'MyISAM';If I missed any tables just create your own ALTER TABLE commands
OK so why INNODB format over ISAM. There are many reasons but the most impactful is that ISAM has table locking on an update where INNODB only locks the row on an update. So when there is an update query for ISAM only one writer is allowed at a time for the entire table even if the second writer and the first writer are updating different rows in the table. INNODB only locks the row that is going to be changed leaving other processes to be able to write to other rows and move on. This change is most impactful when you have many FOG Client computers hitting the server every 5 minutes. SO since you are doing a read query when access the hosts via the web ui, this change may not have an impact at all. BUT we need to rule out ISAM as the performance drag on your system.
