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
> > > >
> > >
> >
>
