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