I finished up the seven different runs of the base queries using different configurations (took a total of three days, part-time) and then cranked up the ATA array. I have to admit I was a bit disappointed when the first query took 9 minutes to run.
I loaded up the script and executed it in the SQLPLUS environment. The TPCH qgen program was used to create the basic query set the queries were placed into a single file and the SET TIMING clause was added to capture the execution times of each SQL statement to the spool file which was used to log each run. The first system to be tested was the SSD array.
With trepidation, I moved to the SQL SELECT testing. While not as spectacular as I hoped, a 30-percent timesaving could be fairly substantial in most environments. The index builds also showed nearly the same results: 30 percent faster for the SSD array. That is, the SSD based system loaded 30 percent faster than the ATA array. The average time to load a table using SSD versus ATA technology was around 30 percent. It should be noted that the load times and index build times, as well as query times, were equivalent on the SCSI and ATA systems. But a third of the way through the second run of the queries, the drive array crashed hard and we had to use an available ATA array. The testing was started with a 2-disk SCSI array using 10K RPM Cheetah technology drives. Of course, the time to load the database tables using SQLLoader and the time required to build the indexes to support the queries was also measured. For example, a disk-based sort is usually only 17-37 times slower than a memory-based sort.Īll of that being said, what did I find in my tests? The tests involved setting up a 20-gigabyte (data volume excluded indexes and support tablespaces and logs) TPCH database and then running the 22 basic TPCH-generated queries. However, due to access speeds of various interfaces, and processing overhead of the CPU and I/O processor, this usually drops to a fraction of that. Typical access rates for memory are measured not in milliseconds, but in nanoseconds (one billionth of a second versus one thousandth of a second) so we are immediately talking a factor of one hundred thousand. Typical average latencies for mechanical disks are seven - nine microseconds. Disk access rates are generally in the range of 0.5 milliseconds to 10 milliseconds. Therefore we must limit ourselves to the non-linear access rate for most Oracle applications.Ī memory-based array has no rotational or actuator arm latency linear or non-linear access is identical. That is, if you can get 100 meg/second of linear access, you will get only 50-60 meg/second of random access.
Combined, these disk latencies result in the difference between “linear access rate” and “actual” or “random access rate” usually the difference is as much as 40-50 percent of linear access rate. Therefore, when you use standard disk technology, you are limited by the disk rotational speed and the actuator arm latency. In a disk drive, you are battling physics to squeeze more performance out of your array there are certain physical properties that just can’t be altered on a whim (to quote Scotty from Star Trek, “Ya cannot change the laws of physics, Captain!”). Logically, if you utilize a “memory” disk you are ridding yourself of the physical limitations of disk drive technology - basically trading in the spinning rust. I had read about the performance possibilities of the SSD and was anxious to prove or disprove them for myself.
#SERIAL ATA DRIVE SERIAL#
The serial ATA array contained 7 - 7500 RPM 200-gigabyte drives in a RAID5 array setup. One of the more interesting projects I participated in this year involved the comparison of a 64-gigabyte solid-state drive (SSD) from Texas Memory Systems to a serial ATA (SATA) array.
0 kommentar(er)
