Is DB2 multi-threaded?

[dotyet]

Hi Everyone,

It would be a real big help if anyone can shed light on whether DB2 UDB
8.2 on Solaris will make optimum use of a Sun T2000 server. The server
has 1 CPU with 8 cores and each core has 4 threads (32 virtual CPUs).
We are exploring this option againts an equivalent performance pSeries
box. But before we delve deeper, we want some basic info on that.

Any suggestions would be helpful.

Regards,
dotyet

[Serge Rielau]

Re: Is DB2 multi-threaded?

dotyet wrote:[color=blue]
> Hi Everyone,
>
> It would be a real big help if anyone can shed light on whether DB2 UDB
> 8.2 on Solaris will make optimum use of a Sun T2000 server. The server
> has 1 CPU with 8 cores and each core has 4 threads (32 virtual CPUs).
> We are exploring this option againts an equivalent performance pSeries
> box. But before we delve deeper, we want some basic info on that.[/color]
I think youneed to differentiate between threading on the OS and virtual
CPU's. I know for fact that DB2 exploits virtual CPU's on pSeries and
I'd be surprised if it couldn't exploit it on Sun.
But DB2 on Unix uses processes and is not using multithreading on the OS
level. IDS by contrast is a multithreaded engine.
Again... this has nothing to do with exploitation of virtual CPU's.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[aj]

Re: Is DB2 multi-threaded?

Interesting...T his was the subject of a conversation here awhile back.
How much would DB2 LUW gain by going multithreaded ala IDS rather
than multiprocess?

Don't get me wrong - I'm a former IDS administrator who has switched
my engines completely to DB2 for primarily business reasons, and I
really like both DB2 and Informix. Both products have their pros and
cons. (Again, *not* looking for a flame war!)

However, I used IDS for a long time (10 years) on both HP-UX and Linux.
Once the engine was "settled in" and configured nicely, it was my
experience that stability was just rock solid.

Would IBM ever consider taking DB2 LUW multithreaded?

cheers
aj


Serge Rielau wrote:[color=blue]
> dotyet wrote:[color=green]
>> Hi Everyone,
>>
>> It would be a real big help if anyone can shed light on whether DB2 UDB
>> 8.2 on Solaris will make optimum use of a Sun T2000 server. The server
>> has 1 CPU with 8 cores and each core has 4 threads (32 virtual CPUs).
>> We are exploring this option againts an equivalent performance pSeries
>> box. But before we delve deeper, we want some basic info on that.[/color]
> I think youneed to differentiate between threading on the OS and virtual
> CPU's. I know for fact that DB2 exploits virtual CPU's on pSeries and
> I'd be surprised if it couldn't exploit it on Sun.
> But DB2 on Unix uses processes and is not using multithreading on the OS
> level. IDS by contrast is a multithreaded engine.
> Again... this has nothing to do with exploitation of virtual CPU's.
>
> Cheers
> Serge[/color]

[dotyet]

Re: Is DB2 multi-threaded?

fair enough, so can you suggest some method, scripts etc to find out if
we would benefit from those 32 virtual CPUs. I am in process of setting
up the test machine, so maybe another day or 2 till we get a functional
DB2 instance on this machine.

there is one more issue which is showing it's face in this testing
phase. I have configured the kernel parameters using the resource
controls. i am allocating about 25 gb of memory for db2 bufferpools, so
my project.max-shm-memory is set to that number. Now, while altering
the size of a large bufferpool in the sample database, the engine says
that suffiicient memory is not available and the bufferpool size
alteration has been deffered till the next restart of instance. many
times it gives this message even when I am increasing the bufferpool by
a few 100 MBs. The pagesize is 32 K. usage of "immediate" keyword does
not make any difference. Once the DB2 instance is restarted, it works
fine, and does allocate the relevant (increased) bufferpool memory.

This machine will host only one user project and, i have the fair share
scheduler and resource cap deamon disabled.

Am i missing something here?

Regards,
dotyet

[dotyet]

Re: Is DB2 multi-threaded?

