Re: [bdbxml] DB XML Performance?

Hi Antony,

Thanks for providing all the relevent information. You will certainly be 
able to get better performance out of DB XML than you are at the moment. 
Further comments below:

Antony Grinyer wrote:
> We've been stress testing our DB XML application which uses one
> container holding 10,000 small XML files. Our Java code has a simple
> query method for which we pass an XQuery expression, which then uses the
> DB XML Java API to query our container. 
> 
> Our simple XML document format is shown:
> 
> <AMP>
>   <APID>59111000001100</APID>
>   <VPID>318052005</VPID>
>   <NM>Amiloride 5mg tablets</NM>
>   <DESC>Amiloride 5mg tablets (The Boots Company)</DESC>
>   <SUPPCD>7497111000001106</SUPPCD>
>   <LIC_AUTHCD>0001</LIC_AUTHCD>
>   <AVAIL_RESTRICTCD>0001</AVAIL_RESTRICTCD>
> </AMP>
> 
> We have added these 7 indexes:
> 
> 1. addIndex "" AMP node-element-presence-none
> 2. addIndex "" AMP/APID edge-element-presence-none 
> 3. addIndex "" AMP/SUPPCD edge-element-presence-none 
> 4. addIndex "" APID node-element-equality-decimal
> 5. addIndex "" SUPPCD node-element-equality-decimal
> 6 addIndex "" NM node-element-substring-string
> 7. addIndex "" DESC node-element-substring-string

You are adding the edge indexes incorrectly. You should only specify one 
name for an edge index - and the index will store an entry for every 
occurance of that one element, detailing what it's parent is.

If you need to see what indexes are being used in a given query, you 
should take a look at the query plan using 
XmlQueryExpression.getQueryPlan(). There is a (partial) explaination on 
how to read the query plans contained in these blog articles:

http://blog.parthenoncomputing.com/dbxml/archives/discussion/index.html

> We're concerned at the performance of the queries despite having indexed
> (we think) all the relevant XML components we query against. We have run
> timing benchmarks against various query scenarios using the following
> xquery:
> 
> collection("Sample.bdbxml")//AMP[SUPPCD="3144701000001104"]

You have a decimal index on the SUPPCD element, but you are querying it 
with a string value. In order to make use of the index, you will need to 
use a query like this:

collection("Sample.bdbxml")//AMP[SUPPCD=3144701000001104]

Alternatively you could use a string index instead - if you really want 
to do string matching.

With regards to your choice of indexes - I wouldn't bother to use any 
presence indexes at all. You certainly don't need a presence index on a 
node that already has an equality index on it, as equality indexes can 
double up as presence indexes when required. I would cut your index 
specification down to this:

1. addIndex "" APID node-element-equality-decimal
2. addIndex "" SUPPCD node-element-equality-decimal
3. addIndex "" NM node-element-substring-string
4. addIndex "" DESC node-element-substring-string

The more indexes you have - the slower it will be to update the database.

> ...and here are the results:
> 
> Test1: Single query run five times (waiting for previous to finish): 18,
> 5.6, 15, 26,
> 33 seconds. i.e. The results for the same query run on its own can take
> anything from 5 through to 33 seconds! Why such a difference? 

My guess would be that this is down to the cache size. If your cache 
size is too small, then the database will constantly be swapping pages 
off of disk, which is more than likely the reason for your variable times.

Finding the right size for the cache can be a process of experimentation 
- but I suggest you start large and reduce it. If you increase the cache 
size, you will need to remove the environment and recreate it.

> Test2: 5 concurrent queries: 67, 79.9, 79.8, 90, 87 seconds. i.e.
> Running 5 queries together slows all queries down considerably.
> 
> Test3: 10 concurrent queries: 72, 74, 79, 102, 115, 119, 130, 140.3,
> 140.1, 140.2 seconds. Slower still.

These test results are also indicative of a small cache. The concurrent 
queries are causing the pages needed by the other queries to be flushed 
from the cache, and interferring with each others progress.

> Test4: 20 concurrent queries: No results -
> com.sleepycat.bdbxml.XmlException: Uncaught exception from C++ API:
> Unknown error, errcode = INTERNAL_ERROR - for all threads except one
> which said: Error: BaseMemoryManager::allocate(): Out of memory, errcode
> = XPATH_EVALUATION_ERROR

The poor error messages have been fixed in the forthcoming 2.2 release, 
which should be out soon. However, the error itself could well be down 
to not calling delete() on the DB XML Java objects when you have 
finished using them. These blog entries will explain why this is important:

http://blog.parthenoncomputing.com/dbxml/archives/2005/01/faq_why_do_i_ge.html
http://blog.parthenoncomputing.com/dbxml/archives/2005/02/faq_what_are_th_1.html

> Can anyone help or offer advice on why this might be happening? Is there
> any way to determine the optimal indexes or something similar to
> optimize query performance?

The blog entries above should give you some help as to what indexes to use.

I hope this helps you out,

John

-- 
John Snelson, Berkeley DB XML Engineer
Sleepycat Software, Inc
http://www.sleepycat.com

Contracted to Sleepycat through Parthenon Computing Ltd
http://blog.parthcomp.com/dbxml


------------------------------------------
To remove yourself from this list, send an
email to [email protected]