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