Thanks speedy.
I set ldap options on my server:
ldap.connectionPoolEnabled & ldap.pagedResultsSize
I think you are right. My DB may have a locking issue on reads.
I tried to update my.cnf as below:
#Optimize for Openfire
skip-locking
skip-name-resolve
max_connections=500
back_log=500
#wait_timeout=1800
max_allowed_packet=4M
sort_buffer_size=32M
read_buffer_size=4M
read_rnd_buffer_size=16M
myisam_sort_buffer_size=64M
#thread_cache_size=128
#query_cache_size=32M
thread_concurrency=16
max_heap_table_size=64M
join_buffer_size=16M
key_buffer_size=512M
innodb_additional_mem_pool_size=4M
tmp_table_size=64M
Here is Database Query Statistics:
SELECT Query Statistics
Total # of operations
1,477,237
Total time for all operations (ms)
873,800
Average time for each operation (ms)
0.59
Operations per second
9.14
Most common SQL queries
Query
Count
Total Time
Avg. Time
SELECT name, list FROM ofPrivacyList WHERE username=? AND isDefault=?
595,322
340,765
0
SELECT bytes from ofRRDs where id = ?
279,820
125,853
0
SELECT groupName from ofGroupProp where name=? AND propValue LIKE ?
94,295
64,986
0
SELECT name, propValue FROM ofUserProp WHERE username=?
71,826
24,379
0
SELECT DISTINCT serviceID FROM ofPubsubNode WHERE serviceID=?
64,014
20,774
0
SELECT bookmarkType, bookmarkName, bookmarkValue, isGlobal FROM ofBookmark WHERE bookmarkID=?
62,595
21,490
0
SELECT bookmarkType, name FROM ofBookmarkPerm WHERE bookmarkID=?
62,595
21,437
0
SELECT name, propValue FROM ofBookmarkProp WHERE bookmarkID=?
42,809
15,848
0
SELECT privateData FROM ofPrivate WHERE username=? AND name=? AND namespace=?
39,818
20,548
0
SELECT groupName from ofGroupProp WHERE name=? AND propValue=?
24,681
17,711
0
SELECT jid, rosterID, sub, ask, recv, nick FROM ofRoster WHERE username=?
24,597
14,535
0
SELECT name, propValue FROM ofGroupProp WHERE groupName=?
19,268
8,551
0
SELECT isExternal FROM ofConversation WHERE conversationID=?
15,483
3,698
0
SELECT id FROM ofID WHERE idType=?
15,247
5,702
0
SELECT room, isExternal, startDate, lastActivity, messageCount FROM ofConversation WHERE conversationID=?
8,263
2,964
0
SELECT bareJID, jidResource, nickname, joinedDate, leftDate FROM ofConParticipant WHERE conversationID=? ORDER BY joinedDate
8,263
44,724
5
SELECT bookmarkID from ofBookmark
6,955
2,472
0
SELECT ofRosterGroups.rosterID, groupName FROM ofRosterGroups INNER JOIN ofRoster ON ofRosterGroups.rosterID = ofRoster.rosterID WHERE username=? ORDER BY ofRosterGroups.rosterID, rank
5,843
5,587
0
SELECT sessionID, transcript, startTime FROM fpSession WHERE workgroupID=? AND startTime>? AND transcript IS NOT NULL ORDER BY startTime
5,386
2,491
0
SELECT name, startTime, endTime FROM ofUserFlag WHERE username=? AND name=?
4,586
1,964
0
INSERT Query Statistics
Total # of operations
52,118
Total time for all operations (ms)
32,852
Average time for each operation (ms)
0.63
Operations per second
0.32
Most common SQL queries
Query
Count
Total Time
Avg. Time
INSERT INTO ofConParticipant(conversationID, joinedDate, bareJID, jidResource, nickname) VALUES (?, ?, ?, ?, ?)
28,929
16,160
0
INSERT INTO ofConversation(conversationID, room, isExternal, startDate, lastActivity, messageCount) VALUES (?, ?, ?, ?, ?, 0)
14,466
5,399
0
INSERT INTO ofOffline (username, messageID, creationDate, messageSize, stanza) VALUES (?, ?, ?, ?, ?)
3,893
1,570
0
INSERT INTO ofMessageArchive(messageID, conversationID, fromJID, fromJIDResource, toJID, toJIDResource, sentDate, body, stanza) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
2,762
8,818
3
INSERT INTO ofPresence(username, offlinePresence, offlineDate) VALUES(?, ?, ?)
2,050
899
0
INSERT INTO ofRosterGroups (rosterID, rank, groupName) VALUES (?, ?, ?)
11
4
0
INSERT INTO ofRoster (username, rosterID, jid, sub, ask, recv, nick) VALUES (?, ?, ?, ?, ?, ?, ?)
5
2
0
INSERT INTO ofPrivacyList (username, name, isDefault, list) VALUES (?, ?, ?, ?)
1
0
0
INSERT INTO ofSecurityAuditLog(msgID, username, entryStamp, summary, node, details) VALUES(?, ?, ?, ?, ?, ?)
1
0
0
UPDATE Query Statistics
Total # of operations
47,527
Total time for all operations (ms)
55,480
Average time for each operation (ms)
1.17
Operations per second
0.29
Most common SQL queries
Query
Count
Total Time
Avg. Time
UPDATE ofRRDs SET bytes = ?, updatedDate=? WHERE id = ?
26,930
25,319
0
UPDATE ofID SET id=? WHERE idType=? AND id=?
15,247
4,566
0
UPDATE ofConversation SET lastActivity=?, messageCount=? WHERE conversationID=?
2,758
7,554
2
UPDATE ofConParticipant SET leftDate=? WHERE conversationID=? AND bareJID=? AND jidResource=? AND joinedDate=?
1,819
17,741
9
UPDATE ofBookmarkProp SET propValue=? WHERE name=? AND bookmarkID=?
716
265
0
UPDATE ofPrivacyList SET isDefault=?, list=? WHERE username=? AND name=?
52
32
0
UPDATE ofRoster SET sub=?, ask=?, recv=?, nick=? WHERE rosterID=?
4
3
0
UPDATE ofProperty SET propValue=? WHERE name=?
1
0
0
DELETE Query Statistics
Total # of operations
72,325
Total time for all operations (ms)
64,238
Average time for each operation (ms)
0.89
Operations per second
0.45
Most common SQL queries
Query
Count
Total Time
Avg. Time
DELETE FROM ofPresence WHERE username=?
44,858
20,839
0
DELETE FROM ofMessageArchive WHERE conversationID=?
8,263
32,434
3
DELETE FROM ofConParticipant WHERE conversationID=?
8,263
7,154
0
DELETE FROM ofConversation WHERE conversationID=?
8,263
2,589
0
DELETE FROM ofGojaraStatistics WHERE messageDate < ?
1,346
596
0
DELETE FROM ofOffline WHERE username=?
789
613
0
DELETE ofPubsubItem FROM ofPubsubItem LEFT JOIN (SELECT id FROM ofPubsubItem WHERE serviceID=? AND nodeID=? ORDER BY creationDate DESC LIMIT ?) AS noDelete ON ofPubsubItem.id = noDelete.id WHERE noDelete.id IS NULL AND ofPubsubItem.serviceID = ? AND nodeID = ?
539
12
0
DELETE FROM ofRosterGroups WHERE rosterID=?
4
1
0
I will monitor again next monday morning…
And my Openfire was installed on HP DL380 Gen8 Server, not running vmware.