Poor performance when adding messages to the database on large databases.
Closed this issue · 5 comments
The plugin uses the result of org.jivesoftware.openfire.archive.ConversationManager.getArchivedMessageCount() to determine the next ID for a new row. This methods implementation is based on this database query: SELECT COUNT(*) FROM ofMessageArchive.
On Ignite (which has a very large table), this query takes 71 seconds.
"archive-service-worker-4" #56 prio=5 running in native
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.socketRead(SocketInputStream.java:116)
at java.net.SocketInputStream.read(SocketInputStream.java:171)
at java.net.SocketInputStream.read(SocketInputStream.java:141)
at sun.security.ssl.InputRecord.readFully(InputRecord.java:465)
at sun.security.ssl.InputRecord.read(InputRecord.java:503)
at sun.security.ssl.SSLSocketImpl.readRecord(SSLSocketImpl.java:975)
- locked java.lang.Object@50ec9fa3
at sun.security.ssl.SSLSocketImpl.readDataRecord(SSLSocketImpl.java:933)
at sun.security.ssl.AppInputStream.read(AppInputStream.java:105)
- locked sun.security.ssl.AppInputStream@7532abe8
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:140)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:109)
at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:67)
at org.postgresql.core.PGStream.receiveChar(PGStream.java:306)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1952)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:308)
- locked org.postgresql.core.v3.QueryExecutorImpl@66451861
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:143)
at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:106)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.apache.commons.dbcp2.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:122)
at org.jivesoftware.database.ProfiledConnection$TimedPreparedStatement.executeQuery(ProfiledConnection.java:780)
at org.jivesoftware.openfire.archive.ConversationManager.getArchivedMessageCount(ConversationManager.java:594)
at org.jivesoftware.openfire.archive.ConversationManager$MessageArchivingRunnable.store(ConversationManager.java:1165)
at org.jivesoftware.openfire.archive.Archiver.run(Archiver.java:141)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
What's the underlying DBMS for Ignite?
Postgres - but count(*) has issues like this on various others.
I don't think that doing count(*) is a good way to get an auto-increment. We should use a sequence of sorts. Openfire has code for that.
To gauge the impact of this issue: on Ignite, it takes more than two minutes for one message to be added to the database (as calculating the next value for message ID takes so long, because of this issue).
Please fix Issue number in changelog:
openfire-monitoring-plugin/changelog.html
Line 57 in 0fa6fd6