Openfire DB Load Balance with Galera MySQL Replication Cluster?

Openfire Openfire Dev
1

Hi,

We are experiencing a high load on the Openfire Server (3.7.1) and we don’t even have that many users yet. I can see from our Openfire Panel that we in average have 300 sessions, mostly with Smack resource (Android) but also from our iOS application and some from php scripts using xmpphp. There is a high load on that data node which seems to slow down greatly Openfire, many db connections drops and timeouts from xmpphp and in Smack server not responding or taking too long time authenticate 30+ seconds.

I am trying to figure out where optimization can be done. Several cache increase for users, rosters, vcards etc. have helped previously and also Openfire RAM increase.

At some point we will try also out the 3.8 upgrade with the Cluster Plugin

There are 3 datanodes in the DB Cluster and currently Openfire is connected to one of them.

With Galera it is possible to load balance on the data nodes using this connection string:

jdbc:mysql:loadbalance://10.1.0.1:3306,10.1.0.2:3306,10.1.0.3:3306/databasename? loadBalanceBlacklistTimeout=5000

I guess we just have to try it out, but I was wondering if anyone have tried something similar or have had any experience or success with this approach?

2

Same applies with Openfire version 3.10.2

We have a MySQL connection string:

jdbc:mysql:loadbalance://server1:3306,server2:3306/openfire?rewriteBa tchedStatements=true&loadBalanceBlacklistTimeout=5000

We get the following errors within openfire’s log:

2015.09.16 14:23:14 org.jivesoftware.util.XMLProperties - Error reading XML properties

org.dom4j.DocumentException: Error on line 41 of document : The reference to entity “loadBalanceBlacklistTimeout” must end with the ‘;’ delimiter. Nested exception: The reference to entity “loadBalanceBlacklistTimeout” must end with the ‘;’ delimiter.

org.xml.sax.SAXParseException; lineNumber: 41; columnNumber: 156; The reference to entity “loadBalanceBlacklistTimeout” must end with the ‘;’ delimiter.

If we add the ; in the connection string, namely:

jdbc:mysql:loadbalance://server1:3306,server2:3306/openfire?rewriteBa tchedStatements=true&loadBalanceBlacklistTimeout=5000;

We are faced with the same error.

Only if we omit the loadBalanceBlacklistTimeout=5000 whatsoever we can load openfire.

3

I found some nice things for getting a stable system from this thread Re: How many connections can Openfire handle?

I lowered the cache and memory settings since I didn’t have a need for that many users.

I also switched over to latest Oracle 64-bit Java 8 release since I had originally installed from the RPM which was just 32-bit Java.

Combined with adding on a connection manager my system has been stable as ever. Usually have about 475-500 users connected during most of the day. Previous to those changes it would use up quite a bit of resources and after a few days become unstable.

Not using any clustering (Openfire or MySQL) at the moment.