Bottleneck on DATABASE

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

[Sebastian Roth]

@EduardoTSeoane Just wanted to see if you are still buried in work…