Does db2 agents only get what it need?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Woody Ling - 30 Mar 2007 16:10 GMT

>From the DB2 UDB memory model document, I found the following info:

==============================
The agent private memory set consist of the following memory pools.
Their sizes are specified in the database configuration parameters
enclosed in parenthesis:

Application Heap (applheapsz)
Sort Heap (sortheap)
Statement Heap (stmtheap)
Statistics Heap (stat_heap_sz)
Query Heap (query_heap_sz)
Java Interpreter Heap (java_heap_sz)
===============================

Since I am now using a 32bits DB2 in AIX and the agent private memory
area is limited to max 256MB, I wonder how my existing setting can be
handled by DB2:

Application Heap (applheapsz) 512 ~= 2MB
Sort Heap (sortheap) 10240 ~= 40MB
Statement Heap (stmtheap) 32767 ~= 134MB
Statistics Heap (stat_heap_sz) 20000 ~= 82MB
Query Heap (query_heap_sz) 1024 ~= 4MB
Java Interpreter Heap (java_heap_sz) 2048 ~= 8MB

Total 270 MB per agent that is larger than the limitation of the agent
private memory segment in AIX.

Actually I can have 7 concurrent applications with intra-parallel ON
that 2-4 agents are assigned to one application. How can these agents
fit into the 256MB agent private memory segment in AIX? Does db2
agents only get what it need? These settings are hard limit only?

Reply to this Message

Liam Finnie - 30 Mar 2007 18:00 GMT

> >From the DB2 UDB memory model document, I found the following info:
>
[quoted text clipped - 29 lines]
> fit into the 256MB agent private memory segment in AIX? Does db2
> agents only get what it need? These settings are hard limit only?

Hi Woody,

These values are only hard limits - we'll only use that much memory
for each heap if and when needed. Also, some of these heaps are
mutually-exclusive - the statistics heap is only used when gathering
table statistics (i.e. the runstats command), and the statement heap
is only used when compiling/optimizing an SQL query before running it
(so, a single agent won't be using the statement heap at the same time
as the sort heap, for example).

Cheers,
Liam.

Reply to this Message

Ian - 31 Mar 2007 04:44 GMT

> Actually I can have 7 concurrent applications with intra-parallel ON
> that 2-4 agents are assigned to one application. How can these agents
> fit into the 256MB agent private memory segment in AIX? Does db2
> agents only get what it need? These settings are hard limit only?

To add to what Liam wrote, each *agent* has its own private memory.

Parallel agents communicate with each other using the Application
Control Heap (which is allocated from the application group shared
memory segment).

Reply to this Message

Woody Ling - 31 Mar 2007 05:55 GMT

> > Actually I can have 7 concurrent applications with intra-parallel ON
> > that 2-4 agents are assigned to one application. How can these agents
[quoted text clipped - 6 lines]
> Control Heap (which is allocated from the application group shared
> memory segment).

Yes, Thank you. :)

Reply to this Message