fair enough, so can you suggest some method, scripts etc to find out if
we would benefit from those 32 virtual CPUs. I am in process of setting
up the test machine, so maybe another day or 2 till we get a functional
DB2 instance on this machine.

there is one more issue which is showing it's face in this testing
phase. I have configured the kernel parameters using the resource
controls. i am allocating about 25 gb of memory for db2 bufferpools, so
my project.max-shm-memory is set to that number. Now, while altering
the size of a large bufferpool in the sample database, the engine says
that suffiicient memory is not available and the bufferpool size
alteration has been deffered till the next restart of instance. many
times it gives this message even when I am increasing the bufferpool by
a few 100 MBs. The pagesize is 32 K. usage of "immediate" keyword does
not make any difference. Once the DB2 instance is restarted, it works
fine, and does allocate the relevant (increased) bufferpool memory.

This machine will host only one user project and, i have the fair share
scheduler and resource cap deamon disabled.

Am i missing something here?

Regards,
dotyet

aj wrote:[color=blue]
> Interesting...T his was the subject of a conversation here awhile back.
> How much would DB2 LUW gain by going multithreaded ala IDS rather
> than multiprocess?
>
> Don't get me wrong - I'm a former IDS administrator who has switched
> my engines completely to DB2 for primarily business reasons, and I
> really like both DB2 and Informix. Both products have their pros and
> cons. (Again, *not* looking for a flame war!)
>
> However, I used IDS for a long time (10 years) on both HP-UX and Linux.
> Once the engine was "settled in" and configured nicely, it was my
> experience that stability was just rock solid.
>
> Would IBM ever consider taking DB2 LUW multithreaded?
>
> cheers
> aj
>
>
> Serge Rielau wrote:[color=green]
> > dotyet wrote:[color=darkred]
> >> Hi Everyone,
> >>
> >> It would be a real big help if anyone can shed light on whether DB2 UDB
> >> 8.2 on Solaris will make optimum use of a Sun T2000 server. The server
> >> has 1 CPU with 8 cores and each core has 4 threads (32 virtual CPUs).
> >> We are exploring this option againts an equivalent performance pSeries
> >> box. But before we delve deeper, we want some basic info on that.[/color]
> > I think youneed to differentiate between threading on the OS and virtual
> > CPU's. I know for fact that DB2 exploits virtual CPU's on pSeries and
> > I'd be surprised if it couldn't exploit it on Sun.
> > But DB2 on Unix uses processes and is not using multithreading on the OS
> > level. IDS by contrast is a multithreaded engine.
> > Again... this has nothing to do with exploitation of virtual CPU's.
> >
> > Cheers
> > Serge[/color][/color]

[Serge Rielau]

Re: Is DB2 multi-threaded?

dotyet wrote:[color=blue]
> fair enough, so can you suggest some method, scripts etc to find out if
> we would benefit from those 32 virtual CPUs. I am in process of setting
> up the test machine, so maybe another day or 2 till we get a functional
> DB2 instance on this machine.[/color]
When I observed this on the p570 witha business partner in SHanghai a
week ago they were running "topas" to show load. topas gives information
on the virtual CPU's and we could see how they all loaded up as we
increased concurrency. On pSeries we found that virtual CPU's were not
used until CPU utiliation for 4 CPU crossed 50% (an AIX decision).
I assume you have some sort of similar tooling to topas for SunOS?
[color=blue]
> there is one more issue which is showing it's face in this testing
> phase. I have configured the kernel parameters using the resource
> controls. i am allocating about 25 gb of memory for db2 bufferpools, so
> my project.max-shm-memory is set to that number. Now, while altering
> the size of a large bufferpool in the sample database, the engine says
> that suffiicient memory is not available and the bufferpool size
> alteration has been deffered till the next restart of instance. many
> times it gives this message even when I am increasing the bufferpool by
> a few 100 MBs. The pagesize is 32 K. usage of "immediate" keyword does
> not make any difference. Once the DB2 instance is restarted, it works
> fine, and does allocate the relevant (increased) bufferpool memory.[/color]
Sorry, I can't help there...


--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Serge Rielau]

Re: Is DB2 multi-threaded?

