activemq-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Nathan Wray <nw...@detroitsci.com>
Subject Re: Performance issue with Oracle backed AMQ
Date Fri, 02 Feb 2018 14:27:28 GMT
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 <tbain@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" <nwray@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
> >
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message