Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Wednesday, April 13, 2005

Going Solid

(Note: This is a blog on a specific technical topic, solid state disk drives, any responses not germane to this topic will be deleted.)

Back in my days as a Nuclear Machinest Mate/ELT on board submarines in the USN, there was a condition known as "going solid" which referred to a controlled process where by the bubble of steam in the reactor coolant loop pressurizer was slowly compressed to force it back into water thus making the entire reactor loop solid with water. Under controlled conditions this was done for various reasons, if it happened by accident it was not good as you would have lost pressure control for the reactor cooling loop. So going solid in those days was both good (if done as a controlled exercise) and possibly very bad (if allowed to happen accidentally.) But that is not what I am referring to in this blog as going solid.

What I am referring to is the new trend (well, actually it has been around since the 90's) to move databases to solid state drives. In almost all cases this form of going solid leads to measurable performance improvements by elimination of the various latencies involved with the standard computer disk drive assemblies. Of course the removal of disk rotational and arm latencies is just one of the things that solid state drives bring to the table, the other is access to your data at near-memory speeds.

What solid state drives mean to an application is that if your application is currently suffering from IO problems some if not all of them are relieved. However, the benefits are a bit lop sided depending on the predominant type of IO wait your system is seeing. If your IO waits are due to contention during the retrieval of information (SELECT statements) then you can expect a dramatic increase in performance. In tests against standard disk arrays using the TPC-H benchmark I was able to achieve a 176 times performance increase for primarily retrieval based transactions. For what I refer to as IUD transactions (INSERT, UPDATE and DELETE) only a 30-60% performance improvement was shown.

Remember that retrieval requires no additional processing once the data location is returned from the search process, while IUD involves data verification via indexes, constraints and other internal processes that tend to slow down the process thus reducing the performance gains. Does solid state disk (SSD) technology eliminate waits? Of course not, what it does is reduce their duration. For example, looking at load times:

Load Graph Using SSD Posted by Hello

You can from the graph that load times for standard disks was on the average about 30% slower than the load times for an equivalent SSD drive array.

However looking at query response time, we see an entirely different picture:

SSD Queries Posted by Hello

When we compare the SSD times with the same queries, same OS settings and same layout on standard drives, we see a dramatic difference (notice the scale on this next graph is logarithmic, not linear):

ATA Queries Posted by Hello

The second graph really drives home the point, I had to make it logarithmic because of the great differences in time duration for the various queries when going to disks. What required a couple of hours (running the 20+ base queries) on SSD required 3 days on the average for the ATA drives.

Of course we don't remove the IO waits by using SSD, their duration is simply reduced to the point they are not a big factor anymore. Of course, for scalability and such you still will need to tune SQL.

Some how, I see going solid in our future.

For a complete copy of the testing look at:


For the latest in Solid State Drives look at:



David Aldridge said...


Impressive performance -- how does the price/performance look at the moment in comparison to other TPC-H results?

Mike said...

If you use price per transaction it beats most other systems. This was a limited TPC-H (as documented in te report and book) just looking at the IUD and Select performance not doing the full benchmark. However, other teams have verified this and done the checks, the site link has the other reports.

Niall said...


did you look at just moving logs (and maybe arch logs) to ssd. I'd expect to see a really big bang for the buck for just moving that io subsystem to, essentially, ram, but haven't seen any figures.


Mike said...

Yes, we thought of that but for the primarily select based TPC-H tests we did we couldn't test it properly. However, you can only get back what you are currently losing to IO. If the log waits are your predominate wait, then yes, it makes sense to move them.

David Aldridge said...

This is a little speculative, but if you had a log of the total number and duration of physical reads and writes per file (direct and other) then that alone ought to give a pretty good idea of the potential performance improvement possible for an arbitrary application and workload, I'd think.

So you could say ... "move files x, y and z to SSD and we'd expect to see the following total wait reduction, therefore ..."?

I'm still thinking about the "therefore ..." bit.