As far as I understand OFID is used to hold PKs for other items managed by openfire. Part of our performance analysis of the table reveals the following…
SQL statements were found waiting for row lock waits.
Significant row contention was detected in the TABLE “ID.OFID” with object ID 80591.
The SQL statement with SQL_ID “88x6pd19w13jr” was blocked on row locks.
SQL TextUPDATE ofID SET id=:1 WHERE idType=:2 AND id=:3
Session with ID “54”, User ID “85”, Program “JDBC Thin Client” and Module “JDBC Thin Client” was the blocking session responsible for 100% of this recommendation’s benefit.
I would actually like more information about the reasoning/purpose behind this table. It would be nice to have support for specific db id measures. I know mysql uses auto increment and in Oracle’s case it uses “Sequences”. I have a few questions in regards to this.
Is there any reasoning behind using this table structure that I’m missing other than generalization for supporting multiple DB packages?
How hard would it be to implement reliance on the specific measures as opposed to relying on this general structure?
Could this be added to the project timeline for a future openfire release?
In Oracle’s specific case it would just require installing a set of Sequences similar to…
CREATE SEQUENCE “OFROSTER_SEQ” MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE ;
Then the insert SQL would just simply be INSERT INTO OFROSTER (ROSTERID) VALUES (OFROSTER_SEQ.nextval);
I’ve been thinking of making a custom build to address this but I wanted to post my thoughts before going through the trouble. Any thoughts or comments would be appreciated.
Then the insert SQL would just simply be INSERT INTO OFROSTER (ROSTERID) VALUES (OFROSTER_SEQ.nextval);
Thanks
Thanks Dustin for your email. I suspect that the community could use more Oracle power users piping up with suggestions on performance issues they see.
While I agree your solution would be elegant, it would/may not be portable to other databases. I suspect you would have to write a custom provider to do this.
Also, I believe there has been some Jira tickets on the performance issues with ofID, but I don’t seem to find them now
Thanks for the quick response. I was more so just curious as to the state of thought surrounding this particular aspect of Openfire. If no one had brought it up I thought it’d be an interesting topic. I know most users probably rely on mysql so any changes specifically for Oracle would only benefit a few and wouldn’t be a priority to the OF team.
I’d be willing to contribute to finding a good performance solution for all the supported DBs but currently I don’t have the time available to do so. If you or anyone on the project has any questions about Oracle in the future feel free to direct them to me.
did you look at the openfire DDL script? It does not use foreign keys, also there one could optimize Openfire a lot. As Openfire should support 1000 different databases the developers did decide a long time ago to not use “foreign keys” or “sequences”. Anyhow “primary keys” are used.
The database design did evolve, so today one would design the database completely different and likely use “foreign keys” and “sequences”. This would require also some code changes, so I guess that we have to live with the database as it is for some time.
Sorry for the late response. Yes I looked at the DDL and I agree FK would be a big performance boost. I completely understand the reasoning behind wanting to support multiple database software packages instead of specializing for a few.
In the end if it poses enough of a performance hit my team will probably revamp the database code to work more specifically with Oracle. I’m not complaining in the least as Openfire is a great jabber server and those that work on it deserve kudos.
There are some interesting topics that could be brought up in terms of performance and integration in regards to openfire’s database structure. Like selection of a native versus managed key system, what types of keys are maintained ie (hash, numerical, sequential, combination), use of indexing services, considerations for grid/distributed based systems, ect.