4 Replies Latest reply: Nov 8, 2012 8:26 PM by 727787 RSS

    Upgrade from 2.4.16 to 2.5.16 downgrade the performance for putDocument

    727787
      I recently migrated my software from Window XP (32bits) to Window 7 (64bits) and upgrade the dbxml version from 2.4.16 to 2.5.16. I noticed that the the performance for the putDocument command is downgraded from 0.5-1s to 3-4s. It's like 5 times slower than before.

      Since I insert over 100 document into the dbxml, it really significantly downgrade the performance of my software. I wonder if anyone also experience that? Is there any way to tune the dbxml container or environment to improve the performance of the putdocument command?

      Thank you!
        • 1. Re: Upgrade from 2.4.16 to 2.5.16 downgrade the performance for putDocument
          Lucas Vogel
          In order to give you any kind of answer, you're going to have to provide a lot more detail. For starters, what does your environment/configuration look like? See the "Questions to answer when investigating Berkeley DB XML Performance" link at the top of the forum for the details to provide. You might want to put together a table of "old vs new" for better comparison. Knowing what language you're doing all of this in (or if you're simply using the dbxml command prompt) is also helpful.

          Regards,
          Lucas
          • 2. Re: Upgrade from 2.4.16 to 2.5.16 downgrade the performance for putDocument
            727787
            Thank you for the reply. Here is more details about my question.

            1. I'm looking at the putDocument command performance. On the same computer (window 7 64bit), I create the container with my C++ software, and executed the putDocument command for a 4MB file on the shell, the performance is downgraded from 0.5-1s to 3-4s after the upgrade of DBXML version from 2.4.16.1.17393 (was built on a window xp 32bit) to 2.5.16 (was built on a window 7 64bit). Ideally, I would like to ensure the performance of the putDocument command to be the same before and after the update.

            2. DBXML version 2.5.16. The containers created with DBXML version 2.4.16.1.17393 and 2.5.16 were created with the same flags

            DB_ENV::set_cachesize 0 128MB 8
            DB_ENV::set_tx_max 500
            DB_ENV::set_lg_regionmax 1MB
            DB_ENV::set_lg_max 100MB
            DB_ENV::set_lk_max_objects 76800
            DB_ENV::set_lk_max_lockers 2000
            DB_ENV::set_lk_max_locks 76800
            DB_ENV::open DB_INIT_MPOOL | DB_CREATE | DB_INIT_LOCK | DB_INIT_TXN | DB_RECOVER | DB_REGISTER

            except that the follow flag is added the container created with DBXML version 2.5.16

            DB_ENV::log_set_config DB_LOG_AUTO_REMOVE

            4. Processor: Intel(R) Core(TM2 Quad CPU Q6600 @ 2.40GHz 2.39 GHz

            5. Window 7

            6. SSD Drive

            7. File System Type: NTFS

            8. Physical Memory Available: 4GB

            9. No

            10. No

            11. Single thread only

            12. API: C++
            Compiler: VS 2010

            13. No

            14. See 2.

            15. See 2.

            16. Number of XML Containers: 1

            1. The Container Configuration Flags: See 2.

            2. How many documents? about 100

            3. What type (node or wholedoc)? node

            4. minimum size of document: 1KB
            maximum size of document: 9MB
            average size of documents: 4MB

            5. No

            17. Where should I send the XML document?


            18. N/A

            19. N/A

            20. N/A


            21. Yes, my software is running with Transaction, but the I also test the putDocument on the shell without transaction, the same behaivor is observed.


            22. yes, the log files are on the same disk as the containers

            23. Do you use AUTO_COMMIT? No

            24. Please list any non-transactional operations performed? No

            25. How many threads of control are running? Single thread

            26. See 1

            27. See 1

            28. Please send us the output of the following db_stat utility commands

            -------------------------------------------------------------------------------
            db_stat.exe" -c
            -------------------------------------------------------------------------------
            328 Last allocated locker ID
            0x7fffffff Current maximum unused locker ID
            9 Number of lock modes
            76800 Maximum number of locks possible
            2000 Maximum number of lockers possible
            76800 Maximum number of lock objects possible
            40 Number of lock object partitions
            0 Number of current locks
            18419 Maximum number of locks at any one time
            9 Maximum number of locks in any one bucket
            0 Maximum number of locks stolen by for an empty partition
            0 Maximum number of locks stolen for any one partition
            0 Number of current lockers
            67 Maximum number of lockers at any one time
            0 Number of current lock objects
            10281 Maximum number of lock objects at any one time
            3 Maximum number of lock objects in any one bucket
            0 Maximum number of objects stolen by for an empty partition
            0 Maximum number of objects stolen for any one partition
            86M Total number of locks requested (86159618)
            86M Total number of locks released (86159618)
            0 Total number of locks upgraded
            382 Total number of locks downgraded
            0 Lock requests not available due to conflicts, for which we waited
            0 Lock requests not available due to conflicts, for which we did not wait
            0 Number of deadlocks
            0 Lock timeout value
            0 Number of locks that have timed out
            0 Transaction timeout value
            0 Number of transactions that have timed out
            45MB 328KB The size of the lock region
            0 The number of partition locks that required waiting (0%)
            0 The maximum number of times any partition lock was waited for (0%)
            0 The number of object queue operations that required waiting (0%)
            0 The number of locker allocations that required waiting (0%)
            0 The number of region locks that required waiting (0%)
            3 Maximum hash bucket length
            -------------------------------------------------------------------------------
            db_stat.exe" -l
            -------------------------------------------------------------------------------
            0x40988 Log magic number
            16 Log version number
            31KB 256B Log record cache size
            0 Log file mode
            100Mb Current log file size
            83M Records entered into the log (83640159)
            6GB 872MB 588KB 382B Log bytes written
            0 Log bytes written since last checkpoint
            230182 Total log file I/O writes
            229617 Total log file I/O writes due to overflow
            762 Total log file flushes
            6 Total log file I/O reads
            71 Current log file number
            17413767 Current log file offset
            71 On-disk log file number
            17413767 On-disk log file offset
            1 Maximum commits in a log flush
            1 Minimum commits in a log flush
            1MB 32KB Log region size
            0 The number of region locks that required waiting (0%)
            -------------------------------------------------------------------------------
            db_stat.exe" -m
            -------------------------------------------------------------------------------
            160MB 2KB 24B Total cache size
            8 Number of caches
            8 Maximum number of caches
            20MB 8KB Pool individual cache size
            0 Maximum memory-mapped file size
            0 Maximum open file descriptors
            0 Maximum sequential buffer writes
            0 Sleep after writing maximum sequential buffers
            0 Requested pages mapped into the process' address space
            354M Requested pages found in the cache (99%)
            259849 Requested pages not found in the cache
            168230 Pages created in the cache
            259849 Pages read into the cache
            243853 Pages written from the cache to the backing file
            177907 Clean pages forced from the cache
            230563 Dirty pages forced from the cache
            0 Dirty pages written by trickle-sync thread
            19575 Current total page count
            19575 Current clean page count
            0 Current dirty page count
            16424 Number of hash buckets used for page location
            4096 Assumed page size used
            354M Total number of times hash chains searched for a page (354625726)
            47 The longest hash chain searched for a page
            597M Total number of hash chain entries checked for page (597850761)
            0 The number of hash bucket locks that required waiting (0%)
            0 The maximum number of times any hash bucket lock was waited for (0%)
            0 The number of region locks that required waiting (0%)
            0 The number of buffers frozen
            0 The number of buffers thawed
            0 The number of frozen buffers freed
            428112 The number of page allocations
            988902 The number of hash buckets examined during allocations
            245 The maximum number of hash buckets examined for an allocation
            408470 The number of pages examined during allocations
            96 The max number of pages examined for an allocation
            0 Threads waited on page I/O
            0 The number of times a sync is interrupted
            Pool File: SharedSegment.dbxml
            8192 Page size
            0 Requested pages mapped into the process' address space
            356 Requested pages found in the cache (99%)
            2 Requested pages not found in the cache
            22 Pages created in the cache
            2 Pages read into the cache
            27 Pages written from the cache to the backing file
            Pool File: AttachmentMetadata.dbxml
            8192 Page size
            0 Requested pages mapped into the process' address space
            419 Requested pages found in the cache (94%)
            24 Requested pages not found in the cache
            0 Pages created in the cache
            24 Pages read into the cache
            2 Pages written from the cache to the backing file
            Pool File: InstrumentProcedure.dbxml
            16384 Page size
            0 Requested pages mapped into the process' address space
            2996 Requested pages found in the cache (99%)
            2 Requested pages not found in the cache
            26 Pages created in the cache
            2 Pages read into the cache
            31 Pages written from the cache to the backing file
            Pool File: Aeronautical.dbxml
            8192 Page size
            0 Requested pages mapped into the process' address space
            354M Requested pages found in the cache (99%)
            259793 Requested pages not found in the cache
            168114 Pages created in the cache
            259793 Pages read into the cache
            243729 Pages written from the cache to the backing file
            Pool File: AeronauticalOriginal.dbxml
            8192 Page size
            0 Requested pages mapped into the process' address space
            894 Requested pages found in the cache (99%)
            2 Requested pages not found in the cache
            42 Pages created in the cache
            2 Pages read into the cache
            32 Pages written from the cache to the backing file
            -------------------------------------------------------------------------------
            db_stat.exe -r
            -------------------------------------------------------------------------------
            db_stat.exe: DB_ENV->repmgr_stat_print interface requires an environment configu
            red for the replication subsystem
            -------------------------------------------------------------------------------
            db_stat.exe -t
            -------------------------------------------------------------------------------
            71/17413711 File/offset for last checkpoint LSN
            Wed Nov 07 16:04:44 2012 Checkpoint timestamp
            0x8000083f Last transaction ID allocated
            500 Maximum number of active transactions configured
            0 Active transactions
            3 Maximum active transactions
            2111 Number of transactions begun
            8 Number of transactions aborted
            2103 Number of transactions committed
            0 Snapshot transactions
            0 Maximum snapshot transactions
            0 Number of transactions restored
            192KB Transaction region size
            0 The number of region locks that required waiting (0%)
            Active transactions:
            -------------------------------------------------------------------------------

            29. No.
            • 3. Re: Upgrade from 2.4.16 to 2.5.16 downgrade the performance for putDocument
              Lucas Vogel
              Thanks for posting this information. Have you upgraded your software to use the new constructs, etc. in the new version? There were a couple of things that changed.

              Here are a couple of things you might want to try:

              - DB_PRIVATE or DB_SHARED_MEM. The Windows version uses filesystem-backed regions unless you explicitly tell it not to. From the documentation:
              ...Alternatively, by not specifying the DB_SYSTEM_MEM flag, filesystem-backed regions will be created instead, and the permissions on those files may be directly specified through the DB_ENV->open() method.
              - DB_TXN_WRITE_NOSYNC and/or DB_TXN_NOSYNC. Using these flags will boost I/O performance but you'll lose a little bit of the D (durability) in your ACID transaction. (http://stackoverflow.com/questions/3825022/optimizing-put-performance-in-berkeley-db)

              - If you're more concerned with throughput than query performance, change your container type to document instead of node and turn off indexing.

              - If you're going to eventually do extensive querying of the data in your documents, you may want to consider XML database normalization in your application. In other words, trying to break down the documents into 'normalized' nodes (typically the most frequently used element) and insert them that way. The simpler the XML, the better all-around performance you should generally get, with respect to both insertions and queries.

              Let me know if this helps!
              Lucas
              • 4. Re: Upgrade from 2.4.16 to 2.5.16 downgrade the performance for putDocument
                727787
                Thank you for the suggestion.

                I upgraded to use the new DB_ENV structure and changed a couple of API call for that, but I'm not sure if I upgraded everything. Is there a list that I can double check?

                DB_PRIVATE
                I added it to the flags for DB_ENV::open(), but it return error EINVAL. Do you have any idea about it?

                DB_SHARED_MEM DB_TXN_WRITE_NOSYNC DB_TXN_NOSYNC
                Tired the above flags, but they doesn't really help for the putDocument.

                Since my software also execute a number of queries against the DBXML, so I can't change the document type either.

                XML database normalization
                Yes, my XML document is pretty complicated, so I can't try this within a short time. Will keep this in mind in the future.

                Again, thank you!