db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kristian Waagan (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3769) Make LOBStoredProcedure on the server side smarter about the read buffer size
Date Wed, 22 Oct 2008 13:28:45 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3769?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12641817#action_12641817

Kristian Waagan commented on DERBY-3769:

I wrote a test that inserts 10 Clobs (separate step) and then reads them back with a configurable
buffer size.
For the configurations that finish quickly, I ran the test twice for warmup and then once
for "benchmark". The server was restarted between different configurations, but the data was
not re-inserted.

Insert params: 10 clobs, 10 MB each, content is latin or cjk looping alphabet.
Server started with Xmx512M. The times below is the time it takes for the transfer of 10 Clobs.
The variance in the test is observable.

Derby 10.5 (insane):
              |   32768            32000            10890
no fix, latin |  10 532            8 558           10 718
fixed, latin  |  12 631           11 178           10 441
no fix, cjk   |<unfeasible>[1] <unfeasible>[1]     20 540
fixed, cjk    |  21 916           20 634           19 870

[1] It took 250719 ms to transfer one Clob (cold db). Estimated time for 10 iterations would
then be around 2 500 000 ms, around 120 times slower than for a buffer size of 10890 chars.

Derby 10.4 (insane)
              |    32768           32000           10890
no fix, latin |1 308 170[2]      672 746       1 632 230
fixed, latin  |2 099 400[2]    1 610 080[2]    1 602 490
no fix, cjk   |4 667 548       3 575 390[2]    3 514 560[2]
fixed, cjk    |5 629 730[2]    4 337 090[2]    4 237 650[2]

[2] Estimated time, average of the two first iterations multiplied by ten.

So, what we're seeing here are the effects of three issues;
 a) Unconditional reset of the stream for each request (buffer size).
 b) Extra (unnecessary) reset of the stream for each request (buffer size).
 c) Increased number of round-trips due to increased number of request (smaller buffer size).

a) is fixed (for StoreStreamClob) with DERBY-3825, and b) is addressed by DERBY-3769. c) comes
as a consequence of a user decision (buffer size) and the fact that we don't have any kind
of prefetching for this request.
The performance can be characterized by the number of stream resets and the number of round-trips.
For some data the current fixes reduce one and increases the other, resulting in sub-optimal

As can be seen, the fix for DERBY-3769 has a severely negative effect without the patch for
DERBY-3825. This is caused by the increased number of stream resets required.
It can also be seen that though the simple buffer size fix improves performance significantly
where a too large buffer causes stream resets, it also hurts performance for Clobs containing
only 1 byte character encodings. Nevertheless, I will commit the current patch 2a and create
a separate Jira for the optimization with the following justification:
 a) Performance improvement is huge for Clobs with 2 or 3 byte characters.
 b) Performance reduction for Clobs with 1 byte characters is modest (around 0% - 25%).
 c) It only affects the client driver.
 d) There are several more optimal solutions, but they partly depend on other patches and
implementation choices.

I would prefer to handle the most critical problems first and then optimize at a later time.
Also note the dramatic performance improvement due to DERBY-3825. Compared to this, the performance
reduction is very small.

> Make LOBStoredProcedure on the server side smarter about the read buffer size
> -----------------------------------------------------------------------------
>                 Key: DERBY-3769
>                 URL: https://issues.apache.org/jira/browse/DERBY-3769
>             Project: Derby
>          Issue Type: Improvement
>          Components: Network Server
>    Affects Versions:,,
>            Reporter: Kristian Waagan
>            Assignee: Kristian Waagan
>             Fix For:,
>         Attachments: derby-3769-1a-buffer_size_adjustment.diff, derby-3769-1b-buffer_size_adjustment.diff,
> Derby has a max length for VARBINARY and VARCHAR, which is 32'672 bytes or characters
> When working with LOBs represented by locators, using a read buffer larger than the max
value causes the server to process far more data than necessary.
> Say the read buffer is 33'000 bytes, and these bytes are requested by the client. This
requests ends up in LOBStoredProcedure.BLOBGETBYTES.
> Assume the stream position is 64'000, and this is where we want to read from. The following
>  a) BLOBGETBYTES instructs EmbedBlob to read 33'000 bytes, advancing the stream position
to 97'000.
>  b) Derby fetches/receives the 33'000 bytes, but can only send 32'672. The rest of the
data (328 bytes) is discarded.
>  c) The client receives the 32'672 bytes, recalculates the position and length arguments
and sends another request.
>  d) BLOBGETBYTES(locator, 96672, 328) is executed. EmbedBlob detects that the stream
position has advanced too far, so it resets the stream to position zero and skips/reads until
position 96'672 has been reached.
>  e) The remaining 328 bytes are sent to the client.
> This issue deals with points b) and d), by avoiding the need to reset the stream.
> Points a) and e) are also problematic if a large number of bytes are going to be read,
say hundreds of megabytes, but that's another issue.
> It is unfortunate that using 32 K (32 * 1024) as the buffer size is almost the worst
case; 32'768 - 32'672 = 96 bytes.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message