How to Make DB2 use all the memory

Today we are going to discuss with you how to make DB2 use all the memory. I have seen materials on the relevant website about how to make DB2 use all the memory, I will share it with you today. The following is a detailed description of the article. I hope you can learn from it.

Have you heard of creative stress? It belongs to one of those pseudo-spiritual philosophies, and its ability to claim interaction creates things as a by-product of struggle. This is a bit like a struggle between good and evil in a book.
Introduction

Have you heard of creative stress? It belongs to one of those pseudo-spiritual philosophies, and its ability to claim interaction creates things as a by-product of struggle. This is a bit like a struggle between good and evil in a book. Now, I don't want to say that all software engineers are good people, or all hardware engineers are bad people, but there is creative pressure between them.

As Joseph Campbell said, "Do not confuse your eyes with your inpractical hate for science, so that you cannot see the glorious realm of computer chips ." If the overall appearance of the waves flow out of the disk and into the memory, what is more romantic than this?

Sometimes, software engineers lament that hardware is growing too slowly: The machine disk speed is too slow, the memory group is too small, and the clock speed is crawling like a snail bait. (When the hardware development catches up, we may forget Java™The application used to be so slow .) When a new generation of hardware emerged, the operating system first adapted, but left users with the pain of running 16-bit or (panic) 8-bit DOS applications with a 32-bit architecture.

Now the pressure is on software engineers: when will they recompile the application and use the new data types and memory addressable capabilities provided by the new hardware? In the final analysis, when you compare the BASIC running on 8086 with the C ++ running on 24 SMP, running the "Hello World" program takes about as long as writing the program.

However, the database must output "Hello World" to the display ". Similar to Web server software's expectation for higher-speed lines, database software expects to benefit as much as possible from each upgrade of disk speed, capacity, and addressable memory. Although application programmers may complain that they must recompile 16-bit programs for 32-bit machines (it is already running well), Database engineers like the idea:

Store data in memory rather than on disks before sorting, clustering, or sending data to users. I/O is the killer of so many demanding workloads-that's why you distribute 1 TB of data to 5 TB disks (more disks = more axes), this means more parallel I/O, at least in the benchmark world ).

Now, in the world of Proteus and iSCSI, the 64-bit architecture is gradually becoming a standard that allows commercial UNIX®(For example, AIX®, HP-UX, Solaris, etc.) to provide a lot of memory for your favorite relational database. The addressing capability of 32-Bit Memory is about 4 GB, and many UNIX machines have 20 to 100 GB memory, so you certainly want to use this large memory.

The Intel world is not far behind: at present, Linux and Windows 2000 running on 64-bit Intel chips are a reality in operating systems, compilers, and database software laboratories, and will soon be sold on the websites around you.

If both the hardware and the operating system are ready to use huge memory and the database can also use large memory, how can you combine them and make them work? Use DB2®In version 7, we must first understand that, internally, DB2 assumes that it uses 32-Bit Memory and hardware. To take advantage of larger memory, you must tell DB2 how to use it and how to use it.

Do not blame DB2-most DB2 clients and many DB2 servers will run on 32-bit Intel machines in the next few years. And even if DB2 detects 96 GB of memory on your machine, who can be sure that you want DB2 to use all the memory instead of sharing it with other applications?

When using this large memory, you have several options. The most obvious choice is to create a 64-bit DB2 instance. DB2 version 7 on AIX, Solaris, and HP-UX now supports this operation. If you have version 7.1, you must download revision package 1 to install the 64-bit DB2 database. If you have version 7.2 or later, you do not have to install the revision package to create a 64-bit DB2 instance. To create a 64-bit DB2 instance, run the db2icrt command and specify the value of-w as 64. For example:

 
 

  
  
db2icrt -w 64 -u db2fenc1 db2inst1 
 
 

The manuals used by DB2 in the 64-bit environment are located:

Http://www-4.ibm.com/cgi-bin/db2www/database/db2/udb/winos2unix/support/document.d2w/report? Fn1_db2q9e71frm3toc.htm

1 + 1 = 2. The power of 2 is a great number.

Each 32-bit DB2 instance can address 4 GB memory. Generally, you want to allocate most of the memory to the buffer pool. However, memory segments on AIX, HP-UX, and Windows Limit the maximum buffer pool size to 4 GB. Even on Solaris with a very clean Memory Model in the 32-bit world, the memory used for the DB2 Buffer Pool cannot exceed 3.35 GB; the remaining 4 GB memory space must be dedicated to other shared memory usage of DB2.

(Fortunately, the memory model is cleaner for all operating systems in the 64-bit world .) On the HP-UX, the maximum buffer pool that a 32-bit DB2 instance can create is approximately 800 MB. On a HP-UX, a buffer pool of more than 1 GB is available only when you run multiple instances by using Memory Windows on a 32-bit HP-UX. (HP Memory Windows is described in the DB2 Release Notes .) On Windows, the buffer pool is limited to 3 GB, 1.75 GB on AIX, and about 1 GB on Linux.

In a large memory system running 32-bit DB2, a large amount of memory should be provided to the buffer pool. The simplest way is to use the Enterprise-Extended Edition (EEE) in a DB2 Enterprise Extended Edition )) run multiple logical DB2 instances in configuration. You only need to run one instance of the operating system, which helps to save costs and allow multiple DB2 instances to communicate with each other through shared memory instead of TCP/IP or communication switch.

With the non-shared architecture of DB2, each instance can happily address 4 GB memory within its own database partition. In most DB2 TPC-H benchmarking-It typically enables DB2 EEE to run decision-making support queries on a database of up to 300 GB or larger-a large SMP divides each DB2 node up to 4 GB memory (each node is a database partition that runs its own DB2 instance ).

DB2 can also use three other methods to leverage large memory machines. On AIX, Solaris, and Windows, DB2 supports Extended Storage (also known as ESTORE ). This allows DB2 to use the maximum memory available in the 32-Bit Memory Model for system temporary tables (for sorting) and read-only user data. When DB2 acquires data from a disk, DB2 determines which data can be considered read-only, but DB2 needs to be configured to use extended storage.

Let's consider a typical situation: you are designing a database where you want to put as many tables as possible into the memory. First, update the database manager configuration and tell it how many extended storage segments (num_estore_segs) it will use ). The default value is zero. N multiple distinct operations depend on the size of the table, the available memory, and the memory you want to use for the specific table. Assuming we are using Solaris, it has 6 GB memory-2 GB memory above 4 GB memory is used to expand memory (also known as estore ):

 
 

  
  
update db cfg for sample using num_estore_segs n 
 
 

Use the database configuration parameters "expand the size of the storage segment" (estore_seg_sz) to define the size of the estore segment:

 
 

  
  
update db cfg for sample using estore_seg_sz 32000 
 
 

Now you have created a buffer pool. In this example, the page size is 8 K, although 16 K and 32 K are allowed. (For Windows, if you want to use more than 2 GB of memory, you must use a page size greater than 4 kb .) You must enable the buffer pool for the extended storage. You can use the extended storage keyword. Highmem is the name I selected for this buffer pool. The size n depends on the amount of memory occupied by the buffer pool:

 
 

  
  
CREATE BUFFERPOOL highmem SIZE n   
  
  
PAGESIZE 8K EXTENDED STORAGE 
 
 

Create a tablespace and allocate it to the buffer pool:

 
 

  
  
CREATE TABLESPACE highmem_tbsp PAGESIZE 8K   
  
  
MANAGED BY SYSTEM   
  
  
USING ('C:highmemdir)   
  
  
BUFFERPOOL highmem 
 
 

The above content is an introduction to the methods that allow DB2 to use all the memory. I hope you will gain some benefits.