Oracle RAC LoadBalance

The loadbalance is to distribute the load evenly to each node in the cluster, thus improving the overall throughput capacity. The Oracle 10g RAC provides two different ways to spread the load:

1. By connection balancing, the user is assigned to a different node according to an algorithm. It can also be considered as a decentralized load of pure technology.

2. Through the service, dispersed on the application layer can also be considered as the distributed load of the business of the face.

A Connection balancing

Connection balancing this load balancing is done at the user connection level, that is, when the user requests to establish a connection, depending on the load of each node to decide which instance to assign the connection to, and once the connection is established, all operations of the session are done on this instance, Instead of being assigned to another node.

Connection Balancing has two implementations of client and server.

1.1 Client equalization (Client-side LB)

Client-side equalization (Client-side LB) is a method used by Oracle 8, and is configured by adding the client's Tnsnames.ora file:

Load_balance=yes entry. When a client initiates a connection, a random selection is taken from the address list and the connection request is assigned to each instance using the algorithm.

A TNS configuration file for clint-side lb is as follows:

RAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = RAC1-VIP) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = RAC2-VIP) (PORT = 1521))

(load_balance = YES)

(Connect_data =

(SERVER = dedicated)

(service_name = RAC)

)

)

)

Note: RAC1-VIP needs to be added to the Hosts file

The disadvantage of this method is obvious, because the actual load of each node is not taken into account when assigning the connection, the final allocation result is not necessarily balanced, and the algorithm requires a long time slice, and if multiple connections are initiated simultaneously in a short time, these connections may all be assigned to a node, or even worse, The connection may be assigned to the failed node. So Oracle introduced a service-side equalization (Sevice-side LB) approach.

1.2 Server-side equalization (Server-side LB)

Server-side LB was introduced from Oracle 9. Its implementation relies on the listener to collect payload information. During the database run, the Pmon background process collects the system payload information and then enlists it into listener. At least 1 minutes, up to 10 minutes Pmon is going to do an update of the information, and if the load of the node is higher, the update frequency is higher, to ensure that the listener can grasp the accurate load of each node. If listener is turned off, the Pmon process checks for listener restarts every 1 seconds. In addition to this automatic, scheduled update task, users can also use the ALTER System Register command to perform this process manually.

This automatic update action can be seen from the listener log, such as the following listener log fragment that clearly records these actions. Note that the first enlistment process made by the Pmon process when the instance starts is called Server-register, and the subsequent update process is called Service-update.

[Email protected] log]# pwd

/u01/app/oracle/product/10.2.0/db_1/network/log

[Email protected] log]# more *.log

.....

27-feb-2010 02:15:10 * service_register * RAC1 * 0

27-feb-2010 02:15:11 * service_update * RAC1 * 0

27-feb-2010 02:15:11 * service_update * RAC1 * 0

27-feb-2010 02:15:23 * service_update * +ASM1 * 0

27-feb-2010 02:15:32 * service_update * +ASM1 * 0

.....

Listener Log while recording the Pmon process registration and update action, but the content of the registration is not reflected, want to get these content, can be obtained by tracking 10257 time, this event is to track Pmon activities.

event= "10257 Trace name Context FOREVER,LEVL 16"

For specific use of the event, refer to my blog:

Http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4977827.aspx

The Pmon process registers not only the local listener, but also the listener on the other nodes. But exactly where to register is determined by the two parameters of Remote_listeners and Local_listener. Local_listener is not set, and Remote_listener needs to be set, the parameter value is a tnsnames item.

[[Email protected] ~]$ set ORACLE_SID=RAC1

[Email protected] ~]$ Sqlplus/nolog

Sql*plus:release 10.2.0.1.0-production on Fri Mar 5 00:52:19 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Sql> Conn/as SYSDBA

Connected.

Sql> Show Parameter Listener

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

Local_listener string

Remote_listener string Listeners_rac

Sql>

The corresponding Listeners_rac contents in the Tnsnames.ora of this machine are as follows:

Listeners_rac =

(Address_list =

(ADDRESS = (PROTOCOL = TCP) (HOST = RAC1-VIP) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = RAC2-VIP) (PORT = 1521))

)

With the Pmon automatic registration mechanism, each node of the cluster listener the load situation of all nodes, when the client connection request is received, the connection is transferred to the node with the least load, it is possible that the node itself may be other nodes, that is, listener will forward the user's request.

