1<?xml version="1.0" encoding="UTF-8" standalone="no"?> 2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 3<html xmlns="http://www.w3.org/1999/xhtml"> 4 <head> 5 <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /> 6 <title>Access method tuning</title> 7 <link rel="stylesheet" href="gettingStarted.css" type="text/css" /> 8 <meta name="generator" content="DocBook XSL Stylesheets V1.73.2" /> 9 <link rel="start" href="index.html" title="Berkeley DB Programmer's Reference Guide" /> 10 <link rel="up" href="am_misc.html" title="Chapter��4.�� Access Method Wrapup" /> 11 <link rel="prev" href="am_misc_db_sql.html" title="Specifying a Berkeley DB schema using SQL DDL" /> 12 <link rel="next" href="am_misc_faq.html" title="Access method FAQ" /> 13 </head> 14 <body> 15 <div class="navheader"> 16 <table width="100%" summary="Navigation header"> 17 <tr> 18 <th colspan="3" align="center">Access method tuning</th> 19 </tr> 20 <tr> 21 <td width="20%" align="left"><a accesskey="p" href="am_misc_db_sql.html">Prev</a>��</td> 22 <th width="60%" align="center">Chapter��4.�� 23 Access Method Wrapup 24 </th> 25 <td width="20%" align="right">��<a accesskey="n" href="am_misc_faq.html">Next</a></td> 26 </tr> 27 </table> 28 <hr /> 29 </div> 30 <div class="sect1" lang="en" xml:lang="en"> 31 <div class="titlepage"> 32 <div> 33 <div> 34 <h2 class="title" style="clear: both"><a id="am_misc_tune"></a>Access method tuning</h2> 35 </div> 36 </div> 37 </div> 38 <p>There are a few different issues to consider when tuning the performance 39of Berkeley DB access method applications.</p> 40 <div class="variablelist"> 41 <dl> 42 <dt> 43 <span class="term">access method</span> 44 </dt> 45 <dd>An application's choice of a database access method can significantly 46affect performance. Applications using fixed-length records and integer 47keys are likely to get better performance from the Queue access method. 48Applications using variable-length records are likely to get better 49performance from the Btree access method, as it tends to be faster for 50most applications than either the Hash or Recno access methods. Because 51the access method APIs are largely identical between the Berkeley DB access 52methods, it is easy for applications to benchmark the different access 53methods against each other. See <a class="xref" href="am_conf_select.html" title="Selecting an access method">Selecting an access method</a> for more information.</dd> 54 <dt> 55 <span class="term">cache size</span> 56 </dt> 57 <dd>The Berkeley DB database cache defaults to a fairly small size, and most 58applications concerned with performance will want to set it explicitly. 59Using a too-small cache will result in horrible performance. The first 60step in tuning the cache size is to use the db_stat utility (or the 61statistics returned by the <a href="../api_reference/C/dbstat.html" class="olink">DB->stat()</a> function) to measure the 62effectiveness of the cache. The goal is to maximize the cache's hit 63rate. Typically, increasing the size of the cache until the hit rate 64reaches 100% or levels off will yield the best performance. However, 65if your working set is sufficiently large, you will be limited by the 66system's available physical memory. Depending on the virtual memory 67and file system buffering policies of your system, and the requirements 68of other applications, the maximum cache size will be some amount 69smaller than the size of physical memory. If you find that 70the <a href="../api_reference/C/db_stat.html" class="olink">db_stat utility</a> shows that increasing the cache size improves your hit 71rate, but performance is not improving (or is getting worse), then it's 72likely you've hit other system limitations. At this point, you should 73review the system's swapping/paging activity and limit the size of the 74cache to the maximum size possible without triggering paging activity. 75Finally, always remember to make your measurements under conditions as 76close as possible to the conditions your deployed application will run 77under, and to test your final choices under worst-case conditions.</dd> 78 <dt> 79 <span class="term">shared memory</span> 80 </dt> 81 <dd>By default, Berkeley DB creates its database environment shared regions in 82filesystem backed memory. Some systems do not distinguish between 83regular filesystem pages and memory-mapped pages backed by the 84filesystem, when selecting dirty pages to be flushed back to disk. For 85this reason, dirtying pages in the Berkeley DB cache may cause intense 86filesystem activity, typically when the filesystem sync thread or 87process is run. In some cases, this can dramatically affect application 88throughput. The workaround to this problem is to create the shared 89regions in system shared memory (<a href="../api_reference/C/envopen.html#envopen_DB_SYSTEM_MEM" class="olink">DB_SYSTEM_MEM</a>) or application 90private memory (<a href="../api_reference/C/envopen.html#open_DB_PRIVATE" class="olink">DB_PRIVATE</a>), or, in cases where this behavior 91is configurable, to turn off the operating system's flushing of 92memory-mapped pages.</dd> 93 <dt> 94 <span class="term">large key/data items</span> 95 </dt> 96 <dd>Storing large key/data items in a database can alter the performance 97characteristics of Btree, Hash and Recno databases. The first parameter 98to consider is the database page size. When a key/data item is too 99large to be placed on a database page, it is stored on "overflow" pages 100that are maintained outside of the normal database structure (typically, 101items that are larger than one-quarter of the page size are deemed to 102be too large). Accessing these overflow pages requires at least one 103additional page reference over a normal access, so it is usually better 104to increase the page size than to create a database with a large number 105of overflow pages. Use the <a href="../api_reference/C/db_stat.html" class="olink">db_stat utility</a> (or the statistics 106returned by the <a href="../api_reference/C/dbstat.html" class="olink">DB->stat()</a> method) to review the number of overflow 107pages in the database. 108<p>The second issue is using large key/data items instead of duplicate data 109items. While this can offer performance gains to some applications 110(because it is possible to retrieve several data items in a single get 111call), once the key/data items are large enough to be pushed off-page, 112they will slow the application down. Using duplicate data items is 113usually the better choice in the long run.</p></dd> 114 </dl> 115 </div> 116 <p>A common question when tuning Berkeley DB applications is scalability. For 117example, people will ask why, when adding additional threads or 118processes to an application, the overall database throughput decreases, 119even when all of the operations are read-only queries.</p> 120 <p>First, while read-only operations are logically concurrent, they still 121have to acquire mutexes on internal Berkeley DB data structures. For example, 122when searching a linked list and looking for a database page, the linked 123list has to be locked against other threads of control attempting to add 124or remove pages from the linked list. The more threads of control you 125add, the more contention there will be for those shared data structure 126resources.</p> 127 <p>Second, once contention starts happening, applications will also start 128to see threads of control convoy behind locks (especially on 129architectures supporting only test-and-set spin mutexes, rather than 130blocking mutexes). On test-and-set architectures, threads of control 131waiting for locks must attempt to acquire the mutex, sleep, check the 132mutex again, and so on. Each failed check of the mutex and subsequent 133sleep wastes CPU and decreases the overall throughput of the system.</p> 134 <p>Third, every time a thread acquires a shared mutex, it has to shoot down 135other references to that memory in every other CPU on the system. Many 136modern snoopy cache architectures have slow shoot down characteristics.</p> 137 <p>Fourth, schedulers don't care what application-specific mutexes a thread 138of control might hold when de-scheduling a thread. If a thread of 139control is descheduled while holding a shared data structure mutex, 140other threads of control will be blocked until the scheduler decides to 141run the blocking thread of control again. The more threads of control 142that are running, the smaller their quanta of CPU time, and the more 143likely they will be descheduled while holding a Berkeley DB mutex.</p> 144 <p>The results of adding new threads of control to an application, on the 145application's throughput, is application and hardware specific and 146almost entirely dependent on the application's data access pattern and 147hardware. In general, using operating systems that support blocking 148mutexes will often make a tremendous difference, and limiting threads 149of control to to some small multiple of the number of CPUs is usually 150the right choice to make.</p> 151 </div> 152 <div class="navfooter"> 153 <hr /> 154 <table width="100%" summary="Navigation footer"> 155 <tr> 156 <td width="40%" align="left"><a accesskey="p" href="am_misc_db_sql.html">Prev</a>��</td> 157 <td width="20%" align="center"> 158 <a accesskey="u" href="am_misc.html">Up</a> 159 </td> 160 <td width="40%" align="right">��<a accesskey="n" href="am_misc_faq.html">Next</a></td> 161 </tr> 162 <tr> 163 <td width="40%" align="left" valign="top">Specifying a Berkeley DB schema using SQL DDL��</td> 164 <td width="20%" align="center"> 165 <a accesskey="h" href="index.html">Home</a> 166 </td> 167 <td width="40%" align="right" valign="top">��Access method FAQ</td> 168 </tr> 169 </table> 170 </div> 171 </body> 172</html> 173