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]

dotyet wrote:

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.

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]

Interesting...This 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:

dotyet wrote:

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.

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

[dotyet]

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]

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:

Interesting...This 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:

dotyet wrote:

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.

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]

dotyet wrote:

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. 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?
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.

Sorry, I can't help there...
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Serge Rielau]

aj wrote:

Interesting...This was the subject of a conversation here awhile back.
How much would DB2 LUW gain by going multithreaded ala IDS rather
than multiprocess? Good question. Note that DB2 runs threaded on Windows.
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!) 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.
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?

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]

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]

On Tue, 24 Jan 2006 15:13:02 UTC Serge Rielau <sr*****@ca.ibm.com>
wrote:

dotyet wrote:

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.

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.

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]

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?"

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

[Serge Rielau]

28******@gmail.com wrote:

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?"

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]

dotyet wrote:

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.

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]

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]

dotyet wrote:

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.

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]

28******@gmail.com wrote:

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?"

See this Developerworks article:

http://www-128.ibm.com/developerwork...505zikopoulos/

[dotyet]

Sun say so.... where? any link which will give me some more insight. So
far, I have read about positive support/performance for Databases as
well. Look at "Key Applications" section
http://www.sun.com/servers/coolthreads/t2000/.

And i guess it is irrespective of whether you are running OLTP or DSS.

Regards,
dotyet

[Mark Townsend]

Ian wrote:

dotyet wrote:

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.

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?

Hmm - I'm pretty sure (so far) that the Niagra kit will kick butt under
a DB load. I guess we will have to wait for the first TPCs to come out
before we are sure however.

[Ian]

dotyet wrote:

Sun say so.... where? any link which will give me some more insight. So
far, I have read about positive support/performance for Databases as
well. Look at "Key Applications" section
http://www.sun.com/servers/coolthreads/t2000/.

And i guess it is irrespective of whether you are running OLTP or DSS.

I found the quote I was looking for from The Inquirer (the article is
pretty old, Nov-2004):

"So, with a single CPU in a box that allows for massive numbers
of concurrent threads, and large memory capacities, what markets
is Niagara aimed at? Marc Tremblay repeatedly mentioned that
Niagara was 'network facing' not 'data facing', which will be
the domain of Rock. This means things that you can hit directly
with a web browser which individually do not require huge number
crunching ability, but are present in great quantity. Searches,
web page serving and streaming media were all mentioned as good
candidates. If you need to service thousands of the same task a
second, Niagara should shine. If you want to crunch huge
databases, wait for Rock."

http://www.theinquirer.net/?article=19423
Again, I don't have any experience with these machines, but I remembered
reading this article and thinking, "OK, wait for next gen chip from
Sun."

Of course, it's interesting that I found other statements from Marc
Tremblay indicating that TPC-C performance should be great.

[dotyet]

OK, here are my findings:

I setup a sample database to check how the virtual CPUs are utlized by
DB2

16 tables like below:

CREATE TABLE TEST1
(
KEYNUM INT,
KEYTIME TIMESTAMP
);

200000 rows in each table, having 200000 different KEYNUM values,
200000 different timestamps.

The keynum data is same in each table, but the timestamp data is
different in each table

No indexes.

SQL Statement executed in a loop in a shell script:

#!/usr/bin/ksh
db2 "connect to test"
db2 "values current timestamp"
integer i=0
while ((i <= 10000));
do
echo $i;
db2 "select avg(keytime - current timestamp) from test1"
(( i = i + 1));
done
db2 "values current timestamp"
I ran 16 scripts for 16 tables, simultaneously.

The number of CPUs being actively used by DB2, rose from 1 to 18 in a
matter of seconds. The system load increased from 0% to 50%. The
bufferpool was about 7 GB, and their was no IO happening as per iostats
command.

So, what did we learn here?

Regards,
dotyet

Ian wrote:

dotyet wrote:

Sun say so.... where? any link which will give me some more insight. So
far, I have read about positive support/performance for Databases as
well. Look at "Key Applications" section
http://www.sun.com/servers/coolthreads/t2000/.

And i guess it is irrespective of whether you are running OLTP or DSS.

I found the quote I was looking for from The Inquirer (the article is
pretty old, Nov-2004):

"So, with a single CPU in a box that allows for massive numbers
of concurrent threads, and large memory capacities, what markets
is Niagara aimed at? Marc Tremblay repeatedly mentioned that
Niagara was 'network facing' not 'data facing', which will be
the domain of Rock. This means things that you can hit directly
with a web browser which individually do not require huge number
crunching ability, but are present in great quantity. Searches,
web page serving and streaming media were all mentioned as goodOK
candidates. If you need to service thousands of the same task a
second, Niagara should shine. If you want to crunch huge
databases, wait for Rock."

http://www.theinquirer.net/?article=19423
Again, I don't have any experience with these machines, but I remembered
reading this article and thinking, "OK, wait for next gen chip from
Sun."

Of course, it's interesting that I found other statements from Marc
Tremblay indicating that TPC-C performance should be great.

[Ian]

dotyet wrote:

OK, here are my findings:

I setup a sample database to check how the virtual CPUs are utlized by
DB2

[...]

I ran 16 scripts for 16 tables, simultaneously.

The number of CPUs being actively used by DB2, rose from 1 to 18 in a
matter of seconds. The system load increased from 0% to 50%. The
bufferpool was about 7 GB, and their was no IO happening as per iostats
command.

So, what did we learn here?

Nothing (yet). I can run 16 queries simultaneously on a 1-CPU system.
The fact that 18 virtual CPUs show up being as used by DB2 only shows
that the scheduler is distributing work (as it should).

The fact that the system shows 50% utilization doesn't mean much,
either. Total System utilization is basically the average of individual
CPU utilizations; if 14 out of 32 virtual CPUs are idle then ~50%
utilization is expected.

The question is, what happened? I'd be interested to see runtimes
comparisons for 1 query, 4 queries, 8 queries and 16 queries running
simultaneously.

I am not trying to prove that Niagara is no good for databases - as I
said I have no experience with it - I would just like to see more info.
Niagara is a totally new architecture, and with 8 cores sharing a
(relatively) small cache. Here is an interesting blog entry that looks
like it's talking about Oracle performance:

http://blogs.sun.com/roller/page/tra...ng_on_sun_fire

Anyway, since you're lucky enough to have one in hand to play with,
I'll just try to learn from your good fortune. :-)

Thanks,

[dotyet]

OK, maybe this may make some sense.

The timeline for those queries (400 iterations only and not 10000)

Intel Xeon 32-bit 4 CPU with HT:
Start time: 2006-01-30-12.55.25.539001
End time: 2006-01-30-13.07.33.086000

V20z Opteron 64-bit:
Start time: 2006-01-30-12.52.00.234001
End time: 2006-01-30-13.02.51.531000

T2000:
Start time: 2006-01-30-12.44.41.815175
End time: 2006-01-30-13.13.17.191322
So, i am a bit disappointed with the timeline of T2000. Is it the FPU
at fault?

Regards,
dotyet