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

    Bottleneck on DATABASE

    Scheduled Pinned Locked Moved Solved
    FOG Problems
    5
    21
    3.3k
    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.
    • EduardoTSeoaneE
      EduardoTSeoane
      last edited by EduardoTSeoane

      Hi to all,
      I’ve a FOG System with 1 Master, 1 database out of server, 110 Storage Nodes on different towns and 17000 computer clients all with a variety of connections from adsl 2MB to 1GB FTTH.

      I’ve been experiencing a big bottleneck on MySQL myISAM database with 200 on workload average and 3.70GB RAM.

      I’ve take a decision i migrate the database to InnoDB to test on production, by the moment the work average are nearly to 6 and the memory on 3.50GB more or less.

      I’ll give you notices on the next friday…, if you have more suggestions I’ll be pleased to study and test it.

      By the moment it’s working by 2h 30min

      EduardoTSeoaneE 1 Reply Last reply Reply Quote 0
      • george1421G
        george1421 Moderator
        last edited by george1421

        Have you already moved the mysql server (service) to its own server instance and not running directly on the FOG server?

        Is the disk where the database stored been moved to a flash based media (SSD or NVMe)?

        Have you run the mysqltuner script against your mysql server? Have you implemented any of its recommendations?

        In your installation, do you use the FOG client? If so what is the check in time you set?

        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 1
        • S
          Sebastian Roth Moderator
          last edited by

          @EduardoTSeoane Which version of FOG do you currently use? I suppose it’s working-1.6 right?

          Just want to cross link Eduardo’s other topics which might add valuable information:
          https://forums.fogproject.org/topic/13948/database-stress-when-cloning-on-big-environments
          https://forums.fogproject.org/topic/13971/working-1-6-login-issues-with-big-database

          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

          EduardoTSeoaneE 1 Reply Last reply Reply Quote 0
          • EduardoTSeoaneE
            EduardoTSeoane
            last edited by EduardoTSeoane

            Yes i have the database virtual server (mariadb 10.4) and fog web virtual server are different virtual machines.
            The Virtual Sysadmin told me than the database server is stored on a mechanical sas.
            On myISAM i have implemented all the recommendations, now i have innodb on basic configuration, I’ll do optimizations next week with a few days working.

            I use FOG client, with a checkin time about 15min as test, currently workload average is about 15-17, but im still doing verifications.

            By the moment this seems to work with out problems.

            I do this changes because i detect a lot of table locks, that make a poor performance.

            george1421G 1 Reply Last reply Reply Quote 0
            • EduardoTSeoaneE
              EduardoTSeoane @Sebastian Roth
              last edited by

              @Sebastian-Roth On production 1.5.7, i’ve been delayed on the working-1-6 tests

              1 Reply Last reply Reply Quote 0
              • george1421G
                george1421 Moderator @EduardoTSeoane
                last edited by

                @EduardoTSeoane said in Bottleneck on DATABASE:

                Yes i have the database virtual server (mariadb 10.4) and fog web virtual server are different virtual machines.

                Ok good this would have been the first step.

                The Virtual Sysadmin told me than the database server is stored on a mechanical sas.

                As long as its a sas array then its OK. A single sas disk is not good for FOG imaging. But since you said its a VM then you probably have a proper VM Host server.

                On myISAM i have implemented all the recommendations, now i have innodb on basic configuration, I’ll do optimizations next week with a few days working.

                The mysqltuner script is very informative on its recommendations. Its good to let your mysql server run for a few days so it collects good run time data for the mysqltuner script to review.

                I use FOG client, with a checkin time about 15min as test, currently workload average is about 15-17, but im still doing verifications.

                For a 15 minute check in time and 17000 fog client computers, that gives you (In an ideal environment) 18 check-ins per second. I wonder if you increased the number of php-fpm worker threads to help with this load.

                I do this changes because i detect a lot of table locks, that make a poor performance.

                First let me say that FOG isn’t designed for this large of an installation. But I’m sure there are things we can do to optimized the installation. The first step is to get the database running well. Then we can look into other tweaks to help productivity.

                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 Reply Quote 0
                • EduardoTSeoaneE
                  EduardoTSeoane @george1421
                  last edited by

                  First at all, maybe this is not the correct thread, this
                  @george1421 said in Bottleneck on DATABASE:

                  First let me say that FOG isn’t designed for this large of an installation. But I’m sure there are things we can do to optimized the installation. The first step is to get the database running well. Then we can look into other tweaks to help productivity.

                  Yes I know, but it’s a good project that fits on our needs.

                  My database analisys reveals than there are too many table locks that fall in poor performance. With this test, changing myISAM by InnoDB change the table locks by row locks, first problem seems solved, and currently have ,on the first moment, a workload average 7 (200 points lesser) with client checkins about 45min 1hour. now we can add cache and memory temporary tables, that can help to get the best database performance.

                  The change has been do it, now we are on “Test mode” and it feels like a great hint, all the help to increase performance on the installation are wellcome, and feel free to request me information, im glad to help.

                  By the other way, now you can get information about the convenience about change the database engine to get more performance, I’ll report all information than you request to me and I can.

                  The change than i made was the ALTER TABLES, no php code was touched.

                  george1421G 1 Reply Last reply Reply Quote 0
                  • fry_pF
                    fry_p Moderator
                    last edited by

                    I did some testing in the past here: https://forums.fogproject.org/topic/13820/load-balancing-and-fog?_=1579975398818

                    Not sure how applicable this is but I wanted to put it here just in case.

                    Been having some medical issues that have kept me from continuing this, but for what it’s worth.

                    Like open source community computing? Why not do it for a good cause?
                    Use your computer/server for humanitarian projects when it is idle!
                    https://join.worldcommunitygrid.org?recruiterId=1026912

                    EduardoTSeoaneE 1 Reply Last reply Reply Quote 0
                    • george1421G
                      george1421 Moderator @EduardoTSeoane
                      last edited by

                      @EduardoTSeoane It would be helpful if you could document the changes you have made to your fog installation. The developers don’t have FOG installations as large as what you have so its hard to simulate the workloads you are seeing. It would be helpful for both the Developers as well as other large campus FOG installations if you could share what you have learned. @fry_p does have a large campus network where he was working on a similar issue to what you have.

                      So it would be very helpful if you could share what you learned from your testing for everyone.

                      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 Reply Quote 0
                      • EduardoTSeoaneE
                        EduardoTSeoane @george1421
                        last edited by

                        @george1421 Yeah, this is my hint. I’ll do the documentation, but I need time.

                        At this moment I’m having a workload of 7 with 17000 clients at 15min of checkin time.
                        Everything seems to work correctly.

                        Next friday I’ll do another report.

                        1 Reply Last reply Reply Quote 0
                        • EduardoTSeoaneE
                          EduardoTSeoane @fry_p
                          last edited by

                          @fry_p Yeah I read it, but my first hit is to keep it more simplest as posible, I think that before to start to implement that it’s bette to do lesser works, there are a few months than I’m thinking that myISAM is not an optimal engine for this, almost for me, and I’m alone as FOG Sysadmin and need to keep my workload under control, I have not much experience with clusters and load balancing, and it’s no so good.

                          When I can I share documentation about our installation and customizations.

                          fry_pF 1 Reply Last reply Reply Quote 1
                          • fry_pF
                            fry_p Moderator @EduardoTSeoane
                            last edited by

                            @EduardoTSeoane said in Bottleneck on DATABASE:

                            @fry_p Yeah I read it, but my first hit is to keep it more simplest as posible, I think that before to start to implement that it’s bette to do lesser works, there are a few months than I’m thinking that myISAM is not an optimal engine for this, almost for me, and I’m alone as FOG Sysadmin and need to keep my workload under control, I have not much experience with clusters and load balancing, and it’s no so good.

                            When I can I share documentation about our installation and customizations.

                            Lone FOG admin? That sounds awfully familiar 🙂 (I am too)

                            I have 0 experience with this other than the testing I did. Perhaps when I am recovered I will dig back into this using my work environment. I am glad you will share your findings when you find time and look forward to reading them.

                            Scalable FOG would really expand the usage of FOG (pun intended) in bigger use-cases. I understand this is all volunteer with everyone having day-jobs, so perhaps while I am on medical leave, I can set up a home lab. I have 2 servers, a few desktops, and proper networking equipment at home. IDK how I will replicate the number of clients we need to properly test large environments, but at least I can do some more proof of concept work.

                            Give me a few weeks (I will be couch bound at my parent’s place starting Feb 7th for about two weeks at least after surgery) to recover and then I can do some testing. If I don’t do something like this, I will most likely go crazy from boredom lol.

                            Like open source community computing? Why not do it for a good cause?
                            Use your computer/server for humanitarian projects when it is idle!
                            https://join.worldcommunitygrid.org?recruiterId=1026912

                            1 Reply Last reply Reply Quote 0
                            • S
                              Sebastian Roth Moderator
                              last edited by

                              @fry_p said in Bottleneck on DATABASE:

                              I understand this is all volunteer with everyone having day-jobs, so perhaps while I am on medical leave, I can set up a home lab.

                              Good call! You are more than welcome to test and discuss things here in the forums when you feel better after the surgery. All the best to you!

                              IDK how I will replicate the number of clients we need to properly test large environments, but at least I can do some more proof of concept work.

                              Good point. While you can lower the checkin time of fog-clients to get higher number of requests in a certain amount of time I don’t have a good idea on how to simulate thousands of clients talking to the FOG server yet. While there are tons of load testing tools out there I don’t think there is one available that would be able to simulate the whole fog-client communication. Maybe we could use something like https://github.com/PragmaticFlow/NBomber (written in C# just as the fog-client is) and reuse lots of our code in that. Just a quick idea. Probably I won’t find the time to put something like this together.

                              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
                              • EduardoTSeoaneE
                                EduardoTSeoane
                                last edited by

                                @Sebastian-Roth @fry_p @george1421

                                Update results:

                                Currently the server is 8 days uptime.
                                workload average ±9
                                workload average max: ±14
                                checking time = 900s
                                clients = 17000
                                Storage nodes = 110
                                Plugins enabled = Location

                                1 Reply Last reply Reply Quote 1
                                • S
                                  Sebastian Roth Moderator
                                  last edited by

                                  @EduardoTSeoane said in Bottleneck on DATABASE:

                                  The change than i made was the ALTER TABLES, no php code was touched.

                                  So this is all there is to do? Any drawbacks? While I have done a fair amont of database stuff over the years I am definitely a pro and can’t see if that might be a culprit in some ways in the future. Definitely something we should look into! Not for 1.5.x but 1.6.x I think.

                                  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

                                  Q 1 Reply Last reply Reply Quote 0
                                  • Q
                                    Quazz Moderator @Sebastian Roth
                                    last edited by Quazz

                                    @Sebastian-Roth Thankfully, Mariadb has a list of possible issues: https://mariadb.com/kb/en/converting-tables-from-myisam-to-innodb/

                                    We’d need to set some baseline settings in the mysql config, imo.

                                    1 Reply Last reply Reply Quote 2
                                    • EduardoTSeoaneE
                                      EduardoTSeoane @EduardoTSeoane
                                      last edited by

                                      @EduardoTSeoane
                                      Last report about this…

                                      Everything seems ok, System runing without stops with the same efficiency.
                                      Everything seems to work ok.

                                      Thanks to all

                                      george1421G 1 Reply Last reply Reply Quote 0
                                      • george1421G
                                        george1421 Moderator @EduardoTSeoane
                                        last edited by

                                        @EduardoTSeoane Can you give us a bit more detail on your current configuration?

                                        Did you convert the table engine from isam to innodb?
                                        Did you make any other changes to the mysql (mariadb) config file?
                                        Have you run the mysqltuneup script? What was its recommendations after a few weeks of running under the new configuration?
                                        What is your current FOG Client check in time?

                                        The reason why I ask is that another FOG Admin with a few hundred computers is having a similar issue to this. We’ve tried a few changes, but it would help to understand the path your are on to success.

                                        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 2 Replies Last reply Reply Quote 0
                                        • EduardoTSeoaneE
                                          EduardoTSeoane @george1421
                                          last edited by

                                          @george1421
                                          Excuse the delay, too much work with sars-covid
                                          this is my my.cnf config file.
                                          Current checking time 900s without server stress

                                          When i have a normal situation I make mysqltunner tests to put results/recomendations on this thread

                                          [mysqld]
                                          datadir=/var/lib/mysql
                                          socket=/var/lib/mysql/mysql.sock

                                          #wait_timeout = 1
                                          performance_schema = On
                                          skip_name_resolve = On
                                          max_connections = 1000
                                          query_cache_size = 0
                                          query_cache_type = 0
                                          open_files_limit = 44801
                                          table_open_cache = 50000
                                          innodb_log_file_size = 16M

                                          Basic, by the moment with all my workplaces closed i cant optimize database.

                                          I’ve the database on separate virtual server

                                          1 Reply Last reply Reply Quote 0
                                          • EduardoTSeoaneE
                                            EduardoTSeoane @george1421
                                            last edited by

                                            @george1421 I help with this in order i can spend time, currently I’ve too much work but I will take a look at the forum when I can.

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

                                            192

                                            Online

                                            12.0k

                                            Users

                                            17.3k

                                            Topics

                                            155.2k

                                            Posts
                                            Copyright © 2012-2024 FOG Project