[
https://issues.apache.org/jira/browse/DERBY-6858?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15155992#comment-15155992
]
somebody commented on DERBY-6858:
---------------------------------
I really appreciate everyone's feedback and support. Unfortunately I am really
busy right now with life stuff and I also really need to get my application
done. I honestly don't have time to take on yet another big task, but I'm
willing to help support you guys in fixing this issue if someone else will take
the lead.
Mike, the TESTINDEX scenario I posted a long time ago and since then we created
a reproducible test file that demonstrates both behaviours that is attached to
this issue without other libraries like Spring and Hibernate. They both
demonstrate the same issue, but I suggest you just run the test file attached
and enable the data collection on that file and compare them. In my experience
they will be identical aside from timing data and dates and times.
Mike, almost always, the query plans were exactly identical and the performance
was orders of magnitude worse, again please just run the test file.
Mike, regarding your comment:
Note that while the query looks simple, it is actually one of the problem cases
for a database. update ChildUpdate set parentName = 'Parent 2' where parentName
= 'Parent 1' . This
is updating the same data that it is looking at to perform the update. Derby
does not have anyway to track what updates it has done in the table for the
given transaction so it has 2
options 1) make sure it only visits each row once and does or does not do the
update or 2) do they updates and track the result in a side table and then
apply them one by one after
making the list. option 2 is referred as deferred update, and option 1 is just
deferred update=false.
We already determined that deferred or not, does not affect the behaviour. You
mention this is a problem case, which is fine, but the performance shouldn't
degrade exponentially (which is what happens now, ie. it's orders of magnitude
longer than a linear growth). Regardless of how problematic this case is for a
DB, Since I changed nothing else except doubled the number of rows, I would
expect the performance to be around twice as slow as with half the rows. This
is unfortunately not the case (please see my latest timing results post and
those by Bryan).
In reply to Mike:
The second "slow" plan has chosen to use the index to find the rows it wants to
look for. An unfortunate choice as we know it is going to have to visit all
2500 rows
The unfortunate part of this is that the optimizer should know how many values
there are of each value in that column because of the index. Isn't this type
of meta-data supposed to be tracked by the database for the table or in the
index? I have also spent weeks on this issue before posting it in Jira. I
have gone over the derby documentation and tried every parameter listed that
made sense, and I did not find any way to improve the performance or change
this behaviour except for the pageSize which as mentioned doesn't scale and is
not a fix.
Mike, having deferred constraints or not makes no difference to the behaviour,
please see the other posts in this issue and also run the test app.
1) In real application do you always change all the rows with this update? Does
the test case actually represent the likely real app scenario?
The test case represents a much much much simpler case then in my real
application. It's possible in my real application that i will change all rows,
but it's also likely i will have multiple different values so not all of the
rows will be changed.
2) what is lifetime of data and size? ie. do you load a bunch and then number
and data distribution stays stable?
the lifetime of the data is likely going to be relatively stable and
constant. Initially a large number of records will be inserted, but after only
a likely relatively small percentage of it will change over time (some updated,
some deleted, and some new added). This all depends on the scenarios but this
is my expectation.
Mike, you seem to be on the right track with your suggestions. I think a lot
of your ideas sound very plausible. I also think the optimizer might need some
work just from what I can tell from some of the output I have seen.
Some things to play with to understand more what is going on:
1) get sizes of the underlying tables and indexes in the good and bad cases.
https://db.apache.org/derby/docs/10.12/ref/rrefsyscsdiagspacetable.html
this can be done on the test app provided, but my observation was that
the entire database size (including logs and all files) was about 3.5 megabytes
(which I believe i posted already in this issue), which is nothing, so I'm not
sure how a table < 3.5 MB or an index < 3.5 MB should be able to cause such a
huge performance decrease
2) see if updating optimizer statistics helps. Derby will eventually
automatically do this in background. But for your test case it seems likely you
created
indexes first and loaded data which will not generate stats. :
https://db.apache.org/derby/docs/10.8/tuning/ctunperfstatistics.html
I already tried forcing an update on statistics, it made no difference, but
perhaps there are other ways to update the statistics. Since we have a
reproducible app I suggest someone else try this approach because when I tried
it it made no difference, but perhaps I missed something
3) i need some help from others on this one, but you may be able to add hints
to your update query if you want to work around this problem and force the scan.
I tried some of the hints that I could find in the derby docs before I
posted this issue and they didn't change the behaviour. However I am not an
expert on which hints derby supports or which hints are good to add or even how
to correctly specify the hint. I think it's a good idea to pursued this path
further as it might also yield information on what needs to be changed in the
derby code if the query performance can be optimized.
4) you might try writing your query with ? parameters vs hard coded variables.
This takes a different path through the optimizer. I am not hopeful on this one
as
i think it will lean more toward index scan vs table scan.
I am not hopeful on this approach either, but it's worth a try
To Mike regarding the derby development team: that is very unfortunate. This
is somewhat surprising to me because Oracle just integrated Derby into the JDK
not that long ago.
> Apache Derby simple update statement performance becomes 1500% worse when
> adding one byte to a column
> -----------------------------------------------------------------------------------------------------
>
> Key: DERBY-6858
> URL: https://issues.apache.org/jira/browse/DERBY-6858
> Project: Derby
> Issue Type: Bug
> Affects Versions: 10.11.1.1, 10.12.1.1
> Environment: windows 7 64 bit
> Reporter: somebody
> Priority: Blocker
> Attachments: repro.java, repro.java, repro.java
>
>
> I have 2 tables as follows:
> ParentUpdate
> name varchar(255)
> value int not null
> primary key: name
> ChildUpdate
> parentName varchar(255)
> name varchar(255)
> value int
> data varchar(1000)
> primary key: name foreign key: parentName to ParentUpdate.name
> When I run the statement "update ChildUpdate set parentName = 'Parent 2'
> where parentName = 'Parent 1'" with 2500 records in the ChildUpdate table and
> 1 record in the ParentUpdate table with only a single byte difference in data
> size in the ChildUpdate table, the performance decreases by 15 times.
> When the ChildUpdate data column has exactly 14 bytes of the same character
> the runtime of the above query is about 500 milliseconds. When I add one more
> byte to the data column of ChildUpdate the performance all of a sudden
> becomes about 7500 milliseconds.
> If i then decrease the data size back to 14 from 15 it's fast again. When i
> put it back to 15 it's slow again. This is reproducible every time.
> Can you please help me figure out how to get the same fast performance
> without such seemingly random behaviour.
> The query plans are below for both cases.
> projection = true
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 16
> close time (milliseconds) = 16
> restriction time (milliseconds) = 0
> projection time (milliseconds) = 0
> optimizer estimated row count: 51.50
> optimizer estimated cost: 796.12
> Source result set:
> Table Scan ResultSet for CHILDUPDATE at read committed isolation
> level using exclusive row locking chosen by the optimizer
> Number of opens = 1
> Rows seen = 2500
> Rows filtered = 0
> Fetch Size = 1
> constructor time (milliseconds) = 0
> open time (milliseconds) = 15
> next time (milliseconds) = 16
> close time (milliseconds) = 16
> next time in milliseconds/row = 0
> scan information:
> Bit set of columns fetched={0, 1}
> Number of columns fetched=2
> Number of pages visited=41
> Number of rows qualified=2500
> Number of rows visited=2500
> Scan type=heap
> start position:
> null
> stop position:
> null
> qualifiers:
> Column[0][0] Id: 0
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
> optimizer estimated row count: 51.50
> optimizer estimated cost: 796.12
> total time: ~500 milliseconds
> and the slow version
> Statement Name:
> null
> Statement Text:
> update ChildUpdate set parentName = 'Parent 2' where parentName = 'Parent
> 1'
> Parse Time: 0
> Bind Time: 0
> Optimize Time: 0
> Generate Time: 0
> Compile Time: 0
> Execute Time: -1453199485700
> Begin Compilation Timestamp : 2016-01-19 05:31:25.684
> End Compilation Timestamp : 2016-01-19 05:31:25.684
> Begin Execution Timestamp : 2016-01-19 05:31:25.7
> End Execution Timestamp : 2016-01-19 05:31:33.141
> Statement Execution Plan Text:
> Update ResultSet using row locking:
> deferred: true
> Rows updated = 2500
> Indexes updated = 2
> Execute Time = -1453199485747
> Normalize ResultSet:
> Number of opens = 1
> Rows seen = 2500
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 47
> close time (milliseconds) = 0
> optimizer estimated row count: 51.50
> optimizer estimated cost: 810.94
> Source result set:
> Project-Restrict ResultSet (3):
> Number of opens = 1
> Rows seen = 2500
> Rows filtered = 0
> restriction = false
> projection = true
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 32
> close time (milliseconds) = 0
> restriction time (milliseconds) = 0
> projection time (milliseconds) = 0
> optimizer estimated row count: 51.50
> optimizer estimated cost: 810.94
> Source result set:
> Project-Restrict ResultSet (2):
> Number of opens = 1
> Rows seen = 2500
> Rows filtered = 0
> restriction = false
> projection = true
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 32
> close time (milliseconds) = 0
> restriction time (milliseconds) = 0
> projection time (milliseconds) = 0
> optimizer estimated row count: 51.50
> optimizer estimated cost: 810.94
> Source result set:
> Index Scan ResultSet for CHILDUPDATE using index TESTINDEX at
> read committed isolation level using exclusive row locking chosen by the
> optimizer
> Number of opens = 1
> Rows seen = 2500
> Rows filtered = 0
> Fetch Size = 1
> constructor time (milliseconds) = 0
> open time (milliseconds) = 0
> next time (milliseconds) = 32
> close time (milliseconds) = 0
> next time in milliseconds/row = 0
> scan information:
> Bit set of columns fetched={0, 1, 2}
> Number of columns fetched=3
> Number of deleted rows visited=0
> Number of pages visited=42
> Number of rows qualified=2500
> Number of rows visited=2500
> Scan type=btree
> Tree height=2
> start position:
> None
> stop position:
> None
> qualifiers:
> Column[0][0] Id: 1
> Operator: =
> Ordered nulls: false
> Unknown return value: false
> Negate comparison result: false
> optimizer estimated row count: 51.50
> optimizer estimated cost: 810.94
> total time: ~7 seconds 500 milliseconds
> please also see post:
> http://stackoverflow.com/questions/34874762/apache-derby-simple-update-statement-performance-becomes-1500-worse-when-adding
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
