|
|
|
|
It's been over a year since my first and very enthusiastic ASM article titled "Optimizing Oracle 10g on Linux Using Automated Storage Management", still currently available at:
Since then, quite a lot has changed in terms of the software technologies now available:
As you can see, the software technology landscape has so extensively changed as to reopen the entire ASM debate. In my first ASM paper I simply assumed either people would be utilizing ASM or not without considering RAC usage ramifications. What numerous people have told me this past year at shows, conferences and on-site visits is that while ASM makes obvious sense for RAC environments, they also desired to know whether ASM was in fact a viable alternative for non-RAC environments. Specifically, does ASM perform as well as Linux file systems using a Logical Volume Manager?
Of course that's a challenge far too enticing to pass up especially when there are tools like Quest's Benchmark Factory that make such tests trivial. So on to the races!
Looking back at the technology change stack above, what we want to benchmark is the new LVM vs. ASM 2.0 on Redhat Advanced Server 4.0's 2.6 kernel running Oracle 10g Release 2 (the bulleted items marked in green above). In other words, we want to test all the latest and greatest software technology available for non-RAC scenarios. The goal is simply to benchmark their fundamental performance characteristics against one another, and where possible declare a winner. For that purpose, we need to simulate two radically different kinds of real-world type workloads to cover differing needs thus the following industry standard benchmark tests will be used:
Both tests will simulate 100 users against 1 GB databases. While these test parameters are not overly large, they are nonetheless the maximum realistic values that our limited test hardware can accommodate. But it's expected that results from such tests should be sufficient for extrapolating to larger environments.
Setting up an industry standard database benchmark, like the TPC-C and TPC-D, using Quest's Benchmark Factory is a snap. There are just five quick and easy steps. First, you press the "New" toolbar icons to launch the "New Project" wizard and specify that you want to create a "Standard Benchmark Workload" as shown in Figure 1.
Second, you choose which industry standard benchmark you want to perform from the list of available tests as shown in Figure 2.
Third, you choose the approximate database size created for performing the benchmark (remember, Benchmark Factory has to create and populate it) as shown in Figure 3.
Fourth, you choose the number of concurrent user to simulate performing the benchmark (note that you can run this from one or more Windows computers) as shown in Figure 4.
Fifth and finally, you run the test and record the results. Total time it generally takes to configure a standard benchmark is roughly 30 seconds. You can't find an easier method for creating and running industry standard benchmarks!
In order for the benchmark test results to provide a fair "apples to apples" comparison, both the LVM and ASM disk layouts must be similar enough to draw meaningful and reliable conclusions. That means that neither setup should get preferential treatment in the allocation of devices. To that end, Figure 5 shows how the two environments were allocated across four identical IDE disks (you can tell they are IDE disks by the /hdb1 through /hde2 naming convention). These were 7200 RPM SATA IDE disks with 2 MB cache each. Note also how two inner and two outer disk partitions are allocated to each solution. The idea was to eliminate any unintentional speed advantages due to quicker access times for inner disk tracks. Finally, note that no operating system, swap or database binary files are on these disks they were used solely for database data.
Although SCSI is obviously the more preferable choice the popularity of SATA IDE for low-cost RAID arrays is increasing. The results obtained should apply equally well to faster and more reliable disk technologies such as SCSI, as well as highly popular RAID array appliances such as NAS and SAN. The chief goal here was to implement Oracle's SAME (stripe and mirror everything) approach. Even though there are but just four disks, we should still nonetheless be able to compare these two methods' fundamental striping capabilities. And minus all the other "bells and whistle" distractions, that's essentially the heart of the question people have been asking: do the ASM striping algorithms match up well against those of the more mature LVM?
Remember as we look at these results that we're not worrying about which environment is easier to setup and maintain, because as the prior paper clearly pointed out ASM has numerous advantages in those areas. Our goal here is simply to see how they perform in head to head speed tests. So the results here focus on only that aspect speed.
Let's look first at the TPC-C. Remember, we simulated 100 concurrent users accessing a 1 GB database. The results are show in Figure 6.
Basically the TPC-C results were too close to award a winner. I suspect that a key reason for the lack of any major difference is that the Oracle data, index, temporary and rollback segments did not have to grow and shrink by any measurable amount in this type of load test scenario (since OLTP transactions tend to be short and bursty in nature). Thus we are measuring primarily read only access across four disk stripes. Therefore we'll have to call the TPC-C benchmark test results a draw with neither ASM nor LVM showing any real performance advantage.
Note While this tie was unexpected, it clearly shows why you need to consider more than one type of benchmark test when comparing such radically different technologies. Benchmark Factory offers additional database benchmarking tests, including TPC-B, TPC-D, AS3AP and Scalable Hardware Benchmark. Make sure that you choose those tests that best reflect the database environment you will be building and maintaining.
Now let's look at the TPC-D. Remember, again we simulated 100 concurrent users accessing a 1 GB database. The results are show in Figure 7.
Aha we have a clear cut winner. The LVM ran 30% faster, achieved a 25% higher transaction per second, scored 56% faster kilobytes per second, and had 108% better average response time! I suspect that the real differentiator here was the temporary segment allocation necessary for the large GROUP BY and ORDER BY operations.
I was not entirely happy with simply running the industry standard benchmarks and speculating as to why the results ended up as they did. I wanted a little more clarity regarding objects' segment creation and allocation and the corresponding tablespace growth issues. My belief was that the LVM somehow handles space allocation due to object growth more efficiently than ASM. Of course this seems totally contrary to what one would expect because ASM touts the advantages of RAW without the headaches. So how could the ext3 file system on top of the LVM be faster? So I devised a simple, brute force benchmark to test this premise. I created a simple table with two indexes whose data format would yield predictable growth with increasing row counts. Thus I could test the object space creations and allocation for both tables and indexes with one simple script. The script is listed here.
set verify off
drop table junk;
create table junk (
c1 number not null,
c2 number not null,
c3 number not null,
c4 number not null,
c5 date not null,
c6 char(100) not null,
c7 varchar2(1000) not null,
constraint junk_pk primary key (c1, c2),
constraint junk_uk unique (c3, c4)
);
variable t1 varchar2(12)
variable t2 varchar2(12)
variable t3 number
begin
:t1 := to_char(sysdate,'DDD:HH24:MI:SS');
end;
/
begin
for i in 1 .. &1 loop
insert into junk values (i,i,i,i,sysdate,
'This is a test of the mergency broadcast system',
'In the case of an actual emergency, you would be told where to tune');
if (mod(i,100) = 0) then
commit;
end if;
end loop;
end;
/
begin
:t2 := to_char(sysdate,'DDD:HH24:MI:SS');
:t3 := to_number(to_date(:t2,'DDD:HH24:MI:SS')-
to_date(:t1,'DDD:HH24:MI:SS'))*60*60*24;
end;
/
print t1
print t2
print t3
col segment_name format a20
col tablespace_name format a20
col megs format 999,999,999
select segment_name, tablespace_name, ceil(bytes/(1024*1024)) MEGS from user_segments where segment_name like 'JUNK%';
exit
The results of calling this script for row counts from 10,000 to 100,000,000 for both LVM and ASM are shown in Figure 8.
The results from this additional experiment were quite simple and conclusive. While both approaches used exactly the same amount of space, the LVM run times consistently beat the ASM run times by from 10-14%. And as you can see by the graphs lines, the trend seems clear LVM is slightly more efficient at bulk data loads than ASM.
So what does all this mean? Well for people doing RAC, ASM is a viable and credible approach for disk space management with numerous administrative and maintenance to its credit. But for those simply doing non-RAC database deployments, ASM is not yet as scalable as the Linux ext3 file system using a Logical Volume Manager. And while all these benchmarks were done using the standard LVM included with Redhat and other popular Linux distributions, it's quite possible that an enterprise targeted LVM like those from either IBM or Veritas would best even these results. Therefore for people not doing RAC and who care more about performance than administrative ease, for now you should stick with the Linux file systems and an LVM.
Bert Scalzo is a product architect for Quest Software and a member of the TOAD development team. He designed many of the features in the TOAD DBA module. Mr. Scalzo has worked as an Oracle DBA with versions 4 through 10g. He has worked for both Oracle Education and Consulting. Mr. Scalzo holds several Oracle Masters, a BS, MS and PhD in Computer Science, an MBA and several insurance industry designations. His key areas of DBA interest are Linux and data warehousing (he designed 7-Eleven Corporation's multi-terabyte, star-schema data warehouse). Mr. Scalzo has also written articles for Oracle's Technology Network (OTN), Oracle Magazine, Oracle Informant, PC Week, Linux Journal and www.linux.com. He also has written three books: "Oracle DBA Guide to Data Warehousing and Star Schemas", "TOAD Handbook" and "TOAD Pocket Reference". Mr. Scalzo can be reached at bert.scalzo@quest.com or bert.scalzo@comcast.net.