@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_SUBSTITUTION
That 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 existing sql-mode key. 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 [mysqld stanza. So as root or an elevated sudo grep -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.
