Oakies Blog Aggregator

IBMダイレクト価格　556,500円。。。エントリレベルの価格ではない。サーバレベルの価格です。
インテル Xeonプロセッサー5600番台搭載。最大8TBのHDD、大容量192GBメモリー搭載可能。

初期メモリは2GBx3で6GBしかないようだが、結構安い。
企業で使うサーバであれば、自作というわけにはいかないだろうから、安い。

でも16GBのメモリで、SSD6本のRAID0+HDD2本と10万円を切ったXeon X5650で合計35万円で自作は出来る。

この動画は日本向けだけなのだろうか？
さすが日本IBMです。

September 6, 2010 (Back to the Previous Post in the Series) In previous blog articles in this series we looked at various methods for starting a 10046 trace file and also the basics of walking through a trace file.  Today’s blog article continues the investigation of 10046 trace files.  To begin, we need a test [...]

OK, this one might be contentious, but what the heck – somebody has to say it. Let’s start with a question:

Raise your hand if you have a feeling, even a slight one, that storage arrays suck?

Most DBAs and sysadmins that I speak to certainly have this feeling. They cannot understand why the performance of this very large and expensive array is nearly always lower than they achieve from the hard drive in their desktop computer. OK, so the array can do more aggregate IOPs, but why is it that 13ms, for example, is considered a reasonable average response time? Or worse, why is that some of my I/Os take several hundred milliseconds? And how is it possible that my database is reporting 500ms I/Os and the array is reporting that they are all less than 10ms? These are the questions that are lodged in the minds of my customers.

Storage Arrays do some things remarkably well. Availability, for example, is something that is pretty much nailed in Storage Array Land, both at the fabric layer and the actual array itself. There are exceptions: I think that large Fibre Channel fabrics are a High Availability disaster, and the cost of entry with director-class switches makes no sense when small fabrics can be built using commodity hardware. I have a more general opinion on Fibre Channel actually – it is an ex-format, it is pushing up the lillies. More on that in another blog post, though, I’m not done with the array yet!

The Storage Array became a real success when it became possible to access storage through Fibre Channel. Until then, the storage array was a niche product, except in the mainframe world where Escon was available. Fibre Channel, and subsequently Ethernet and Infiniband, enabled the array to be a big shared storage resource. For clustered database systems this was fantastic – an end to the pure hell of multi-initiator parallel SCSI. But then things started getting a little strange. EMC, for example, started advertising during Saturday morning children’s television about how their storage arrays allowed the direct sharing of information between applications. Well, even an eight year old knows that you can’t share raw data that way, it has to go via a computer to become anything meaningful. But this became the big selling point: all your data in one place. That also has the implication that all the types of data are the same – database files, VMware machines, backups, file shares. They are not, from an access pattern, criticality or business value standpoint, and so this model does not work. Some back-pedalling has occurred since then, notably in the form of formal tiered storage, but this is  still offered under guise of having all the data in one place – just on cheap or expensive drives.

So now we have this big, all-eggs-in-one-basket, expensive array. What have we achieved by this: everything is as slow as everything else. I visited a customer last week with just such an array, and this was the straw that broke the camel’s back (it’s been a long time coming). They have a heavily optimised database system that ultimately means the following speeds and feeds are demanded from the array:

• 10 megabytes per second write, split into around 1300 IOPs
• 300 reads per second

If you don’t have a good feel for I/O rates let me tell you: That is a very moderate amount of I/O. And yet the array is consistently returning I/Os that are in several hundred milliseconds, both reads and writes. Quite rightly, the customer thinks this is not very acceptable. Let’s have a little analysis of those numbers.

First the writes.: Half of those writes are sequential to the Oracle redo log and could easily be serviced by one physical drive (one 15k drive can sustain at least 100MB/s of sequential I/O). The rest of them are largely random (let’s assume 100% random), as they are dirty datafile blocks being written by the database writer. Again, a single drive could support 200 random writes per second, but let’s conservatively go for 100 – that means we need six or seven drives to support the physical write requirements of the database, plus one for the redo log. Then we need to add another three drives for the reads. That makes a very conservative total of eleven drives to keep up with the sustained workload for this customer, going straight to disk without any intermediate magic. This array also has quite a chunk of write-back cache, which means that  writes don’t actually even make it to disk before they can be acknowledged to the host/database. Why then, is this array struggling to delivery low latency I/O when it has sixty four drives inside it?

The answer is that a Storage Array is just a big computer itself. Instead of taking HTTP requests or keyboard input, it takes SCSI commands over Fibre Channel. Instead of returning a web page, it returns blocks of data. And like all complex computer systems, the array is subject to performance problems within itself. And the more complex the system, the more likely it is that performance problems will arise. To make things worse, the storage arrays have increasingly encouraged the admin to turn on more and more magic in the software to the point where it is now frequently an impossibility for the storage admin to determine how well a given storage allocation might perform. Modern storage administration has more to do with accountancy than it does performance and technology. Consider this equation:

(number of features) x (complexity of feature) = (total complexity)

Complexity breeds both performance problems and availability problems. This particular customer asked me if there was a way to guarantee that, when they replace this array, the new one will not have these problems. The answer is simple: ‘no’.

