You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@activemq.apache.org by nathanwray <nw...@detroitsci.com> on 2018/02/01 17:00:16 UTC
Performance issue with Oracle backed AMQ
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
Re: Performance issue with Oracle backed AMQ
Posted by Nathan Wray <nw...@detroitsci.com>.
I'm going to look into the driver detection Monday and see if I can
determine why the driver isn't recognized, if I can articulate the problem
I'm create a bug.
We're using the stock ojdbc7.jar Oracle thin driver so I'm not aware of any
reason why detection should not work.
I found the resource folder with the driver-named files that refer to the
correct resource adapter class but I haven't found where the correct
filename is generated from the driver class. I'm assuming it's reflection
somewhere.
On Fri, Feb 2, 2018 at 9:15 PM, Tim Bain <tb...@gmail.com> wrote:
> 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 <ga...@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
> > >> > > >
> > >> > >
> > >> >
> > >>
> > >
> > >
> >
>
Re: Performance issue with Oracle backed AMQ
Posted by Tim Bain <tb...@gmail.com>.
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 <ga...@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
> >> > > >
> >> > >
> >> >
> >>
> >
> >
>
Re: Performance issue with Oracle backed AMQ
Posted by Nathan Wray <nw...@detroitsci.com>.
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 <ga...@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
>> > > >
>> > >
>> >
>>
>
>
Re: Performance issue with Oracle backed AMQ
Posted by Nathan Wray <nw...@detroitsci.com>.
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 <ga...@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
> > > >
> > >
> >
>
Re: Performance issue with Oracle backed AMQ
Posted by Gary Tully <ga...@gmail.com>.
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
> > >
> >
>
Re: Performance issue with Oracle backed AMQ
Posted by Nathan Wray <nw...@detroitsci.com>.
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
> >
>
Re: Performance issue with Oracle backed AMQ
Posted by Tim Bain <tb...@alumni.duke.edu>.
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
>