Fog running very slow

[John L Clark]

As of late Fog is running extremely slow. We have had no changes to fog but pulling up the GUI takes forever. Then searching for anything is very very slow. This is a screenshot of the server information. Any help would be appreciated. Thanks

[Sebastian Roth]

@John-L-Clark See if you can figure out what is causing this high system load using the command top. Post a screenshot of that output here.

[John L Clark]

@sebastian-roth
This is what I see. Thanks

[george1421]

@john-l-clark How many computers have the FOG client installed on them in this environment?

[John L Clark]

@george1421 probably a few 1000. Not sure of the exact number. We only started seeing this a few months ago.

[george1421]

@john-l-clark OK I’m going to suspect with 1000 clients the database is becoming a bottleneck. Since it has a high CPU usage with many php-fpm worker threads. I have an thread here I’m referencing. https://forums.fogproject.org/post/133490

Log into mysql and paste in this these commands.

SELECT TABLE_NAME,ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'fog' and ENGINE = 'MyISAM';

It should list all of the tables in MyISAM format. If that is the case then run this command and post the results here.

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;

[John L Clark]

@george1421

[george1421]

@john-l-clark I could be a bit more precise if you would have copied the text and pasted it into the thread so I’ll use the resutls from the other thread.

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Out of that response string copy that entire line starting with only and ending with substitution and paste it into a text editor. Then remove the bold text from above. The resultant string . Now from this post: https://forums.fogproject.org/post/134207

You need to find the mysql configuration files that call out [mysqld] and add in this line with your parameters minus the zero date options just below the section header [mysqld] like this.

[mysqld]
sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Then restart the FOG server.

Now what you have done is set the stage to migrate the databases from MyISAM to InnoDB format. Just a little background the MyISAM engine locks the entire table on an update, where InnoDB locks just the row when a record is updated. What is happening is all of those clients connecting to the mysql database is putting a heavy lock load and resource content on the mysql database.

So once that is done rerun the mysql command to ensure those zero date values are gone

SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session;

If they are then you are ready for the next step.

[John L Clark]

@george1421 When i did this. I could not get mysql to start until i removed the sqlmode.cnf.

To do that we need to add a configuration file into /etc/mysql/conf.d/ called sqlmode.cnf. Into that file add in the following:

[mysqld]
sql-mode=“ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION”

[george1421]

@john-l-clark said in Fog running very slow:

until i removed the sqlmode.cnf.

This may be distro specific.

what I would do is go into /etc and grep search for mysqld

grep -R -e "[mysqld]" /etc/*

That should find the config file where that section is. why it probably had a problem was it found two of those sections in the config files and then it had a fit not knowing which one to use.

[John L Clark]

@george1421 not sure what changed but it is running very well now. I have never been able to get the SQL commands to work or adding that cnf file to work but FOG is running great now. Searches in the GUI running the full host registration that used to take a few minutes is working great now. I will keep testing. Thanks again for all the help.

[george1421]

@john-l-clark Up til this point we didn’t change anything. If you were able to make the database server change then we could do the next step that will solve the speed issue. SO with that said, its probably running fine now but the slowness will be back.

When you are to the point where you want to continue let me know. We can work through the rest of the changes.