13 Replies Latest reply: Jun 13, 2012 4:27 PM by Laurenfoutz-Oracle RSS

    Performance degradation

    761557
      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-Oracle
          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
            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
              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
                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-Oracle
                  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
                    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
                      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-Oracle
                        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
                          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
                            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
                              Thanks for the patch Lauren, the issue appears to be resolved.
                              • 12. Re: Performance degradation
                                Lucas Vogel
                                Lauren,

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

                                Thanks!
                                • 13. Re: Performance degradation
                                  Laurenfoutz-Oracle
                                  Unfortunately we are not allowed to post patches anymore, but the new release should be out very soon.

                                  Lauren