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 11. 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="baseapi.html" title="Part II. Programming with the Base API" /> 11 <link rel="previous" href="javaindexusage.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 11. Database Configuration</th> 19 </tr> 20 <tr> 21 <td width="20%" align="left"><a accesskey="p" href="javaindexusage.html">Prev</a> </td> 22 <th width="60%" align="center">Part II. Programming with the Base API</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 11. 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 131 132 <span><tt class="methodname">DatabaseConfig.setPageSize()</tt>.</span> 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 196 <span> 197 obtaining a <tt class="classname">DatabaseStats</tt> object using 198 the <tt class="methodname">Database.getStats()</tt> method, 199 </span> 200 201 or by examining your database using the 202 <tt class="literal">db_stat</tt> command line utility. 203 </p> 204 </div> 205 <div class="sect2" lang="en" xml:lang="en"> 206 <div class="titlepage"> 207 <div> 208 <div> 209 <h3 class="title"><a id="Locking"></a>Locking</h3> 210 </div> 211 </div> 212 <div></div> 213 </div> 214 <p> 215 Locking and multi-threaded access to DB databases is built into 216 the product. However, in order to enable the locking subsystem and 217 in order to provide efficient sharing of the cache between 218 databases, you must use an <span class="emphasis"><em>environment</em></span>. 219 Environments and multi-threaded access are not fully described 220 in this manual (see the Berkeley DB Programmer's Reference Manual for 221 information), however, we provide some information on sizing your 222 pages in a multi-threaded/multi-process environment in the interest 223 of providing a complete discussion on the topic. 224 </p> 225 <p> 226 If your application is multi-threaded, or if your databases are 227 accessed by more than one process at a time, then page size can 228 influence your application's performance. The reason why is that 229 for most access methods (Queue is the exception), DB implements 230 page-level locking. This means that the finest locking granularity 231 is at the page, not at the record. 232 </p> 233 <p> 234 In most cases, database pages contain multiple database 235 records. Further, in order to provide safe access to multiple 236 threads or processes, DB performs locking on pages as entries on 237 those pages are read or written. 238 </p> 239 <p> 240 As the size of your page increases relative to the size of your 241 database entries, the number of entries that are held on any given 242 page also increase. The result is that the chances of two or more 243 readers and/or writers wanting to access entries on any given page 244 also increases. 245 </p> 246 <p> 247 When two or more threads and/or processes want to manage data on a 248 page, lock contention occurs. Lock contention is resolved by one 249 thread (or process) waiting for another thread to give up its lock. 250 It is this waiting activity that is harmful to your application's 251 performance. 252 </p> 253 <p> 254 It is possible to select a page size that is so large that your 255 application will spend excessive, and noticeable, amounts of time 256 resolving lock contention. Note that this scenario is particularly 257 likely to occur as the amount of concurrency built into your 258 application increases. 259 </p> 260 <p> 261 Oh the other hand, if you select too small of a page size, then that 262 that will only make your tree deeper, which can also cause 263 performance penalties. The trick, therefore, is to select a 264 reasonable page size (one that will hold a sizeable number of 265 records) and then reduce the page size if you notice lock 266 contention. 267 </p> 268 <p> 269 You can examine the number of lock conflicts and deadlocks occurring 270 in your application by examining your database environment lock 271 statistics. Either use the 272 273 274 275 method, or use the <tt class="literal">db_stat</tt> command line utility. 276 The number of unavailable locks that your application waited for is 277 held in the lock statistic's <tt class="literal">st_lock_wait</tt> field. 278 279 </p> 280 </div> 281 <div class="sect2" lang="en" xml:lang="en"> 282 <div class="titlepage"> 283 <div> 284 <div> 285 <h3 class="title"><a id="IOEfficiency"></a>IO Efficiency</h3> 286 </div> 287 </div> 288 <div></div> 289 </div> 290 <p> 291 Page size can affect how efficient DB is at moving data to and 292 from disk. For some applications, especially those for which the 293 in-memory cache can not be large enough to hold the entire working 294 dataset, IO efficiency can significantly impact application performance. 295 </p> 296 <p> 297 Most operating systems use an internal block size to determine how much 298 data to move to and from disk for a single I/O operation. This block 299 size is usually equal to the filesystem's block size. For optimal 300 disk I/O efficiency, you should select a database page size that is 301 equal to the operating system's I/O block size. 302 </p> 303 <p> 304 Essentially, DB performs data transfers based on the database 305 page size. That is, it moves data to and from disk a page at a time. 306 For this reason, if the page size does not match the I/O block size, 307 then the operating system can introduce inefficiencies in how it 308 responds to DB's I/O requests. 309 </p> 310 <p> 311 For example, suppose your page size is smaller than your operating 312 system block size. In this case, when DB writes a page to disk 313 it is writing just a portion of a logical filesystem page. Any time 314 any application writes just a portion of a logical filesystem page, the 315 operating system brings in the real filesystem page, over writes 316 the portion of the page not written by the application, then writes 317 the filesystem page back to disk. The net result is significantly 318 more disk I/O than if the application had simply selected a page 319 size that was equal to the underlying filesystem block size. 320 </p> 321 <p> 322 Alternatively, if you select a page size that is larger than the 323 underlying filesystem block size, then the operating system may have 324 to read more data than is necessary to fulfill a read request. 325 Further, on some operating systems, requesting a single database 326 page may result in the operating system reading enough filesystem 327 blocks to satisfy the operating system's criteria for read-ahead. In 328 this case, the operating system will be reading significantly more 329 data from disk than is actually required to fulfill DB's read 330 request. 331 </p> 332 <div class="note" style="margin-left: 0.5in; margin-right: 0.5in;"> 333 <h3 class="title">Note</h3> 334 <p> 335 While transactions are not discussed in this manual, a page size 336 other than your filesystem's block size can affect transactional 337 guarantees. The reason why is that page sizes larger than the 338 filesystem's block size causes DB to write pages in block 339 size increments. As a result, it is possible for a partial page 340 to be written as the result of a transactional commit. For more 341 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>. 342 </p> 343 </div> 344 </div> 345 <div class="sect2" lang="en" xml:lang="en"> 346 <div class="titlepage"> 347 <div> 348 <div> 349 <h3 class="title"><a id="pagesizeAdvice"></a>Page Sizing Advice</h3> 350 </div> 351 </div> 352 <div></div> 353 </div> 354 <p> 355 Page sizing can be confusing at first, so here are some general 356 guidelines that you can use to select your page size. 357 </p> 358 <p> 359 In general, and given no other considerations, a page size that is equal 360 to your filesystem block size is the ideal situation. 361 </p> 362 <p> 363 If your data is designed such that 4 database entries cannot fit on a 364 single page (assuming BTree), then grow your page size to accommodate 365 your data. Once you've abandoned matching your filesystem's block 366 size, the general rule is that larger page sizes are better. 367 </p> 368 <p> 369 The exception to this rule is if you have a great deal of 370 concurrency occurring in your application. In this case, the closer 371 you can match your page size to the ideal size needed for your 372 application's data, the better. Doing so will allow you to avoid 373 unnecessary contention for page locks. 374 </p> 375 </div> 376 </div> 377 </div> 378 <div class="navfooter"> 379 <hr /> 380 <table width="100%" summary="Navigation footer"> 381 <tr> 382 <td width="40%" align="left"><a accesskey="p" href="javaindexusage.html">Prev</a> </td> 383 <td width="20%" align="center"> 384 <a accesskey="u" href="baseapi.html">Up</a> 385 </td> 386 <td width="40%" align="right"> <a accesskey="n" href="cachesize.html">Next</a></td> 387 </tr> 388 <tr> 389 <td width="40%" align="left" valign="top">Secondary Database Example </td> 390 <td width="20%" align="center"> 391 <a accesskey="h" href="index.html">Home</a> 392 </td> 393 <td width="40%" align="right" valign="top"> Selecting the Cache Size</td> 394 </tr> 395 </table> 396 </div> 397 </body> 398</html> 399