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

InnoDB?

Scheduled Pinned Locked Moved
General
5
12
922
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.
  • W
    Wayne Workman
    last edited by Apr 3, 2020, 9:19 PM

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

    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!
    Daily Clean Installation Results:
    https://fogtesting.fogproject.us/
    FOG Reporting:
    https://fog-external-reporting-results.fogproject.us/

    1 Reply Last reply Reply Quote 0
    • G
      george1421 Moderator
      last edited by Apr 3, 2020, 9:59 PM

      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.

      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!

      1 Reply Last reply Reply Quote 0
      • S
        Sebastian Roth Moderator
        last edited by Apr 4, 2020, 10:39 AM

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

        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

        G 1 Reply Last reply Apr 4, 2020, 11:11 AM Reply Quote 0
        • S
          Sebastian Roth Moderator
          last edited by Sebastian Roth Apr 4, 2020, 5:05 AM Apr 4, 2020, 11:03 AM

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

          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

          G 1 Reply Last reply Apr 4, 2020, 11:26 AM Reply Quote 0
          • G
            george1421 Moderator @Sebastian Roth
            last edited by Apr 4, 2020, 11:11 AM

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

            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!

            1 Reply Last reply Reply Quote 0
            • G
              george1421 Moderator @Sebastian Roth
              last edited by Apr 4, 2020, 11:26 AM

              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.

              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!

              1 Reply Last reply Reply Quote 0
              • S
                Sebastian Roth Moderator
                last edited by Apr 4, 2020, 1:52 PM

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

                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
                • W
                  Wayne Workman
                  last edited by Wayne Workman Apr 4, 2020, 9:15 AM Apr 4, 2020, 3:13 PM

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

                  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!
                  Daily Clean Installation Results:
                  https://fogtesting.fogproject.us/
                  FOG Reporting:
                  https://fog-external-reporting-results.fogproject.us/

                  G 1 Reply Last reply Apr 4, 2020, 3:29 PM Reply Quote 1
                  • G
                    george1421 Moderator @Wayne Workman
                    last edited by Apr 4, 2020, 3:29 PM

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

                    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!

                    EduardoTSeoaneE 1 Reply Last reply Oct 15, 2020, 1:24 PM Reply Quote 0
                    • Tom ElliottT
                      Tom Elliott
                      last edited by Tom Elliott Apr 4, 2020, 1:46 PM Apr 4, 2020, 7:45 PM

                      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.

                      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 3
                      • EduardoTSeoaneE
                        EduardoTSeoane @george1421
                        last edited by Oct 15, 2020, 1:24 PM

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

                        G 1 Reply Last reply Oct 15, 2020, 1:33 PM Reply Quote 0
                        • G
                          george1421 Moderator @EduardoTSeoane
                          last edited by Oct 15, 2020, 1:33 PM

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

                          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!

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

                          153

                          Online

                          12.0k

                          Users

                          17.3k

                          Topics

                          155.2k

                          Posts
                          Copyright © 2012-2024 FOG Project