Embedded dbase, how many users?

Openfire Openfire Support
1

If anyone is running an embedded database, how many users are you using and not seeing any issues? I’'ve got 100 on mine running just fine, but I am going to throw about 1000 on it real soon and need to know if I will have to go to MS sql or Mysql. Thanks!

2

Hi pmac2,

Are you talking about 1000 registered users or 1000 concurrent users? Big difference.

The embedded database (url=http://hsqldb.org/ HSQLDB[/url]) is actually very powerful and under the url=http://jamie.ideasasylum.com/notebook/index.php?id=4right circumstances[/url] can outperform other databases. There are however at least two big advantages of not using the embedded database:

  1. You’'re freeing up memory and processing power on your server to be used by Wildfire.

  2. Most organizations already have a dedicated server for, MySQL, Oracle, Postgres, etc., in place for other applications and as a result that server is carefully maintained, i.e. regular backups, spare harddrives/NICs, etc. So, if you use a server that is already in place you can “piggyback” on that existing infrastructure.

Hope that helps,

Ryan

3

Thanks Ryan.

I will have about 1000 registered users and about 700 on at any one time. The reason I went with the emebedded because even in my small 100 user test, the MS SQL DB was performing poorly. The server is pretty robust with dual 3.6Ghz and 2GB of RAM so when I went to the embedded and my test group of 100, I wasn’‘t seeing any issues at all. I just don’'t want to get blown out of the water when I start adding hundreds of users.

Thanks for the info!

PM.

4

Hi PM,

be carefully using the internal database. Afaik the connection pool is limited to 10 concurrent connections and this is hard coded so you need to recompile to tune this. The connection timeout is set to 3 hours.

I ran some load tests with 50 concurrent clients using Smack and this usually locks the database (no deadlock), a restart of the whole server is necessary, maybe waiting three hours would have solved this problem.

The lock occurs in ConnectionPool.java in synchronized (waitLock) { … waitLock.wait(); … }.

LG

5

it2000,

I’'d be pretty interested in finding out exactly what deadlocks you were seeing. If you still have the full stack traces, can you past them in?

-Matt

6

Also, as another follow-up – I updated the max number of connections the embedded db can use to 25. That should help it to scale a bit better.

-Matt

7

Hi Matt,

there’'s a new one with 100 Users, this time the steps createAccounts(), loginAccounts() and sendMessages() run fine, but deleteAccounts() did fail for all 100 users.

“netstat -an | grep 5223” shows 100 lines like this (always another ClientPort):

TCP MyServerIP:5223 MyClientIP:1528 CLOSE_WAIT

LG

100 times:

“Client SR - 15834478” daemon prio=6 tid=0x02e60ef8 nid=0x18c8 in Object.wait()

at java.lang.Object.wait(Native Method)

  • waiting on <0x231d5668> (a java.lang.Object)
    at java.lang.Object.wait(Unknown Source)
    at org.jivesoftware.database.ConnectionPool.getConnection(ConnectionPool.java:162)
  • locked <0x231d5668> (a java.lang.Object)

at org.jivesoftware.database.EmbeddedConnectionProvider.getConnection(EmbeddedConn ectionProvider.java:54)

at org.jivesoftware.database.DbConnectionManager.getConnection(DbConnectionManager .java:104)

at org.jivesoftware.database.DbConnectionManager.getTransactionConnection(DbConnec tionManager.java:125)

at org.jivesoftware.wildfire.user.DefaultUserProvider.deleteUser(DefaultUserProvid er.java:149)

at org.jivesoftware.wildfire.user.UserManager.deleteUser(UserManager.java:137)

at org.jivesoftware.wildfire.handler.IQRegisterHandler.handleIQ(IQRegisterHandler. java:221)

at org.jivesoftware.wildfire.handler.IQHandler.process(IQHandler.java:48)

at org.jivesoftware.wildfire.IQRouter.handle(IQRouter.java:242)

at org.jivesoftware.wildfire.IQRouter.route(IQRouter.java:78)

at org.jivesoftware.wildfire.PacketRouter.route(PacketRouter.java:65)

at org.jivesoftware.wildfire.net.SocketReader.processIQ(SocketReader.java:390)

at org.jivesoftware.wildfire.net.ClientSocketReader.processIQ(ClientSocketReader.j ava:50)

at org.jivesoftware.wildfire.net.SocketReader.readStream(SocketReader.java:255)

at org.jivesoftware.wildfire.net.SocketReader.run(SocketReader.java:118)

at java.lang.Thread.run(Unknown Source)

“Client SR - 19780920” daemon prio=6 tid=0x02e5f8b8 nid=0x18b0 in Object.wait()

at java.lang.Object.wait(Native Method)

  • waiting on <0x231d5668> (a java.lang.Object)
    at java.lang.Object.wait(Unknown Source)
    at org.jivesoftware.database.ConnectionPool.getConnection(ConnectionPool.java:162)
  • locked <0x231d5668> (a java.lang.Object)

at org.ji…

/code

8

Hey guys,

Thanks for reporting this problem and LG for pointing out the problematic areas in the code. Today I checked in a fix for JM-566 that should fix the problem you were observing. The fix will be available in the next nightly build.

Keep in mind that the nightly build version includes a database modification since privacy lists are now supported in Wildfire. That means that if you are going to use the nightly build version you should not only use the new wildfire.jar file but also you will need to copy the resources\database\upgrade folders. Those folders contain the upgrade scripts that are going to be automatically run by the server to update the database structure. Since the db is going to be altered by Wildfire I would recommend doing a backup before running Wildfire 2.5.0 (nightly build version).

Regards,

– Gato

9

Wildfire 2.5 with update of database structure. Why do I feel the chill?

wmhtet

10

Hey wmhtet,

This upgrade should be really easy. The only db modification consist in creating a new table. No existing table is modified.

Hope that helps.

Thanks,

– Gato