Yes, we can go right through the I/O stack, including all the components and software features of the array and fix them up. We can make sure that Fibre Channel ports are private to the host, remove all other workloads from the array so that there is no scheduling or capacity problems there. We can turn off all dynamic optimisations in the array software and we can layout the storage across known physical drives. Then, and only then, might there be a slim chance of a reduced number of high latency I/Os. I have a name for this way of operating a storage array. It’s call Direct Attached Storage (DAS): Welcome to the 1990s.

Now let me combine this reality with the other important aspect: semiconductor-based  storage. What happens when the pent up frustrations of the thousands of storage array owners meets the burgeoning reality of a new and faster storage that is now governed by some kind of accelerated form of Moore’s Law? As my business partner Jeff describes it: It’s gonna be a bloodbath.

I think that we will now see a sea change in the way we connect and use storage. It’s already started with products such as Oracle’s Exadata. I’m not saying that because I am an Oracle bigot (I won’t deny that), but because it is the right thing to do – it’s focused on doing one thing well and it uses emerging technology properly, rather than pretending nothing has changed. I don’t think it’s plug and play for many transactional customers (because of the RAC implication), but the storage component is on the money. Oh, and it is effectively DAS – a virtual machine of Windows running a virus scan won’t slow down your critical invoicing run.

I think that the way we use the storage will have to change too – storage just took a leap up the memory hierarchy. Low latency connectivity such as Infiniband will become more important, as will low latency request APIs, such as SRP. We simply cannot afford to waste time making the request when the response is no longer the major time component.

With all this change, is it now acceptable to have the vast majority of I/O latency accounted for in the complex software and hardware layers of a storage array? I don’t think so.

Here’s a little detail that I’ve known for years – but keep forgetting until I embarrass myself by rediscovering it (usually in front of a client). I’ll start with a demonstration of a useful little feature of mandatory columns:

drop table t1 purge;

create table t1
as
select
	*
from
	all_objects
where
	rownum <= 10000
;

execute dbms_stats.gather_table_stats(user,'t1')

create index t1_i1 on t1(object_name);

set autotrace traceonly explain

select count(*) from t1;

/*

--------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time
--------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    13   (0)| 00:00:0
|   1 |  SORT AGGREGATE       |       |     1 |            |
|   2 |   INDEX FAST FULL SCAN| T1_I1 | 10000 |    13   (0)| 00:00:0
--------------------------------------------------------------------

*/

Oracle can use the index on column object_name to count the number of rows in the table because the column has been declared NOT NULL, so every row in the table also has to appear in the index. Let’s just demonstrate that by changing the column definition:

alter table t1 modify object_name null;
select count(*) from t1;

/*

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    40   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |    40   (0)| 00:00:01 |
-------------------------------------------------------------------

*/

Now let’s make the column mandatory again – by adding a constraint:

alter table t1 add constraint t1_ck_on_nn check(object_name is not null);
select count(*) from t1;

What do you think the execution path is going to be ?

Oracle uses a full table scan – even if you supply the optimizer with an index hint.

The special NOT NULL optimisation only applies if the column is declared not null – an ordinary constraint isn’t just good enough.

You may remember a short note I wrote a few days ago about adding a not null constraint to a table with 200 million rows without causing the system to stop for a few minutes. I did it with the intent of giving the optimizer a few extra options for exection plans – but now I need to go back and see whether or not the constraint has any effect at all on any of the plans we generate.

Update 6th Sept 2010

Some of the early comments have highlighted the importance of being very careful with lnaguage when describing a problem. In this case I haven’t made the distinction between an “ordinary constraint” and a “not null declaration” clear enough.  To ensure that a column does not allow nulls you can add a check constraint, or you can declare it “not null” – which, in effect, is a special type of constraint. When you declare a column to be not null Oracle will create a check constraint for that column, but also set the null$ column in sys.col$ to be non-zero.

A quick cut and paste job to show the differences (in 11.1)

SQL> alter table t1 modify n1 not null;

Table altered.

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 N1                      NOT NULL NUMBER
 N2                               NUMBER
 V1                               VARCHAR2(180)

SQL> alter table t1 modify n1 null;

Table altered.

SQL> alter table t1 add constraint t1_ck_n1_nn check(n1 is not null);

Table altered.

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 N1                               NUMBER
 N2                               NUMBER
 V1                               VARCHAR2(180)

SQL> alter table t1 drop constraint t1_ck_n1_nn;

Table altered.

SQL> alter table t1 modify n1 not null enable novalidate;

Table altered.

SQL> desc t1
 Name                    Null?    Type
 ----------------------- -------- ----------------
 N1                               NUMBER
 N2                               NUMBER
 V1                               VARCHAR2(180)

SQL

I haven’t queried user_constraints to show it but in all these cases there will be a check constraint with the search_condition: “N1″ IS NOT NULL. As you can see, though, the only time that the describe command shows the column as NOT NULL (and col$.null$ becomes non-zero) is when you use the not null declaration with the implicit attributes of enable, validate.

It’s only this declaration/setting of null$ that allows the optimizer to do its special optimisation. You might have thought that the optimizer could be programmed to read the constraint information to look for a “check column is not null” constraint to do the same trick but (at present) it simply doesn’t work that way.