Using primary keys instead of getSequence from ofID table

Openfire Openfire Support
1

Hi there,

Later today, I am venturing into hacking all over the codebase to shift away from ofID and into using MySQL primary keys for its DefaultConnectionProvider. It’s pretty straightforward to do, a lot of grunt work and hopefully gain scale after that.

I was caught by surprise when reviewing the codebase and saw no implementation of auto increment usage (a.k.a Oracle sequence via nextval)! The codebase simply bangs real hard on the ofID table for 5 attempts and quits after that if it never manages to gain atomicity in getting the next ID. I’d imagine this would be the first thing to do to increase concurrency and reduce race conditions (DB row locks)

I was wondering whether someone else have done something simliar and saw any significant gains after that. I’d imagine it will.

There’s a similar thread on this nearly 5 years ago (!!!) and it was never answered. http://community.igniterealtime.org/message/183135

ps: Currently hacking on adding a DefaultSlaveConnectionProvider . . . guess what that’ll give us?

Jack

2

Howdy,

Openfire’s database usage is very old and has to use the lowest-common demoninator approach to support all these various databases from one code base. Some features exist in mysql that don’t in hsql, etc.

daryl

3

No idea on what would be involved… but perhaps a migration to something newerish (and imho better) such as H2 Database would provide some benefit? I think out of the box, it supports things such as foriegn keys and constraints, etc. H2 is also written in pure java goodness I believe it’s the original creator of HSQL but he moved on when the project shifted away from where he wanted to go. H2 also supports an in-memory database which would provide huge performance improvements, but would need a mechanism to make it presistant (such as hourly sync to disk or something).

http://www.h2database.com/html/main.html

4

The Openfire history is quite long, and I believe that some databases did simply not support sequences when Jive did develop Openfire. Another problem is that sequences / UUIDs / auto-increments are implemented in different ways, depending on the vendor.

I agree that relying on auto-increment and database provided UUIDs is the better way to handle these things. HSQLDB supports auto increments.

Openfire uses direct SQL and no database abstraction layer. One may want to use a DAL. So it will be easier for the developer to test new code.

And users will likely need a migration tool to get their data migrated.

Back into 2006: Database Schema Guide does not match database schema

foreign keys, database table creation

5

Thanks for providing the backdrop on this. Appreciate it.

Does anyone out there know of an implementation out there that made this change to see a significant improvement?

6

Took the easy and yet extensible way of doing things by creating a set of “sequence-like” tables with foo values. And then modifying the generatedKey functionality to essentially get the last_insert_id() from MySQL

Sample of a MySQL table additions ``

# OFFLINE CREATE TABLE ofSeqType19 (

  `id` bigint(20) UNSIGNED AUTO_INCREMENT,

  `foo` tinyint(1), PRIMARY KEY (`id`)

);

Sample code changes to java.org.jivesoftware.database.SequenceManager.java

``

``

private static final String CREATE_SEQ_ID =
    "INSERT INTO ofSeqType? (foo) VALUES (1)";           :
          :
          :
    // Drop this into a new function to replace getNextBlock
    con = DbConnectionManager.getConnection();
    // Get the current ID from the database.
    pstmt = con.prepareStatement(CREATE_SEQ_ID.replace("?", Integer.toString(type)));
    // pstmt.setInt(1, type); // Doesn't work for table name manipulation
    int result = pstmt.executeUpdate();     if (result > 0) {
        ResultSet key = pstmt.getGeneratedKeys();
        if (key.next()) {
            this.maxID = key.getLong(1);
            this.currentID = maxID - 1; // Does not respect the blocksize
            success = true;
        }
    }