To wrap this up, this issue is known and has been fixed via the
OracleJDBCAdapter class.

The problem we're seeing is due to AMQ not recognizing our driver as an
Oracle driver, which is not unheard of.
We should be able to force the correct adapter with something like:


<persistenceAdapter>

<jdbcPersistenceAdapter adapter="oracleJDBCAdapter"
dataDirectory="${activemq.base}/activemq-data" dataSource="#oracle-ds"
lockKeepAlivePeriod="3000">




which will override limitQuery with an inner select and a ROWNUM clause:

@Override
    public String limitQuery(String query) {
        return "SELECT * FROM (" + query + ") WHERE ROWNUM <= " +
getMaxRows();
    }

Thank you again Gary and Tim for your help.

Nathan



On Fri, Feb 2, 2018 at 11:29 AM, Nathan Wray <nw...@detroitsci.com> wrote:

> Gary, great leads, thank you.
>
> I'm planning to verify the performance of replacing the stock query:
>
> SELECT
>     ID
>     ,MSG
> FROM
>     table
> WHERE
>     CONTAINER=?
>     AND ID < ?
>     AND ID > ?
>     AND XID IS NULL
> ORDER BY
>     ID
>
> With something oracle-specific that will limit the rows in the database,
> and only perform the inner query on ID.
> I'm assuming ID is the PK and is unique w/o container, I'll verify.
> (The inner query below should be less expensive as a constraint without
> the MSG column, it's required in order to not break the order by semantic.)
>
> SELECT
>     t1.ID
>     ,t1.MSG
> FROM
>     table t1
> WHERE
>     t1.ID IN (
>         SELECT t2.ID FROM table t2 WHERE t2.CONTAINER=? t2.ID < ? AND
> t2.ID > ? AND t2.XID IS NULL ORDER BY t2.ID
>     )
>     AND ROWNUM < maxRows
>
> BR
> Nathan
>
>
>
>
> On Fri, Feb 2, 2018 at 10:09 AM, Gary Tully <gary.tu...@gmail.com> wrote:
>
>> there was some work on limiting queries:
>> https://issues.apache.org/jira/browse/AMQ-6049
>>
>> also - the statements can be configured - so you can provide your own
>> statement via configuration.
>>
>> http://activemq.2283324.n4.nabble.com/Statements-in-Activemq
>> -Xml-Jdbcpersistence-adapter-td4668983.html
>>
>> On Fri, 2 Feb 2018 at 14:27 Nathan Wray <nw...@detroitsci.com> wrote:
>>
>> > Tim, thanks for writing.
>> >
>> > Does AMQ support DB-speciifc queries?  I could likely write the patch
>> > myself if pointed in the right direction.
>> >
>> > DefaultJDBCAdapter.doRecoverNextMessages looks like a method that
>> would be
>> > called at start-up, can you verify?
>> > Or, under what circumstances is that invoked?
>> >
>> > The default install had an index on ID; we added an index on Container
>> plus
>> > ID that changed the plan:
>> >
>> >
>> > *Temp Space*
>> >
>> > *Plan*
>> >
>> > *SELECT STATEMENT *ALL_ROWS Cost: 4
>> >
>> > *3 *
>> >
>> >
>> >
>> > *3 **FILTER *
>> >
>> > *2 *
>> >
>> >
>> >
>> >
>> >
>> > *2 **TABLE ACCESS BY INDEX ROWID TABLE *ACTIVEMQ.ACTIVEMQ_MSGS Cost: 4
>> > Bytes: 1,802 Cardinality: 1
>> >
>> > *1 *
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > *1 **INDEX RANGE SCAN INDEX (UNIQUE) *ACTIVEMQ.SYS_C0010280 Cost: 3
>> > Cardinality: 1
>> >
>> >
>> >
>> > *Temp Space*
>> >
>> > *Plan*
>> >
>> > *SELECT STATEMENT *ALL_ROWS Cost: 7
>> >
>> > *4 *
>> >
>> >
>> >
>> > *4 **SORT ORDER BY *Cost: 7 Bytes: 7,208 Cardinality: 4
>> >
>> > *3 *
>> >
>> >
>> >
>> >
>> >
>> > *3 **FILTER *
>> >
>> > *2 *
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > *2 **TABLE ACCESS BY INDEX ROWID BATCHED TABLE *ACTIVEMQ.ACTIVEMQ_MSGS
>> > Cost:
>> > 6 Bytes: 7,208 Cardinality: 4
>> >
>> > *1 *
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > *1 **INDEX RANGE SCAN INDEX *ACTIVEMQ.ACTIVEMQ_MSGS_CIDX Cost: 4
>> > Cardinality: 4
>> >
>> >
>> >
>> > Thank you
>> > Nathan
>> >
>> >
>> >
>> >
>> > On Fri, Feb 2, 2018 at 9:08 AM, Tim Bain <tb...@alumni.duke.edu> wrote:
>> >
>> > > I've never heard of this particular problem being reported, but it
>> should
>> > > be fairly straightforward to turn it into a top-N query by doing a
>> > > sub-select as described in
>> > > http://www.oracle.com/technetwork/issue-archive/
>> > > 2006/06-sep/o56asktom-086197.html.
>> > > Would you please submit an enhancement request in JIRA for this
>> change?
>> > >
>> > > BTW, have you had your DBA ensure that an optimal index is in place on
>> > the
>> > > table and that it's in good repair (Oracle indexes get cluttered with
>> > > deleted rows over time when your use pattern is frequent insertions
>> and
>> > > deletions, so semi-regular rebuilds may be necessary.)
>> > >
>> > > Tim
>> > >
>> > > On Feb 1, 2018 10:00 AM, "nathanwray" <nw...@detroitsci.com> wrote:
>> > >
>> > > > We recently had over 1M messages back up in a container.
>> > > >
>> > > > For reasons that aren't completely clear, AMQ executed the query
>> found
>> > in
>> > > > Statements.getFindNextMessagesStatement mid-morning:
>> > > >
>> > > > SELECT ID, MSG
>> > > >     FROM activemq.ACTIVEMQ_MSGS
>> > > >    WHERE CONTAINER = :1 AND ID > :2 AND ID < :3 AND XID IS NULL
>> > > > ORDER BY ID;
>> > > >
>> > > > With the parameters:
>> > > > 1              queue://generic_createContract
>> > > > 2              -1
>> > > > 3              183893253
>> > > >
>> > > > This call appears to originate from
>> > > > DefaultJDBCAdapter.doRecoverNextMessages, which calls setMaxRows
>> on the
>> > > > PreparedStatement with (apparently) 200 rows.
>> > > >
>> > > > However it appears the Oracle thin driver makes no attempt to limit
>> the
>> > > > result set based on the max rows value; instead it selects and sorts
>> > the
>> > > > entire 1M row plus values and makes them available to the client,
>> which
>> > > > stops creating objects after reading the first 200.
>> > > >
>> > > > The net result was that our Oracle server spiked to 90%+ on this one
>> > > query
>> > > > and caused a complete AMQ failure in production, knocking over a
>> number
>> > > of
>> > > > critical systems.
>> > > >
>> > > > We wound up stopping all of our brokers and manually dropping the
>> > > messages
>> > > > in this container in order to resolve the 3 hour outage.
>> > > >
>> > > > Is there a known issue with using Oracle persistence with AMQ that
>> > > > precludes
>> > > > having more than thousands of messages?  If the "max rows" approach
>> > with
>> > > > the
>> > > > thin driver works as it appears to, we can't be the first to have
>> seen
>> > > this
>> > > > problem.
>> > > >
>> > > > Any insight would be appreciated.
>> > > >
>> > > > Thank you
>> > > > Nathan
>> > > >
>> > > >
>> > > >
>> > > >
>> > > >
>> > > > --
>> > > > Sent from: http://activemq.2283324.n4.nabble.com/ActiveMQ-User-
>> > > > f2341805.html
>> > > >
>> > >
>> >
>>
>
>

Reply via email to