Exadata Part Deux

I just watched Larry Ellison’s webcast announcing the new Exadata-2, the Database Machine made for both data warehouse and OLTP systems. The new Exadata contains both DDR3 (about 450 GB) and Flash (around 5.6 TB) cache areas (not SSD) and up to 100 TB of SAS or 336 TB of SATA raw disk capacity in a full rack of 8 DB servers and 14 Exadata-2 cells. Of course formatted and ASM redundancy will eat at least half of that disk capacity or more.

Larry promises 10X at a minimum improvement in speed and response with 1,000,000 IOPS per full rack.

All of this is wonderful news except for one thing, it locks you completely into Oracle technology. The Oracle database, Oracle Linux OS and Oracle SUN hardware. None of this new toy works without special Oracle software and licensing. You can’t run anything else on it but Oracle. It comes preconfigured with Oracle and OS. The cost for a full rack is 1.15 million plus around another million in license fees.

Interesting thing that you could take the 8 DB servers with a normal amount of memory, not the massive 72 GB per server in the Database Machine, 4 RamSan-620’s and 2 RamSan-420’s, a couple of FC switches and some HBAs and get that 1,000,000 random IOPS Flash plus an additional 1,200,000 random IOPS DDR and have 5 TB of Flash, fully redundant, and 512 GB of DDR2 fully redundant and still come in at less than 1.15 million and a lot less on license fees. Shoot, even add in some SATA or SAS drives and use the preferred read technology in 11g ASM or other global filesystems and viola! The same configuration. Nice thing, you can run anything on it. Of course Oracle runs really sweet on this configuration but so will SQLServer, MySQL, BSD, or anything else. It can also run Windows, Linux, or with IBM servers, AIX as well as Solaris’ various flavors.

So you get the same bang, since Oracle11g software will run (with its compression technology, query optimization and partitioning) the only thing you don’t get is the added power requirements of the SAS or SATA drives in the 10 extra Cells needed to get what we get out of 12-U of space.

One other point Larry made was that to expand you just add more servers of more cells. Supposedly in Oracle11gR2 RAC allows this anyway. Now we don’t have fancy software that automatically rebalances the whole storage array, but guess what, Oracle provides ASM for free and it does.

Ok, I will concede one point, the parallel query software at the cell level (10K USD per disk license fee in Exadata-1) will probably result in some queries (who knows, maybe all) running a wee bit faster than without it, but I would love to see a comparison between equal configurations just to see!

On Gun Control in the USA

Gun control is a hot button item with liberal democrats and conservative republicans. The desire of the most rabid of the gun control crowd is to remove all guns from everyone except the police and military (and some would take the guns from the police as well!) The conservatives want almost no limits on gun ownership. I believe the reality of the matter should be somewhere in between.

Coming from a long line of hunters (going back to the first Alt’s in America in 1738 or so) I own a rifle and a shotgun. The rifle is an old converted Mauser and the shotgun is a semi-automatic plugged to only allow 5 shells to be loaded at any one time. At this time I don’t own any handguns but have no negative feelings towards them. In fact, when finances allow I plan on purchasing a hand gun for home defense. Of course I will be sure both my wife and I take a class on its safe use from a local gun range or police station.

Now, to the meat of the matter, I am a card carrying member of the NRA, does that mean I stand for everything they do? No, of course not. I believe that Americans have the right to keep and bear arms, both for home defense and just in case politicians get too uppity and decide to try to take more power than they need to govern. And I believe that is the reason that the framers of the constitution put the right to arms clause in there, to remind politicians that they aren’t all powerful. Do I think the average citizen should own fully automatic weapons, flame throwers, grenade launchers or Abrams tanks? No, definitely not.

Currently there are some half baked proposals before the Congress and Senate proposing bans on semiautomatic weapons with some fairly liberal definitions of exactly what is a semiautomatic weapon, unfortunately, that definition would not only ban a semi-automatic AK47 with an extended banana clip holding hundreds of rounds, but my son-in-laws 30-06 semiautomatic hunting rifle. Thus, it is a bad bill and should not be passed.

I am all for limits based on cyclic rate of fire and clip size, but not so loosely specified that it can produce a cascade effect onto sporting rifles. In fact, the very weapons that the bill is designed to restrict are already restricted by existing laws. Most of the crimes that the liberals are saying would be curtailed by this new ban were not committed with the semi-automatic rifles they are trying to ban! The one thing that the liberals who propose these bans forget is that criminals don’t follow the law, that is implicit in their being criminals. As countries such as England and Australia have found, once you make it impossible for a law abiding citizen to own guns, crime rates increase.

Why is it that liberals can’t read statistics? The states and cities with the most restrictive bans on guns have the highest crime rates, the ones with the least restrictive tend to have the lowest. When a criminal (who could care less that he is using an illegal hand gun) knows that a home owner or car driver probably won’t be armed because the law forbids it, it makes them an easy target for that criminal. Some states have already made it illegal to defend yourself, allowing criminals injured by homeowners to sue the homeowner for damages! Talk about legal insanity! More people where killed by bad Doctors last year than by guns in the USA, should we ban all Doctors? More children were killed by bicycles than by guns, should we ban bicycles?

Remember, it is already illegal to own or sell fully automatic weapons, grenades, rocket launchers and generally illegal for anyone to own anything other than a rifle or handgun that can be used for hunting or sport shooting. By law the guns must be registered. Most states require a permit and a safety course.

I agree that before a person can own a gun they should have to take a gun safety course, not be a felon and be at a responsible age. However, beyond restrictions on fully automatic weapons, ridiculous calibers (bullet sizes) and rocket launchers, if Granny wants a semiautomatic AK47 with a case of ammo, the more power to her!

If gun laws will make us safe then why are the states and cities with the most restrictive gun laws the most unsafe? If laws make us safe then we should have nothing to fear since it is illegal to commit a crime with a gun, it is illegal for felons to own guns and the most dangerous fully-automatic guns are already illegal. Face it, the reason most politicians want guns banned is they are afraid that if they really screw up we will hold them accountable for it, an unarmed population is much easier to control than an armed one.

Preliminary TPC-C Results

(Note: These are preliminary, non-reviewed results)

I just wrapped up my series of TPC-C based tests involving the TMS RamSan SSDs and HDD technologies (SSD-Solid State Disk, HDD-Hard Disk Drive.) For those not familiar a TPC-C has 9 tables with related indices and uses 5 basic transactions to compute a number called a tpm-C, essentially a transactions per minute completed for a given size of database and user load. I used a scale factor of 1000, which means that in the WAREHOUSE table of the TPC-C schema I had 1000 entries, the rest of the database is based on multiples of this value. The TPC-C is a benchmark used for OLTP type systems.

There are two basic methods to do a TPC-C test, one with many clients (read thousands) and built in latencies (usually on the order of up to a second per transaction) and the other with few clients and no latencies. In the first methodology in order to achieve tpm-C approaching 100,000 you would need close to 10,000 clients, roughly a 1 client per 10 tpm-C ratio. In the second method you can get to a tpm-C of over 100,000 with as few as 250 clients. Since I was limited as to the number of client boxes I had access to I decided to use the second method and utilized a ramp-up from 5 to 500 clients in increments of 5 using the Benchmark Factory (BMF) tool from Quest to both build, and, repopulate the TPC-C database before each run. Kevin Dalton at Quest supplied me with some custom build scripts for BMF that allowed me to build any or all of the tables simultaneously, this helped immensely with the build and needed rebuilds. The scripts he provided should be available on Toadworld.

The test system consisted of a 4-node RAC cluster with each node having 8-2 GHz CPUs and 16 GB of memory using an Infiniband cross connect running Oracle11g, 11.1.0.7. The storage subsystems consisted of 2-RamSan400’s, a RamSan500 and 6-15 disk racks of 114GB 15K RPM disks. The RamSan400’s were used for Redo logs and Undo and Temporary tablespaces. ATPC-C schema was built on the RamSan500 (which also held the SYSTEM, USERS and SYSAUX tablesapces for both tests) the RamSans where configured with multiple 4Gbps fibre channel lines each in a multipathed configuration. A duplicate TPC-C schema was built on the HDD array which was configured as two groups of 45 disk drives on different 2Gbps fibre channel links and then used as a single diskgroup in ASM with a failure group (essentially RAID10.)

The RamSan based subsystem utilized 20-4Gbps fibre channel links to access the 500,000+ IOPS that the system was capable of delivering. The HDD subsystem utilized the 2-2Gbps links to utilize the 27,000 IOPS the system was capable of delivering. The database was configured using 8K blocks, in a TPC-C the major IO path is single block reads. Using 8K read/write sizes the interfaces for the RamSan subsystem can handle 1,310,720 IOPS, the subsystem for the HDD can handle 64,000. Now before you scream foul, remember that each disk drive can only handle a maximum (and this is being generous) of 300 IOPS each, 90*300=27,000, so the HDD interface is more than enough for the available IOPS.

In the above configuration the impact of Redo logs, Undo segments and temporary tablespace access are essentially eliminated and access times to the SYSTEM, SYSAUX and USER tablespaces minimized, thus we are only looking at the access times for the TPC_C data and indices as the possible variables in the tests.

