Ways to make the DB2 database use all memory

Brief introduction

Have you ever heard of creative stress? It belongs to one of those pseudo spiritual philosophies, claiming that the forces of interaction create things that are by-products of struggle. It's kind of like the struggle between good and evil in a comic book. Now, I don't want to say that all software engineers are good people, or that all hardware engineers are bad guys, but there's a creative pressure between them. As Joseph Campbell said, "Don't let your blind dislike of science confuse your eyes, and you cannot see the brilliance of computer chips." "What could be more romantic if the whole wave of imagery gushed out the disk and burst into memory?"

Sometimes, software engineers lament the slow pace of hardware development: machine disks are too slow, memory groups are too small, and clocks are crawling like snails. (When hardware development catches up, we may forget that the Java™ application used to be so slow.) When the next generation of hardware appears, the operating system first adapts, but leaves the user with the pain of running a 16-bit or (breathless) 8-bit DOS application with a 32-bit architecture. Now the pressure is turning to software engineers: When are they going to recompile the application and take advantage of the new data types and memory-addressable capabilities that new hardware offers? In the final analysis, you will run the "Hello World" program as long as it takes to write the program when you run the BASIC in 8086 compared to C + + running on 24-way SMP.

However, the database has to do much more than to output "Hello world" to the display. As the Web server software expects a higher speed line, the database software expects to benefit as much as possible from each upgrade of disk speed, capacity, and addressable memory. Although application programmers may complain about having to recompile a 16-bit program for a 32-bit machine (which is already running well), database engineers like the idea of keeping it in memory rather than on disk before sorting, aggregating, or sending the data to the user. I/O is the cause of so many demanding workloads-that's why you spread 1 terabytes of data to 5 TB of disk (more disks = more axes, which means more parallel I/O, at least in the benchmark world).

Now, in the RISC and Sparc World, the 64-bit architecture is becoming a standard, allowing commercial UNIX® such as aix®, HP-UX, and Solaris to provide a large amount of memory for your favorite relational database. 32-bit memory has an addressable capacity of approximately 4 GB, and many UNIX machines have 20 to GB of RAM, and you certainly want to use such a large amount of memory. The Intel world doesn't lag much behind: now Linux and Windows 2000, which are running on 64-bit Intel chips in operating systems, compilers, and database software labs, are a reality and will soon be sold on the sites around you.

So, if both the hardware and the operating system are ready to use huge memory and the database can take advantage of large memory, how do you combine them and make them work? With db2® version 7, the first thing to figure out is that internally, DB2 assumes the use of 32-bit memory and hardware. To take advantage of larger memory, you must tell DB2 you can 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 over the next few years. And even if DB2 detects gigabytes of RAM on your machine, who is sure you want DB2 to use all of the memory instead of sharing it with other applications?

When you use this large memory, you have several options. The most obvious option is to create a 64-bit DB2 instance. This operation is now supported in DB2 version 7 on AIX, Solaris, and HP-UX. If you have version 7.1, you must download Revision Pack 1 to install the 64-bit DB2 library. If you have version 7.2 or later, you do not have to install the revision pack to create a 64-bit DB2 instance. To create a 64-bit DB2 instance, you can use the DB2ICRT command and specify 64 for the parameter-W value. For example:

db2icrt -w 64 -u db2fenc1 db2inst1

Manuals that describe the use of DB2 in 64-bit environments are located at:

http://www-4.ibm.com/cgi-bin/db2wwwhttp://www.bianceng.cn/database/db2/udb/winos2unix/support/document.d2w/ Report?fn=db2q9e71frm3toc.htm

1 + 1 = 2. 2 of 32 times = a great number.

Each 32-bit DB2 instance can address 4 GB of memory. Typically, you want to dedicate most of your memory to the buffer pool. However, memory fragmentation on AIX, HP-UX, and Windows limits the maximum buffer pool size to less than 4 GB. Even on Solaris, which has a very clean memory model in the 32-bit world, the memory used for the DB2 buffer pool cannot exceed the rest of the 3.35 gb;4 GB memory space must be dedicated to other shared memory uses of DB2. (Luckily, the memory model is cleaner for all operating systems in the 64-bit world.) On HP-UX, the maximum buffer pool that a 32-bit DB2 instance can create is approximately MB. On HP-UX, a buffer pool of more than 1 GB can be used only if multiple instances are run by using Memory Windows on 32-bit HP-UX. (DB2 Release Notes) describes HP Memory Windows. On Windows, the buffer pool is limited to 3 Gb,aix 1.75 GB, while Linux is about 1 GB.

On a large memory system running 32-bit DB2, the simplest way to give a large amount of memory to a buffer pool is to run multiple logical EEE instances in a DB2 enterprise extension (enterprise-extended Edition (DB2)) configuration. Only one instance of the operating system needs to be run, which will help save overhead and allow multiple DB2 instances to communicate with each other through shared memory rather than through TCP/IP or communication switches. With a DB2-free architecture, each instance can happily address 4 GB of memory within its own database partition. In most DB2 tpc-h benchmarking-it usually lets DB2 EEE run decision support queries on databases of up to GB or greater-a large SMP divides up to 4 GB of memory for each DB2 node (each node is a number of DB2 instances running its own) According to the library partition).

DB2 can also use three other methods to take advantage of large memory machines. On AIX, Solaris, and Windows, DB2 supports extended storage (Extended Storage) (also known as ESTORE). This allows DB2 to use the memory in excess of 32-bit memory models for system temp tables (for sorting) and read-only user data. When DB2 obtains data from a disk, the DB2 determines which data can be considered read-only, but requires DB2 to be configured to use the extended storage.