aj wrote:[color=blue]
> Interesting...T his was the subject of a conversation here awhile back.
> How much would DB2 LUW gain by going multithreaded ala IDS rather
> than multiprocess?[/color]
Good question. Note that DB2 runs threaded on Windows.[color=blue]
>
> Don't get me wrong - I'm a former IDS administrator who has switched
> my engines completely to DB2 for primarily business reasons, and I
> really like both DB2 and Informix. Both products have their pros and
> cons. (Again, *not* looking for a flame war!)[/color]
IDS is doing more than expoiting multi threading. IDS implements it's
own threading routines to begin with because at the time threading was
not available or stable in some OS.
[color=blue]
> However, I used IDS for a long time (10 years) on both HP-UX and Linux.
> Once the engine was "settled in" and configured nicely, it was my
> experience that stability was just rock solid.
>
> Would IBM ever consider taking DB2 LUW multithreaded?[/color]
Why not? All a question of cost vs benefits.
Performance is not everything btw. since multi threading simplifies
(shared) memory management.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[2803stan@gmail.com]

Re: Is DB2 multi-threaded?

Serge,

Almost nothing to do with this thread, but I can't resist because the
topic is very current.

"CPUs" can now be mulit-cored -- ie, one physical CPU unit can have
several CPUs in it. Each one of those will ultimately have what Intel
calls "hyperthreading ." Then there are "virtual CPUs."

IBM has committed to charging licensing fees "by the physical CPU."
Will that still mean that, even if I have the processing power of 16
CPUs in one physical unit, I'll still pay for one "physical unit?"

[Will Honea]

Re: Is DB2 multi-threaded?

On Tue, 24 Jan 2006 15:13:02 UTC Serge Rielau <srielau@ca.ibm .com>
wrote:
[color=blue]
> dotyet wrote:[color=green]
> > Hi Everyone,
> >
> > It would be a real big help if anyone can shed light on whether DB2 UDB
> > 8.2 on Solaris will make optimum use of a Sun T2000 server. The server
> > has 1 CPU with 8 cores and each core has 4 threads (32 virtual CPUs).
> > We are exploring this option againts an equivalent performance pSeries
> > box. But before we delve deeper, we want some basic info on that.[/color]
> I think youneed to differentiate between threading on the OS and virtual
> CPU's. I know for fact that DB2 exploits virtual CPU's on pSeries and
> I'd be surprised if it couldn't exploit it on Sun.
> But DB2 on Unix uses processes and is not using multithreading on the OS
> level. IDS by contrast is a multithreaded engine.
> Again... this has nothing to do with exploitation of virtual CPU's.[/color]

We used DB2 for several years running OS/2 on 4 and 8-way servers.
Since 7.2 was the end of the line for OS/2, I don't know what V8 might
do but we found that the scaling in performance tracked the number of
CPUs pretty close as we would enable CPUs one at a time, especially if
we re-tuned the installation as the number of available CPUs changed.
On the same hardware, we did not see the same scaling with NT4 or W2K
(although W2K was a little better than NT4). What we found was that
it was a matter of how the 2 OSs handled threading. The native
process allocation model for OS/2 was to allocated processor resources
on a per-thread basis while Windows allocated them on a per process
basis. OS/2 could basically evenly employee all the available
processors for a single instance on a single query while the Win model
tended to max out each processor before moving on to the next by
trying to keep each process isolated on a single processor. In short,
how effective multi-threading is depends very heavily on the threading
model used by the underlying OS as well as on the application mix
being run. I have no clue as to how Linux would have worked - these
were production servers so corp. was a tad touchy about playing
benchmark gmaes on them <g>. Our AIX servers showed a similar scaling
response, so I suspect that DB2 for AIX was similarly tuned for that
model.

--
Will Honea

[Mark Townsend]

Re: Is DB2 multi-threaded?

[color=blue]
>
> IBM has committed to charging licensing fees "by the physical CPU."
> Will that still mean that, even if I have the processing power of 16
> CPUs in one physical unit, I'll still pay for one "physical unit?"
>[/color]

Especially relevant question with the new Niagra cores from Sun, which
BTW, rock.

[Serge Rielau]

Re: Is DB2 multi-threaded?

