activemq-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gary Tully <gary.tu...@gmail.com>
Subject Re: Performance issue with Oracle backed AMQ
Date Fri, 02 Feb 2018 15:09:38 GMT
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 <nwray@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 <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