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