The node selection method for listener differs depending on how the user requests the connection:

1). If the user is requesting a delicate private connection, listener first selects the node with the least load, and if multiple nodes have the same load, select the instance with the least load from the node.

2). If the user requests a Shre server shared feature connection, in addition to the node load comparison and the instance load comparison, also select the least loaded dispatcher to forward on the lock selection instance.

Server-side lb and client-side lb are not mutually exclusive, they can work together, this is the user's connection request will be selected from the address list of a random address, and then to the address of the Listener to make a request; Listener received the request, Select the most appropriate node forwarding connection request based on the load situation of each node.

1.3 Configuration methods for two LB types

For client-side lb, you need to add load_balance=yes to the customer's TNSNames entry, and for server-side lb, you need to configure Remote_listener for this parameter. 、

Note: When you configure LB, you need to remove the default generated from the Listener.ora file for each node instance

Sid_list_listener_nodename entries so that the information obtained by LISTENER is dynamically registered, not static information that is read from the file.

We want to delete:

SID_LIST_LISTENER_RAC1 =

(Sid_list =

(Sid_desc =

(Sid_name = Plsextproc)

(Oracle_home =/u01/app/oracle/product/10.2.0/db_1)

(program = Extproc)

)

)

Keep only:

LISTENER_RAC1 =

(Description_list =

(DESCRIPTION =

(address = (PROTOCOL = TCP) (HOST = RAC1-VIP) (PORT = 1521) (IP = first)

(address = (PROTOCOL = TCP) (HOST = 10.85.10.119) (PORT = 1521) (IP = first)

)

)

Two. Distribute the load with service

The deficiencies of the connection balancing method are analyzed first. Oracle's cluster is a "share everything" architecture, and all nodes share a single copy of the disk data. Data synchronization between instances through the cache fusion mechanism, so the performance of RAC is largely limited by the performance of cache fusion. Therefore, to improve the performance of RAC, we can start with two aspects:
1. Improve the cache fusion capability by using better connected devices such as the G-class private network, or using DRA technology such as InfiniBand.

2. You can minimize cache fusion traffic and reduce the interdependence between instances. Service is the latter way of thinking based on the development of the deletion.

Look at the partition technology that is very similar to the service. If the number in a table is huge, Oracle recommends using partition table to spread the data across multiple physical segments according to a certain pattern, such as time, so that access to the data is limited to some local segment.

The idea of "decentralized data" is further enhanced, in a RAC environment, if data can be separated by application. For example: An ERP application includes production, sales, supply chain management of multiple modules. Assuming that the database uses 2-node RAC, before the "scatter data", two users are using the Sales module, then the two users may be assigned to two nodes, during the operation, the sales data will be in the role of cache fusion, and continuously in two bytes passed. If another two users of the production module are assigned to two nodes in two users, during operation, the production part will synchronize with the two instances with the assistance of the cache fusion.

As can be seen, if only connection balancing a mechanism, it appears that the user is dispersed to different instance, it seems that the load is scattered. But this dispersion is not combined with the business needs of each user, is a purely technical means. This dispersion may increase the burden on the system.

If a different idea, if the Sales module users are assigned to Node 1, the Production module users are assigned to Node 2, in the assumption that the data between the two modules are not cross. At this point the Sales module data are concentrated on Node 1, the Production module data are concentrated on Node 2, the Cache fusion workload will be sharply less, you can fundamentally solve the performance problem.

This idea is the basic idea of using service to distribute load. By dividing the application into service according to the function module, and then fixing each service on a RAC node, the performance of the system is fundamentally decent. This decentralized approach is not done by DBAs alone, and requires the DBA and the developer to work together to understand the business data features before they can see the effect.

In a RAC environment, service is not required, but if it is possible to use service-corresponding partitioning, it is a great benefit to improve the performance of the whole system. There is another benefit of using the service: You can create service TAF parameters inside the database, and if the customer connects to the database through a service, many of the Fail-over settings are no longer required in the client's Tnsnames.ora. You only need to add the following entries:

RAC =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = RAC1-VIP) (PORT = 1521))

(ADDRESS = (PROTOCOL = TCP) (HOST = RAC2-VIP) (PORT = 1521))

(load_balance = YES)

(Connect_data =

(SERVER = dedicated)

(service_name = RAC)

)

)

)

Ext.: http://blog.csdn.net/tianlesoftware/article/details/5347775

Oracle RAC LoadBalance