4 replies Latest Post - ‏2012-11-21T12:31:54Z by SystemAdmin
49 Posts

Pinned topic Memory Configuration with 4gb Express-C 10.1 on Windows 7 Pro SP1 64-bit

‏2012-05-17T08:26:42Z |
Hi folks, since updating to 10.1 and being able to take advantage of the new memory restriction of 4gb rather than 2, I have been having a difficult time finding the right setup without experiencing errors in the stmm.log regarding unable to find donors.

Firstly I am running a personal database of some 36gb in size, with 4 main tablespaces being results, indexes, rawdata and static data. These all share a single bufferpool called 'DATAPOOL'.

My database is NOT OLTP, it is used for datamining, storing and processing association rules, sequential pattern rules etc. Rules are received with bodies of elements and a rule head. The bodies are stored then broken into individual elements and stored again. This way I am able to use relational division queries to the rawdata table and get the dates on which the rules occur. The datamining engine only returns counts ie: confidence support etc, no dates. So it is up to me to find the actual dates the each rule occurs on in the past, then be able to check when active and store future dates as they occur.

Now in order to get dates etc, the relational division query must gather a significant number of dates that each of the individual elements has occurred on, then using 'having count(*) = the number of elements in the rule body' it is able to find the specific dates they all happened together.

My problem is finding the right balance of bufferpool versus database (sort etc) memory. I have thought about using stmm but it reacts too slowly to the processing as the database is only active for processing at the end of the day, with no activity at other times. Leaving the database active, stmm reduces all the appropriate values over time so come processing there is only a minimal setup and performance is terrible.

Given 4gb of available memory, I started very basically going for something like 2gb for bufferpools and 2gb for the rest, but no matter how I try to set it up, it always gives me these stmm.log donor errors.

System has two (2) physical discs that I split the 4 tablespace containers over, logs and results on 1, indexes, rawdata and static on the other. System has 8gb of real memory, running 64-bit win 7 pro sp1 and db2 express-c luw 10.1 64-bit. Processor is an i5 2400 quad core.

I use oorexx 4.1 32-bit to run the whole thing executing sql stored procedures etc. As I am the only one connecting to the database, everything usually happens sequentially in that my rexx programs run one at a time with a single connection only. There are only very rare occasions where there may be more than 1 connection active at a time and that would be for minimal queries.

Anyone that could help with some suggestions as to where best to start at least would be most welcome. I realize specifics are not practical but some suggestions and how tos would be gratefully appreciated.

Relevant config params as follows:

Datapool bufferpool = 375000 4kb pages, with 5000 block pages 24 extents.
All others are set to automatic sizing by stmm.

Database Manager Configuration

CPU speed (millisec/instruction) (CPUSPEED) = 1.180861e-007
Diagnostic error capture level (DIAGLEVEL) = 2
Notify Level (NOTIFYLEVEL) = 2
Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(66)
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC(1048576)
Instance memory for restart light (%) (RSTRT_LIGHT_MEM) = 10
Agent stack size (AGENT_STACK_SZ) = 16
Sort heap threshold (4KB) (SHEAPTHRES) = 0
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10
Priority of agents (AGENTPRI) = SYSTEM
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)

Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = YES

No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(1024)
No. of int. communication channels (FCM_NUM_CHANNELS) = AUTOMATIC(512)
db2start/db2stop timeout (min) (START_STOP_TIME) = 10

C:\SQ4BLANK>db2 get db cfg for dbname

Database Configuration for Database dbname
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = ANY
Number of frequent values retained (NUM_FREQVALUES) = 50
Number of quantiles retained (NUM_QUANTILES) = 50

Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(904928)
Database memory threshold (DB_MEM_THRESH) = 15
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(12320)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(4550)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 200000
Sort list heap (4KB) (SORTHEAP) = 20000

Database heap (4KB) (DBHEAP) = AUTOMATIC(4778)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 3072
Log buffer size (4KB) (LOGBUFSZ) = 4024
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 65000
Buffer pool size (pages) (BUFFPAGE) = 5000
SQL statement heap (4KB) (STMTHEAP) = 20400
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(1024)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(10016)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4096)

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Lock timeout (sec) (LOCKTIMEOUT) = -1

Changed pages threshold (CHNGPGS_THRESH) = 33
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 6
Number of I/O servers (NUM_IOSERVERS) = 18
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 24

Max number of active applications (MAXAPPLS) = AUTOMATIC(40)
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 32768

Log file size (4KB) (LOGFILSIZ) = 15000
Number of primary log files (LOGPRIMARY) = 25
Number of secondary log files (LOGSECOND) = 200
Changed path to log files (NEWLOGPATH) =
Percent log file reclaimed before soft chckpt (SOFTMAX) = 175
Updated on 2012-11-21T12:31:54Z at 2012-11-21T12:31:54Z by SystemAdmin
  • p175
    49 Posts

    Re: Memory Configuration with 4gb Express-C 10.1 on Windows 7 Pro SP1 64-bit

    ‏2012-05-19T01:18:28Z  in response to p175
    No takers ? Surely someone out there has installed the 64-bit version of 10.1 LUW Express-C and configured it for 4gb without getting the unable to find a donor errors while being able to utilize the entire 4gb or memory allowed ?

    Simply after how it is done is all.
    • p175
      49 Posts

      Re: Memory Configuration with 4gb Express-C 10.1 on Windows 7 Pro SP1 64-bit

      ‏2012-05-21T18:36:25Z  in response to p175
      Look people, don't make me beg ok .. heh

      Just after very rough estimates of how to take full advantage of the 4gb available to Express-C 10.1 64-bit in a WAREHOUSE environment.

      As a rough estimate, how much would one assign to bufferpools and to the other main memory config params such as SHEAPTHRES_SHR and SORTHEAP whilst all other params are set to automatic.

      Presently I am getting a mess of unable to find donor messages filling up the stmm.log file. This is what I am trying to avoid while taking FULL advantage of the entire 4gb. The PC has 8gb memory so I do not need to worry about 'other' applications. The db2syscs process is only utilizing some 2.3gb in memory at the moment.
      • SystemAdmin
        5837 Posts

        Re: Memory Configuration with 4gb Express-C 10.1 on Windows 7 Pro SP1 64-bit

        ‏2012-11-19T15:05:57Z  in response to p175
        I have the same Db2-version running on Windows 2008 bit.

        When allocation a bufferpool with fixed size 500 000, I experienced the same problem, with STMM reporting no memory left to grab.
        I would start by reducing the size of your BP, may setting it to Automatic
        • SystemAdmin
          5837 Posts

          Re: Memory Configuration with 4gb Express-C 10.1 on Windows 7 Pro SP1 64-bit

          ‏2012-11-21T12:31:54Z  in response to SystemAdmin

          I would start by setting everything AUTOMATIC, including the bufferpools then continuously perform mining runs for a few hours, until the parameter values do not evolve, giving an idea of the optimal values for the parameters.

          Then I would update the parameters with these optimal values, without the automatic option.

          Also, since you typically perform a lot of sequential access to the data, consider using a large pagesize for the bufferpool and the tablespaces.


          Yves-Antoine Emmanuelli