db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Piet Blok" <pbh...@wanadoo.nl>
Subject Re: Strange performance issue with BLOB's
Date Thu, 13 Jul 2006 05:30:24 GMT
Thank you Mike,

Your explanation does make sense to me.

Now what should I do? I think there are two possible JIRA candidates:

1) A documentation issue for the Tuning Guide.
I read that guide to find a solution for my performance problem
but could not find any tip that seemed applicable. Maybe
something could be added with respect to Blob's, especially
when these Blob's are really big, compared to page size.
The trick of using a dummy where clause might be mentioned.

2) A design issue for the optimizer: always use an index
if one is available.

Do you think I should file these issues? (one, both or none).

I will anyway try to find out how to produce query plans.
If not for this problem, then for future problems. I read
about them somewhere in the documentation. Do you think they are
helpfull in this particular case? Your explanation seems quite
satisfactory to me.

The suggestion by Michael Segel (thank you too for your
contribution) to redesign the storage of LOB data separate from
normal table data is way beyond my league to comment on.
However, to me it seems that he may be right in pinning down
a real problem.

There is something else that I was wondering about. You may have noticed
the SIZE and CRC column in my blob table together with the UNIQUE
constraint. Obviously, I want to prevent duplicate blob's. What happens if
I add a UNIQUE constraint on the blob data itself? Would it be accepted?
If so, how would Derby enforce this constraint? Comparing all the data from
start to finish? I hesitate to try, because I am afraid that when I add a 
blob, that
some process may be started that will run virtually for ever.


Kind regards,

Piet Blok


----- Original Message ----- 
From: "Mike Matrigali" <mikem_app@sbcglobal.net>
To: "Derby Discussion" <derby-user@db.apache.org>
Sent: Wednesday, July 12, 2006 7:40 PM
Subject: Re: Strange performance issue with BLOB's


>I think the following is what is going on. The blobs are not
> really instantiated in memory in one sense, but the entire
> container is read through the cache a page at a time.
>
> The base container of a derby table is a sequence of pages.  Derby
> stores blob data in the same container as the rest of the data,
> in a page link chain.  There is a map of pages used only for
> space allocation, not page types.  So in the scan case Derby
> visits every page in the container and then checks whether each
> page is a "main" page or not.  Main pages are returned up the
> stack to access to process each row.  In the case of your
> query no blob data is actually accessed so "access" never requests
> the column data for the blob data - but the whole table is
> still read through the cache.  There is no support in the base
> container implementation to "skip" to the next main page - that
> is what indexes provide.
>
> This is an interesting case for the optimizer.  My guess is that
> the optimizer never considers using an index if there is no
> where clause unless the index "covers" (includes all the columns
> of the query).  I am not sure how hard it would be to get the
> optimizer to consider using the index in this case, and I am
> not sure if the existing costing handles this blob case - but
> from your experiment is sort of looks like the costing is right
> as the optimizer is choosing the index when you added the
> dummy where - I am sure the costing of that where indicated that
> it would scan the entire index.
>
> With performance issues like this a lot is made much more obvious
> if you can include the query plan of the 2 queries.
>
> Piet Blok wrote:
>> Hi all,
>>
>> When experimenting with BLOB's I ran into a performance issue
>> that I cannot completely explain, but it could be a bug.
>>
>> Given the following table:
>>
>> CREATE TABLE BLOB_TABLE (
>> BLOB_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, 
>> INCREMENT BY 1),
>> BLOB_SIZE BIGINT NOT NULL,
>> BLOB_CRC BIGINT NOT NULL,
>> BLOB_NAME VARCHAR(255) NOT NULL,
>> BLOB_DATA BLOB(2G) NOT NULL,
>> UNIQUE (BLOB_CRC, BLOB_SIZE),
>> PRIMARY KEY (BLOB_ID)
>> );
>>
>> which is populated with 27 rows,
>> where the sum of all BLOB sizes is 5,885,060,164 bytes
>> (about 200 MB average per BLOB, but ranging from 10 MB to 750 MB).
>>
>> Some queries on this table are executed really
>> fast (almost instantaneous response).
>>
>> However, the following query needs about 10 minutes to complete:
>>
>> SELECT BLOB_ID, BLOB_NAME, BLOB_SIZE, BLOB_CRC FROM BLOB_TABLE;
>>
>> I reasoned that maybe Derby is scanning the whole table
>> (including the blob contents) so I tried to add a dummy WHERE
>> clause (dummy because all BLOB_ID's are greater than 0)
>> to offer a clue as to what rows (all of course) are needed,
>> as follows
>>
>> SELECT BLOB_ID, BLOB_NAME, BLOB_SIZE, BLOB_CRC FROM BLOB_TABLE WHERE 
>> BLOB_ID > 0;
>>
>> and that helped: instantaneous response.
>>
>> But I really think that the original query,
>> without the where clause, should not be any slower.
>>
>>
>> I am using Derby 10.1.3.1 embedded, Windows XP and Sun Java 1.5.0_06.
>> Both queries executed with a Statement, not a PreparedStatement.
>>
>> Kind regards,
>>
>> Piet Blok
>>
>>
>>
>>
>
> 



Mime
View raw message