Bottleneck on DATABASE

[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

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

[Sebastian Roth]

@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

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

[EduardoTSeoane]

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

[george1421]

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

[EduardoTSeoane]

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.

[fry_p]

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.

[george1421]

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

[EduardoTSeoane]

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

[EduardoTSeoane]

@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_p]

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

[Sebastian Roth]

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

[EduardoTSeoane]

@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

[Sebastian Roth]

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

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

[EduardoTSeoane]

@EduardoTSeoane
Last report about this…

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

Thanks to all

[george1421]

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

[EduardoTSeoane]

@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

[EduardoTSeoane]

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