This discussion is archived
4 Replies Latest reply: Nov 8, 2012 6:26 PM by 727787 RSS

Upgrade from 2.4.16 to 2.5.16 downgrade the performance for putDocument

727787 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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!

Legend

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