5.6. Instance and Database Design Considerations Now that you are familiar with the concepts of instances and how to configure instances and databases, you may be wondering about design issues. Is it better to have one instance or multiple instances per server machine? Is it better to have one database or multiple databases per instance? You may want to have multiple instances per server machine for the following reasons. You want to separate your development and test environments but you cannot afford to have different servers. For security reasons, you want to have different instances. As you will see in Chapter 10, Implementing Security, you can grant SYSADM authority to an operating system group by assigning the name of this group to the SYSADM_GROUP Database Manager Configuration parameter. For example, if not all system administrators should have access to the PAYROLL database, you can put this database in its own instance and create an operating system group with only authorized user IDs, which you would then assign to SYSADM_GROUP. The other instances would have another operating system group assigned to their own SYSADM_GROUP Database Manager Configuration parameter. You want to configure instances differently depending on your application. For example, as you will see in Chapter 16, Database Performance Considerations, the INTRA_PARALLEL Database Manager Configuration parameter should be set to NO when the workload is OLTP type, but set to YES when the workload is OLAP (or DSS) type. Because this is an instance-level parameter, all the databases inside the instance will use the same setting. Creating databases that will be used for either OLTP or OLAP in different instances will allow you to set INTRA_PARALLEL correctly for each case.
If none of the above applies to you, we recommend having one instance per server and one database per instance; this will guarantee that all resources are allocated to that one database and instance. NOTE OLTP stands for online transaction processing. The OLTP type of workload implies short transactions performing simple SELECT, INSERT, UPDATE, and DELETE operations affecting a small number of rows. OLAP stands for online analytical processing and DSS for decision support systems. OLAP and DSS have similar types of workload, and they imply complex SELECT statements, normally using several JOIN operations.
If you do decide to create multiple instances on a server, remember that an instance consumes some resources when started. Chapter 15, The DB2 Memory Model, discusses the memory allocated for instances. In general, an instance does not consume a great amount of resources; however, the databases inside the instances may consume a lot of memory depending, for example, on the size of their buffer pools. Keep in mind when you have many instances, each with many databases, that the memory used by all the databases and instances when they are active should be less than the physical memory of the machine; otherwise paging or swapping will occur, which will affect the performance of the entire system. |