4 replies Latest Post - ‏2012-11-30T14:08:45Z by SystemAdmin
17917 Posts

Pinned topic Max active databases on Solaris 10

‏2012-11-05T13:15:29Z |
Maximum active databases and error SQL1084N

First, I'm not a DB2 specialist and I'm facing a huge challenge.
Here some background info:
  • We have a db2 installation, v9r5, running on a solaris 10 zone. the host has 8Gb memory and 8Gb swap file configured.
  • Our installation is intended to support application development, so performance isn't a issue, but since our customer is a huge financial institution, we have to keep too many databases, most of them active simultaneously: from 40 and up to 50 databases!
  • With that setup, we can't reach this number. At this time we can have up to 24 active databases, and the next database activation will return the SQL1084N message!

The problem is: we don't see the DB2 using all the memory and we can't figure out how to improve database activation usage!

We played with some individual database configuration and with some database manager configuration and we had no success, and our last setup is running with default configuration parameters (except NUMDB = 32 - for now).

Our DBM CFG follow:

Database Manager Configuration

Node type = Enterprise Server Edition with local and remote clients

Database manager configuration release level = 0x0c00

CPU speed (millisec/instruction) (CPUSPEED) = 1.324768e-07
+Communications bandwidth (MB/sec) (COMM_BANDWIDTH) = 1.000000e+02+

+ * * *+

Max number of concurrently active databases (NUMDB) = 32

+ * * *+

Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(90)
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(1744356)
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024

Agent stack size (AGENT_STACK_SZ) = 1024
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
Query heap size (4KB) (QUERY_HEAP_SZ) = 1000

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)

+ * * *+

One of our test DB CFG:

Database Configuration for Database

Database configuration release level = 0x0c00
Database release level = 0x0c00

+ * * *+

Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(79560)
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(608)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(397)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(255)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(51)

Database heap (4KB) (DBHEAP) = AUTOMATIC(2561)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
Log buffer size (4KB) (LOGBUFSZ) = 98
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 51752
Buffer pool size (pages) (BUFFPAGE) = 1000
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(8192)
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(256)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40000)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)

+ * * *+

The "db2mtrk -i -d -v" command get something totalizing from 31784960 bytes and up to 81395712 bytes and all active databases are summing up to just 1Gb memory.

Running "db2osconf" at the host:

/etc/system setting:

set msgsys:msginfo_msgmni = 3584
set semsys:seminfo_semmni = 4096
set shmsys:shminfo_shmmax = 17162158080
set shmsys:shminfo_shmmni = 4096

Resource control setting for project "user.root":

projmod -a -K "project.max-shm-memory=(privileged,34324316160,deny)" user.root
projmod -a -K "project.max-shm-ids=(privileged,4096,deny)" user.root
projmod -a -K "project.max-msg-ids=(privileged,3584,deny)" user.root
projmod -a -K "project.max-sem-ids=(privileged,4096,deny)" user.root

  • We aren't using resources pools for this zone.

Our /etc/project follows:


  • Setting project user.db2inst1 since this is the user who is running the instance!.

And our /etc/system:

set msgsys:msginfo_msgmni = 3584
set semsys:seminfo_semmni = 4096
set shmsys:shminfo_shmmax = 17162158080
set shmsys:shminfo_shmmni = 4096
set rlim_fd_max = 65536

Thanks in advance.
Updated on 2012-11-30T14:08:45Z at 2012-11-30T14:08:45Z by SystemAdmin
  • SystemAdmin
    17917 Posts

    Re: Max active databases on Solaris 10

    ‏2012-11-22T13:31:48Z  in response to SystemAdmin

    How about configuring these databases in several instances ? My understanding is that you attempt to create all these databases in instance db2inst1.


    Yves-Antoine Emmanuelli
  • nivanov1
    231 Posts

    Re: Max active databases on Solaris 10

    ‏2012-11-22T15:00:46Z  in response to SystemAdmin
    Are both Solaris and the DB2 instance 64-bit?
  • mwandishi
    47 Posts

    Re: Max active databases on Solaris 10

    ‏2012-11-27T07:44:43Z  in response to SystemAdmin

    I suggest taking a step back here. Firstly, increasing NUMDB will allow for more DBs to be concurrently active.

    However, the SQL1084 means: "Shared memory segments cannot be allocated." So increasing NUMDB will only compound this situation. Typically this would be because we are hitting an OS kernel parameter limit but the shm project limit setting appears sufficient. It could be 9.5 APAR: IZ16855:

    Which is fixed in 9.5 fixpack 2. You haven't posted your db2level so I can't determine the relevance.

    With regard to the use of memory, are all DBs configured with (SELF_TUNING_MEM) = ON & DATABASE_MEMORY = AUTOMATIC? If so then STMM will make memory changes based on demand. Given this is a development box I can only assume there isn't that much demand. As more 'work' comes in requiring more memory I'd expect to see the various pool & heap sizes increase up to ~75-95% of INSTANCE_MEMORY, although in this case I'd expect it to be closer to 75% of INSTANCE_MEMORY given the system memory size. INSTANCE_MEMORY is only a target.

    Incidentally, how are you measuring the memory usage?

    That said prior to 9.5.5 there is an issue with system mem utilisation if there are lots of very small DBs in an instance. Later fixpacks for 9.5 and 9.7 contains STMM fixes so consider moving to a more recent fixpack.

    So in summary:
    1) Check db2level to determine if IZ16855 applies.
    2) Apply later/latest fixpacks to improve functionality / fix bugs.
    3) Check STMM logs to confirm if STMM is changing parameters as expected.

    If after applying the fixpacks to resolve known STMM issues you still have a problem I would open a PMR and have support review the config.
    We need to see why specifically we are hitting SQL1084, db2diag.log + pmap & ipcs will determine this.
    • SystemAdmin
      17917 Posts

      Re: Max active databases on Solaris 10

      ‏2012-11-30T14:08:45Z  in response to mwandishi
      Hi mwandishi,
      Tks for your response.

      Regarding the installed db2level and other detail's:
      • Solaris 5.10 x86/64
      • DB2 v9.5.0.10 64bits (fix pack 10)

      We improved the number of databases we can put together into a machine changing the way we create our databases - adding "autoconfigure apply none".

      Seems to me that when DB2 configuration parameters says "TUNNING MEMORY", actually we must read: "using more memory to optimize query performance" and not "memory usage"!

      This way we're disregarding all optimizations that DB2 can possibly give us. At least in a non-productive environment this is not an issue but even so we think this approach are somewhat radical, even taking in account our lack of knowledge managing a DB2 server! This can cost us in the future and we can lost all management gain we're laboring to get now!

      May be someone can figure a way to optimize the way the DB2 instance use the memory or a design to a environment where someone have a lot of small databases and are not concerned with huge tables and long running queries!

      Today our problem is solved - until our solution starts to haunt us tomorrow!