In the first set of tests the database was run with 1 to 4 servers with 9 GB of DB cache size. The HDD results peaked at 1051 TPS and 55 users. The RamSan results peaked at 3775 TPS and 245 users. The HDD results fell off from 1051 TPS with 55 users to 549 TPS and 15 users going from 4 down to 1 server. The SSD results fell from 3775 TPS and 245 users down to 1778 TPS and 15 users. However, the 1778 TPS seems to be a transitory spike in the SSD data with an actual peak occurring at 1718 TPS and 40 users. From this data, given the choice, you would get better performance from a single, 8-CPU, 16 GB memory server running against a RamSan configuration than you would with a 4-node RAC configuration running against a HDD system by a factor of 1.63.

In the second test the affects of memory starvation on the RAC environment was tested. The DB cache size was ramped down from 9 GB to around 1 GB. The tests showed that the RamSan configuration handles the memory stress better by a performance factor ranging from 3 at the top end at 9 GB total cache size to a huge 7.5 at the low end comparing a 1.05 GB cache on the SSD to a 4.5 GB cache on the HDD run. The HDD run was limited to 4.5 GB at the lower end by time constraints however, as performance would only get worse as the cache was reduced more, further testing was felt to be redundant.

Of course the reason for the wide range between the upper and lower memory results, from a factor of 3 to 7.5 times better performance by the SSD, can be traced to the increase in physical IO that resulted from not being able to cache results and the subsequent increase in db file sequential reads.

The tests show that the SSD array handles reduction in available memory much better than the HD array. Even at a little over 1 GB of total cache area per node for a 4-node RAC environment, the SSD outperformed the HD array at a 9 GB total cache size per node for a 4-node RAC using identical servers and database parameters. Unfortunately due to bugs in the production release of Oracle11g, release 11.1.0.7, we were unable to test the automatic memory management feature of Oracle, the bug limits total SGA size to less than 3-4 gigabytes per server.

A third test involved attempting to defeat the cache fusion logic that assumes that getting data over the interconnect is faster than from the storage subsystem. In our test system the transfer of blocks was taking 3 milliseconds while reading and writing was taking less than 1 millisecond so using a ping (write to disk, read from disk) would theoretically be better performance than using the interconnect to transfer the block. A look at documentation seems to indicate that if you set the gc_files_to_locks parameter then cache fusion is defeated.

In the first run we set the GC_FILE_TO_LOCKS parameter to “1-13:0” which was supposed to turn on fine grain locking (the “0” setting) for files 1 to 13 (all of our datafiles.) Unfortunately this increased the number of cache transfers and caused a decrease in performance.

In the second test we researched the GC_FILES_TO_LOCKS parameter a bit more and found that we shouldn’t have set the parameter for the UNDO tablespaces and, even though the examples showed the “0” setting for setting fine grain locks, we decided to set it to a hard number of locks equal to or greater than the number of blocks in each datafile. The documentation also showed that generally speaking the SYSTEM and SYSAUX tablespaces don’t need as many locks so we decided to set the value to 1000 for those tablespaces. You don’t set the parameter for temporary tablespaces. This led to a new setting of “1-2:1000EACH:7-13:4128768EACH”. Tests with this new setting showed an increase in GC related waits.

The attempts to limit or eliminate cache fusion failed and resulted in poorer performance overall. While we reduced the gc buffer busy acquire waits and in some cases increased the db file sequential reads, the increase in the wait times for the GC related waits offset any gains that were made. All attempts to defeat cache fusion with the use of the gc_files_to_locks parameter were unsuccessful and resulted in poorer performance.

Once the full paper is available I will post a link to the TMS website.

More fun with Oracle11g 11.1.0.7

I have been working with the 11.1.0.7 release of Oracle11g for a couple of months now. I have to say I have not been impressed with it. As released there are several major bugs that really limit 11.1.0.7 usability and stability.

Right out of the gate when I upgraded my 11.1.0.2 environment to 11.1.0.7 I had problems. If you utilize the automated memory management features using MEMORY_MAX_SIZE and MEMORY_TARGET, on 64 bit RedHat Linux you are limited to 3 gigabytes of total SGA, that’s right folks, less than you can get on 32 bit! This was easily mitigated by going back to full manual memory management. So much for the much touted AMM improvements in 11g.

My setup for testing is a 4 node luster using Dell servers with 8 CPUs each and 16 gigabytes of memory per server. I am using Infiniband interconnects on the 4 server cluster. Initially I would get ORA-00600 errors and server reboots if we loaded down the system doing a single user 300 GB TPC-H. For the most part I was able to fix that by relinking the Oracle kernel to use the RDS protocol with Infiniband, however, on some large queries using cross instance parallel query and large amounts of partitioning I still cannot complete a full 22 query TPC-H without at least one of the queries throwing an ORA-00600.

The next test was TPC-C using large numbers of users and cross instance parallel query. Following the example of some recent HP TPC-H runs I attempted to use clustered tables. As long as I used single table clusters, everything seemed to go alright, but as soon as I attempted a dual table hash cluster things went pear-shaped and I had to revert to normal tables. This was a known Oracle bug it seems but hasn’t been made publically available in Metalink. It seems I could load one of the tables in the cluster but then when attempting to load the second table in the 11g cluster it would error.

The next bit of testing involved creating a large table (60,000,000 2K rows) then duplicating the table, doing a select that forced temp usage, doing a self-join with large temp usage and creating a large index. Since I had 8 CPUs I initially started with a DOP of 8 increasing it by 8 as I added in each node up to a maximum of 32. Sometimes this would work, other times parallel query servers would die horrible deaths causing the transaction to fail. Now, for a 16 gig server with 8 CPUs I would expect to be able to get well above a DOP of 8 on a single table. In fact, during the load process the last several iterations use a single instance DOP of 64 with no issues. It seems as soon as I add more than 1 or 2 additional nodes, things start to get wonky (a scientific term meaning “wonky”), parallel query slaves commit suicide at an alarming rate and sometimes entire servers reboot. I reduced to 6 DOP and still see the failures on most runs above a server count of 2.

Of course you must realize from talking with support most of the internal testing and support is done on 32 bit machines. Many times support told me that they can’t test large memories or large databases because of this. Come on Oracle, take some of the interest on your billions in profits and upgrade your support servers! Buy some disk drives! Geesh!

Of course most of my work is done on gear that supports over 100,000 IOPS (actually more than this by several factors) and many beta testers may not have the 500 disk drives to allow for 100,000 IOPS or access to SSD technology to get this type of IO load. Maybe these Oracle11g stability issues only show up at high IO rates. However, with many manufacturers now offering SSD technology (SUN, HP, EMC) to go with their standard disk systems and of course, TMS, Violin, FusionIO and several other SSD vendors offering SSD systems that easily top 100,000 IOPS or more, Oracle better get with it and start doing significant testing in high IOPS situations, or at least, make sure and include the partners who have access to this technology in their betas.

I am not sure what happened with the beta testing of this (11.1.0.7) release, had I been included I would have quickly showed them these bugs as I believe in testing real world database situations. I don’t know whether to look forward to 11gR2 with anticipation or dread (I was not invited into that beta either), if the beta programs are as detailed as they were for 11gR1 we might be in for a rough ride.

Outsource This!

I just watched President Obama’s speech on the educational reforms that he proposes. As laudable as his proposed fixes are, I mean after all, who isn’t for tougher standards for graduates, more pay for good teachers and accountability at all levels, I feel perhaps he has missed the boat in a key area.

The New York Times recently published an article stating that the college graduate has seen a bigger decline in employment than just about any other sector. Don’t get me wrong, a college graduate still gets better pay and benefits than a non-college graduate, but just getting a degree will not guarantee you the American dream any longer.

The key area that President Obama has neglected is that American college graduates expect to receive wages in excess of $60K per year. Unfortunately foreign graduates who still reside overseas are usually pleased to receive the equivalent of a third of this amount or less, especially in the Asian countries and in Latin America. This means that by outsourcing a company can get 3 times the number of employees and hopefully three times the productivity. This isn’t always the case but that is the logic used to justify sending jobs overseas.

What many companies are finding however is that those budget employees come with their own issues. For example, a programmer who has never experienced a free market economy may not understand all the intricacies of accounting in such a system. Of course I probably needn’t mention the various cultural and language difficulties that are also experienced with outsourcing.

I am afraid that many of the stimulus jobs that seem to be offered from the President’s plans seem to be short term blue collar type jobs that won’t do much to help the college graduate with advanced degrees. If you work outside of the computer industry, for example, for two years as a heavy machinery operator on a construction job, you will find your computer science degree probably isn’t worth much anymore.

Soon the only way to get a job will be to move out of the USA and go to a country where the cost of living is in line with what the companies who outsource are willing to pay employees, but don’t look for benefits!

God and Technology

Many times I have been asked how as a technologist/scientist I can believe in God. It is as if these people believe you cannot be a thinking person and also believe in a higher power. Now, this is not to say I am one of the folks who think every word in the Bible (Old and New Testament) is the gospel truth (no pun intended.) Like I said, I am a thinking Christian, as such I question everything and look for confirmation of those things that can be confirmed. Of course with religion, as in all things in science and lay areas, eventually you reach a point where it is either believe or not believe and I chose to believe.

I prefer to think of myself as more a Jeffersonian Christian rather than a Paulian Christian. It amazes me that many Christians swallow hook-line-and-sinker every word penned by the one Apostle that never actually met Jesus face-to-face. Many scholars feel that Paul was sent on so many missions not because he was good at them but because the other Apostles really couldn’t stand him and hoped that he wouldn’t return. Many believers take Paul’s letters out of context and usually completely incorrectly as their meaning is understood by true bible scholars. In fact many articles of faith were added in after the fact to the regular gospels as can be proved by stylistic differences and from going back to the earliest known translations. The fact is that no matter how current your translation you are still starting from flawed beginnings. Many of the books that “didn’t make the cut” when the first bibles were compiled were destroyed as heresy thus removing them from possible future examination.

