A database study comparing the performance of storage architectures using Sun Storage F5100 Flash Array and storage architecture based only on traditional hard disks was recently conducted, and its results presented by Chang Shu. Both atomic queries such as sorts / joins, and advanced queries containing complex business logic were tested in order to observe the performance gains in using F5100 Flash Array.
Shu used a Sun Fire X4275 server with two quad-core Intel Xeon X5570 processors and 72 GB of RAM to host the DB2 database server. The Sun Fire X4275 server ran the Solaris 10 OS (Update 8) and the DB2 UDB V9.5 Fixpak 4. Four SAS HBA cards were installed in the database server to support the access to four array domains of the F5100 Flash Array, and up to 960 GB of raw storage (40 Sun Flash Modules). Shu noted that host mirroring across HBAs and domains can be used to increase data availability, which reduces the usable capacity to 480 GB. The second SAS channel of two HBAs were connected to a Sun Storage J4400, which contains 24 x 146 GB disk drives. Two Storage I/O Modules (SIM 0 and SIM 1) were used with the J4400 to support dual-path. A Sun Fire X4200 was the workload driver with two duo-core AMD Opteron processors and 16 GB RAM.
In the first test, three joins including nested loop join, merge join and hash join were perform during the testing process. Shu points out that DB2 use temporary table space to store data during its execution in a transient or temporary work table. This means if the size of the sort heap can not fit in all temporary data, DB2 overflows the data to the temporary table space.
The overall system activity per second for the disk-only test registered 5629.60 total IOPs, while the I/O activity for the flash/disk system came in as 12983.86. The IOPS improved 3.2 times by putting the temporary table space in the F5100 Flash Array, writes Shu.
The second test involved complex queries. Shu notes data table spaces, temporary table spaces and index table spaces are all heavily accessed during the queries execution. Results for the overall I/O activity for the disk-only test came in at 6416.05. In comparison, the I/O activity for the flash/disk system resulted in 11345.43, demonstrating a 77% improvement.
Shu observes, "The queries execution time improves 10.8 times to 180 seconds compared to 1952 seconds in the disk-only system. ... The above testing shows how the combination of flash and disk technology can be applied to improve IBM DB2 UDB database performance for data warehousing workload. In the experiments, indexes and temporary tablespaces are stored on the F5100 Flash Array, which improves the overall performance by reducing the application response time and increasing the I/O throughput. Hybrid flash/disk technology is becoming a practical solution for database applications."
Many more details are provided in Shu's blog entry with tables and bar graphs illustrating specific reads, writes, service time, disk usage, etc.
Shu's blog entry
Accelerate Databases with the Sun Storage F5100 Flash Array
Oracle on Sun Beats IBM's Best Results on DB2 with Power 595
New Sun Storage F5100 Flash Array
Sun Storage F5100 Flash Array Product Page
Read More ...