Bottleneck on DATABASE



  • 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


  • Moderator

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


  • Developer

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



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


  • Developer

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


  • Moderator

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



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



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


  • Moderator

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


  • Moderator

    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.



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


  • Moderator

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



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



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


  • Developer

    @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


  • Moderator

    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?


Log in to reply
 

378
Online

6.6k
Users

14.0k
Topics

132.3k
Posts