With any scientific field of study you reach a certain point and you can go no further, from that point on you have to accept things on theories and faith. Even with the less than whole cloth parts of the Bible’s New Testament removed, what is left is still an amazing history of a real man who lived, and died for his faith and his friends. Is Jesus the Son of God? Yes, but then we all are the sons and daughters of God. Did Jesus die for our sins? Yes. Was he raised from the dead? This is where there is some contention over what was added after the fact and what is whole cloth. But let’s examine this.

Two men: One knows he is the physical Son of God, he knows that no matter what evil painful things happen on Earth he has a place at the right hand of God. Second man, a man, with man’s frailties, and doubts. Now, both give up their Earthly lives for what they believe in, which one required greater faith? Would Jesus be less or more of an inspiration if he was a frail human or the anointed Son of God? Would you believe it was a vote by a group of flawed humans (the Nicene Council) that decided Jesus was a deity and it was actually a very close vote.

Unfortunately the only documents that provide “proof” of Jesus’ deity are in the Bible and using the Bible to prove the Bible is circular logic and therefore flawed. It is like using the Dianetics text to prove L. Ron Hubbard’s qualifications as a deity. Since most Islamic accounts of Jesus are actually taken from the Bible then related texts quoting them are not relevant. While Jesus is mentioned in some historical texts none go into great detail as to his birth, (a virgin was a young maiden, not someone who had never had sex) life (he existed and taught and was hated by Rome), death (he was probably crucified) or resurrection. These accounts of the resurrection were actually added after the original text in the gospels, it was felt that the Mithran belief, having a virgin birth, life and resurrection was a big spur to add these passages. (see: http://www.near-death.com/experiences/origen048.html) Among non-Christian historians, Pliny the Younger, Suetonius and Tacitus refer to Jesus, as does Josephus (Joseph ben Matthias).

So, do I believe Jesus is my savior? Yes, his teachings show the way to the father as he himself said “There is no way to the father but through me” meaning through his teachings we find the way. Do I believe in the resurrection? That is more complex to answer. Unfortunately the resurrection is one of the parts added after the original text, that makes it suspect in my eyes. The key question is “Would I believe without the resurrection?” The answer is yes, I would, so whether I believe in the resurrection or not is moot. You are free to believe as you wish and I would never dream of pushing my beliefs onto you, after all, we have free will.

Do I believe in the life everlasting? Yes. There is enough anecdotal evidence to show that something of us exists after death, that the spirit left gets rewarded or punished based on a set of criteria created within its own belief structure is not that far of a reach. After all Jesus also said “In my Father’s house are many mansions, I go there now to prepare a place for you.”
So what have I attested to? I believe in God and I believe in Jesus. I believe Jesus died for my sins. I believe Jesus’ teachings show the way to true belief in God. If this diminishes me in some folk’s eyes, so be it. However, it is not for people that I live, I live for God, my family and myself.

Does God reject technology? No, he gave us technology to better ourselves. As with any tool, how we use it determines whether the tool is good or bad. Do modern teachings contradict the Bible? No. If you realize that most of the creation story is metaphor, used to explain something we don’t have full understanding of even today, to ignorant herders. When looked at as metaphor it actually parallels what we know. Look at the theory of vacuum fluctuations and compare it to the story of genesis. As to what timelines are used in the Bible, again, try to explain millions of years to someone who barely understands how to count his herd of sheep.

It is odd that those that insist on a literal interpretation of their favorite passages that damn certain behaviors or exalt other behaviors they profess to believe in themselves but then they tell us other parts are metaphor. Remember that the true test of a prophet is that what he prophesizes comes true. I am afraid many of the added texts in the Bible fail this test as do many of the founders of many splinter religions who used imperfect understanding to make prophesies of specific dates for events such as the “rapture” and the second coming. Of course instead of applying the test to these false prophets and rejecting them, they merely allowed that they were mistaken but that their prophesies would come true eventually.

Limiting God to a simplistic creation story is demeaning to God. That God could put into motion such marvelous mechanisms as those behind vacuum fluctuations and evolution is a testament to his greatness, not a detractor from it. That we cannot understand everything is a testament to God’s greatness . God guides technology, giving us tools to better understand his universe.

Burying our heads in simplistic beliefs because we cannot understand God’s plan as implemented in his universe is an affront to God.

RMOUG Notes

Well, I am on my way home from the Rocky Mountain Oracle Users Group Trainings Days event. I presented a paper titled “Is Oracle Tuning Obsolete,” a copy of which can be found on the http://www.rmoug.org/ site or at http://www.superssd.com/. While I was there I attended two presentations on the Oracle/HP Exadata Database Machine, one by Kevin Closson and another by Tom Kyte, both of Oracle.

My only complaint about both presentations was that when they presented the user test results they neglected to show the full (or even partial) configurations of the servers and disk systems they had tested against. Rather like saying my car is 10 times faster than Joe’s and telling you mine is a 1995 Dodge Avenger and failing to mention Joe’s is a Stanley Steamer. Be that as it may, I still enjoyed the presentations and the best take away was from Kevin’s presentation when he said that “If your current system is fully tuned, has adequate disk resources, and is performing well, the Exadata has nothing to offer you.” An example from kevin would be a 128 CPU Superdome with 128 4GFC HBAs that were being fed by ample XP storage as that would be 51GB/s ingest-capable. Also during Tom’s presentation he admitted the primary target of the Exadata was those shops with row-after-row of Oracle servers followed by a single Netezza or Teradata server or servers.

Essentially the Exadata Database Machine is targeted at the larger (several terabytes) data warehouse that would otherwise be placed on a Netezza or Teradata machine and I couldn’t agree more. However, it would be a fun test to replace the disks in an Exadata cell with a RamSan-500 and see what (if any) additional performance could be gained. After all, the disks are still the limiting factor in the performance of the system. For example, a single Exadata cell tops out at around 2,700 IOPS, according to white papers on the Oracle site; a single RamSan-500 can sustain 100,000 mixed read/write IOPS and 25,000 pure write IOPS with minimal response times. As far as I can tell, no additional smarts are built into the Exadata disk drives in the place of special firmware, such as is supposedly done with EMC systems, so replacing the drives with a single RamSan-500, either set up as 12 LUNs, or as a single large LUN, should be easy.

Another interesting discussion I had during this time frame was with our (Texas Memory Systems) own Matt Key, one of our Storage Applications Engineers, about why adding the Enterprise Flash Drives (EFDs) to arrays produces little if any benefit for large levels of writes. Turns out there is an upward limit on the bandwidth a single disk tray can handle and with the EFDs instead of disk drives the disk tray tops out at around 3000 (between 1600 and 3200) or so IOPS (based on a 64K stripe) so you actually need several trays (with a max of only 4 drives to a tray because of other limits) to get significant write IOPS. For comparison, the RamSan-500 can handle 25,000 sustained write IOPS. Now don’t get me wrong, the EFDs can improve the performance of certain types of loads when compared to a standard array with no EFDs, but if you are write-heavy you may wish to consider other technologies. Note: The calculations are based on a 200 megabyte/second FC-AL bandwidth with 64K writes, since RAID6 is used there are 2-64K writes for each write, 200MBS/64K=3200 IOPS, 200MBS/128K=1600 IOPS. These limitations apply to all array-based EFDs.

The RamSan-500 makes an excellent complement to any enterprise array, especially if you use the preferred read technology to read from the RamSan-500 while writing to both, for example, when you are using array-based replication, such as SRDF, to provide geo-mirroring of the frame to a remote site. By offloading the reads, the number of writes that can be supported by the array can be increased as a factor of the percent of reads in the work load, thus increasing the performance of the entire system. As an example, if you have an 80/20 read/write workload and you offload the 80 percent of reads to the RamSan, this frees up the array to handle a factor of 4 more writes, up to the actual maximum IOPS of the array. This is a 4X increase in I/O with 0-impact to infrastructure or BCVs.

Oh, on February 24-25 I’ll be in Charlotte, NC presenting at the Southeast Oracle Users Convention (SEOUC). My two presentations are: “My Ideal Data Warehouse System” and “Going Solid: Use of Tier Zero Storage in Oracle Databases.” I hope I see you there!

As I digest more of the information I obtained this week, I will try to write more blog entries. So for now I will sign off. Good bye from 37,000 feet over Colorado!

Mike

Do You Need Solid State Technology?

Many times I am asked the question “Should I buy solid state devices for my system?” and each time I have to answer “It depends.” Of course the conversation evolves beyond that point into the particulars of their system and how they are currently using their existing IO storage subsystem. However, the question raised is still valid; do you need SSD technology in your IO subsystem? Let’s look at this question.

SSD Advantages

SSD technology has one big advantage over your typical hard disk based storage array: SSD does not depend on physical movement for retrieval of data. Being non-dependent on physical movement for data retrieval means that you can significantly reduce the latency involved with each data retrieval operation, usually on the order of a factor of 10 (for Flash-based technology) to over 100 (for RAM-DDR -based technology.) Of course cost increases as latency decreases with SSD technology, with Flash running about a quarter of the cost of RAM-DDR technology.

SSD Costs

Flash and DDR-based SSD technology are usually on a par with, or can be cheaper than, IO equivalent SAN based technology. Due to the much lower latency of SSD technology you can get many more input-output operations per second (IOPS) from them than you can from a hard disk drive system. For example, from the “slow” Flash-based technology you can get 100,000 IOPS with an average latency of 0.20 milliseconds worse case. From the fastest DDR based technology you can achieve 600,000 IOPS with a latency of .015 milliseconds.

To achieve 100,000 IOPS from hard drive technology you would need around 500 or more 15K rpm disks at between 2 and 5 milliseconds latency, giving a peak IOPS of around 200 per disk drive for random IO, regardless of their storage capacity. A 450 gigabyte 15K rpm disk drive may have up to 4 or more individual disk platters with 12 read-write heads (one for each side of the disks); however, these read-write heads are mounted on a single armature and are not capable of independent positioning. This limits the latency and IOPS to that of a single disk platter with two heads, so an 146 gigabyte 15K rpm drive will have the same IOPS and latency as a 450 gigabyte 15K rpm drive from the same manufacturer (http://www.seagate.com/docs/pdf/datasheet/disc/ds_cheetah_15k_6.pdf.)

Given that the IOPS and latency are the same regardless of storage capacity for a 146 to 450 gigabytes array of disk drive sizes why not pick the smallest drive and save money? The reason is that to get the best latency you need to be sure not to fill the various disks in the disk drive (from 2 to 4) more than 30% hence the need for so many drives. So to get high performance from your disk based IO subsystem you need to throw away 60-70% of your storage capacity!

Do I Need That Many IOPS?

Many critics of SSD technology state that most systems will never need 100,000 IOPS, and in many cases they are correct. However, in testing using a 300 gigabyte TPC-H (data warehouse) type test load using SSD I was able to get peak loads of over 100,000 IOPS using a simple 4 node Oracle11g Real Application Clusters-based setup. Since many systems are considerably larger than 300 gigabytes and have more users than the 8 users with which I reached 100,000 IOPS, it is not inconceivable that given the capability to achieve 100,000 IOPS of throughput many current databases would easily exceed that value. It must also be realized that the TPC-H system I was testing utilized highly optimized indexing, partitioning, and parallel query technology; eliminate any of these capabilities and the IOPS required increases, sometimes dramatically.

Questions to Ask

So now we reach the heart of the question, do you need SSD for your system? The answer depends on several questions which only you can answer:

1. Is my performance satisfactory? If yes then why are you asking about SSD?
2. Have you maximized use of memory and optimization technologies built into your database system? If no, then do this first.
3. Has my disk IO subsystem been optimized? (Enough disks and HBAs?)
4. Is my system spending an inordinate amount of time waiting on the IO subsystem?

If the answer to question 1 is no, and questions 2, 3 and 4 are yes then you are probably a candidate for SSD technology. Don’t get me wrong, if I had my choice I would skip disk based systems altogether and use 100% SSD in any system I bought, given the choice. However, you are probably locked into a disk-based setup with your existing system until you can prove it doesn’t deliver the needed performance. Let’s look closer at the 4 questions.

Question 1 is sometimes hard to answer quantitatively. Usually the answer to 1 is more of a gut reaction than anything that can be put on paper. The users of the system can usually tell you if the system is as fast as they need it to be. Another consideration in question 1 is: performance is fine now, but what if you grow by 25-50%? If your latency is at 3-5 milliseconds on the average now, adding more load may drive it much higher.

Question 2 will require analysis of how you are currently configured. An example from Oracle is that a wait on db file sequential reads can indicate that not enough memory has been allocated to cache data blocks read based on index reads. So, even if the indexes are cached, the data blocks are not and must be read into the cache on each operation. A sequential read is an index-based read followed by a data table read and usually should be cached if there is sufficient memory. Another Oracle wait, db file scattered reads indicates full table scans are occurring. Usually full table scans can be mitigated by use of indexes or partitioning. If you have verified your memory is being used properly (perhaps everything that can be allocated has been) and you have utilized the proper database technologies and performance is still bad, then it is time to consider SSD.

A key source of wait information is of course the Statspack or AWR report for Oracle based systems. One additional benefit to the Statspack or AWR reports is that they both can contain a Cache Advisory sub-section that is used to actually determine if adding memory will help your system. By examining waits and looking at the cache advisory section of the report you can quickly determine if adding memory will help your performance. Another source of information about the database cache is the V$BH dynamic performance view. The V$BH view contains an entry for every block in the cache and with a little SQL against the view you can easily determine if there are any free blocks or if you have used all available and are in need of more. Of course use of the automated memory management features in 10g and 11g limit the usefulness of the V$BH view. In Oracle Grid and Database control interfaces (providing you have the proper licenses) you also get performance advisories which will tell you when you need more memory. Of course if you have already maximized the size of your physical memory, most of this is moot.

Question 3 may have you scratching your head. Essentially if your disk IO subsystem has reached its lowest latency and the number of IO channels (as determined by the number and type of host bus adapters) is such that no channel is saturated, then your disk-based system is optimized. Usually this is shown by latency being in the 3-5 millisecond range and still having high IO waits with low CPU usage.

Question 4 means you look at you system CPU statistics and you see that your CPUs are being under utilized and the IO waits are high, indicating the system is waiting on IO to complete before it can continue processing. A Unix or Linux based system in this condition may show high values for runqueue even when the CPU is idle.

SSD Criticisms

Other critics of SSD technology cite problems with reliability and possible loss of data with Flash and DDR technologies. In some forms of Flash and DDR they are correct; if the Flash isn’t wear leveled properly or the DDR is not properly backed-up. However, as long as the Flash technology utilizes proper wear leveling and the RAM-DDR system uses proper battery backup with permanent storage on either a Flash or hard disk based subsystem, then those complaints are groundless.

The final criticism of SSD technology is usually that the price is still too high compared to disks. I look at an advertisement from a local computer store and I see a terabyte disk drive for $99.00; it is hard for SSD to compete with that low base cost. Of course, I can’t run a database on a single disk drive. Given our 300 gigabyte system, I was hard-pressed to get reasonable performance placing it on 28 – 15K high performance disk drives; most shops would use over 100 drives to get performance. So on a single disk to SSD comparison yes, this cost would appear to be an issue; however, you must look at other aspects of the technology. To achieve high performance most disk-based systems utilize specialized controllers and caching technology and spread IO across as many disk drives as possible. This is known as short-stroking the drive so that only 20-30% of each disk drive is ever actually used. The disks are rarely used individually, instead they are placed in a RAID array (usually RAID 5, RAID 10, or some exotic RAID technology). Once the cost of additional cabinets, controllers, and other support technology is added to the base cost of the disks, not to mention any additional firmware costs added by an OEM, the costs soon level between SSD and standard hard drive SAN systems.

In a review of benchmark results the usual ratio between needed capacity and capacity utilized to achieve performance is 40-50 to 1, meaning our 300 gigabyte TPC-H system would require at least 12 terabytes of storage to provide adequate performance spread over at least 200 or more disk drives. To contrast that, an SSD based system would only need a factor of 2 to 1 (to allow for the indexes and support files).

In addition to the base equipment costs, most disk arrays consume a large amount of electricity which then results in larger heat loads for your computer center. In many cases the SSD technology only consumes a fraction of the energy and cooling costs of regular disk based systems, providing substantial electrical and cooling cost savings over their lifetimes. SSD by its very nature is green technology.

When Doesn’t SSD help?

SSD technology will not help CPU bound systems. In fact, SSD may increase the load on overworked CPUs by reducing IO based waits. Therefore it is better to resolve any CPU loading issues before considering a move to SSD technology.

In Summary

The basic rule for determining if your system would benefit from SSD technology is that if your system is primarily waiting on IO then SSD technology will help mitigate the IO wait issue.

Scuba Diving New York City

A few months ago I wrote a blog about global warming and some possible mitigating actions we could all take (here: http://mikerault.blogspot.com/2008_04_01_archive.html) some might have been a bit off the wall, but were meant to make people think. Of course now many leading scientists are saying there is nothing we can do to radically affect global warming trends for the next 1000 years (http://www.abc.net.au/news/stories/2009/01/27/2475687.htm). The article pretty much echoed the sentiments I expressed in an earlier blog of mine in which I stated that while we as a species may have added 1 or 2 percent to the overall picture we are on a warming and CO2 curve that is following a natural cycle that seems to occur about every 100,000 years or so according to studies of ice cores from the Vostok site in Antarctica (http://www.daviesand.com/Choices/Precautionary_Planning/New_Data/). If there was a direct correlation between temperature and CO2 concentration then we would all be par boiled since we are at a level almost 100 PPM above highest historical levels and so the temperature should be 5-10 degrees above what it currently is, when in fact, we are actually cooler by up to 2 degrees than we should be according to the historical data.

Perhaps my most wild eyed suggestion was to place a solar shield between the Earth and the sun to reduce the amount of solar energy reaching the Earth. Oddly the most wild eyed suggestion seems to be the only one that would make a difference at all. Unless we find a way to reduce the amount of solar energy reaching the Earth’s surface we can expect global temperatures to increase steadily until there are no ice caps, Arctic or Antarctica.

The net effect of the melting of the melting of the Arctic ice cap would be negligible since it is in effect floating so the change in sea levels would be near zero, however, the polar bears would have a few issues. The biggest problem would be the Antarctic ice sheet which is resting on the continent of Antarctica. As it melts and adds to the water levels the weight compressing the Antarctic land mass decreases causing rebound. Between the added water level and the displacement from rebound we are talking over 100 feet of additional water levels around the world. Say good bye to New York, London, Hong Kong, Tokyo, almost all of Florida, heck, most of our seaports. You think Bangladesh has problems with flooding now, just wait.

We need to start planning and doing something now. By reducing the amount of sunlight we receive by 10% we could nip this issue in the bud before 42nd street is considered an advanced level scuba dive. This would be done by orbiting a single large sunscreen or multiple smaller sunscreens in the L1 Lagrange point. Maybe by making these sunscreens actually into thermionic generators or simple solar cell arrays we could also provide large amounts of energy which could be sent back to Earth via microwave beams for use as a green energy source(http://gltrs.grc.nasa.gov/reports/2004/TM-2004-212743.pdf). To block 10 percent of the energy of the sun that reaches the Earth sounds a bit crazy but it could be done.

Even by spreading large clouds of metallic debris (how about all those aluminum cans we see lining the roadways?) or using a few large asteroids pushed into place using low thrust ion drives (http://www.grc.nasa.gov/WWW/ion/) .

It is time to look out for all of us and put aside petty (in the scheme of global warming scale disaster) differences and pull together to really do something to fix this problem. Driving a fuel efficient car and turning your thermostat down in the winter and up in the summer may give you warm and fuzzy (or cold and fuzzy depending on the season) feeling but it won’t amount to a hill of beans when it comes to helping fix global warming.

Database Entomology in Oracle11g Land

For the past several months I have been working with Oracle11g, 11.1.0.6 to be exact, doing TPC-H runs, tuning exercises and putting it through its paces. My platform is a 4-node, 32 CPU 64 bit Dell cluster connected to both a solid state disk (SSD) storage array set and standard 15K hard drive (HD) JBOD arrays. On this test rack I created a 300 gigabyte TPC-H test database, well, actually 600 gigabytes with dual identical TPC-H setups in the same database one on SSD and the other on HD.

As a pre-test I had created a small TPC-H (not more than 30 gigabytes) and on a single-server rig I could run the complete 22 query TPC-H set of queries in parallel query. Of course I wasn’t using high levels of partitioning and parallel query simultaneously with the small data set.
I knew it would be interesting when I got to query 9 on the 300 gigabyte dataset and on query 9 I got an ORA-00600:

ORA-00600: internal error code, arguments [kxfrGraDistNum3],[65535],[4]

When I ran an 8 stream randomized order query run I also periodically received:

ORA-12801: error signaled in parallel query server P009, instance dpe-d70:TMSTPCH3 (3)
ORA-00600: internal error code, arguments: [qks3tStrStats4], [], [], [], [], [], [], []

On query 18 once in a while but not every run. Due to not being a full customer (only having a partner CSI number) I was unable to report these as possible bugs. I did completely check out OTN and Metalink as well as Google and no one else seems to be having these issues, of course how many folk are running Oracle11g 11.1.0.6 or 11.1.0.7 with RAC, cross instance parallel query and heavy partitioning and sub-partitioning?

I had a quick look at the Oracle11g 11.1.0.7 release notes and saw a load of bug fixes and hoped mine were covered, even though a text search didn’t show up the ORA-00600 arguments I received. So I bit the Oracle bullet and performed an upgrade.

Well, actually 2 sets of upgrades. First I upgraded my home, 32-bit (this is important later) servers and other than the usual documentation gotchas and needing to set the database as exclusive, start it, stop it and then reset it as a cluster before the dbua program would run properly, I was successful and now have an Oracle11g 11.1.0.7 instance running on my home RAC setup. A quick run against the 30 GB database showing no really stellar improvements against my test setup using JBOD arrays for TPC-H and it successfully ran Query 9 against the non-partitioned, smaller 30 gigabyte data set.

Feeling pleased with the success I immediately set out the next morning to update my large test environment, my 64 bit cluster. The CRS update went smoothly, and other than some space issues (you may want to add a datafile to your SYSTEM tablespace) and some package problems (for some reason DBMS_SQLTUNE and DBMS_ADVISOR where missing) the database upgrade went fine, right up to the point of starting the instances under 11.1.0.7. It seems there is just a small bug with the use of the new MEMORY_TARGET parameter and release 11.1.0.7…you can’t go above 3 gigabytes! This is why I said that the upgrade on 32 bits was important to remember, in 32 bit systems you will rarely get above a 3 gigabyte SGA size once you allow for user logins, process space and operating system memory needs. However, one of the major reasons for going to 64 bit is to have SGA sizes in Oracle greater than 4 gigabytes. Now, if you go back to using the SGA_MAX_SIZE and SGA_TARGET or the full manual specifications such as SHARED_POOL_SIZE and DB_CACHE_SIZE you can get above the 3 gigabyte setting.

Another annoying thing with 11g and the MEMORY_MAX_SIZE setting is that you cannot exceed MEMORY_MAX_SIZE with the sum of your SGA settings plus PGA_AGGREGATE_TARGET. Now for those of you with small sort sizes this isn’t really a problem and you probably won’t have any issues. However, with a TPC-H you need a large sort size so you need a large PGA_AGGREGATE_TARGET but, you quickly get into trouble with the limits on MEMORY_MAX_SIZE and large PGA_AGGREGATE_TARGETS. With my 16 gigabytes of memory per server I was only able to allocate about 8 gigabytes to Oracle (actually about 7.5 in 11.1.0.6 and 3 in 11.1.0.7) anything larger and I would get errors. So needless to say, I turned off the total memory management and did it the old fashioned way.

Finally, I had my instances up, about a 7 gigabyte SGA with 5 gigabytes of DB_CACHE_SIZE and 5.5 gigabytes of PGA_AGGREGATE_TARGET. Oh, did I mention, your SHARED_POOL_SIZE must be at least 600 megabytes for Oracle11g 11.1.0.7? If it isn’t you will get 4030 errors on startup, I ended up with 750 megabytes worth.

So after 8 hours of upgrade time for my main set of instances I was finally ready to run a TPC-H. Guess what, with the upgrade in place, more cache and bigger sort areas, I seem to be getting worse performance than with the sub-optimal query resolving, bug-ridden 11.1.0.6 version. Looks like they fed the bugs instead of killed them. Oh well, back to the tuning bench.

Happy Holiday Shopping - Not!

Recently, due to the dramatic increase in the number of online shoppers and unplanned for peaks in attempted access/logins, several major retail websites have experienced anything from dramatic slowdowns to out and out failure. Of note are the recent problems at the Sears and Dr. Pepper websites.

Sears and Others:
http://money.cnn.com/2008/11/28/technology/bc.apfn.tec.holidayshop.ap/index.htm
http://tasquatch-sentinelling.blogspot.com/2008/11/blog-post_7519.html

Dr. Pepper:
http://www.bizjournals.com/atlanta/stories/2008/12/01/daily45.html

In order to understand what is happening you must understand what occurs when a user attempts to access a website, let alone when they attempt a complex transaction. When a user logs in to a website the user identification must be validated from a database using several database queries. For example: Is the user ID valid? Is the password correct? Has the password expired? What type of user is this? etc. It is even worse if the user has to create a login and password as well as enter other data such as address or credit data. All of this transactional traffic causes a flurry of underlying IO subsystem activity and web traffic across the networks. And of course all of this is magnified when actual query and sales transactions are also being performed.

All of the traffic to the system database can overwhelm the underlying IO subsystem, especially when it is disk array based which is the case with a majority of databases. Disks generally can only respond within a 5 millisecond window. Now, with on-disk caching and large caches in the disk arrays this response time can sometimes be reduced to 1 millisecond but as the caches flood with high activity performance generally drops to 5 milliseconds per IO or more. As the time to respond increases the number of users which can be served drops in a direct example of Little’s Law.

Unfortunately, while you can increase the IOPS (input output per second) by increasing the number of disks in an array, you cannot decrease the latency beyond that of any one disk. In fact, for a large read you will suffer from convoy effect where the slowest disk in the array involved in the IO operation will drive down the performance of the IO itself.

Several websites have found that by placing the user tables and other transaction dependent tables on low latency storage such as solid state SAN replacements like the RamSan 400 or 500 series from Texas Memory Systems they can dramatically increase the capability to support many more transactions (read clients) than before. An example of the dramatic improvements that can be achieved is shown in the recent press release from The Container Store group:

http://www.superssd.com/pressrelease/2008-12-02.htm

Realizing that their underlying IO subsystem couldn’t support the expected peak load generated from a positive blurb about the company on The Oprah Winfrey Show, the folks at The Container Store turned to TMS RamSan technology.

Another area that must support high concurrent logins and maintain strict inventories is in the online gaming community. Eve Online was able to go from 15,000 online users to over 17,000 with a 40x performance improvement:

http://www.superssd.com/success/ccpgames.htm

As a final example, IC Source first tried doubling the number of disks in their infrastructure; the net result? Zero improvement only to solve the problem with SSD (RamSan) technology:

http://www.superssd.com/success/icsource.htm

What all of these examples show is that many times your problem cannot be solved by throwing more disks at it, you must get to the ultimate problem, latency, to fix the issue.

With latency numbers of 15 microseconds (.015 milliseconds) and the resulting ability to support 600,000 IOPS and 4.5 GB/sec the RamSan-440 is the heavy hitter in the TMS line as far as throughput however, it is DDR RAM based and currently limited to 0.5 terabytes of storage capacity per unit. Compared to normal disk latency of 5 milliseconds the RamSan-440 shows a factor of 333 decrease in latency, even if you get 1.0 millisecond latency due to short-stroking and aggressive caching the 440 is still a factor of 67 times faster.

http://www.superssd.com/products/RamSan-440/

The RamSan-500 series utilizing Flash memory technology tops out at 2 terabytes (with promises to go to 8 terabytes in the near future) of storage capacity per unit and 200 microsecond ( 0.2 milliseconds ) peak latency with a minimal IOPS rating of 100,000 IOPS. Just to put this in perspective, EMC recently achieved 100,000 IOPS using 2-CX30 racks and 391 disk drives in a RAID0 configuration, the RamSan-500 does it with a single 4-U unit, and the RamSan-440 beats it by a factor of 6 in a similar footprint as the RamSan-500.

http://www.superssd.com/products/RamSan-500/

Being solid state (except for the cooling fans) means the RamSan technology is inherently more reliable and less prone to crashes. Current estimates of MTBF show a value of at least 500,000 hours per RamSan before a critical failure. With built in RAID, write leveling for Flash and the use of ECC memory as well as ChipKill the RamSans have built in redundancy. Utilizing Flash drives for backup, the 440 also provides unparalleled data persistence with triple battery backup ensuring that all data is written to Flash before shutdown. The RamSan-500 also uses battery backup to ensure that the 64 gigabytes of DDR cache is written to Flash on shutdown.

Another big movement is the green technology push we see today. I’ll leave the math on figuring the amount of electrical and cooling costs disk arrays to you, but at under 300 watts for the RamSan-500 and 600 watts for the RamSan-440 it is easy to see the cost savings from the energy footprint reduction.

So what should you take away from all of this? Essentially, if you have reached the latency limit of your IO subsystem, increasing the number of disks will not help. The only way to improve the performance of the system is to reduce overall latency. If you are an online retailer looking at the coming holiday season with dread because of performance issues, look at using SSD technology such as the TMS RamSan 400/500 series to slay the latency monster and achieve stellar website performance.

From Mike at 30,000 feet

Sorry I haven’t been blogging much the last few weeks. Between various conferences and presentations I have been on the road and when not playing road warrior I have been configuring the lab to run a 300 gigabyte TPCH. Unfortunately during my first tries of the TPCH protocols I tripped over a bug in Oracle11g dealing with parallel query and partitioning, both of which are a requirement if you are to complete a 300 gigabyte TPCH. Hopefully I will find a patch waiting once I get back to the office so I can really dig in and see what I can wrest from the RamSan 500, 400 and my 4 node configuration.

I have also been busy creating a demonstration database to show a side-by-side comparison of the differences in performance between disk and RamSan, after all seeing is believing as the old saw goes. I am writing this entry while flying over the mountains of Virginia and North Carolina on my way home from giving a one day tuning seminar in Stirling, Virginia at Oracles offices there under the auspices of the NatCap Oracle group. I leave again next week to give the same seminar to the Dallas Oracle User Group at the Oracle office in Plano Texas and from there go on to the Super Computing conference in Austin and then finally home again to Alpharetta for the Thanksgiving holiday.

I am also compiling a set of statistics to show the IOPS/gigabyte needed by Oracle databases of various sizes, in this task I will be gathering what historical data I have from past clients and if any of you have that type of data for your systems I would love to have it to add to the mix. If I have time I will try to come up with a query that shows this for a system and will post it. I imagine it will involve a rather simple sum of gigabytes (maybe just the active ones from dba_segments) and a v$sysstat capture of read and write IOPS. Here is a simple first cut, no doubt someone can make it simpler:

set serveroutput on
SET FEEDBACK OFF
col tod new_value now noprint
select to_char(sysdate, 'ddmonyyyyhh24mi') tod from dual;
TTITLE 'IOs Per Second'
col null noprint
select null from dual;
spool io_sec&&now
declare
cursor get_io is select
nvl(sum(a.phyrds+a.phywrts),0) sum_io1,nvl(sum(b.phyrds+b.phywrts),0) sum_io2
from sys.gv_$filestat a,sys.gv_$tempstat b;
cursor get_gig is select
sum(bytes)/(1024*1024*1024) from dba_segments;
gig number;
now date;
elapsed_seconds number;
sum_io1 number;
sum_io2 number;
sum_io12 number;
sum_io22 number;
tot_io number;
tot_io_per_sec number;
fixed_io_per_sec number;
temp_io_per_sec number;
iopsgig number;
begin
open get_io;
fetch get_io into sum_io1, sum_io2;
open get_gig;
fetch get_gig into gig;
close get_io;
close get_gig;
select sum_io1+sum_io2 into tot_io from dual;
select sysdate into now from dual;
select ceil((now-max(startup_time))*(60*60*24)) into elapsed_seconds from gv$instance;
fixed_io_per_sec:=sum_io1/elapsed_seconds;
temp_io_per_sec:=sum_io2/elapsed_seconds;
tot_io_per_sec:=tot_io/elapsed_seconds;
iopsgig:=tot_io_per_sec/gig;
dbms_output.put_line('Elapsed Sec :'to_char(elapsed_seconds, '9,999,999.99'));
dbms_output.put_line('Fixed IO/SEC :'to_char(fixed_io_per_sec,'9,999,999.99'));
dbms_output.put_line('Temp IO/SEC :'to_char(temp_io_per_sec, '9,999,999.99'));
dbms_output.put_line('Total IO/SEC :'to_char(tot_io_Per_Sec, '9,999,999.99'));
dbms_output.put_line('Total Used Gig:'to_char(gig, '9,999,999.99'));
dbms_output.put_line('Total IOPS/Gig:'to_char(iopsgig, '9,999,999.99'));
end;
/
spool off
ttitle off
set feedback on

Here is what the output show look like and as you can see it will generate a report as well:

Fri Nov 07 page 1
IOs Per Second

Elapsed Sec : 132,900.00
Fixed IO/SEC : 3.93
Temp IO/SEC : .01
Total IO/SEC : 3.94
Total Used Gig: 1.34
Total IOPS/Gig: 2.94

Well, I will sign off for now, hope to see some of you at the Dallas Bootcamp seminar and more at the Supercomputing conference.

Two Tales

I’ve been on serious Oracle and SSD related topics for a few months now so I thought I would relax for one entry and tell you a tale, well, actually two of them, and allow you to judge which is probably the truth.

Tale 1:

In 1979 or so a man who called himself R.C Christian (not his real name) came to the office of the Pyramid Quarry in Elberton, Georgia and contracted them, as the representative to several private individuals, create a large granite monument (the town of Elberton is the self proclaimed Granite capital of the world.) The design and location of the monument was provides and in March of 1980 it was erected by the Elberton Granite Finishing Company at a cost of approximately $41 million. Known as The Georgia Guidestones it sits outside of Elberton, Georgia on one of the tallest hills in Elbert county Georgia.

The Georgia Guidestones

The monument consists of 4-standing stones with a central Gnome stone and capstone. The faces of the standing stones are all inscribed with the same set of guidelines, each in a different “modern” language:

Maintain humanity under 500,000,000 in perpetual balance with nature

Guide reproduction wisely — improving fitness and diversity

Unite humanity with a living new language

Rule passion — faith — tradition and all things with tempered reason

Protect people and nations with fair laws and just courts

Let all nations rule internally resolving external disputes in a world court

Avoid petty laws and useless officials Balance personal rights with social duties.

Prize truth — beauty — love —seeking harmony with the infinite

Be not a cancer on the earth — leave room for nature — leave room for nature

Note that the last guideline has “leave room for nature” repeated twice on each of the 4 monoliths eight total sides with the exception of the one in Russian, where it looks like they ran out of space. The monoliths have the message in the following languages, moving clockwise around the structure from the north: English, Spanish, Swahili, Hindi, Hebrew, Arabic, ancient Chinese, and Russian.

On the capstone is the message, in Babylonian Cuneiform (north), Classical Greek (east), Sanskrit (south), and Egyptian Hieroglyphs (west), and the stone embedded in the Earth on the wesern exposure provides what is supposed to be an English translation: "Let these be guidestones to an age of reason." It is rumored to be engraved on the top of the capstone but as I am not 20 years old anymore I didn't try to get up there and see!

The guidestones messages and capstone message give a tip of the hat to Thomas Paine and other “radical” authors who espoused these types of rules for life in ages past.

The monument itself is aligned on several astronomical axis, it has a slit for viewing the sunset, a hole to look at the North Star (if you move to the left and squint as the monument is supposedly two degrees off proper placement) and a hole in the capstone that marks noon throughout the year. The width of the monument supposedly aligns with the migration of the moon throughout the year.

The setting sun through the slit

The sight of the setting sun through the aperture provided is quite fetching. Like I said, the North Star (“Celestial North”) is also visible through a hole in the center slab, if you move the left and look carefully through the hole due to a misalignment of the monument. I wasn’t there for noon so I couldn’t verify the noon time affect but I am assured it works well.

Now, tale 2.

As I was wandering around the site video taping and taking pictures an older gentleman and his wife drove up, he got out and his wife waited in the truck. Tall (about 6 foot) and a little heavy although not overly so, he reminded me of several rural Georgia farmers I have met in my wanderings. He told me that he had heard someone had vandalized the stones and wanted to see how badly (you can see what appears, upon close inspection, to be some type of clear polymer resin splashed on two of the stones.) We struck up a conversation and I asked him about the mystery surrounding who had them erected. He asked if I had heard the story and I said I read it on the web. He then stated:

“All of that is bullshit.” And smiled at me. “I worked at the quarry when this was built, Mr. Fendley himself had it built and made up that story” Mr. Fendley is/was the owner of the Pyramid Quarry. “He liked publicity and Lord knows this gave it to him.”

He also said he confronted Mr. Fendley about the monument and “He didn’t deny it, he looked mad, but kind of half smiled at me.”

Seems Joe F. Fenley Sr., Wayne Mullinex (the man who donated the land) and Wyatt C. Martin, President of the Granite City Bank involved with the financing were all Shriners (and therefore Masons) and agreed to do this together.

Now even if you take out having to pay for the granite itself (119 tones give or take) and the cost of the land, the cost of paying the workers to extract, cut, shape and carve the custom sized stones as well as place them is still rather high just for a publicity stunt, however, I wouldn’t put it past some folks. Also, the alignments and other parts of the monument are intriguing, why go to such effort when just the slabs themselves and their message would have been enough?

To add to the message there is supposedly a time capsule buried (or will be buried) under the slab that is on the west side of the monument. The slab explains the monuments message, who supposedly built it and details of the stones themselves. The “six feet under” makes me wonder if it isn’t someone who will be buried, hence the missing dates.

Inscription about the time capsule, notice missing dates.

All in all the stones looked like they were rather hurriedly inscribed because some of the inscriptions overrun the finished part of the slabs, there are translation errors and character errors on some messages and the lack of the repeat on the Russian stone due to running out of space. On the stone showing noon for several different parts of the year, the final marker overruns the finished part of the stone, was this a mistake, or is that marker more (or less) significant than the others?. Also, the North Star viewing hole appears to have been done as two holes drilled to run into each other, unfortunately they are off access to each other giving the hole a curved appearance and the noon hole looks like it was added as an afterthought.

Some have commented that if you wanted people to see the stones why not place them in a more significant location than a rural Georgia county? Well, for one thing, the sunset view and the North Star views probably would be difficult to guarantee in the heart of Atlanta or in areas where development might throw a skyscraper in the way. Besides if this really is a message for post-Armageddon survivors you don’t want it near any ground –zero targets.

Final “noon” marker overruns finished stone (circled in red)

Now, do the “mistakes” add up to rushed workers or are they deliberate? Are they sending us a message? I’ll leave that to the “DiVinci Code” fans to determine.

So, which tale is the true one? I know I have my opinion although the romantic side of me leans towards the mysterious group of strangers. For those close enough, go see this “America’s Stonehenge” yourself, for those who can’t, look them up on the net. I’ll be posting a bunch of other photos from my visit at my http://www.scubamage.com/ site as soon as I finish processing them, I’ll provide a more substantial link at that time.

Oracle's Data(Warehouse)base Machine

I just got back from OOW08, my wife and I tagged a couple of days in Napa, California onto the conference for our 35st anniversary so I am still getting back into the swing of things.

The biggest news at the conference was Larry Ellison’s announcement of the Exadata storage concept and the Oracle Database Machine both developed jointly with HP. These new storage and database devices offer up to 168 terabytes of raw storage with 368 gigabytes of caching and 64 main CPUs in 8 stacked DL 360 G5 servers and each Exadata unit has a HP Proliant DL 180 G5 with dual quadcore CPUs, 8 gigabytes of memory and 12 SAS 300 GB or SATA 1 terabyte drives. The entire HP Oracle Database Machine contains 14 Exadata blocks and 8 – dual quadcore servers in a full configuration. The Exadata blocks can be purchased separately. There are 4-24 port Infiniband switches provided in the Database Machine. The entire device provides a throughput of 10.5 (SATA) to 14 (SAS) GB/second.

Now, each Exadata block can only provide 1 terabyte if the 300 GB drives are utilized and 3.3 terabytes if the 1 terabyte drives are used unless Oracle compression is also used. This space calculation (from Oracle documentation) is based on mirroring of all the drives and subtracting space for logs, undo and temp space. The usual “your mileage may vary” warning applies to this available space. ASM with what appears to be high redundancy storage is being used to manage the drives. So while raw storage appears to be 3.3 TB to 12 TB the actual space that ends up being usable is only 1/3 of those amounts. Each Exadata has 2 – 20 Gigabit Infiniband interfaces. However, the blocks can only support 1 GB per second of output with the SAS configuration and 750 MB per second in the SATA configuration.

The Oracle Database Machine was actually designed for large data warehouses but Larry assured us we could use it for OLTP applications as well. Performance improvements of 10X to 50X if you move your application to the Database Machine are promised. This dramatic improvement over existing data warehouse systems is provided through placing an Oracle provided parallel processing engine on each Exadata building block so instead of passing data blocks, results are returned. How the latency of the drives is being defeated wasn’t fully explained.

The HP Oracle Database Machine must run Oracle11g, 11.1.0.7, RAC and Linux and each Exadata block must have the new Oracle 11g parallel query engine installed. So in a full configuration you are on the tab for a 64 CPU Oracle and RAC license and 112 Oracle parallel query licenses (assuming it is per CPU, if it is per Exadata block then it will be 14) as well as any Grid control licenses you may need. The base cost of the full Database Machine is around $650K which seems quite a bargain for 14-46 terabytes of usable storage and a 64 processor stack, however, you will also need over a million dollars in licenses even with aggressive reductions from your sales representative.

The HP-Oracle Database Machine only works with Oracle databases (just thought I should throw that in.)

Whew! The HP-Oracle database Machine offers quite an impressive array of facts, figures, promises and price tags. It will be interesting to see how this all sorts out over the coming months. Will there be enough profit in the HP-Oracle Database Machine to keep HP interested? Or is this another network computer? For those too young to remember Larry’s last hardware foray was the Network Computer, a device that would replace all the desktops and centralize application and data storage, it failed. [Note: don’t forget about Pillar Data another Ellision investment that would seem to be hurt by this announcement]. I believe this system is designed to help Oracle protect turf from Netezza and promote growth in the analytics market. Targeting the product to OLTP environments is just sloppy marketing as the system will not offer the latency needed in real OLTP transaction intensive shops. These applications do not need parallel queries, they need low latency database writes and reads.

So for nearly 2 million dollars (licenses plus hardware) you get a dedicated Oracle server in a rack with 64 CPUs of central processing and 46 terabytes of usable storage managed by 112 block resident CPUs and a wee bit less than 224 gigabytes of cache area (168 gigabytes of cache were promised after processing overhead was subtracted). However, you must throw away your existing infrastructure, upgrade to Oracle11g and marry your future to the HP Oracle Database Machine to do so.

What might be an alternative? Well, how about keeping your existing hardware, keep your existing licenses, and just purchase solid state disks to supplement your existing technology stack? For that same amount of money you will shortly be able to get the same usable capacity of Texas Memory Systems RamSan devices. By my estimates that will give you 600,000 IOPS, 9 GB/sec bandwidth (using fibre Fibre Channel , more withor Infiniband), 48 terabytes of non-volatile flash storage[S1] , 384 GB of DDR cache and a speed up of 10-50X depending on the query (based on tests against the TPCH data set using disks and the equivalent Ram-San SSD configuration). More importantly, this performance can be delivered with sub-millisecond response time. At Oracle World I was presenting on the importance of latency to Oracle databases. The Exadata is massive and offers great bandwidth but will have nearly awful disk access tedencies due to the massive and slow disk drives included in the system.

Of course, if you don’t need 48 terabytes you can purchase Ram-San SSD technology from 32 gigabytes up to whatever you actually need. Ram-San SSD technology works with all Oracle versions and requires no special licensing or changes to your system. Ram-San uses fibre channel or Infiniband for connection to your infrastructure and looks identical to a disk drive once configured (it takes about 10 minutes.)

Oh, and the Ram-San doesn’t care if you are on Oracle, SQL Server, MySQL, or cousin Joe’s Ozark Mountain special database.

So let’s recap:

Purchase the complex, tied to Oracle with a golden chain, HP-Oracle Database Machine and end up throwing away your existing technology stack, and spend up to 2 million dollars for the privilege, for a speed up of 10x to 50x on data warehouse type queries.

Or

Purchase just as much Ram-San SSD technology as you need ($43K base price for 32 GB mirrored), keep your existing hardware and license structure (or possibly reduce it) and get a 10x to 50x speed up on data warehouse type queries, with the freedom to change databases as you need to.

Call me simple, but I think I see the proper choice.

[S1]Note that the 8TB systems are projected to have 1.5GB/second of bandwidth sustained reads or writes (to Flash). This can be accomplished with four 4Gbit FC ports or two 4x IB ports per unit.

Is there a DBA in the House?

No, I wasn’t recently at a theater that needed an emergency restart of the database for the show to go on. However I do watch a TV show called House. For those who haven’t seen the program House it is about an acerbic, disabled, pain pill addicted Doctor who just happens to be a genius diagnostician. Think of an anti-Marcus Welby (for those who remember the show Marcus Welby M.D.) Anyway, in each episode Doctor House and his team solve a medical problem by looking at the facts and applying logic, common sense and luck. Doctor House also has one rule, “Everyone Lies” and of course in each episode the pivotal point occurs when they find the lie that is obscuring the true source of the problem.

All of the problem solving that occurs on House reminds me of trouble shooting in the Oracle world. In many cases there are a number of symptoms with database problems and some of them are contradictory just as with problems in the human health areas. Oh, and everybody lies. “No, there weren’t any changes”, “No, nothing is different between these two test runs”, ”Yes, we used the same data/transactions/parameters”.

In almost every episode of House they go through at least three different “cures” before they find the real problem and solution. Many times in the Oracle universe we apply a fix for a problem, only to find it wasn’t really the issue, or, in fixing it we transfer the problem to another area of the database. Another similarity is that many times House and his team will take a shotgun approach when there isn’t a clear solution, applying two or more “cures” at the same time, much like a DBA will apply multiple fixes in a single pass, thus not really knowing what was fixed but just breathing a sigh of relief when performance improves.

I think the character portrayed as Dr. House would make a great DBA, but somehow I can’t see folks glued to their TV screens hoping that next index will fix the query…

SSD is Green Technology

In a recent research project I compared the energy consumption of SSD based technology to IOPS equivalent disk based fibre attached SAN systems. As you can well imagine the SSD technology was much more space efficient, required less cooling and of course less electricity while providing faster data access.

But just how much can be saved? In comparisons to state of the art disk based systems (sorry, I can’t mention the company we compared to) at 25K IOPS, 50K IOPS and 100K IOPS with redundancy, SSD based technology saved from a low of $27K per year at 6 terabytes of storage and 25K IOPS to a high of $120K per year at 100K IOPS and 2 terabytes of storage using basic electrical and cooling estimation methods. Using methods documented in an APC whitepaper the cost savings varied from $24K/yr to $72K/yr for the same range. The electrical cost utilized was 9.67 cents per kilowatt hour (average commercial rate across the USA for the last 12 months) and cooling costs were calculated at twice the electrical costs based on data from standard HVAC cost sources. It was also assumed that the disks were in their own enclosures separate from the servers while the SSD could be placed into the same racks as the servers. For rack space calculations it was assumed 34U of a 42U rack was available for the SSD and its required support equipment leaving 8U for the servers/blades.

Even figuring in the initial cost difference, the SSD technology paid for itself before the first year was over in all IOPS and terabyte ranges calculated. In fact, based on values utilized at the storage performance council website and the tpc.org website for a typically configure SAN from the manufacturer used in the study, even the cost for the SSD was less for most configurations in the 25K-100K IOPS range.

Obviously, from a green technology standpoint SSD technology (specifically the RamSan 500) provides directly measurable benefits. When the benefits from direct electrical, space and cooling cost savings are combined with the greater performance benefits the decision to purchase SSD technology should be a no brainer.

A Tale of Two Databases

It was the best of times, it was the...oops, wrong story. I’ve been doing a little testing of databases lately (imagine that…) anyway, while I am waiting for my own personal RAMSAN device my employer has set me up with a remote laboratory setup with a RAMSAN 400 - 128 gigabyte SSD connected through a single fibre channel to a 4 CPU server running RedHat 4.0 Linux. The server has 8 gigabytes of memory and the CPUs run at X gigahertz, a most satisfying sandbox.

In my own laboratory (no, I don’t have an Igor, but he would come in handy to help dispose of dead computers now and then) I am running a two-node RAC cluster with each cluster node being a single 3 gigahertz 32 bit CPU with hyperthreading and 3 gigabytes of memory. The cluster interconnect is a single gigE Ethernet. As to storage, I utilize two JBOD arrays, a NexStor 18F and a NexStor 8F each fully loaded with 72 gigabyte 10K Seagate SCSI drive and connected to the servers via a Brocade 2250 with dual QLA2200 1gb HBAs on each server. The arrays themselves are connected through single 1 gb fibre channel connections. Oh, it is also running RedHat 4.0 Linux.

On the system in the remote lab, let’s call it TMSORCL for ease of identification, I have 4-3.6 Ghz CPUS, 4-2 port QLA2642 4 Gb HBAs (I used 1) and 8 gigabytes of memory. I have Oracle10g 10.1.0.2 installed, on the home lab (let’s call it AULTDB) I have Oracle11g, 11.1.0.2 installed.

AULTDB is utilizing ASM on two diskgroups. One diskgroup consists of 16 drives, 8 on the 18F and 8 on the 8F using normal redundancy and failgroups, the other diskgroup uses 8 drives on the 8f and uses external redundancy. I have placed the application data files and index files on the datagroup with the 16 drives and the rest (system, temp, user, undotbs, redo logs) on the 8 disk externally redundant datagroup. Using ORION (Oracle’s disk IO simulator) I was able to achieve nearly 6000 IOPS using 24 drives, so I hope that I can get near that using this configuration.

TMSORCL is only using the RAMSAN 400 through its 4 gb fibre channel connection for all database files. The RAMSAN should be able to produce enough IOPS to provide at least 100,000 through the single HBA (if it doesn’t saturate that is.)

Using Benchmark Factory from Quest Software I created two identical databases (you guessed it, TMSORCL and AULTDB.) These are TPCH type environments with a scale factor of 18, the largest I could support and still have enough temporary space to build indexes and run queries.

Now, some may be saying that I have set up an apples to oranges environment for comparison, and they would be correct, however, many folks will be facing just such a choice very soon, that is, to stick with an Oracle10g environment or upgrade to an 11g environment. Another question that many folks have, should I go to RAC? So this test is not as useless as you may have first thought.

I set up both TMSORCL and AULTDB to be as near in effective size (memory settings wise) allowing that AULTDB was spread across two 3 gigabyte memory areas allowing for a total memory footprint of about 3-4 GB, I set up the TMSORCL environment to have a maximum size of 4 GB with a target of 3 GB for memory.

I had some interesting problems with the number 5 query in the TPCH query set until I added a few additional indexes, it kept blowing out the temporary tablespace even though I had it as large as 50 gigabytes. If you want the script for the additional indexes, email me. I also had issues with parallel query slaves not releasing their temporary areas. Anyway, after adding the indexes and incorporating periodic database restarts into the test régime I was able to complete multiple full TPCH power runs on each machine (just the single user stream 0 power run for now).

So, before we get to the results, let’s recap:

AULTDB – 32 bit RedHat 4 Linux RAC environment with two single 3 Ghtz CPUS running hyperthreaded to simulate 4 CPU total in 6 gigabytes of memory utilizing 4-1gb QLA2200 HBAs to access 24-10K 72 gigabyte drives using ASM. Effective SGA 4 gigabytes.

TMSORCL – 64 bit RedHat 4.0 Linux single server with 4 3.6 Ghtz CPUS with 8 gigabytes of memory utilizing 1-4 gb HBA port to access a single 128 GB RAMSAN 400. Effective SGA constrained to 4 gigabytes.

I ran the TPCH on each database (after getting stable runs) a total of 4 times. I will use the best run, as measured by total time to complete the 22 queries, for each database for the comparison runs. For AULTDB the best time to complete the run was 1:37:55 (One hour, thirty seven minutes and fifty-five seconds.) For TMSORCL the best time was 0:15:15 (zero hours, fifteen minutes and 15 seconds.) So on just raw, total time elapsed for identical data volumes, identical data contents and identical queries the TMSORCL database completed the runs 6.42 times faster (642%). The actual query timings in seconds are shown in the following chart. Based on summing the given query times the performance improvement factor from AULTDB to TMSORCL is 6.68 or 668% faster.



As you can see, TMSORCL beat out AULTDB on all queries with a range of 79% up to a whopping 4,474% improvement being shown based on the individual query times in seconds.

During the tests I monitored vmstat output at 5 second intervals, at no time did run queue length get over 3 and IO wait was less than 5% on both servers. This indicates that the IO subsystem never became over burdened, which of course was more of a concern with AULTDB rather than TMSORCL.

Now the TPCH benchmark is heavy on IOPS, so we would expect the database using the RAMSAN to perform better, and that is in fact what we are seeing, in spite of only having a single HBA and being on an older, less performing version of Oracle. So what conclusions can we draw from this test? Well, there are several:

For IOP heavy environments RAMSAN technology can improve performance by up to 700% against a JBOD array properly sized for the same application, depending on number and type of queries.

Use of a RAMSAN can delay moving to larger, more expensive sets of hardware or software if the concern is IO performance related.

Now there are several technologies out there that offer query acceleration, most of them place a large data cache in front of the disks to “virtualize” the data into what are essentially memory disks. The problem with these various technologies (including TimesTen from Oracle) is that there are coding issues, placement issues (what gets cached and what is left out?) and management issues, for example, with TimesTen there are logging and backup issues to contend with. In addition, utilities that use the hosts memory such as TimesTen add CPU as well as memory burden to what is probably an overloaded system.

What issues did I deal with using RAMSAN? Well, using the provided management interface GUI (via a web browser) I configured two logical units (LUNS), assigned them to the HBA talking to my Linux host and then refreshed the SCSI interface to see the LUNS. I then created a single EXT3 partition on each LUN and pointed the database creation with DBCA at those LUNs. Essentially the same exact things you would do with a disk you had just assigned to the system. The RAMSAN LUNs are treated exactly as you would a normal disk LUN (well, you have to grant execute permission to the owner, but other than that…) Now, if you don’t place the entire database on the RAMSAN then you have to make the choice of what files to place there, usually a look at a Statspack or AWR report will head you in the correct direction.

An interesting artifact from the test occurred on both systems, after a couple of repetitive runs the times would degrade on several queries, if I restarted the database, the times would return to near the previous good values. This artifact probably points to temporary space or undo tablespace cleanup and management issues.

I next intend to run some TPCC and maybe if I can get the needed infrastructure in place, a TPCE on each system, watch here for the results.