This discussion is archived
13 Replies Latest reply: Jun 13, 2012 2:27 PM by LaurenFoutz RSS

Performance degradation

761557 Newbie
Currently Being Moderated
Hello,

I'm experiencing gradual query performance on a container that has rapid insert/deletions. It seems related to indexes as a reindex of a container solves it. See the output of my dbxml client session below. Of particular note is the index statistics values.
dbxml> open test.dbxml

dbxml> lookupStats edge-element-equality-dateTime '' starttime
Number of Indexed Keys: 1.07939e+08
Number of Unique Keys:  8.37247e+07
Sum Key Value Size:     -1.40897e+09


dbxml> lookupStats unique-node-metadata-equality-string 'http://www.sleepycat.com/2002/dbxml' name
Number of Indexed Keys: 1.07939e+08
Number of Unique Keys:  1.07939e+08
Sum Key Value Size:     -2.03354e+09


dbxml> cquery "/*"
10096 objects returned for eager expression '/*'

dbxml> time query "for $let in collection('test.dbxml')/* order by $let/starttime return $let"
10096 objects returned for eager expression 'for $let in collection('test.dbxml')/* order by $let/starttime return $let'

Time in seconds for command 'query': 354.986

quit/load dbxml again

dbxml> reindexContainer test.dbxml
Container reindexed: test.dbxml

dbxml> open test.dbxml

dbxml> lookupStats unique-node-metadata-equality-string 'http://www.sleepycat.com/2002/dbxml' name
Number of Indexed Keys: 10096
Number of Unique Keys:  10096
Sum Key Value Size:     222112

dbxml> lookupStats edge-element-equality-dateTime '' starttime
Number of Indexed Keys: 10096
Number of Unique Keys:  7242
Sum Key Value Size:     272592

dbxml> time query "for $let in collection('test.dbxml')/* order by $let/starttime return $let"
10096 objects returned for eager expression 'for $let in collection('test.dbxml')/* order by $let/starttime return $let'

