For UNIX operating systems, the following commands are
useful: top, iostat, and sar. Depending on the command you will receive
slightly different output.
The "Top" command, when executed on a Solaris system,
produces results that have the following format:
load averages: 0.09, 0.04, 0.03
66 processes: 65 sleeping, 1 on cpu
CPU states: 69.2% idle, 18.9% user, 11.9% kernel, 0.0%
iowait, 0.0% swap
Memory: 128M real, 4976K free, 53M swap in use, 542M swap free
The key is that this command provides the "% iowait"
for the system. It is important to note, that "Top" provides a
snapshot of performance. It is helpful to look at I/O wait results over peak
processing periods to better understand the average I/O wait time.
It is also reasonable to look at the "vmstat"
command. This command will tell you how frequently your system is paging to
virtual memory (disk). If you have frequent paging, it makes sense to consider
adding RAM to your system or using a RamSan-210 as the disk for paging. Paging
to disk is another way that hard disk drives can introduce bottlenecks into
Texas Memory Systems Oracle Benchmarks
Texas Memory Systems completed a series of test involving
Oracle 8i running on a Sun 220R server with two 450Mhz processors running
Solaris. A set of tests compared a Departmental RAID versus our RAM-SAN solid
state disk. The testing was done using Oracle 8i.
The test results were staggering. The following average results were
observed from "Top":
CPU states: 1.0% idle, 24.0% user, 15.0% kernel, 60.0% iowait,
CPU states: 0.0% idle, 80.0% user, 20.0% kernel, 0.0% iowait,
These results demonstrate the impact of a RAM-SAN solid state
disk running Oracle tests. The "iowait" statistics show right away
the amount of time that is wasted while the CPU waits for data to return from
the RAID system, while the RAM-SAN did not cause any "iowait". The
net result is that the "user" application, in this case Oracle, is
able to more effectively utilize the processor. With the RAID system the
processor only spends 24% of its time working on the application. With the
RAM-SAN the processor spends 80% of its time working on the Oracle application.
These differences translated directly into impacts on the
execution time for the Oracle queries and operations. As the number of
concurrent users increased and as the complexity of the queries increased the
RAM-SAN pulled further and further away from the performance of the Departmental
RAID. On simple sequential tests, the two systems performed very similarly with
the cache on the RAID providing some extra performance. As the tests increased
in randomness and complexity the RAM-SAN completed processing as much as 15
times faster than the Departmental RAID.
The higher your I/O wait time, the more a RAM-SAN will be able
to improve your throughput. Once you have determined that you have I/O wait
time, the next step is to determine whether the entire database or just a subset
of files should be moved to a solid state disk.
Oracle Components that should be
Moved to Solid State Disk
Once you determine that your system is experiencing I/O
subsystem problems the next step is to determine which components of your Oracle
database are experiencing the highest I/O and in turn causing I/O wait time.
The following database components should be looked at:
Entire Database. There are some databases that should have all
of their files moved to the RamSan-210. These databases tend to have at least
one of the following characteristics:
- High concurrent access. Databases that are being hit by a large number of
concurrent users should consider storing all of their data on solid state disk.
This will make sure that storage is not a bottleneck for the application and
maximize the utilization of servers and networks. I/O wait time will be
minimized and servers and bandwidth will be fully utilized.
- Frequent random accesses to all tables. For some databases, it is
impossible to identify a subset of files that are frequently accessed. Many
times these databases are effectively large indices.
- Small to medium size databases. Given the fixed costs associated with
buying RAID systems, it is often economical to buy a solid state disk to store
small to medium sized databases. A RamSan-210 can provide 32GB of database
storage for the price of some enterprise RAID systems.
- Database performance is key to company profitability. There is some subset
of databases that help companies make more money, lose less money, or improve
customer satisfaction if they process faster. The RamSan-210 can help make
these companies more profitable.
Redo Logs. Redo logs are one of the most important factors in
the write performance for Oracle databases. Whenever a database write occurs,
Oracle creates a redo entry. Each redo entry is written to two redo logs.
Oracle strongly encourages the use of two redo logs so that a backup redo log is
available in the event of a failure. The operation is considered committed once
the write to the redo logs is complete.
The redo logs are a source of constant I/O during database
operation. It is important that the redo logs are stored on the fastest
possible disk. Writing a redo log to a solid state disk, such as the RamSan-210
is a natural way to improve overall database performance. Because the
RamSan-210 writes once to memory and twice to internal disk drives, the redo
logs are protected from volatility issues.
Indices. An index is a data structure that speeds up access to
database records. An index is usually created for each table in a database.
These indices are updated whenever records are added and when the identifying
data for a record is modified. When a read occurs an index is consulted so that
Oracle can quickly get to the correct record. Furthermore, many concurrent
users may read any index simultaneously. The activity to the disk drive is
characterized by frequent, small, and random transactions. Under these
conditions, disk drives are unable to keep up with demand and I/O wait time
By storing indices on a RamSan-210 solid state disk,
performance of the entire application can be increased. For on-line transaction
processing (OLTP) systems with a high number of concurrent users this can result
in faster database access. Because indices can be recreated from the existing
data, they have historically been a common Oracle component to be moved to solid
Temporary Tablespace. Temporary segments are used to support
temporary data during certain Oracle operations. The tables support complex
queries, joins and index creations. Because temporary segments support many
kinds of operations they can quickly become fragmented. In internal tests at
Texas Memory Systems, we have found that Oracle database performance degrades
quickly as data becomes fragmented.
When complex operations occur they will complete more quickly if the
temporary tablespace is moved to solid state disk. Because the I/O to the
temporary tablespaces can be frequent, disk drives cannot easily handle them.
Rollback Data. In databases with a high number of concurrent
users, the rollback segments can be a cause of contention. Rollback data is
created any time an Oracle transaction updates a record. In other words, if a
delete command is issued, all of the original data is stored in the rollback
segment until the operation commits. If the transaction is rolled-back, then
the data is moved from the rollback segment back to the table(s) it was removed
Because the rollback segments are hit with every update
operation, and because the number of rollback segments is limited, it is useful
to have the rollback segments stored on solid state disk. This will provide
fast writes when the update transaction is created and will make rollback
segments available more quickly for the next update operation.
Frequently Accessed Tables. It is estimated that only 5%-10%
of data stored in OLTP systems are frequently accessed. These tables typically
account for a large percentage of all database activity and thus I/O to storage.
When a large number of users hit a table, they are likely going after different
records and different attributes. As a result, the activity on that table is
random. Disk drives are notoriously bad at servicing random requests for data.
In fact, the peak performance of a disk drive drops as much as 95% when
servicing random transactions. When a table experiences frequent access,
transaction queues develop where other transactions are literally waiting on the
disk to service the next request. These queues are another sign that the system
is experiencing I/O wait time.
It makes sense to move the frequently accessed tables to a
RamSan-210. RamSan-210 performance is not impacted if performance is random.
Additionally, solid state disks by definition have faster access times than disk
drives. Therefore, application performance can be improved up to 10x if
frequently accessed tables are moved to the RamSan-210. Because the RamSan-210
mirrors all memory writes to two internal hard disk drives, your frequently
accessed data is protected.
Identifying the Most Frequently
The Oracle database constantly acquires data on the files that
are accessed. This data is stored in the V$FILESTAT table. This table starts
gathering information as soon as a database instance is started. When a
database instance is stopped, the data in the V$FILESTAT table is cleared.
Therefore, if the database instance is routinely stopped, it is important to
capture the data from the V$FILESTAT table before the data is cleared. It is
possible to create a program to gather this data and move it to a permanent
table. Additionally, Oracle's Statspack tool will gather this information and
store it in permanent tables.
The following fields are available from V$FILESTAT:
- FILE#: Number of the file
- PHYRDS: Number of physical reads done
- PHYBLKRD: Number of physical blocks read
- PHYWRTS: Number of physical writes done
- PHYBLKWRT: Number of physical blocks written
A simple query and report from the V$FILESTAT table will
indicate which Oracle database files are frequently accessed. Adding PHYRDS and
PHYWRTS gives the total I/O for a single file. By sorting the files by total
I/O it is possible to quickly identify the files that are most frequently
accessed. The most frequently accessed files are good candidates for moving to
By sampling the V$FILESTAT table at set intervals, it is possible to
estimate the average number of I/O's per second.
(Change in PHYRDS + Change in PHYWRTS)/Elapsed Time in seconds
= Average I/O per second
Once frequently accessed files are moved to the RamSan-210 it
is good to periodically evaluate the performance of the files that remain on
RAID or hard disk based storage to see if new candidates have emerged and need
to be migrated to solid state disk.
Integrating the RamSan-210 into
your Storage Network
The RamSan-210 is designed to accommodate the needs of diverse
storage environments. Our Fibre Channel interfaces can be configured to support
point-to-point operation, arbitrated loop and switched fabric. Therefore, the
RamSan-210 can be connected directly or to Fibre Channel switches or Fibre
Entire Database Configuration
As discussed earlier, it is practical to store an entire
database to a RamSan-210. The RamSan-210 is capable of storing up to 32GB of
database components. Using host software, additional RamSan-210's can be
arrayed for individual databases that exceed 32GB in capacity.