Use an external database

Fernando Gietz

Hi,

I am having performance trouble with my FOG server. The problem is the big resources that consumes my FOG database (I have 8000 client that load the database) and I am thinking the possibility to use an external database and not a local one.

I found a post about this: Database migration and database in external host. But doesn’t explain big thing.

How can I use an external database? Is possible?

george1421

I would say that using an external database for the size of your campus is probably a good idea. While I haven’t setup this configuraiton on the test bench, the concept isn’t very hard. In FOG there should be a configuration file that points to the local FOG database. If you have storage nodes, the configuration file there, is what you would need on the main fog server because the storage nodes use the master FOG server database. Its all the same concept. (You will also have to update all storage nodes if you move the database off the fog server).

I would start by building your MYSQL server database base server. Do system configurations specific to mysql performance tuning. Then follow the process as you would migrate one fog server to the next in that you would export the master fog server database using mysqldump and then import that database into your mysql server. You will then need to update the fog configuration file on the master fog server and storage nodes, and finally reboot reboot all fog related servers.

Fernando Gietz

Hi @george1421 ,

The configutarion file is .fogsettings in /opt/fog?

george1421

@Fernando-Gietz said in Use an external database:

Hi @george1421 ,

The configutarion file is .fogsettings in /opt/fog?

You will need to update that one too for upgrades, but there is a configuration file in the /var/www/html/fog path that holds the startup settings. I’ve been grep’ing to try to find the config file. I thought it was in /var/www/html/fog/commons but it looks not.

george1421

@george1421 Ah found it: /var/www/html/fog/lib/fog/config.class.php

    private static function _dbSettings()
    {
        define('DATABASE_TYPE', 'mysql'); // mysql or oracle
        define('DATABASE_HOST', '127.0.0.1');
        define('DATABASE_NAME', 'fog');
        define('DATABASE_USERNAME', 'root');
        define('DATABASE_PASSWORD', '<no_password_for_you>');
    }

Fernando Gietz

Hi @george1421,

I tried to do it and worked fine!! You can use an external database changing the both files: /var/www/html/fog/lib/fog/config.class.php adn /opt/fog/.fogsettings.

snmysqluser='<MySQL_user>'
snmysqlpass='<password_of_your_MySQL_user>'
snmysqlhost='<ip_of_your_MySQL_Server>'

I don’t do any fresh install lastly, then I don’t know if the installer asks you about the location of your MySQL server or by default is always localhost. If the installer is not prepare to use an external database, I will suggest to implement this feature in future versions.

I want to take advantage of the occasion to say that the mariadb version, 5.5 is obsolete, I updated it to the last stable version the last month, MariaDB 10.3. Maybe will be interesant to update the installer to install this last version of MariaDB.

Under RedHat 7 the packages are MariaDB-client and MariaDB-server.

george1421

@Fernando-Gietz It will be interesting to see if you have any performance differences since you are now running an external database and its on version 10 over the integrated database on version 5. If you had nagios setup to measure cpu usage, you could have had a great baseline to know if the change helped or hurt your setup.

Fernando Gietz

Ummm … I can not say about the performance, I follow with my mysql performance problems XD both in one version and in another. I can say that the configuration in 5.5 version is different than 10.3, I have to retuning the my.cfg file.

Tom Elliott

@Fernando-Gietz you can define this inline when running the installer.

snmysqlhost=“193.168.1.110” snmysqluser=“someuser” snmysqlpass=“somepassword” ./installfog.sh -y

george1421

@Fernando-Gietz said in Use an external database:

I can not say about the performance

I’m considering performance on a FOG system level.

<thinking out loud> There must have been a reason why Fernando made this change. He wouldn’t just make this change for fun?? There must have been some kind of issue with the integrated database to move it external. I wonder if he can tell performance wise the difference.

Sebastian Roth

@george1421 said in Use an external database:

There must have been some kind of issue with the integrated database to move it external. I wonder if he can tell performance wise the difference.

With 8000 hosts checking into your FOG server I can definitely see database queries being a bottleneck. FOG is not optimized to run in such huge environments (yet). Not saying that it cannot be done but some of the things have not been coded with such amount of clients in mind.

@Fernando-Gietz Which version of FOG do you currently run? I have pushed a couple of changes to the dev-branch (late last year I think) that should help performance wise. Not sure if you are aware of that.

Wayne Workman

You should consider more than just a remote database, you need to scale. You should probably put a few FOG servers behind HAProxy as a load balancer (And have HAProxy do SSL offloading if you use https), and turn sticky on for HAProxy. Then, each of those FOG Servers should point to another HAProxy load balancer that distributes database queries across a Galara MariaDB cluster.

^ I know how to do all that, and can guide you.

A quick google search gives some tutorials:

HAProxy http load balancing:
https://thegeeksalive.com/how-to-setup-haproxy-http-load-balancer-on-centos/
https://www.digitalocean.com/community/tutorials/how-to-use-haproxy-to-set-up-http-load-balancing-on-an-ubuntu-vps

HAProxy Galara load balancing:
http://galeracluster.com/documentation-webpages/haproxy.html#using-haproxy
https://www.cloudandheat.com/blog/tutorial-simple-mariadb-galera-cluster-with-haproxy-load-balancing/

Fernando Gietz

@george1421 said in Use an external database:

@Fernando-Gietz said in Use an external database:

I can not say about the performance

I’m considering performance on a FOG system level.

<thinking out loud> There must have been a reason why Fernando made this change. He wouldn’t just make this change for fun?? There must have been some kind of issue with the integrated database to move it external. I wonder if he can tell performance wise the difference.

I only done a proof of concept, use an external database is a solution that is on the table. We wanted to answer the questioni if use an external database is possible. And the answer is YES, and without too much effort.

@Sebastian-Roth said in Use an external database:

@george1421 said in Use an external database:

There must have been some kind of issue with the integrated database to move it external. I wonder if he can tell performance wise the difference.

With 8000 hosts checking into your FOG server I can definitely see database queries being a bottleneck. FOG is not optimized to run in such huge environments (yet). Not saying that it cannot be done but some of the things have not been coded with such amount of clients in mind.

@Fernando-Gietz Which version of FOG do you currently run? I have pushed a couple of changes to the dev-branch (late last year I think) that should help performance wise. Not sure if you are aware of that.

The database is the bottleneck XD. In our case the server can not proccess all the SQL queries, the locks appear in the tables, these locks “lock” the apache server and noting works Yesterday the System guys moved the FOG server to a new VMWARE host and the MySQL performance has increased a lot of. But this is a temporal solution, as @Sebastian-Roth says FOG is not optimized for large environments. We have 8000 client in my server but I know an environment with 14000 client XD.

My FOG version is 1.5.5 for working branch, the last version.

@Wayne-Workman the university has a Galera Cluster, in case that we need use an external database, we wiil use an instance in this Galera environment.

Sebastian Roth

@Fernando-Gietz said in Use an external database:

My FOG version is 1.5.5 for working branch, the last version.

So you don’t have the fixes I pushed to dev-branch. We might consider merging your code changes form working to dev-branch soon. This way you can make use of all those changes.

@Tom-Elliott What do you think?

Use an external database

165

11.6k

17.1k

154.6k