Time in seconds for command 'query': 1.09906
I'm pretty sure that I shouldn't have to run reindexContainer to fix this problem. Anyone have any insights?
  • 1. Re: Performance degradation
    LaurenFoutz Journeyer
    Currently Being Moderated
    That certainly should not be happening. Can you post more information on your application? What version of dbxml, what platform, what type of container (node or whole). The more information you can give the better I can help you.

    It would also be very helpful if you could post the program that did all the inserts and deletes.

    Lauren Foutz
  • 2. Re: Performance degradation
    761557 Newbie
    Currently Being Moderated
    Hi Lauren,

    This is running on 32-bit x86 Red Hat Enterprise 5. DBXML is version 2.5.16, and the container is a node container. See details below:
    dbxml> info
    Version: Oracle: Berkeley DB XML 2.5.16: (December 22, 2009)
             Berkeley DB 4.8.26: (December 18, 2009)
    Default container name: test.dbxml
    Type of default container: NodeContainer
    Index Nodes: on
    Auto-indexing: off
    Shell and XmlManager state:
         Transactional, no active transaction
         Verbose: on
         Query context state: LiveValues,Eager
    I'm attempting to isolate the problem to a simple test case, I'll repost when I have something.
  • 3. Re: Performance degradation
    761557 Newbie
    Currently Being Moderated
    Hi Lauren,

    Here is a Python test case I have developed that appears to exhibit the same behavior:
    from dbxml import *
    from bsddb3.db import *
    import shutil
    import os
    
    try:
        shutil.rmtree('/tmp/dbxml')
    except OSError:
        pass
    
    os.mkdir('/tmp/dbxml')
    
    env = DBEnv()
    env.open('/tmp/dbxml', DB_CREATE|DB_INIT_LOCK|DB_INIT_LOG|DB_INIT_MPOOL|DB_INIT_TXN|DB_RECOVER|DB_THREAD)
    
    mgr = XmlManager(env, 0)
    
    config = XmlContainerConfig()
    config.setAllowCreate(True)
    
    txn = mgr.createTransaction()
    container = mgr.openContainer(txn, 'testing.dbxml', config)
    txn.commit()
    
    # lookupStats unique-node-metadata-equality-string http://www.sleepycat.com/2002/dbxml name
    
    for n in range(2):
        txn = mgr.createTransaction()
        uc = mgr.createUpdateContext()
        for n in range(100):
            container.putDocument(txn, 'test', '<xml/>', uc, DBXML_GEN_NAME)
        qc = mgr.createQueryContext()
        names = list(mgr.query(txn, '''for $a in collection('testing.dbxml')/* order by $a/beginTime return $a/dbxml:metadata('dbxml:name')''', qc))
        print "Number of committed documents: %d" % len(names)
        txn.commit()
    
        txn = mgr.createTransaction()
        names = [value.asString() for value in mgr.query(txn, '''for $a in collection('testing.dbxml')/* order by $a/beginTime return $a/dbxml:metadata('dbxml:name')''', qc)]
        txn.commit()
    
        txn = mgr.createTransaction()
        uc = mgr.createUpdateContext()
        for name in names:
            container.deleteDocument(txn, name, uc)
        txn.commit()
    
        txn = mgr.createTransaction()
        names = list(mgr.query(txn, '''for $a in collection('testing.dbxml')/* order by $a/beginTime return $a/dbxml:metadata('dbxml:name')''', qc))
        txn.commit()
        print "Number of documents after delete: %d" % len(names)
    
    
    stats = container.lookupStatistics('http://www.sleepycat.com/2002/dbxml', 'name', 'unique-node-metadata-equality-string')
    print "Number of final documents: %d" % len(names)
    print "Number of indexed keys: %d" % stats.getNumberOfIndexedKeys()
    This is the output I get from this test:
    Number of committed documents: 100
    Number of documents after delete: 0
    Number of committed documents: 100
    Number of documents after delete: 0
    Number of final documents: 0
    Number of indexed keys: 200
    Edited by: jddbxml on 02-May-2012 16:18
  • 4. Re: Performance degradation
    637288 Journeyer
    Currently Being Moderated
    What version of DB XML do you use?
    The older versions of DB XML had this problem, when doing many XQuery Updates.

    Vyacheslav
  • 5. Re: Performance degradation
    LaurenFoutz Journeyer
    Currently Being Moderated
    Thank you, that information will be very useful.

    I will try to get back to you next week with more information on this bug.

    Lauren Foutz
  • 6. Re: Performance degradation
    761557 Newbie
    Currently Being Moderated
    detonator413 wrote:
    What version of DB XML do you use?
    The older versions of DB XML had this problem, when doing many XQuery Updates.

    Vyacheslav
    Hi Vyacheslav,

    I am running:
    Version: Oracle: Berkeley DB XML 2.5.16: (December 22, 2009)
             Berkeley DB 4.8.26: (December 18, 2009)
  • 7. Re: Performance degradation
    637288 Journeyer
    Currently Being Moderated
    Hi,

    sorry I've missed you previous message somehow.

    The problem seems unrelated to what I have experienced before.
    Probably Lauren can help you more here. Would be just interesting whether Java/C++ API has the same issues

    Vyacheslav
  • 8. Re: Performance degradation
    LaurenFoutz Journeyer
    Currently Being Moderated
    Good news is that this bug has already been fixed in our next release. You can wait for the new release, or I can email you a patch. If you want the patch contact me at lauren dot foutz at thiscompany .

    Lauren Foutz
  • 9. Re: Performance degradation
    761557 Newbie
    Currently Being Moderated
    It appears so, here is an identical C++ test case:
    #include <stdio.h>
    #include <db_cxx.h>
    #include <vector>
    #include <string>
    #include <dbxml/XmlManager.hpp>
    #include <dbxml/XmlContainerConfig.hpp>
    #include <dbxml/XmlTransaction.hpp>
    #include <dbxml/XmlContainer.hpp>
    #include <dbxml/XmlUpdateContext.hpp>
    #include <dbxml/XmlQueryContext.hpp>
    #include <dbxml/XmlResults.hpp>
    #include <dbxml/XmlStatistics.hpp>
    
    int main(void)
    {
        DbEnv env(0);
        env.open("/tmp/dbxml", DB_CREATE|DB_INIT_LOCK|DB_INIT_LOG|DB_INIT_MPOOL|DB_INIT_TXN|DB_RECOVER|DB_THREAD, 0);
    
        DbXml::XmlManager mgr(env.get_DB_ENV(), 0);
    
        DbXml::XmlContainerConfig config;
        config.setAllowCreate(true);
    
        DbXml::XmlTransaction txn = mgr.createTransaction();
        DbXml::XmlContainer container = mgr.openContainer(txn, "testing.dbxml", config);
        txn.commit();
    
        for (int i=0; i < 2; i++)
        {
            txn = mgr.createTransaction();
            DbXml::XmlUpdateContext uc = mgr.createUpdateContext();
            for (int j=0; j < 100; j++)
            {
                container.putDocument(txn, "test", "<xml/>", uc, DbXml::DBXML_GEN_NAME);
            }
            DbXml::XmlQueryContext qc = mgr.createQueryContext();
            DbXml::XmlResults res = mgr.query(txn, "for $a in collection('testing.dbxml')/* order by $a/beginTime return $a/dbxml:metadata('dbxml:name')", qc);
            std::vector<std::string> docNames;
            while(res.hasNext())
            {
                DbXml::XmlValue val;
                res.next(val);
                docNames.push_back(val.asString());
            }
            printf("Number of committed documents: %d\n", docNames.size());
            txn.commit();
    
            txn = mgr.createTransaction();
            std::vector<std::string>::iterator it;
            for (it=docNames.begin(); it < docNames.end(); it++)
            {
                container.deleteDocument(txn, *it, uc);
            }
            txn.commit();
    
    
            txn = mgr.createTransaction();
            res = mgr.query(txn, "for $a in collection('testing.dbxml')/* order by $a/beginTime return $a/dbxml:metadata('dbxml:name')", qc);
            int count = 0;
            while(res.hasNext())
            {
                count++;
                DbXml::XmlValue val;
                res.next(val);
            }
            printf("Number of documents after commit: %d\n", count);
            txn.commit();
        }
    
        txn = mgr.createTransaction();
        DbXml::XmlValue xmlvalue;
        DbXml::XmlStatistics stats = container.lookupStatistics(txn, "http://www.sleepycat.com/2002/dbxml", "name", "unique-node-metadata-equality-string", xmlvalue);
        txn.commit();
        printf("Number of indexed keys: %.1f\n", stats.getNumberOfIndexedKeys());
    
    
        return 0;
    }
    and output:
    Number of committed documents: 100
    Number of documents after commit: 0
    Number of committed documents: 100
    Number of documents after commit: 0
    Number of indexed keys: 200.0
  • 10. Re: Performance degradation
    761557 Newbie
    Currently Being Moderated
    LaurenFoutz wrote:
    Good news is that this bug has already been fixed in our next release. You can wait for the new release, or I can email you a patch. If you want the patch contact me at lauren dot foutz at thiscompany .

    Lauren Foutz
    Great, I will email you.
  • 11. Re: Performance degradation
    761557 Newbie
    Currently Being Moderated
    Thanks for the patch Lauren, the issue appears to be resolved.
  • 12. Re: Performance degradation
    Lucas Vogel Newbie
    Currently Being Moderated
    Lauren,

    Any way we can get the patch posted? Or will the new version be coming out soon?

    Thanks!
  • 13. Re: Performance degradation
    LaurenFoutz Journeyer
    Currently Being Moderated
    Unfortunately we are not allowed to post patches anymore, but the new release should be out very soon.

    Lauren

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points