Hey, need a little help here!
For some reason this query is running like mad, 2,583,772, in last two hours. Can anyone tell my why??
SELECT username FROM ofGroupUser WHERE administrator=? AND groupName=? ORDER BY username 2,583,772 372,979 0
Thanks!!!
Maybe one of the caches is too small. Do you have a high CPU usage on your database host or how did you identify this possible problem?
‘top’ is showing us massive spikes in cpu usage by java, closely followed by a mysql spike. So java will burst up to 50% of the cpu, and mysql will go to 25%. So we think its the connectivity between the two servers.
Yesterday this query “SELECT username FROM ofGroupUser WHERE administrator=? AND groupName=? ORDER BY username” was running at around 1.1 million times an hour. We got this from the query logs in Openfire.
Our users table in a separate database, the groups table is still in the standard Openfire tables.
I’m more than willing to pay for troubleshooting help if anyone is interested in a consulting job.
Hello,
Looking at the code, I only see this query run by: ./org/jivesoftware/openfire/group/DefaultGroupProvider.java , but it does not match what you have above. The code has:
private static final String LOAD_ADMINS =
“SELECT username FROM ofGroupUser WHERE administrator=1 AND groupName=? ORDER BY username”;
private static final String LOAD_MEMBERS =
“SELECT username FROM ofGroupUser WHERE administrator=0 AND groupName=? ORDER BY username”;
which version of openfire are you using? Which plugins do you have enabled?
daryl
We were on 3.7, we upgraded 3.7.1 with no changes.
We have not changed any openfire source.
Monitoring Service
Monitors conversations and statistics of the server.
1.2.0
Jive Software
Presence Service
Exposes presence information through HTTP.
1.5.0
Jive Software
Search
Provides support for Jabber Search (XEP-0055)
1.5.1
Ryan Graham
Hey Daryl,
Working on the same project as OP.
We were seeing the issue in 3.7.0 and still seeing the issues after updating to 3.7.1
Plungins:
Monitoring Service 1.2.0
Presence Service 1.5.0
Search 1.5.1
The query listed above is as it appears on the Database > Database Query Statistics. And is performed on single roster requests thousands of times.
Do you also see a lot of “SELECT description FROM ofGroup WHERE groupName=?” or “INSERT INTO ofGroup (groupName, description) VALUES (?, ?)” statements?
If it is called within the DefaultGroupProvider then the number of these SQLs should be 50% of the number of the “SELECT username …” statements.
Yes it looks like it is roughly 50%.
Here is alittle more detailed information…
At the moment we’ve been up for about an hour with a peak load of 45 users. The database statistics for the two queires metioned show:
SELECT username FROM ofGroupUser WHERE administrator=? AND groupName=? ORDER BY username
7,440
2,113
0
SELECT description FROM ofGroup WHERE groupName=?
3,916
754
0
As a reference we only have ~1400 created users. 323 groups with 1480 group assignments (ofGroupUsers)
We are using a seperate auth and user provider, but not group provider. Switching back to the default providers for auth and user made no difference.
Could it just be some kindof malformed roster request? I’ll turn on auditing and follow this post with an example requst and response…