From users-return-49300-archive-asf-public=cust-asf.ponee.io@activemq.apache.org Sat Feb 3 16:46:07 2018 Return-Path: X-Original-To: archive-asf-public@eu.ponee.io Delivered-To: archive-asf-public@eu.ponee.io Received: from cust-asf.ponee.io (cust-asf.ponee.io [163.172.22.183]) by mx-eu-01.ponee.io (Postfix) with ESMTP id C8C09180621 for ; Sat, 3 Feb 2018 16:46:07 +0100 (CET) Received: by cust-asf.ponee.io (Postfix) id B877C160C3B; Sat, 3 Feb 2018 15:46:07 +0000 (UTC) Delivered-To: archive-asf-public@cust-asf.ponee.io Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by cust-asf.ponee.io (Postfix) with SMTP id B577E160C38 for ; Sat, 3 Feb 2018 16:46:06 +0100 (CET) Received: (qmail 34753 invoked by uid 500); 3 Feb 2018 15:46:05 -0000 Mailing-List: contact users-help@activemq.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: users@activemq.apache.org Delivered-To: mailing list users@activemq.apache.org Received: (qmail 34738 invoked by uid 99); 3 Feb 2018 15:46:04 -0000 Received: from pnap-us-west-generic-nat.apache.org (HELO spamd3-us-west.apache.org) (209.188.14.142) by apache.org (qpsmtpd/0.29) with ESMTP; Sat, 03 Feb 2018 15:46:04 +0000 Received: from localhost (localhost [127.0.0.1]) by spamd3-us-west.apache.org (ASF Mail Server at spamd3-us-west.apache.org) with ESMTP id 553BE185CDF for ; Sat, 3 Feb 2018 15:46:04 +0000 (UTC) X-Virus-Scanned: Debian amavisd-new at spamd3-us-west.apache.org X-Spam-Flag: NO X-Spam-Score: 5.191 X-Spam-Level: ***** X-Spam-Status: No, score=5.191 tagged_above=-999 required=6.31 tests=[DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, HTML_MESSAGE=2, RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001, URI_HEX=1.313, URI_TRY_3LD=1.999] autolearn=disabled Authentication-Results: spamd3-us-west.apache.org (amavisd-new); dkim=pass (1024-bit key) header.d=detroitsci.com Received: from mx1-lw-eu.apache.org ([10.40.0.8]) by localhost (spamd3-us-west.apache.org [10.40.0.10]) (amavisd-new, port 10024) with ESMTP id jKXVZi0PKPP2 for ; Sat, 3 Feb 2018 15:46:00 +0000 (UTC) Received: from mail-pg0-f41.google.com (mail-pg0-f41.google.com [74.125.83.41]) by mx1-lw-eu.apache.org (ASF Mail Server at mx1-lw-eu.apache.org) with ESMTPS id E782C5F397 for ; Sat, 3 Feb 2018 15:45:59 +0000 (UTC) Received: by mail-pg0-f41.google.com with SMTP id m136so15496453pga.12 for ; Sat, 03 Feb 2018 07:45:59 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=detroitsci.com; s=google; h=mime-version:in-reply-to:references:from:date:message-id:subject:to; bh=h/IgWS2Eda1JAUy7StECuNa/o8BDPxzUmnivVaHuXL4=; b=Qr7rJ5NR01F8Rgq54Wjs8HvNes6+czl3h73Aeu3O+d8QT6+7J47arZM6lP1OmzCUbX G3u2AKYFw2lvGhz9cfLf/UtXTUcdBE3fZOvH6EJFNst2fKg9WDkJUPchvnHqnApTotn3 pKA0ftHxVMGKBAK0tfTtcsTVLIYsKiaqG2JzY= X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to; bh=h/IgWS2Eda1JAUy7StECuNa/o8BDPxzUmnivVaHuXL4=; b=RE7fiUoqpUvanNgRj16fXsb8OSFT+9dM6p6K40mqIgNU03liV/YGsieV9azgc/NbjH zseI8kQW4YQG9tuohVuIsbNWOpPFtSPoGgEc1CXQYEWACoe/6sXNxqwEP4N0Pq2vNq/q 6FOCg1IJTLG2XgDXKstXS1fqOsxfkTrVXjXNHGicgmQCeOagfam7JadcGg9v/mD3jVRh SN6fxLUPieh8m3wPSnmgu9dsxCqvW3RCdwNuGUkeq4UBmmDUliLtNmSEusrqCPH3ssOH H++GY++z/8IyIOkda/xGGTtHG1HoEjYujYTHTo/4SHf5drYtVXAle0Iw2KsrU4e2JQhk jAYA== X-Gm-Message-State: AKwxytelkgRHuS/YnB++ShgnP1C6R8FYCKESGm75TLOZN3JVOX7Z4Eh2 oHwx1GgEmgC1e+uM+672kfF2W9VORXIwwgxNgLVQMw== X-Google-Smtp-Source: AH8x227Z2+SuuG+EJ1E3DIf6PAsdk7de1jj0gbR9zYUP0KQc/EUoVQlYK2SsDY4GIEoG9RCj8Q5w/sYs7ufHapg6GC8= X-Received: by 10.101.82.130 with SMTP id y2mr7317264pgp.68.1517672750994; Sat, 03 Feb 2018 07:45:50 -0800 (PST) MIME-Version: 1.0 Received: by 10.100.218.130 with HTTP; Sat, 3 Feb 2018 07:45:49 -0800 (PST) In-Reply-To: References: <1517504416282-0.post@n4.nabble.com> From: Nathan Wray Date: Sat, 3 Feb 2018 10:45:49 -0500 Message-ID: Subject: Re: Performance issue with Oracle backed AMQ To: users@activemq.apache.org, tbain@alumni.duke.edu Content-Type: multipart/alternative; boundary="089e0820373c3571e2056450b9f5" --089e0820373c3571e2056450b9f5 Content-Type: text/plain; charset="UTF-8" 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 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" 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: > > > > > > > > > > > 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 > 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 > > 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 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 > > 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" > 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 > > >> > > > > > >> > > > > >> > > > >> > > > > > > > > > --089e0820373c3571e2056450b9f5--