I'm glad you found a solution (one that's already in the codebase, even).

Is the driver one that we should be recognizing as an Oracle driver? Should
we be writing a bug against the fact that we didn't recognize the driver?
Or are you using a driver that's custom or exotic in some way?

Tim

On Feb 2, 2018 12:45 PM, "Nathan Wray" <nw...@detroitsci.com> wrote:

> 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