ActiveMQ
  1. ActiveMQ
  2. AMQ-4628

Improve Performance of JDBC store with large number of messages

    Details

    • Type: Bug Bug
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: 5.8.0
    • Fix Version/s: 5.9.0
    • Component/s: None
    • Labels:
      None

      Description

      Currently JDBC store doesn't behave well when there's a huge backlog of messages (like 1 million or more). With some SQL improvements, we can make it work properly for this use case as well.

        Activity

        Hide
        Dejan Bosanac added a comment -

        That's it for now. We should just add those schema upgrade to the release notes

        Show
        Dejan Bosanac added a comment - That's it for now. We should just add those schema upgrade to the release notes
        Hide
        Claus Ibsen added a comment -

        Dejan, any more work to do? Or can we move this to 5.10 or should it be included in 5.9?

        Show
        Claus Ibsen added a comment - Dejan, any more work to do? Or can we move this to 5.10 or should it be included in 5.9?
        Hide
        Dejan Bosanac added a comment -

        One thing though is that this schema is not compatible with the previous version if you're using XA transactions. You'll need to:

        • Make sure you don't have an inflight transactions
        • Stop the broker
        • Alter the tables, by executing something like
        ALTER TABLE ACTIVEMQ_MSGS ALTER COLUMN XID VARCHAR(250)
        CREATE INDEX ACTIVEMQ_MSGS_XIDX ON ACTIVEMQ_MSGS (XID)
        ALTER TABLE ACTIVEMQ_ACKS ALTER COLUMN XID VARCHAR(250)
        CREATE INDEX ACTIVEMQ_ACKS_XIDX ON ACTIVEMQ_ACKS (XID)
        • Start the broker

        This isn't needed if XA transactions are not used.
        We need to put this into migration guide for 5.9

        Show
        Dejan Bosanac added a comment - One thing though is that this schema is not compatible with the previous version if you're using XA transactions. You'll need to: Make sure you don't have an inflight transactions Stop the broker Alter the tables, by executing something like ALTER TABLE ACTIVEMQ_MSGS ALTER COLUMN XID VARCHAR(250) CREATE INDEX ACTIVEMQ_MSGS_XIDX ON ACTIVEMQ_MSGS (XID) ALTER TABLE ACTIVEMQ_ACKS ALTER COLUMN XID VARCHAR(250) CREATE INDEX ACTIVEMQ_ACKS_XIDX ON ACTIVEMQ_ACKS (XID) Start the broker This isn't needed if XA transactions are not used. We need to put this into migration guide for 5.9
        Hide
        Dejan Bosanac added a comment -

        svn commit 1502206 contains the improvement related to XID column. The column was blob and not indexed, and most of the queries for non-xa case uses "AND XID IS NULL" in the queries, which kills the performance on large tables.

        The change was introduced to treat XID as String, and put it into indexed varchar column.

        In my tests it made the broker work properly with close to 2 million messages.

        The testing scenario:

        • Download the latest snapshot with the fix (or build locally)
        • I introduced a bit optimized configuration that turns off every non-essential feature, like expiry processing, database cleanup and priority messages. So start a broker with
        bin/activemq console xbean:conf/activemq-jdbc-performance.xml
        • Send large number of messages with
        ant producer -Durl=tcp://localhost:61616 -DparallelThreads=10 -Dtransacted=true -Dsubject=BenchmarkQueue -Dmax=200000 -Ddurable=true -Dbatch=1000

        Note that I added batch to work with producer tool, so we can send faster

        • Restart the broker to check broker startup time
        • Consumer messages with something like
        ant consumer -Durl=tcp://localhost:61616 -DparallelThreads=10 -Dtransacted=true -Dsubject=BenchmarkQueue -Dmax=80000

        I tested with local MySQL and it seems to work fine. It'd be good if people could test it with other RDBMS they use (Oracle, Postgres, etc.), to check if there's any other bottlenecks.

        Show
        Dejan Bosanac added a comment - svn commit 1502206 contains the improvement related to XID column. The column was blob and not indexed, and most of the queries for non-xa case uses "AND XID IS NULL" in the queries, which kills the performance on large tables. The change was introduced to treat XID as String, and put it into indexed varchar column. In my tests it made the broker work properly with close to 2 million messages. The testing scenario: Download the latest snapshot with the fix (or build locally) I introduced a bit optimized configuration that turns off every non-essential feature, like expiry processing, database cleanup and priority messages. So start a broker with bin/activemq console xbean:conf/activemq-jdbc-performance.xml Send large number of messages with ant producer -Durl=tcp: //localhost:61616 -DparallelThreads=10 -Dtransacted= true -Dsubject=BenchmarkQueue -Dmax=200000 -Ddurable= true -Dbatch=1000 Note that I added batch to work with producer tool, so we can send faster Restart the broker to check broker startup time Consumer messages with something like ant consumer -Durl=tcp: //localhost:61616 -DparallelThreads=10 -Dtransacted= true -Dsubject=BenchmarkQueue -Dmax=80000 I tested with local MySQL and it seems to work fine. It'd be good if people could test it with other RDBMS they use (Oracle, Postgres, etc.), to check if there's any other bottlenecks.

          People

          • Assignee:
            Dejan Bosanac
            Reporter:
            Dejan Bosanac
          • Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development