Databases Reference
In-Depth Information
Because disk I/O is much slower than memory I/O, any time the database
retrieves data from or stores data to the disk on the database server, performance
degrades significantly. The first time the application accesses a table row in a
database, the database places a copy of the row on disk into a fixed-length block
of memory known as a
page
. If the database can find the requested data on a
page in memory when subsequent data requests are processed, the database opti-
mizes its operation by avoiding disk I/O.
When the database fills up a page with data, it creates a new page. The pages
in memory are ordered from MRU (Most Recently Used) to LRU (Least Recently
Used). If the allocated memory buffer becomes full, the database makes room for
a new page by discarding the LRU page. This method of memory management
counts on the fact that the LRU page will probably not be needed any time soon.
When your application retrieves, inserts, or updates data in the real world,
typically, the database has been running for some time, allowing your application
to access data in memory. Running the benchmark at least once without timing it
allows the database to place some, or possibly all, the data you will be working
with in memory where it can be accessed on subsequent runs of the benchmark.
This also helps model how your application will run in your production environ-
ment because applications typically access the same tables over and over.
The most important guideline to remember when running a benchmark is that a
seemingly insignificant change can have a dramatic effect on performance. It's
crucial that you can demonstrate whether any change has a positive or negative
impact; otherwise, your benchmark efforts are useless. With this in mind, make
sure that you only change one variable at a time when you run the benchmark.
For example, suppose you want to explore the effect of setting two different
connection options in your database driver. Instead of making both changes at
once, make the changes one at a time and rerun the benchmark after each
change. If you make both changes at once, how do you know if either change
made a difference? Suppose one change has a positive effect on performance and
a second change has a negative effect, cancelling any performance gain caused by
the first change. How would you know if either change was good or bad?
