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 >
