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>Chapter 6. Database Configuration</title> 7 <link rel="stylesheet" href="gettingStarted.css" type="text/css" /> 8 <meta name="generator" content="DocBook XSL Stylesheets V1.62.4" /> 9 <link rel="home" href="index.html" title="Getting Started with Berkeley DB" /> 10 <link rel="up" href="index.html" title="Getting Started with Berkeley DB" /> 11 <link rel="previous" href="coreindexusage.html" title="Secondary Database Example" /> 12 <link rel="next" href="cachesize.html" title="Selecting the Cache Size" /> 13 </head> 14 <body> 15 <div class="navheader"> 16 <table width="100%" summary="Navigation header"> 17 <tr> 18 <th colspan="3" align="center">Chapter 6. Database Configuration</th> 19 </tr> 20 <tr> 21 <td width="20%" align="left"><a accesskey="p" href="coreindexusage.html">Prev</a> </td> 22 <th width="60%" align="center"> </th> 23 <td width="20%" align="right"> <a accesskey="n" href="cachesize.html">Next</a></td> 24 </tr> 25 </table> 26 <hr /> 27 </div> 28 <div class="chapter" lang="en" xml:lang="en"> 29 <div class="titlepage"> 30 <div> 31 <div> 32 <h2 class="title"><a id="dbconfig"></a>Chapter 6. Database Configuration</h2> 33 </div> 34 </div> 35 <div></div> 36 </div> 37 <div class="toc"> 38 <p> 39 <b>Table of Contents</b> 40 </p> 41 <dl> 42 <dt> 43 <span class="sect1"> 44 <a href="dbconfig.html#pagesize">Setting the Page Size</a> 45 </span> 46 </dt> 47 <dd> 48 <dl> 49 <dt> 50 <span class="sect2"> 51 <a href="dbconfig.html#overflowpages">Overflow Pages</a> 52 </span> 53 </dt> 54 <dt> 55 <span class="sect2"> 56 <a href="dbconfig.html#Locking">Locking</a> 57 </span> 58 </dt> 59 <dt> 60 <span class="sect2"> 61 <a href="dbconfig.html#IOEfficiency">IO Efficiency</a> 62 </span> 63 </dt> 64 <dt> 65 <span class="sect2"> 66 <a href="dbconfig.html#pagesizeAdvice">Page Sizing Advice</a> 67 </span> 68 </dt> 69 </dl> 70 </dd> 71 <dt> 72 <span class="sect1"> 73 <a href="cachesize.html">Selecting the Cache Size</a> 74 </span> 75 </dt> 76 <dt> 77 <span class="sect1"> 78 <a href="btree.html">BTree Configuration</a> 79 </span> 80 </dt> 81 <dd> 82 <dl> 83 <dt> 84 <span class="sect2"> 85 <a href="btree.html#duplicateRecords">Allowing Duplicate Records</a> 86 </span> 87 </dt> 88 <dt> 89 <span class="sect2"> 90 <a href="btree.html#comparators">Setting Comparison Functions</a> 91 </span> 92 </dt> 93 </dl> 94 </dd> 95 </dl> 96 </div> 97 <p> 98 This chapter describes some of the database and cache configuration issues 99 that you need to consider when building your DB database. 100 In most cases, there is very little that you need to do in terms of 101 managing your databases. However, there are configuration issues that you 102 need to be concerned with, and these are largely dependent on the access 103 method that you are choosing for your database. 104 </p> 105 <p> 106 The examples and descriptions throughout this document have mostly focused 107 on the BTree access method. This is because the majority of DB 108 applications use BTree. For this reason, where configuration issues are 109 dependent on the type of access method in use, this chapter will focus on 110 BTree only. For configuration descriptions surrounding the other access 111 methods, see the <i class="citetitle">Berkeley DB Programmer's Reference Guide</i>. 112 </p> 113 <div class="sect1" lang="en" xml:lang="en"> 114 <div class="titlepage"> 115 <div> 116 <div> 117 <h2 class="title" style="clear: both"><a id="pagesize"></a>Setting the Page Size</h2> 118 </div> 119 </div> 120 <div></div> 121 </div> 122 <p> 123 Internally, DB stores database entries on pages. Page sizes are 124 important because they can affect your application's performance. 125 </p> 126 <p> 127 DB pages can be between 512 bytes and 64K bytes in size. The size 128 that you select must be a power of 2. You set your database's 129 page size using 130 <span><tt class="methodname">DB->set_pagesize()</tt>.</span> 131 132 133 </p> 134 <p> 135 Note that a database's page size can only be selected at database 136 creation time. 137 </p> 138 <p> 139 When selecting a page size, you should consider the following issues: 140 </p> 141 <div class="itemizedlist"> 142 <ul type="disc"> 143 <li> 144 <p> 145 Overflow pages. 146 </p> 147 </li> 148 <li> 149 <p> 150 Locking 151 </p> 152 </li> 153 <li> 154 <p> 155 Disk I/O. 156 </p> 157 </li> 158 </ul> 159 </div> 160 <p> 161 These topics are discussed next. 162 </p> 163 <div class="sect2" lang="en" xml:lang="en"> 164 <div class="titlepage"> 165 <div> 166 <div> 167 <h3 class="title"><a id="overflowpages"></a>Overflow Pages</h3> 168 </div> 169 </div> 170 <div></div> 171 </div> 172 <p> 173 Overflow pages are used to hold a key or data item 174 that cannot fit on a single page. You do not have to do anything to 175 cause overflow pages to be created, other than to store data that is 176 too large for your database's page size. Also, the only way you can 177 prevent overflow pages from being created is to be sure to select a 178 page size that is large enough to hold your database entries. 179 </p> 180 <p> 181 Because overflow pages exist outside of the normal database 182 structure, their use is expensive from a performance 183 perspective. If you select too small of a page size, then your 184 database will be forced to use an excessive number of overflow 185 pages. This will significantly harm your application's performance. 186 </p> 187 <p> 188 For this reason, you want to select a page size that is at 189 least large enough to hold multiple entries given the expected 190 average size of your database entries. In BTree's case, for best 191 results select a page size that can hold at least 4 such entries. 192 </p> 193 <p> 194 You can see how many overflow pages your database is using by 195 <span> 196 using the 197 <tt class="methodname">DB->stat()</tt> 198 199 method, 200 </span> 201 202 203 or by examining your database using the 204 <tt class="literal">db_stat</tt> command line utility. 205 </p> 206 </div> 207 <div class="sect2" lang="en" xml:lang="en"> 208 <div class="titlepage"> 209 <div> 210 <div> 211 <h3 class="title"><a id="Locking"></a>Locking</h3> 212 </div> 213 </div> 214 <div></div> 215 </div> 216 <p> 217 Locking and multi-threaded access to DB databases is built into 218 the product. However, in order to enable the locking subsystem and 219 in order to provide efficient sharing of the cache between 220 databases, you must use an <span class="emphasis"><em>environment</em></span>. 221 Environments and multi-threaded access are not fully described 222 in this manual (see the Berkeley DB Programmer's Reference Manual for 223 information), however, we provide some information on sizing your 224 pages in a multi-threaded/multi-process environment in the interest 225 of providing a complete discussion on the topic. 226 </p> 227 <p> 228 If your application is multi-threaded, or if your databases are 229 accessed by more than one process at a time, then page size can 230 influence your application's performance. The reason why is that 231 for most access methods (Queue is the exception), DB implements 232 page-level locking. This means that the finest locking granularity 233 is at the page, not at the record. 234 </p> 235 <p> 236 In most cases, database pages contain multiple database 237 records. Further, in order to provide safe access to multiple 238 threads or processes, DB performs locking on pages as entries on 239 those pages are read or written. 240 </p> 241 <p> 242 As the size of your page increases relative to the size of your 243 database entries, the number of entries that are held on any given 244 page also increase. The result is that the chances of two or more 245 readers and/or writers wanting to access entries on any given page 246 also increases. 247 </p> 248 <p> 249 When two or more threads and/or processes want to manage data on a 250 page, lock contention occurs. Lock contention is resolved by one 251 thread (or process) waiting for another thread to give up its lock. 252 It is this waiting activity that is harmful to your application's 253 performance. 254 </p> 255 <p> 256 It is possible to select a page size that is so large that your 257 application will spend excessive, and noticeable, amounts of time 258 resolving lock contention. Note that this scenario is particularly 259 likely to occur as the amount of concurrency built into your 260 application increases. 261 </p> 262 <p> 263 Oh the other hand, if you select too small of a page size, then that 264 that will only make your tree deeper, which can also cause 265 performance penalties. The trick, therefore, is to select a 266 reasonable page size (one that will hold a sizeable number of 267 records) and then reduce the page size if you notice lock 268 contention. 269 </p> 270 <p> 271 You can examine the number of lock conflicts and deadlocks occurring 272 in your application by examining your database environment lock 273 statistics. Either use the 274 <tt class="methodname">DB_ENV->lock_stat()</tt> 275 276 277 method, or use the <tt class="literal">db_stat</tt> command line utility. 278 The number of unavailable locks that your application waited for is 279 held in the lock statistic's <tt class="literal">st_lock_wait</tt> field. 280 281 </p> 282 </div> 283 <div class="sect2" lang="en" xml:lang="en"> 284 <div class="titlepage"> 285 <div> 286 <div> 287 <h3 class="title"><a id="IOEfficiency"></a>IO Efficiency</h3> 288 </div> 289 </div> 290 <div></div> 291 </div> 292 <p> 293 Page size can affect how efficient DB is at moving data to and 294 from disk. For some applications, especially those for which the 295 in-memory cache can not be large enough to hold the entire working 296 dataset, IO efficiency can significantly impact application performance. 297 </p> 298 <p> 299 Most operating systems use an internal block size to determine how much 300 data to move to and from disk for a single I/O operation. This block 301 size is usually equal to the filesystem's block size. For optimal 302 disk I/O efficiency, you should select a database page size that is 303 equal to the operating system's I/O block size. 304 </p> 305 <p> 306 Essentially, DB performs data transfers based on the database 307 page size. That is, it moves data to and from disk a page at a time. 308 For this reason, if the page size does not match the I/O block size, 309 then the operating system can introduce inefficiencies in how it 310 responds to DB's I/O requests. 311 </p> 312 <p> 313 For example, suppose your page size is smaller than your operating 314 system block size. In this case, when DB writes a page to disk 315 it is writing just a portion of a logical filesystem page. Any time 316 any application writes just a portion of a logical filesystem page, the 317 operating system brings in the real filesystem page, over writes 318 the portion of the page not written by the application, then writes 319 the filesystem page back to disk. The net result is significantly 320 more disk I/O than if the application had simply selected a page 321 size that was equal to the underlying filesystem block size. 322 </p> 323 <p> 324 Alternatively, if you select a page size that is larger than the 325 underlying filesystem block size, then the operating system may have 326 to read more data than is necessary to fulfill a read request. 327 Further, on some operating systems, requesting a single database 328 page may result in the operating system reading enough filesystem 329 blocks to satisfy the operating system's criteria for read-ahead. In 330 this case, the operating system will be reading significantly more 331 data from disk than is actually required to fulfill DB's read 332 request. 333 </p> 334 <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> 335 <h3 class="title">Note</h3> 336 <p> 337 While transactions are not discussed in this manual, a page size 338 other than your filesystem's block size can affect transactional 339 guarantees. The reason why is that page sizes larger than the 340 filesystem's block size causes DB to write pages in block 341 size increments. As a result, it is possible for a partial page 342 to be written as the result of a transactional commit. For more 343 information, see <a href="http://www.oracle.com/technology/documentation/berkeley-db/db/ref/transapp/reclimit.html" target="_top">http://www.oracle.com/technology/documentation/berkeley-db/db/ref/transapp/reclimit.html</a>. 344 </p> 345 </div> 346 </div> 347 <div class="sect2" lang="en" xml:lang="en"> 348 <div class="titlepage"> 349 <div> 350 <div> 351 <h3 class="title"><a id="pagesizeAdvice"></a>Page Sizing Advice</h3> 352 </div> 353 </div> 354 <div></div> 355 </div> 356 <p> 357 Page sizing can be confusing at first, so here are some general 358 guidelines that you can use to select your page size. 359 </p> 360 <p> 361 In general, and given no other considerations, a page size that is equal 362 to your filesystem block size is the ideal situation. 363 </p> 364 <p> 365 If your data is designed such that 4 database entries cannot fit on a 366 single page (assuming BTree), then grow your page size to accommodate 367 your data. Once you've abandoned matching your filesystem's block 368 size, the general rule is that larger page sizes are better. 369 </p> 370 <p> 371 The exception to this rule is if you have a great deal of 372 concurrency occurring in your application. In this case, the closer 373 you can match your page size to the ideal size needed for your 374 application's data, the better. Doing so will allow you to avoid 375 unnecessary contention for page locks. 376 </p> 377 </div> 378 </div> 379 </div> 380 <div class="navfooter"> 381 <hr /> 382 <table width="100%" summary="Navigation footer"> 383 <tr> 384 <td width="40%" align="left"><a accesskey="p" href="coreindexusage.html">Prev</a> </td> 385 <td width="20%" align="center"> 386 <a accesskey="u" href="index.html">Up</a> 387 </td> 388 <td width="40%" align="right"> <a accesskey="n" href="cachesize.html">Next</a></td> 389 </tr> 390 <tr> 391 <td width="40%" align="left" valign="top">Secondary Database Example </td> 392 <td width="20%" align="center"> 393 <a accesskey="h" href="index.html">Home</a> 394 </td> 395 <td width="40%" align="right" valign="top"> Selecting the Cache Size</td> 396 </tr> 397 </table> 398 </div> 399 </body> 400</html> 401