Prev: When are projections done?
Next: db2lused process
From: Troels Arvin on 3 Mar 2010 04:06
Setup:

- Server with data residing on an XIV storage system. The XIV storage
system spreads data over all available disks with no knobs to control
priorities, cache-pools, etc. (This is by design.)

- DB2 v. 9.7.1 installed; one instance. A strategy of using automatic
storage, in order to cut down on complexity. The instance has been
set up with DFTDBPATH pointing to a mountpoint on the XIV; let's
call this /db2/data

When I created a new database, I didn't specify any paths.
For this database, I'm seeing great performance for some database
operations, but others are somewhat slow for no obvious reason.

I talked to two different DB2 consultants, and they independently and
spontaneously suggested that we create databases specifying a handful of
paths, even paths which hit the exact same storage device. The reasoning
seems to be that we need to trick DB2 to be more I/O aggressive this way.
Some initial (and rather superficial) experimentation seems to confirm
the need to use such an absurd practice.

Can this really be true? Can the same effect not be obtained using a less
illogical approach?

--
Troels
From: Mark A on 3 Mar 2010 06:03
"Troels Arvin" <troels(a)arvin.dk> wrote in message
news:hml8r6$o05$1(a)news.net.uni-c.dk...
> Setup:
>
> - Server with data residing on an XIV storage system. The XIV storage
> system spreads data over all available disks with no knobs to control
> priorities, cache-pools, etc. (This is by design.)
>
> - DB2 v. 9.7.1 installed; one instance. A strategy of using automatic
> storage, in order to cut down on complexity. The instance has been
> set up with DFTDBPATH pointing to a mountpoint on the XIV; let's
> call this /db2/data
>
> When I created a new database, I didn't specify any paths.
> For this database, I'm seeing great performance for some database
> operations, but others are somewhat slow for no obvious reason.
>
> I talked to two different DB2 consultants, and they independently and
> spontaneously suggested that we create databases specifying a handful of
> paths, even paths which hit the exact same storage device. The reasoning
> seems to be that we need to trick DB2 to be more I/O aggressive this way.
> Some initial (and rather superficial) experimentation seems to confirm
> the need to use such an absurd practice.
>
> Can this really be true? Can the same effect not be obtained using a less
> illogical approach?
>
> --
> Troels

Yes it is true if you are using an sophisticated SAN or NAS solution with
fiber channel adapter cards that can handle multiple accesses at one time
without maxing out the I/O bandwidth. You are not tricking DB2, you are
trying to get parallel I/O operations with multiple paths and you are
telling DB2 exactly how many parallel paths to use since your control the
number of mount points and number of containers. But you should also set the
following variable:
db2set DB2_PARALLEL_IO=*

I don't recall if this has to be set before the tablespace is created or can
be set afterward. The asterisk means it applies to all tablespace ids, or
you can specify those where it does apply if not using RAID on some mount
points.


From: Troels Arvin on 3 Mar 2010 07:41
Mark A wrote:
> But you should also set the following variable:
> db2set DB2_PARALLEL_IO=*

Yes, that was one of my first post-installation steps, even before
creating a database. And NUM_IOCLEANERS was raised. But it seems that
there was still a gain by adding a number of "cheating"-containers (as I
see them).

--
Troels
From: Ian on 5 Mar 2010 15:45
On 3/3/10 5:41 AM, Troels Arvin wrote:
> Mark A wrote:
>> But you should also set the following variable:
>> db2set DB2_PARALLEL_IO=*
>
> Yes, that was one of my first post-installation steps, even before
> creating a database. And NUM_IOCLEANERS was raised. But it seems that
> there was still a gain by adding a number of "cheating"-containers (as I
> see them).
>

What is your EXTENTSIZE set to on the tablespaces, and how does this
compare to how XIV stripes data across its storage?


 | 
Pages: 1
Prev: When are projections done?
Next: db2lused process