2803stan@gmail. com wrote:[color=blue]
> Serge,
>
> Almost nothing to do with this thread, but I can't resist because the
> topic is very current.
>
> "CPUs" can now be mulit-cored -- ie, one physical CPU unit can have
> several CPUs in it. Each one of those will ultimately have what Intel
> calls "hyperthreading ." Then there are "virtual CPUs."
>
> IBM has committed to charging licensing fees "by the physical CPU."
> Will that still mean that, even if I have the processing power of 16
> CPUs in one physical unit, I'll still pay for one "physical unit?"
>[/color]
I'm a techie, not a sales or marketing person.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Liam Finnie]

Re: Is DB2 multi-threaded?


dotyet wrote:[color=blue]
> there is one more issue which is showing it's face in this testing
> phase. I have configured the kernel parameters using the resource
> controls. i am allocating about 25 gb of memory for db2 bufferpools, so
> my project.max-shm-memory is set to that number. Now, while altering
> the size of a large bufferpool in the sample database, the engine says
> that suffiicient memory is not available and the bufferpool size
> alteration has been deffered till the next restart of instance. many
> times it gives this message even when I am increasing the bufferpool by
> a few 100 MBs. The pagesize is 32 K. usage of "immediate" keyword does
> not make any difference. Once the DB2 instance is restarted, it works
> fine, and does allocate the relevant (increased) bufferpool memory.
>[/color]

The reason for this is that the DB2 bufferpools are allocated out of
the database shared memory set, which is a fixed-size on Solaris. At
database activation/first connect, if the DATABASE_MEMORY db
configuration parameter is set to AUTOMATIC (the default setting), DB2
internally calculates how much memory is required for all bufferpools,
the package cache, the database heap, etc., and allocates that amount
of memory. This means that there is no room for dynamic bufferpool
actions, so those requests are deferred until the next database
activation. If you want to leave room for dynamic bufferpool actions,
you can set the DATABASE_MEMORY configuration parameter to the desired
amount (say, 6553600 * 4K pages for 25GB) before database
activation/first connect, and then you should see the dynamic
bufferpool actions succeed.

On DB2 UDB for Windows and AIX5L 64-bit, we are able to dynamically
grow the database shared memory set, so you should see dynamic
bufferpool actions succeed on these two platforms without having to
play with the DATABASE_MEMORY configuration parameter.

Cheers,
Liam.

[dotyet]

Re: Is DB2 multi-threaded?

OK, that makes some sense now. i was through out confused about why it
was working in Windows and not working in Solaris. i did have a look at
that parameter, but then i though that when the same default is used in
both the platforms, then why are they not behaving in the same manner.
Anyways, I will update the param and see how it works.

Thanks a lot.

Regards,
dotyet

[Ian]

Re: Is DB2 multi-threaded?

dotyet wrote:[color=blue]
> Hi Everyone,
>
> It would be a real big help if anyone can shed light on whether DB2 UDB
> 8.2 on Solaris will make optimum use of a Sun T2000 server. The server
> has 1 CPU with 8 cores and each core has 4 threads (32 virtual CPUs).
> We are exploring this option againts an equivalent performance pSeries
> box. But before we delve deeper, we want some basic info on that.
>[/color]

Not that this has anything to do with your question, but didn't Sun say
that the Niagara processors aren't really designed to handle the kind of
load that an RDBMS puts on a system?

[Ian]

Re: Is DB2 multi-threaded?

2803stan@gmail. com wrote:[color=blue]
> Serge,
>
> Almost nothing to do with this thread, but I can't resist because the
> topic is very current.
>
> "CPUs" can now be mulit-cored -- ie, one physical CPU unit can have
> several CPUs in it. Each one of those will ultimately have what Intel
> calls "hyperthreading ." Then there are "virtual CPUs."
>
> IBM has committed to charging licensing fees "by the physical CPU."
> Will that still mean that, even if I have the processing power of 16
> CPUs in one physical unit, I'll still pay for one "physical unit?"[/color]


See this Developerworks article:

http://www-128.ibm.com/developerworks/db2/library/techarticle/dm-0505zikopoulos/