InnoDB?

[Wayne Workman]

Is there any reasons why InnoDB isn’t the default database engine? @george1421 says it performs better.

[george1421]

Simply the default for MySQL for years is ISAM.

I’ve converted almost all of the mysql tables over to innodb format (not just FOG DB). I only found one application that broke using the innodb format. That is one that used FULL TEXT SEARCH function. I don’t know why this application is/was specific because FOG seems to find hosts still based on word fragment searches. Once I converted the table format back to isam it worked.

[Sebastian Roth]

@george1421 said in InnoDB?:

Once I converted the table format back to isam it worked.

Just so we get this right. This was for the other application you mentioned earlier not FOG?

[Sebastian Roth]

@Wayne-Workman Definitely a good question. FOG was just using MyISAM because InnoDB wasn’t available back in 2007 when FOG came out. Even when FOG 0.32 was released in Summer 2011 most distros still used MySQL 5.0 which didn’t have InnoDB yet.

https://docs.oracle.com/cd/E17952_01/mysql-5.5-relnotes-en/news-5-5-5.html

InnoDB has been upgraded to version 1.1.1. This version is considered of “early adopter” quality.
InnoDB is now the default storage engine, rather than MyISAM, in the regular and enterprise versions of MySQL.

I know it’s a long time since then but as things were sort of running fine with MyISAM the FOG developers (me included) didn’t think about touching the very basis of the DB up until now.

[george1421]

@Sebastian-Roth said in InnoDB?:

This was for the other application you mentioned earlier not FOG?

No this was a Joomla plugin called fabrik that used a full text search command. Looking into it last night it looks like it would have worked if I would have recreated the index once the table was converted if I understood it right https://www.w3resource.com/mysql/mysql-full-text-search-functions.php It was only the search function of this add on module that cause the problem. Joomla and the rest of the fabrik plugin worked with innodb tables.

[george1421]

The ISAM format only really becomes an issue when you have a large campus of computers with the fog client installed (guess that its the fog client). MYISAM uses table locking for updates. Once a client requests to update data MYISAM engine blocks all other updates to that table for other clients until the first clients transaction is done. Where the innodb engine only locks the row the client is updating not the whole table. If you assume sequential check ins of the fog client, lets say you have a 500 client campus and your checking timing is the default 300 seconds. That means you will have a fog client check in every 0.6 seconds. While 0.6 seconds is pretty fast to a human its a long time to a computer. The issue is the client check ins are not sequential, but random so in one second you could have 20 computers checking in. With table locking you will have a queued requests waiting for the previous locks to be released so the next one in line can complete.

In the other thread, that’s why I asked the OP to revert his settings back to the default (typical) settings with FOG, then convert just the database over to innodb format. I wanted to see how much better/faster the sql server was by just changing the database table engine to innodb if everything else was equal. I’d have to go back and look at the numbers to get a real percentage improvement but the OP said it helped quite a bit with overall CPU usage on the mysql task.

From an imaging standpoint there is no value in changing from ISAM to INNODB format, because there are just not that many transactions during imaging and typically you don’t image 100s of computers at the same time. So there is not really any performance hit just imaging. IMO its the fog clients that have the biggest impact on the mysql server.

[Sebastian Roth]

@george1421 said in InnoDB?:

From an imaging standpoint there is no value in changing from ISAM to INNODB format, because there are just not that many transactions during imaging …

As I have learned not too long ago there are quite a few updates when capturing an image. That is because FOS updates the client size figures in a loop while capturing to update the status. I have looked into optimizing this behavior but it’s a larger change that I don’t want to apply to the current 1.5.x branch anymore. Will work on this for 1.6.x at some point.

But beside that I still think you are right with the following!

IMO its the fog clients that have the biggest impact on the mysql server.

[Wayne Workman]

So maybe set the default to InnoDB, and we think further on how existing installs can get onto InnoDB?

[george1421]

@Wayne-Workman said in InnoDB?:

So maybe set the default to InnoDB, and we think further on how existing installs can get onto InnoDB?

Now that the OP of the other thread has all of the tables converted I wanted the OP to run for a few weeks under this default configuration (using innodb) and then run the mysqltuner script to see what suggestions it recommends. The current virus situation has delayed this testing both from the FOG Admin side as well as having a full fleet of client computers hitting the fog server. I think changing the database engine to innodb is the first step, the second step is to see what mysqltuner suggests for innodb cache sizes and then make an educated guess where to start from with mysql tuning.

I really feel the sql server and its configuration really needs some attention. It has worked well over the years, so no one really looked at it with a large client base so we could fine tune it. Whenever someone would report high mysql utilization, we would as for them to help debug and they would ghost us after that. So debugging really happened until now.

[Tom Elliott]

Just to chime in here, 1.6 is now setup with innodb as default. So while we discuss things for 1.5.x, just know that this has been corrected.

[EduardoTSeoane]

@george1421 said in InnoDB?:

@Wayne-Workman said in InnoDB?:

So maybe set the default to InnoDB, and we think further on how existing installs can get onto InnoDB?

Now that the OP of the other thread has all of the tables converted I wanted the OP to run for a few weeks under this default configuration (using innodb) and then run the mysqltuner script to see what suggestions it recommends. The current virus situation has delayed this testing both from the FOG Admin side as well as having a full fleet of client computers hitting the fog server. I think changing the database engine to innodb is the first step, the second step is to see what mysqltuner suggests for innodb cache sizes and then make an educated guess where to start from with mysql tuning.

I really feel the sql server and its configuration really needs some attention. It has worked well over the years, so no one really looked at it with a large client base so we could fine tune it. Whenever someone would report high mysql utilization, we would as for them to help debug and they would ghost us after that. So debugging really happened until now.

Hi, invoking an raise dead…

i have my fog database full migrated with out problems, current configuration on this post

https://forums.fogproject.org/topic/14140/bottleneck-on-database/2?_=1602767986457

i have pending optimize the database…

[george1421]

@EduardoTSeoane It will be interesting to see how optimization works for you. With the size of your campus we may need to split the database from the FOG server to give the sql database more CPU time. We need feedback from large campuses like you have to help with performance tuning of FOG.