15.10. Case Study
You can use the tools available in DB2 to examine how much memory is being used by different DB2 operations.
To get the most useful information, start with a stopped DB2 instance. Running the memory tracker tool, db2mtrk, indicates that the instance is not running (see Figure 15.7)
Figure 15.7. Verifying that an instance is not running
Start the instance and see how much memory it will use (see Figure 15.8).
Figure 15.8. Seeing how much memory an instance uses
C:>db2start 03/28/2005 13:47:08 0 0 SQL1063N DB2START processing was successful. SQL1063N DB2START processing was successful. C:>db2mtrk -i -p -v Tracking Memory on: 2005/03/28 at 13:47:10 Memory for instance Database Monitor Heap is of size 16384 bytes Other Memory is of size 7667712 bytes Total: 7684096 bytes No active agents |
The output in Figure 15.8 shows this particular instance consumes 7,684,096 bytes (7.3MB) when it is started.
If you activate a database, all of its shared memory, such as the lock list and buffer pool, will be allocated (see Figure 15.9).
Figure 15.9. Verifying how much memory is allocated
C:>db2 activate db samp82 DB20000I The ACTIVATE DATABASE command completed successfully. C:>db2mtrk -i -p -v Tracking Memory on: 2005/03/28 at 13:47:43 Memory for instance Backup/Restore/Util Heap is of size 16384 bytes Package Cache is of size 81920 bytes Catalog Cache Heap is of size 65536 bytes Buffer Pool Heap is of size 1179648 bytes Buffer Pool Heap is of size 655360 bytes Buffer Pool Heap is of size 393216 bytes Buffer Pool Heap is of size 262144 bytes Buffer Pool Heap is of size 196608 bytes Lock Manager Heap is of size 278528 bytes Database Heap is of size 3637248 bytes Database Monitor Heap is of size 180224 bytes Other Memory is of size 7733248 bytes Total: 14680064 bytes No active agents |
The output in Figure 15.9 indicates that when this particular database is activated, 14,680,064 bytes (14MB) are consumed.
If you connect to the database, you will create a db2agent process, and this will also consume some memory (see Figure 15.10).
Figure 15.10. Seeing how much memory an agent uses
C:>db2 connect to sample Database Connection Information Database server = DB2/NT 8.2.0 SQL authorization ID = DSNOW Local database alias = SAMPLE C:>db2mtrk -i -p -v Tracking Memory on: 2005/03/28 at 13:49:12 Memory for instance Backup/Restore/Util Heap is of size 16384 bytes Package Cache is of size 81920 bytes Catalog Cache Heap is of size 65536 bytes Buffer Pool Heap is of size 1179648 bytes Buffer Pool Heap is of size 655360 bytes Buffer Pool Heap is of size 393216 bytes Buffer Pool Heap is of size 262144 bytes Buffer Pool Heap is of size 196608 bytes Lock Manager Heap is of size 278528 bytes Database Heap is of size 3637248 bytes Database Monitor Heap is of size 180224 bytes Other Memory is of size 7766016 bytes Total: 14712832 bytes Memory for agent 2224 Other Memory is of size 65536 bytes Application Heap is of size 131072 bytes Application Control Heap is of size 16384 bytes Total: 212992 bytes |
In this case, the output in Figure 15.10 shows the agent consumes 212,992 bytes (208KB) of memory.
Let's execute some SQL statements and see how that affects the agent private memory allocation (see Figure 15.11).
Figure 15.11. How executing SQL statements affects an agent's memory allocation
C:>db2mtrk -i -p -v
Tracking Memory on: 2005/03/28 at 13:56:02
Memory for instance
Backup/Restore/Util Heap is of size 16384 bytes
Package Cache is of size 262144 bytes
Catalog Cache Heap is of size 65536 bytes
Buffer Pool Heap is of size 1179648 bytes
Buffer Pool Heap is of size 655360 bytes
Buffer Pool Heap is of size 393216 bytes
Buffer Pool Heap is of size 262144 bytes
Buffer Pool Heap is of size 196608 bytes
Lock Manager Heap is of size 278528 bytes
Database Heap is of size 3637248 bytes
Database Monitor Heap is of size 180224 bytes
Other Memory is of size 7766016 bytes
Total: 14893056 bytes
Memory for agent 2224
Other Memory is of size 65536 bytes
Application Heap is of size 212992 bytes
Application Control Heap is of size 16384 bytes
Total: 294912 bytes
|
As shown in Figure 15.11, the memory tracker tool indicates now that the agent's memory has grown to 294,912 bytes (288KB), an increase of 80KB from the output shown in Figure 15.10, due to an increase in the application heap (from 131,072 to 212,992 bytes). Since the statements would have been optimized to be run, their access plans would have been copied into the application heap as they were run.
Let's back up the database and see how that affects the memory used. Figure 15.12 shows a snapshot of db2mtrk while the backup is running, and Figure 15.13 shows it when it has completed. During the backup operation you can see that the backup/restore buffer is allocated.
Figure 15.12. Memory in use while a backup is running
C:>db2mtrk -i -p -v
Tracking Memory on: 2005/03/28 at 13:59:19
Memory for instance
Backup/Restore/Util Heap is of size 18432000 bytes
Package Cache is of size 81920 bytes
Catalog Cache Heap is of size 65536 bytes
Buffer Pool Heap is of size 1179648 bytes
Buffer Pool Heap is of size 655360 bytes
Buffer Pool Heap is of size 393216 bytes
Buffer Pool Heap is of size 262144 bytes
Buffer Pool Heap is of size 196608 bytes
Lock Manager Heap is of size 278528 bytes
Database Heap is of size 3620864 bytes
Database Monitor Heap is of size 16384 bytes
Other Memory is of size 7864320 bytes
Total: 33046528 bytes
|
Figure 15.13. Memory when a backup has finished
C:>db2mtrk -i -p -v
Tracking Memory on: 2005/03/28 at 14:01:08
Memory for instance
Database Monitor Heap is of size 16384 bytes
Other Memory is of size 7782400 bytes
Total: 7798784 bytes
No active agents
|
Once the backup completes, the backup/restore buffer gets released. Since this was an offline backup, it also releases the connection to the database, so the buffer pools, database heap, lock list, and other memory areas are also released.