1<!--$Id: throughput.so,v 10.31 2002/04/02 17:07:05 bostic Exp $--> 2<!--Copyright (c) 1997,2008 Oracle. All rights reserved.--> 3<!--See the file LICENSE for redistribution information.--> 4<html> 5<head> 6<title>Berkeley DB Reference Guide: Transaction throughput</title> 7<meta name="description" content="Berkeley DB: An embedded database programmatic toolkit."> 8<meta name="keywords" content="embedded,database,programmatic,toolkit,btree,hash,hashing,transaction,transactions,locking,logging,access method,access methods,Java,C,C++"> 9</head> 10<body bgcolor=white> 11<a name="2"><!--meow--></a> 12<table width="100%"><tr valign=top> 13<td><b><dl><dt>Berkeley DB Reference Guide:<dd>Berkeley DB Transactional Data Store Applications</dl></b></td> 14<td align=right><a href="../transapp/tune.html"><img src="../../images/prev.gif" alt="Prev"></a><a href="../toc.html"><img src="../../images/ref.gif" alt="Ref"></a><a href="../transapp/faq.html"><img src="../../images/next.gif" alt="Next"></a> 15</td></tr></table> 16<p align=center><b>Transaction throughput</b></p> 17<p>Generally, the speed of a database system is measured by the 18<i>transaction throughput</i>, expressed as a number of 19transactions per second. The two gating factors for Berkeley DB performance 20in a transactional system are usually the underlying database files and 21the log file. Both are factors because they require disk I/O, which is 22slow relative to other system resources such as CPU.</p> 23<p>In the worst-case scenario:</p> 24<p><ul type=disc> 25<li>Database access is truly random and the database is too large for any 26significant percentage of it to fit into the cache, resulting in a 27single I/O per requested key/data pair. 28<li>Both the database and the log are on a single disk. 29</ul> 30<p>This means that for each transaction, Berkeley DB is potentially performing 31several filesystem operations:</p> 32<p><ul type=disc> 33<li>Disk seek to database file 34<li>Database file read 35<li>Disk seek to log file 36<li>Log file write 37<li>Flush log file information to disk 38<li>Disk seek to update log file metadata (for example, inode information) 39<li>Log metadata write 40<li>Flush log file metadata to disk 41</ul> 42<p>There are a number of ways to increase transactional throughput, all of 43which attempt to decrease the number of filesystem operations per 44transaction. First, the Berkeley DB software includes support for 45<i>group commit</i>. Group commit simply means that when the 46information about one transaction is flushed to disk, the information 47for any other waiting transactions will be flushed to disk at the same 48time, potentially amortizing a single log write over a large number of 49transactions. There are additional tuning parameters which may be 50useful to application writers:</p> 51<p><ul type=disc> 52<li>Tune the size of the database cache. If the Berkeley DB key/data pairs used 53during the transaction are found in the database cache, the seek and read 54from the database are no longer necessary, resulting in two fewer 55filesystem operations per transaction. To determine whether your cache 56size is too small, see <a href="../../ref/am_conf/cachesize.html">Selecting 57a cache size</a>. 58<li>Put the database and the log files on different disks. This allows reads 59and writes to the log files and the database files to be performed 60concurrently. 61<li>Set the filesystem configuration so that file access and modification times 62are not updated. Note that although the file access and modification times 63are not used by Berkeley DB, this may affect other programs -- so be careful. 64<li>Upgrade your hardware. When considering the hardware on which to run your 65application, however, it is important to consider the entire system. The 66controller and bus can have as much to do with the disk performance as 67the disk itself. It is also important to remember that throughput is 68rarely the limiting factor, and that disk seek times are normally the true 69performance issue for Berkeley DB. 70<li>Turn on the <a href="../../api_c/env_set_flags.html#DB_TXN_WRITE_NOSYNC">DB_TXN_WRITE_NOSYNC</a> or <a href="../../api_c/env_set_flags.html#DB_TXN_NOSYNC">DB_TXN_NOSYNC</a> flags. 71This changes the Berkeley DB behavior so that the log files are not written 72and/or flushed when transactions are committed. Although this change 73will greatly increase your transaction throughput, it means that 74transactions will exhibit the ACI (atomicity, consistency, and 75isolation) properties, but not D (durability). Database integrity will 76be maintained, but it is possible that some number of the most recently 77committed transactions may be undone during recovery instead of being 78redone. 79</ul> 80<p>If you are bottlenecked on logging, the following test will help you 81confirm that the number of transactions per second that your application 82does is reasonable for the hardware on which you're running. Your test 83program should repeatedly perform the following operations:</p> 84<p><ul type=disc> 85<li>Seek to the beginning of a file 86<li>Write to the file 87<li>Flush the file write to disk 88</ul> 89<p>The number of times that you can perform these three operations per 90second is a rough measure of the minimum number of transactions per 91second of which the hardware is capable. This test simulates the 92operations applied to the log file. (As a simplifying assumption in this 93experiment, we assume that the database files are either on a separate 94disk; or that they fit, with some few exceptions, into the database 95cache.) We do not have to directly simulate updating the log file 96directory information because it will normally be updated and flushed 97to disk as a result of flushing the log file write to disk.</p> 98<p>Running this test program, in which we write 256 bytes for 1000 operations 99on reasonably standard commodity hardware (Pentium II CPU, SCSI disk), 100returned the following results:</p> 101<blockquote><pre>% testfile -b256 -o1000 102running: 1000 ops 103Elapsed time: 16.641934 seconds 1041000 ops: 60.09 ops per second</pre></blockquote> 105<p>Note that the number of bytes being written to the log as part of each 106transaction can dramatically affect the transaction throughput. The 107test run used 256, which is a reasonable size log write. Your log 108writes may be different. To determine your average log write size, use 109the <a href="../../utility/db_stat.html">db_stat</a> utility to display your log statistics.</p> 110<p>As a quick sanity check, the average seek time is 9.4 msec for this 111particular disk, and the average latency is 4.17 msec. That results in 112a minimum requirement for a data transfer to the disk of 13.57 msec, or 113a maximum of 74 transfers per second. This is close enough to the 114previous 60 operations per second (which wasn't done on a quiescent 115disk) that the number is believable.</p> 116<p>An implementation of the previous <a href="writetest.cs">example test 117program</a> for IEEE/ANSI Std 1003.1 (POSIX) standard systems is included in the Berkeley DB 118distribution.</p> 119<table width="100%"><tr><td><br></td><td align=right><a href="../transapp/tune.html"><img src="../../images/prev.gif" alt="Prev"></a><a href="../toc.html"><img src="../../images/ref.gif" alt="Ref"></a><a href="../transapp/faq.html"><img src="../../images/next.gif" alt="Next"></a> 120</td></tr></table> 121<p><font size=1>Copyright (c) 1996,2008 Oracle. All rights reserved.</font> 122</body> 123</html> 124