Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
Hi Mike, here's the query plan. It's pretty lengthy, sorry. After I fixed the worst problem the performance difference between PostgreSQL and Derby is ca. 5x. In my table comparing PostgreSQL and Derby before after, Derby is flat at 200ms or so for each query. I *think* what's happening is that Derby has two large sets of records TREATMENT and FIELD. Derby needs to scan the entire main table to check if each main record TREATMENT is in the set of FIELD records. Hence the performance is roughly proportional to the # of main records and indeed 200ms seems to be like the 'standard' time to complete a query for all these queries now. There are 6311 TREATMENT records and 322 FIELD records. If you are able to post a query plan for that query I would be willing to look at it to see if derby is doing a scan or if the optimizer is using the expected multi-probe join strategy. Thu Apr 16 07:53:10 CEST 2015 Thread[qtp1419014327-22,5,main] (XID = 11827028), (SESSIONID = 1), SELECT DISTINCT t0.TREAT_CORRECTIVEDESC, t0.TREAT_NO, t0.TREAT_INCIDID, t0.TREAT_ENTEREDBYCONTACT, t0.TREAT_CRITID, t0.treat_treatcontact, t0.TREAT_ENTEREDBY, t0.TREAT_ID, t0.TREAT_REFINT, t0.TREAT_REPORTEDDATE, t0.TREAT_UPDATEDDATE, t0.TREAT_DESC, t0.TREAT_SOURCETYPE, t0.TREAT_QUALITYTYPE, t0.causedSysUser, t0.areaResponsible, t0.TREAT_PROID, t0.TREAT_TREATASPECID, t0.TREAT_TREATROLE, t0.TREAT_TREATUSER, t0.TREAT_PARENTID, t0.TREAT_SYSORGUNITID, t0.sev_id, t0.TREAT_LIMITDATE, t0.TREAT_RESPONSIBLECONTACT, t0.TREAT_ACTIONDESC, t0.TREAT_ELCSTATUSID, t0.TREAT_REPORTEDBYCONPER, t0.responsibleSysUser, t0.freq_id, t0.TREAT_ENTEREDDATE, t0.TREAT_REPORTEDBYCONTACT, t0.TREAT_TREATCSPECID, t0.treat_causedcontact FROM TREATMENT t0 JOIN ELC_STATUS t1 ON (t0.TREAT_ELCSTATUSID = t1.ELCSTATUS_ID) JOIN fieldlink t2 ON (t0.TREAT_ID = t2.treatment) JOIN field t3 ON (t2.field = t3.id) JOIN fieldchildren t4 ON (t3.id = t4.child) WHERE (t0.TREAT_REPORTEDDATE IS NOT NULL) AND (t0.TREAT_NO IS NOT NULL) AND (t1.ELCSTATUS_PROGVALUE ?) AND (t0.TREAT_REPORTEDDATE IS NOT NULL) AND (t0.TREAT_NO IS NOT NULL) AND (t2.customfield = ?) AND (t4.parent = ?) *** Sort ResultSet: Number of opens = 1 Rows input = 1292 Rows returned = 1292 Eliminate duplicates = true In sorted order = false Sort information: Number of merge runs=1 Number of rows input=1292 Number of rows output=1292 Size of merge runs=[1279] Sort type=external constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 1006.81 Source result set: Project-Restrict ResultSet (15): Number of opens = 1 Rows seen = 1292 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 1006.81 Source result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 4995 Rows seen from the right = 1292 Rows filtered = 0 Rows returned = 1292 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 1006.81 Left result set: Nested Loop Exists Join ResultSet: Number of opens = 1 Rows seen from the left = 4995 Rows seen from the right = 4995 Rows filtered = 0 Rows returned = 4995 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 952.18 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 4995 Rows seen from the right = 4995 Rows filtered = 0 Rows returned = 4995 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 optimizer estimated row count: 11.59 optimizer estimated cost: 915.75 Left result set: Nested Loop Join ResultSet: Number of opens = 1 Rows seen from the left = 6 Rows seen from the right = 4995
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
Hi Mike, after mulling over the feedback, I failed to explain my yellow rubber duck why I can't introduce some new tables here to avoid the pathologically long OR statements that match main records against fields. Basically I had to check if a main record was under a category or any of it's subcategories. This meant first creating a list of all categories, then a very, very long select statement to match against all those categories. The customer has really gone to town on creating an enormous number of categories whereas the system was originally designed to have a few dozen categories and hence performance broke down. The solution was to create an extra table where each category has a map from itself to all it's children. This way I can immediately tell if a main record is under a category or any of the subcategories with a join statement. Here are the before/after numbers for PostgreSQL. As you can see the pathologically slow Derby statement(2561ms) has been fixed. It's also interesting to note that these optimizations do have no effect on PostgreSQL(in the noise). If I'm able to get this improvement to production quality(there are a few pesky issues in the app), then I avoid the performance implosion, so even if PostgreSQL is 5x faster, there are will then be bigger fish to fry in the app and we avoid the deployment issues with PostgreSQL. PostgreSQL/ms Derby/ms RowsBefores After Before After 13 0 0 0 1 0 5 13 0 1 129242 46 2561231 757 44 46 237 223 100 16 13 194 218 121 24 20 157 212 818 46 48 197 244 85 13 11 168 212 41 17 13 135 217 17 10 17 145 223 118 23 25 156 225 21 12 9 151 213 58 17 25 139 212 1 15 13 139 226 134062 61 297 220 226 18 36 170 211 7 1 0 0 0 34 4 5 1 1 0 5 4 1 1 0 1 1 0 0 480976 69 59 55 91 5 5 1 1 61 10 5 1 1 0 3 6 0 1 0 5 7 0 0 129242 48 2566222 757 45 40 238 217 100 13 17 195 221 121 20 15 175 240 818 40 45 199 217 85 20 11 169 211 41 18 11 135 209 17 10 16 143 211 118 19 16 141 213 21 9 17 153 212 58 22 24 138 217 1 12 11 137 284 134048 59 384 227 226 18 16 165 218 7 1 0 1 1 34 7 4 1 1 0 5 3 0 1 0 1 1 0 0 480973 68 53 58 91 6 5 1 1 61 6 5 1 2 0 5 4 1 1 13 0 0 1 0 11 0 0 1 0 20031 914 934 99076333 -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
Hi Mike, see answers below inline: On Wed, Apr 15, 2015 at 2:03 AM, mike matrigali mikema...@gmail.com wrote: Do many of the queries share a similar structure to the query you describe in point 5? Yes, but few have hundreds of arguments. When they do, these queries, unsurprisingly, take a long time though. Can you give an order of magnitude on the number of expected rows in t2 from that query, also maybe an estimate of overall size of the database tables involved in all the queries. We have ca. 6000 main records and there are ca. 1000 'field' records. The report defines a group of fields that have to be enumerated one-by-one and all cases have to be checked if they are in that group. There are 6000 main records X 1000 fields so a potential of 6m links. If we created direct links between all the cases and the fields(6m isn't the end of the world), then that table would have to be dumped anytime the fields change. Have you, or are you willing to look at query plans: http://wiki.apache.org/db-derby/PerformanceDiagnosisTips If you are able to post a query plan for that query I would be willing to look at it to see if derby is doing a scan or if the optimizer is using the expected multi-probe join strategy. I'll dig into the query plan, I've looked at the other things there, but nothing yielded appreciable results. Is it option to change the query? Our application sits on top of Apache Cayenne ORM. It is Apache Cayenne ORM that formulates the queries. This gives us limited and indirect control of the actual SQL queries. Systems like PostgreSQL shine because they are more forgiving to the situations that happen when there are many layers between the query and the database engine. 2) I have seen issues with large in-lists (which also should equate to large number of OR's as described below), where compilation cost is high, and optimizer chooses scan vs the probes. Large in-lists crashed so I had to split it into multiple queries: https://issues.apache.org/jira/browse/DERBY-3876 I don't know if it is an option for you, but I have seen others change queries of this form into a join on either a real or temp table depending on if the values are actually variable or not. Perhaps we can discover something that Apache Cayenne ORM can use? Our application is stuck on the current version of Cayenne due to some PITA technical debt that needs to be paid off. -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
While it may be that PostgreSQL is faster, make sure you are measuring accurately. First, you should be running the Java Server VM, preferably Java 8. Second, the server VM JIT compile threshold is 10,000 executions of a given method -- so the VM will get faster and faster as it progressively compiles code. I would run 1 queries though Derby before taking measurements. Lastly, PostgreSQL may be running with substantially larger page caches by default. To the point where much of the data may be completely in memory. Read the Derby Tuning Guide to set the page cache to as close to the same size as PostgreSQL as possible. Sent from my iPhone On Apr 14, 2015, at 15:30, Øyvind Harboe oyvind.har...@zylin.com wrote: I compared the performance on PostgreSQL vs. Derby for this report. Unsurprisingly PostgreSQL performs much better.
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
I compared the performance on PostgreSQL vs. Derby for this report. Unsurprisingly PostgreSQL performs much better. The database that I'm working with has grown over the years. I have a feeling that we crossed some threshold and that Derby performance has decreased to the point where it is now very noticeable to humans. Perhaps I'm looking for patterns where there are none and my brain is only too happy to provide me with patterns. I prefer Derby from a maintenance point of view, it is a lot simpler. RowsPSQL/ms Derby/ms 13 0 1 1292127 2662 757 44 262 100 22 188 121 13 163 818 39 190 85 15 164 41 8 132 17 8 140 118 12 134 21 19 144 58 15 273 1 9 133 134065 292 226 20 157 7 1 0 34 4 1 480991 50 91 8 2 61 4 1 1292122 2684 757 46 253 100 23 190 121 13 161 818 40 209 85 14 167 41 11 131 17 15 141 118 19 135 21 14 144 58 16 133 1 12 133 134066 294 226 17 156 7 0 1 34 4 0 480977 67 91 8 1 61 6 1 13 1 1 11 0 1 -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
Our application is a bit off the beaten path in that what matters to us is performance from standstill to user logging in and running a report. Caching isn't very helpful because the user won't be running the reports twice in a row and new data will be entered in-between each time the user runs a report. If caching was an option, then I would just generate the reports overnight and they would be instantaneous. I will try Java 8 server VM. I tried increasing -Dderby.storage.pageCacheSize=1 but it didn't have a measurable effect. I tried tinkering with other settings too but nothing really yielded any result. I tried increasing/decreasing the compilation threshold, without effect on the JVM JIT. I presume JIT isn't really useful to my case because we aren't running stored procedures, so I presume that Derby will generate fresh bytecode every time. Compilation doesn't really appear to take enough time or improve performance enough to make a difference one way or the other. You point out something important though: performance measurement is fraught with subtle traps. Lots of projects have unit tests, but have you seen one that has cracked the problem of performance regression testing? I would rather get Derby fast enough that we don't have to switch to PostgreSQL. However, the performance of a database is a result of how much effort one puts into it and how good the match happens to be between our access patterns and the default configuration of the database. PostgreSQL might be 'lucky' on our data, but that doesn't make the performance difference any less real. -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
Do many of the queries share a similar structure to the query you describe in point 5? Can you give an order of magnitude on the number of expected rows in t2 from that query, also maybe an estimate of overall size of the database tables involved in all the queries. Is the target system running embedded or client/server, derby should best perform embedded, though the number of rows being returned is pretty small so may not matter. Have you, or are you willing to look at query plans: http://wiki.apache.org/db-derby/PerformanceDiagnosisTips If you are able to post a query plan for that query I would be willing to look at it to see if derby is doing a scan or if the optimizer is using the expected multi-probe join strategy. Is it option to change the query? Derby does have 2 known issues when it comes to queries of this form: 1) derby compile time is often longer than other systems. priority was given to optimizing execution hoping that usual case was either prepared statement or internally cached statement plans. I think logStatementText or RUNTIMESTATISTICS can provide compile vs execution time in derby. 2) I have seen issues with large in-lists (which also should equate to large number of OR's as described below), where compilation cost is high, and optimizer chooses scan vs the probes. I don't know if it is an option for you, but I have seen others change queries of this form into a join on either a real or temp table depending on if the values are actually variable or not. On 4/13/2015 12:54 PM, Øyvind Harboe wrote: 5. There are some queries that stick out as a sore thumb below, e.g. the one which returns 1292 rows in table 1 below. I'm loathe to reproduce the query here because it is *LONG*. It takes a few hundred arguments to match against a list of primary keys. The SQL statement contains a few hundred of these .. OR (t2.field = ?) OR (t2.field = ?) OR (t2.field = ?) Stored queries are not an option here because the query is generated by Java code at the Cayenne ORM level. This query can't be simplified because the report is checking if the records are in one out of 322 specific categories where each category is a 'field' record as alluded to above. Table 1. Jetty Tomcat Rowstime/ms time/ms 13 0 1 13 0 0 11 0 0 13 1 1 13 1 0 0 1 1 129226622625 757 262 240 100 188 193 121 163 152 818 190 211 85 164 174 41 132 133 17 140 139 118 134 133 21 144 143 58 273 131 1 133 136 1340292 323 226 157 161 7 0 1 34 1 1 0 0 0 0 1 0 480950 52 91 2 1 61 1 1 0 0 0 0 1 0 129226842572 757 253 242 100 190 196 121 161 153 818 209 200 85 167 184 41 131 128 17 141 147 118 135 142 21 144 151 58 133 133 1 133 139 1340294 311 226 156 161 7 1 0 34 0 0 0 0 0 0 0 0 480967 51 91 1 1 61 1 0 0 0 0 13 1 5 11 1 1 Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/ -- email:Mike Matrigali - mikema...@gmail.com linkedin: https://www.linkedin.com/in/MikeMatrigali
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
I've been able to a more real-world testing that takes into account other things going on in the system that takes time. For the worst report(the default report), the overall system performance I get: First run: PostgreSQL = 15s / Derby = 45s Second run: PostgreSQL = 5s / Derby = 23s The actual PostgreSQL queries are *much* faster, but there is a lot of other things going on too. Over time, I think the performance problem will 'spread' to other parts of the system just because there's more data being added. For other types of reports and elsewhere in the system, the difference in overall performance is more slight and it won't cause us to take action. The biggest impediment to switching to PostgreSQL is that the backup routines are much more complicated and not a great match for our current setup. -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
Are you sure that under Tomcat the application is really using the Tomcat connection pool? If not that would certainly account for the differences. Even if Tomcat DBCP is configured, checking that the pool configurations are similar would seem like an important check. -Brett On Mon, Apr 13, 2015 at 4:56 PM, Øyvind Harboe oyvind.har...@zylin.com wrote: I know the Tomcat setup isn't using the clientdriver when it slows down, because when I first tried to switch to the ClientDriver it failed. After I copied the derbyclient.xxx.jar to tomcat/lib/, it worked. On Mon, Apr 13, 2015 at 9:51 AM, Dyre Tjeldvoll dyre.tjeldv...@oracle.com wrote: On 04/13/2015 12:27 AM, Øyvind Harboe wrote: I found another crucial clue: if I use ClientDriver instead of EmbeddedDriver and connect to the Derby database running under Jetty, I get identical performance with Tomcat and Jetty. This is very strong indication that there is something about the combination of EmbeddedDriver and Tomcat that is gumming up the works. Could it be that your Tomcat setup always uses the ClientDriver for some reason? I don't know much about setting up Databases with AppServers, but based on the number of questions on SO and other places, it isn't trivial... -- Regards, Dyre -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
I don't think that's the problem: 1. The same amount of heap is configured 2. I've checked with VisualVM and it uses much less than the provided memory(ca. 250mbyte vs. 1024mbyte heap). On Mon, Apr 13, 2015 at 4:54 AM, Bryan Pendleton bpendleton.de...@gmail.com wrote: I've tried to figure out what the differences are between these two environments without luck. Perhaps the amount of memory available to Derby is dramatically different in the one case than in the other, and Derby is working very hard to accomplish its tasks with insufficient memory? bryan -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
I know the Tomcat setup isn't using the clientdriver when it slows down, because when I first tried to switch to the ClientDriver it failed. After I copied the derbyclient.xxx.jar to tomcat/lib/, it worked. On Mon, Apr 13, 2015 at 9:51 AM, Dyre Tjeldvoll dyre.tjeldv...@oracle.com wrote: On 04/13/2015 12:27 AM, Øyvind Harboe wrote: I found another crucial clue: if I use ClientDriver instead of EmbeddedDriver and connect to the Derby database running under Jetty, I get identical performance with Tomcat and Jetty. This is very strong indication that there is something about the combination of EmbeddedDriver and Tomcat that is gumming up the works. Could it be that your Tomcat setup always uses the ClientDriver for some reason? I don't know much about setting up Databases with AppServers, but based on the number of questions on SO and other places, it isn't trivial... -- Regards, Dyre -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
I'll be trying to do more logging. Do you have any handy pointers to documentation? To log SQL statements seems like the most promising: https://db.apache.org/derby/docs/10.11/ref/rrefproper43517.html I didn't find anything to log connection IDs (distinct/count). I never tried JMX mbeans w/VisualVM, so that should be interesting.
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
And, by testing with a network server I think you can access JMX mbeans with VisualVM/JConsole to check connection counts. -- Kristian 13. apr. 2015 13:12 skrev Kristian Waagan krist...@apache.org: Hi Øivind, Have you turned on SQL-logging/-tracing in Derby to check the activity? Possible things to check: o connection IDs (distinct/count) o expensive connection validation queries (not all of these are direct SQL, check pool docs/conf) o commits? o different queries? While one would expect the load / queries to be the same, one never knows... In theory one would think the client driver was more affected by a pool misconfiguration. Regards, -- Kristian 13. apr. 2015 11:56 skrev Øyvind Harboe oyvind.har...@zylin.com: I've made them identical near as I can. It isn't the same implementation of the connection pool. Is it only EmbeddedDriver that would be negatively affected by a mis-configured connection pool? Is there some logging in Derby that I could enable to verify that things are set up correctly, i.e. that new connections are not made all the time? On Mon, Apr 13, 2015 at 11:16 AM, Brett Wooldridge brett.wooldri...@gmail.com wrote: Are you sure that under Tomcat the application is really using the Tomcat connection pool? If not that would certainly account for the differences. Even if Tomcat DBCP is configured, checking that the pool configurations are similar would seem like an important check. -Brett On Mon, Apr 13, 2015 at 4:56 PM, Øyvind Harboe oyvind.har...@zylin.com wrote: I know the Tomcat setup isn't using the clientdriver when it slows down, because when I first tried to switch to the ClientDriver it failed. After I copied the derbyclient.xxx.jar to tomcat/lib/, it worked. On Mon, Apr 13, 2015 at 9:51 AM, Dyre Tjeldvoll dyre.tjeldv...@oracle.com wrote: On 04/13/2015 12:27 AM, Øyvind Harboe wrote: I found another crucial clue: if I use ClientDriver instead of EmbeddedDriver and connect to the Derby database running under Jetty, I get identical performance with Tomcat and Jetty. This is very strong indication that there is something about the combination of EmbeddedDriver and Tomcat that is gumming up the works. Could it be that your Tomcat setup always uses the ClientDriver for some reason? I don't know much about setting up Databases with AppServers, but based on the number of questions on SO and other places, it isn't trivial... -- Regards, Dyre -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/ -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
Hi Øivind, Have you turned on SQL-logging/-tracing in Derby to check the activity? Possible things to check: o connection IDs (distinct/count) o expensive connection validation queries (not all of these are direct SQL, check pool docs/conf) o commits? o different queries? While one would expect the load / queries to be the same, one never knows... In theory one would think the client driver was more affected by a pool misconfiguration. Regards, -- Kristian 13. apr. 2015 11:56 skrev Øyvind Harboe oyvind.har...@zylin.com: I've made them identical near as I can. It isn't the same implementation of the connection pool. Is it only EmbeddedDriver that would be negatively affected by a mis-configured connection pool? Is there some logging in Derby that I could enable to verify that things are set up correctly, i.e. that new connections are not made all the time? On Mon, Apr 13, 2015 at 11:16 AM, Brett Wooldridge brett.wooldri...@gmail.com wrote: Are you sure that under Tomcat the application is really using the Tomcat connection pool? If not that would certainly account for the differences. Even if Tomcat DBCP is configured, checking that the pool configurations are similar would seem like an important check. -Brett On Mon, Apr 13, 2015 at 4:56 PM, Øyvind Harboe oyvind.har...@zylin.com wrote: I know the Tomcat setup isn't using the clientdriver when it slows down, because when I first tried to switch to the ClientDriver it failed. After I copied the derbyclient.xxx.jar to tomcat/lib/, it worked. On Mon, Apr 13, 2015 at 9:51 AM, Dyre Tjeldvoll dyre.tjeldv...@oracle.com wrote: On 04/13/2015 12:27 AM, Øyvind Harboe wrote: I found another crucial clue: if I use ClientDriver instead of EmbeddedDriver and connect to the Derby database running under Jetty, I get identical performance with Tomcat and Jetty. This is very strong indication that there is something about the combination of EmbeddedDriver and Tomcat that is gumming up the works. Could it be that your Tomcat setup always uses the ClientDriver for some reason? I don't know much about setting up Databases with AppServers, but based on the number of questions on SO and other places, it isn't trivial... -- Regards, Dyre -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/ -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
Have you put the app under a profiler to see what components are taking the most time in each container? Seems like the best way to get some facts on the table. On Sun, 2015-04-12 at 23:50 +0200, Øyvind Harboe wrote: Hi, I'm having problems with performance degrading dramatically when I deploy our application to a Tomcat container. The application uses Apache Cayenne ORM on top of Derby. Does anyone have any pointers on how I can debug this to figure out what I'm doing wrong? Generating a report which includes ca. 100 queries and thousands of records takes less than one second on Jetty, but in the Tomcat container it takes more than 10 seconds, so a 10x slowdown or so. I can't believe that Jetty is 10x faster than Tomcat. I've tried to figure out what the differences are between these two environments without luck. Jetty and Tomcat are equal in terms of: - same VM options - debug vs. non-debug mode - tests run on same machine side-by-side - same database files(copied to another location on the harddisk) Some differences: - Jetty is using org.apache.cayenne.conn.PoolManager rather than Tomcat's connection pools. When I suspend Tomcat in the debugger, the typical stack trace is something about fetching pages: BaseContainerHandle(Observable).addObserver(Observer) line: 82 StoredPage(BasePage).preLatch(BaseContainerHandle) line: not available StoredPage(BasePage).setExclusive(BaseContainerHandle) line: not available RAFContainer4(BaseContainer).latchPage(BaseContainerHandle, BasePage, boolean) line: not available RAFContainer4(FileContainer).latchPage(BaseContainerHandle, BasePage, boolean) line: not available RAFContainer4(FileContainer).getUserPage(BaseContainerHandle, long, boolean, boolean) line: not available RAFContainer4(FileContainer).getPage(BaseContainerHandle, long, boolean) line: not available BaseContainerHandle.getPage(long) line: not available OpenHeap(OpenConglomerate).latchPage(RowPosition) line: not available HeapController(GenericConglomerateController).fetch(RowLocation, DataValueDescriptor[], FormatableBitSet) line: not available IndexRowToBaseRowResultSet.getNextRowCore() line: not available ProjectRestrictResultSet.getNextRowCore() line: not available NestedLoopJoinResultSet.getNextRowCore() line: not available ProjectRestrictResultSet.getNextRowCore() line: not available SortResultSet.getRowFromResultSet() line: not available SortResultSet.getNextRowFromRS() line: not available SortResultSet.loadSorter() line: not available SortResultSet.openCore() line: not available SortResultSet(BasicNoPutResultSetImpl).open() line: not available GenericPreparedStatement.executeStmt(Activation, boolean, boolean, long) line: not available GenericPreparedStatement.execute(Activation, boolean, long) line: not available EmbedPreparedStatement(EmbedStatement).executeStatement(Activation, boolean, boolean) line: not available EmbedPreparedStatement.executeStatement(Activation, boolean, boolean) line: not available EmbedPreparedStatement.executeQuery() line: not available SelectAction.performAction(Connection, OperationObserver) line: 75 DataNodeQueryAction.runQuery(Connection, Query) line: 87
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
I did. See the stacktrace in my initial post. On Apr 13, 2015 3:18 PM, Tim Watts t...@cliftonfarm.org wrote: Have you put the app under a profiler to see what components are taking the most time in each container? Seems like the best way to get some facts on the table. On Sun, 2015-04-12 at 23:50 +0200, Øyvind Harboe wrote: Hi, I'm having problems with performance degrading dramatically when I deploy our application to a Tomcat container. The application uses Apache Cayenne ORM on top of Derby. Does anyone have any pointers on how I can debug this to figure out what I'm doing wrong? Generating a report which includes ca. 100 queries and thousands of records takes less than one second on Jetty, but in the Tomcat container it takes more than 10 seconds, so a 10x slowdown or so. I can't believe that Jetty is 10x faster than Tomcat. I've tried to figure out what the differences are between these two environments without luck. Jetty and Tomcat are equal in terms of: - same VM options - debug vs. non-debug mode - tests run on same machine side-by-side - same database files(copied to another location on the harddisk) Some differences: - Jetty is using org.apache.cayenne.conn.PoolManager rather than Tomcat's connection pools. When I suspend Tomcat in the debugger, the typical stack trace is something about fetching pages: BaseContainerHandle(Observable).addObserver(Observer) line: 82 StoredPage(BasePage).preLatch(BaseContainerHandle) line: not available StoredPage(BasePage).setExclusive(BaseContainerHandle) line: not available RAFContainer4(BaseContainer).latchPage(BaseContainerHandle, BasePage, boolean) line: not available RAFContainer4(FileContainer).latchPage(BaseContainerHandle, BasePage, boolean) line: not available RAFContainer4(FileContainer).getUserPage(BaseContainerHandle, long, boolean, boolean) line: not available RAFContainer4(FileContainer).getPage(BaseContainerHandle, long, boolean) line: not available BaseContainerHandle.getPage(long) line: not available OpenHeap(OpenConglomerate).latchPage(RowPosition) line: not available HeapController(GenericConglomerateController).fetch(RowLocation, DataValueDescriptor[], FormatableBitSet) line: not available IndexRowToBaseRowResultSet.getNextRowCore() line: not available ProjectRestrictResultSet.getNextRowCore() line: not available NestedLoopJoinResultSet.getNextRowCore() line: not available ProjectRestrictResultSet.getNextRowCore() line: not available SortResultSet.getRowFromResultSet() line: not available SortResultSet.getNextRowFromRS() line: not available SortResultSet.loadSorter() line: not available SortResultSet.openCore() line: not available SortResultSet(BasicNoPutResultSetImpl).open() line: not available GenericPreparedStatement.executeStmt(Activation, boolean, boolean, long) line: not available GenericPreparedStatement.execute(Activation, boolean, long) line: not available EmbedPreparedStatement(EmbedStatement).executeStatement(Activation, boolean, boolean) line: not available EmbedPreparedStatement.executeStatement(Activation, boolean, boolean) line: not available EmbedPreparedStatement.executeQuery() line: not available SelectAction.performAction(Connection, OperationObserver) line: 75 DataNodeQueryAction.runQuery(Connection, Query) line: 87
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
I've tried to figure out what the differences are between these two environments without luck. Perhaps the amount of memory available to Derby is dramatically different in the one case than in the other, and Derby is working very hard to accomplish its tasks with insufficient memory? bryan
Re: Performance degradation with Derby Embedded under Tomcat vs Jetty
I found another crucial clue: if I use ClientDriver instead of EmbeddedDriver and connect to the Derby database running under Jetty, I get identical performance with Tomcat and Jetty. This is very strong indication that there is something about the combination of EmbeddedDriver and Tomcat that is gumming up the works. -- Øyvind Harboe - Can Zylin Consulting help on your project? http://www.zylin.com/
