An Oracle White Paper March PHP Scalability and High Availability Database Resident Connection Pooling and Fast Application Notification - PDF

Please download to get full document.

View again

of 20
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information Report
Category:

Math & Engineering

Published:

Views: 7 | Pages: 20

Extension: PDF | Download: 0

Share
Related documents
Description
An Oracle White Paper March 2011 PHP Scalability and High Availability Database Resident Connection Pooling and Fast Application Notification Introduction... 3 Connection Pooling with DRCP... 4 What is
Transcript
An Oracle White Paper March 2011 PHP Scalability and High Availability Database Resident Connection Pooling and Fast Application Notification Introduction... 3 Connection Pooling with DRCP... 4 What is Database Resident Connection Pooling?... 4 How DRCP Works... 4 PHP OCI8 Connections and DRCP... 7 When to use DRCP... 8 Sharing the Server Pool...10 Using DRCP in PHP...11 Configuring and Enabling the Pool...11 Configuring PHP for DRCP...14 Application Deployment for DRCP...15 Closing Connections...15 Transactions Across Re-connection...16 LOGON and LOGOFF Triggers with DRCP...17 Changing Passwords with DRCP Connections...17 Monitoring DRCP...17 DBA_CPOOL_INFO View...18 V$PROCESS and V$SESSION Views...18 V$CPOOL_STATS View...19 V$CPOOL_CC_STATS View...20 V$CPOOL_CONN_INFO View...21 DRCP Scalability Benchmark...21 High Availability with FAN and RAC...23 Configuring FAN Events in the Database...23 Configuring PHP for FAN...23 Application Deployment for FAN...24 RAC Connection Load Balancing with PHP...25 Conclusion...25 More Information...26 PHP Scalability and High Availability Page 2 INTRODUCTION PHP is a popular dynamic programming language for web applications. It comes with extensions providing a wide range of capabilities. The OCI8 extension 1 included in PHP allows applications to connect to Oracle Database. It has support for advanced Oracle Database features, allowing easy and efficient use of SQL and PL/SQL. This paper describes how PHP's OCI8 extension can use: Oracle Database 11g Database Resident Connection Pooling (DRCP) Oracle Database 10gR2 or 11g Fast Application Notification (FAN) The Oracle features are usable separately or together. PHP 5.3 OCI8 has immediate support for them. Older PHP versions can have the OCI8 extension upgraded. With DRCP, Oracle Database 11g has a connection pooling solution usable by PHP's multi-process architecture where Oracle's traditional middle-tier connection pools are not applicable. Without pooling, PHP's standard connections cause frequent creation and destruction costs that can be expensive and crippling to high scalability of the middle tier and database. Also PHP's persistent connections remove connection creation and destruction costs but do not achieve optimal connection resource utilization, incurring unnecessary memory overhead in the database. DRCP solves these issues. Database web applications can now be highly scalable. In addition, PHP OCI8 can exploit advanced Oracle RAC (Oracle's Real Application Clusters) features for high availability and scalability. Without this a database instance or machine failure could cause an application hang until a network timeout occurred and there would be no proactive cleanup of cached connections to failed instances. The FAN support in OCI8 allows PHP database applications to be resilient. Whether these features are used together, separately, or even not used at all, PHP s OCI8 is an efficient and reliable extension for building database applications. The latest extension can be built with PHP 4 and PHP 5, and will compile with Oracle 9iR2, 10g and 11g client libraries. Oracle s standard cross-version compatibility between database clients and the server is applicable. 1 PHP s OCI8 extension gets its name from Oracle Database s OCI8 C language API. PHP OCI8 gives PHP scripts a higher abstraction of database functionality than the OCI8 C API. PHP Scalability and High Availability Page 3 CONNECTION POOLING WITH DRCP What is Database Resident Connection Pooling? DRCP is a new feature of Oracle Database 11g that addresses scalability requirements in environments requiring large numbers of connections with minimal database resource usage. DRCP pools a set of dedicated database server processes (known as pooled servers), which can be shared across multiple applications running on the same or several hosts. A connection broker process manages the pooled servers at the database instance level. DRCP is a configurable feature chosen at program runtime, allowing traditional and DRCP-based connection architectures to be in concurrent use. How DRCP Works The architecture of DRCP is shown in Figure 1. A connection broker accepts incoming connection requests from PHP processes (e.g. web server processes handling PHP requests) and assigns each a free server in the pool. Each PHP process that is executing a PHP script communicates with this Oracle server until the connection is released. This happens automatically at the end of the script, or the connection can be explicitly released. When the connection is released, the server process is returned to the pool and the PHP process keeps a link only to the connection broker. Active pooled servers contain the Process Global Area (PGA) and the user session data. Idle servers in the pool retain the user session for reuse by subsequent persistent PHP connections. When the number of persistent connections is less than the number of pooled servers, a dedicated optimization avoids unnecessarily returning servers to the pool when a PHP connection is closed. Instead, the dedicated association between the PHP process and the server is kept in anticipation that the PHP process will quickly become active again. If PHP scripts are executed by numerous web servers, the DRCP pool can grow to its maximum size (albeit typically a relatively small size) even if the rate of incoming user requests is low. Each PHP process, either busy or now idle, will be attached to its own pooled server. When the pool reaches its maximum size, a script that is handled by a PHP process without a pooled server will cause an idle server to be returned to the pool for immediate reuse. PHP Scalability and High Availability Page 4 Figure 1. DRCP Architecture. The pool size and number of connection brokers are configurable. There is always at least one connection broker per database instance when DRCP is enabled. Also, at any time, around 5% of the current pooled servers are reserved for authenticating new PHP connections. Authentication is performed when a PHP process establishes a connection to the connection broker. DRCP boosts the scalability of the database and the web server tier because connections to the database are held at minimal cost. Database memory is only used by the pooled servers, and scaling can be explicitly controlled by DRCP tuning options. Without DRCP, each PHP process creates and destroys database servers when connections are opened and closed. Or alternatively, each process keeps connections open ( persistent connections ) even when they are not processing any user scripts. This consumes database resources, shown in Figure 2. PHP Scalability and High Availability Page 5 Figure 2. Without DRCP, idle persistent connections from PHP still consume database resources. With the introduction of pooled servers used by DRCP, there are now three types of database server process models that Oracle applications can use: dedicated servers, shared servers and pooled servers. Table 1. Differences between dedicated servers, shared servers, and pooled servers for oci_connect() calls. 2 Dedicated Servers Shared Servers Pooled Servers When the PHP connection is created, a network connection to a dedicated server process and associated session are created Activity on a connection is handled by the dedicated server When the PHP connection is created, a network connection to the dispatcher process is established. A session is created in the SGA Each action on a connection goes through the dispatcher, which hands the work to a shared server When the PHP connection is created, a network connection to the connection broker is established Activity on a connection wakes the broker, which hands the network connection to a pooled server process. The server then handles subsequent requests directly, just like a dedicated server 2 See Table 2 for the differences between connection types. PHP Scalability and High Availability Page 6 Dedicated Servers Shared Servers Pooled Servers Scripts executing but with idle PHP connections hold a server process and session resources Closing a PHP connection causes the session to be freed and the server process to be terminated Memory usage is proportional to the number of server processes and sessions. There is one server and one session for each PHP connection Scripts executing but with idle PHP connections hold session resources but not a server process Closing a PHP connection causes the session to be freed Memory usage is proportional to the sum of the shared servers and sessions. There is one session for each PHP connection Scripts executing but with idle PHP connections hold a server process and session resources Closing a PHP connection causes the session to be destroyed and the pooled server to be released to the pool. A network connection to the connection broker is retained Memory usage is proportional to the number of pooled server processes and their sessions. There is one session for each pooled server Pooled servers in use by PHP are similar in behavior to dedicated servers. After connection, PHP directly communicates with the pooled server for all database operations. PHP OCI8 Connections and DRCP The PHP OCI8 extension has three functions for connecting to a database: oci_connect(), oci_new_connect(), and oci_pconnect(). The implementation of these functions was reworked in OCI8 1.3 and all benefit from using DRCP. Table 2 compares dedicated and pooled servers. Shared servers are similar to dedicated servers with the exception that only the session and not the server is destroyed when a connection is closed. Table 2. Behavior of OCI8 connection functions for Dedicated and Pooled Servers. OCI8 Function Dedicated Servers Pooled Servers oci_connect() Creates a PHP connection to the database using a dedicated server. The connection is cached in the PHP process for reuse by subsequent oci_connect() calls in the same script. At the end of the script or with oci_close(), the connection is closed and the server process and session are destroyed Gets a pooled server from the DRCP pool and creates a brand new session. Subsequent oci_connect() calls in the same script use the same connection. Upon end of the script or with oci_close(), the session is destroyed and the pooled server is available for other PHP connections to use PHP Scalability and High Availability Page 7 OCI8 Function Dedicated Servers Pooled Servers oci_new_connect() Similar to oci_connect() above, but an independent new PHP connection and server process is created each time this function is called, even within the same script. All PHP connections and the database servers are closed when the script ends or with oci_close(). Sessions are destroyed at that time Similar to oci_connect() above, but an independent server in the pool is used and a new session is created each time this function is called in the same script. All sessions are destroyed at the end of the script or with oci_close(). The pooled servers are made available for other connections to use oci_pconnect() Creates a persistent PHP connection which is cached in the PHP process. The database connection is not closed at the end of the script. When no script is executing, an idle PHP process still holds the server process and session resource. The server and session are available for reuse by subsequent oci_pconnect() calls that pass the same credentials in any script handled by this PHP process Creates a persistent PHP connection. Calling oci_close() releases the connection and returns the server with its session intact to the pool for reuse by other PHP processes. If oci_close() is not called, the connection release happens at the end of the script. When no script is executing, an idle PHP process retains only an authenticated network connection to the broker. Subsequent oci_pconnect() calls passing the same credentials in scripts handled by this PHP process reuse the existing network connection to quickly get a server and session from the pool With DRCP, all three connection functions save on the cost of authentication and benefit from the network connection to the connection broker being maintained, even for connections that are closed from PHP s point of view. They also benefit from having pre-spawned server processes in the DRCP pool. The oci_pconnect() function reuses sessions, allowing even greater scalability. The non-persistent connection functions create and destroy new sessions each time they are used, allowing less sharing at the cost of reduced performance. Overall, after a brief warm-up period for the pool, DRCP allows reduced connection times in addition to the reuse benefits of pooling. When to use DRCP DRCP is typically preferred for applications with a large number of connections. Shared servers are useful for a medium number of connections and dedicated sessions are preferred for small numbers of connections. The threshold sizes are relative to the amount of memory available on the database host. PHP Scalability and High Availability Page 8 DRCP provides the following advantages: It enables resource sharing among multiple client applications and middle-tier application servers. It improves scalability of databases and applications by reducing resource usage on the database host. DRCP can be used if: PHP applications mostly use the same database credentials for all connections. The applications acquire a database connection, work on it for a relatively short duration, and then release it. Connections look identical in terms of session settings, for example date format settings and PL/SQL package state. These are all typically true for PHP applications. For persistent PHP connections, dedicated servers can be fastest. There is no broker or dispatcher overhead. The server is always connected and available whenever the PHP process needs it. But as the number of connections increases, the memory cost of keeping connections open quickly reduces efficiency of the database system. For non-persistent PHP connections, DRCP can be fastest because the use of pooled server processes removes the need for PHP connections to create and destroy processes, and removes the need to reauthenticate for each connect call. Consider an application in which the memory required for each session is 400 KB. On a 32 bit operating system the memory required for each server process could be 4 MB, and DRCP could use 35 KB per connection (mostly in the connection broker). If the number of pooled servers is configured at 100, the number of shared servers is configured at 100, and the deployed application creates 5000 PHP connections, then the memory used by each type of server is estimated in Table 3. Table 3. Example database host memory use for dedicated, shared and pooled servers. Dedicated Servers Shared Servers Pooled Servers Database Server Memory 5000 * 4 MB 100 * 4 MB 100 * 4 MB Session Memory 5000 * 400 KB 5000 * 400 KB DRCP Connection Broker Overhead Note: For Shared Servers, session memory is allocated from the SGA 100 * 400 KB 5000 * 35 KB Total Memory 21 GB 2.3 GB 610 MB There is a significant memory saving when using DRCP. PHP Scalability and High Availability Page 9 Even if sufficient memory is available to run in dedicated mode, DRCP can still be a viable option if the PHP application needs database connections for only short periods of time. In this case the memory saved by using DRCP can be used towards increasing the SGA, thereby improving overall performance. Pooling is available when connecting over TCP/IP with user ID/password based database authentication. It is not available using Oracle's bequeath connections. With Oracle Database 11g Release 2, pooled connections can take advantage of Oracle's Client Result Cache feature. Sharing the Server Pool DRCP guarantees that sessions in pooled servers initially used by one database user are only ever reusable by connections with that same user identifier. DRCP also further partitions the pool into logical groups or connection classes. A connection class is a user-chosen name set in the php.ini configuration file. Session-specific attributes, like the date format or an explicit role, may be re-usable by any connection in a particular application. Subsequent persistent connections will reuse the session and inherit those settings if the user name and connection class are the same as the previous connection. Applications that need different state in the sessions should use different user names and/or connection classes. For example, applications in a suite called RPT may be willing to share pooled servers between themselves but not with an application suite called HR. The different connection classes and resulting logical partitioning of the DRCP server pool is shown in Figure 3. Connections with the same user name and connection class from any host will share the same sub-pool of servers. Figure 3. The DRCP pool is logically partitioned by user name and connection class. PHP Scalability and High Availability Page 10 If there are no free pooled servers matching a request for a user ID in the specified connection class, and if the pool is already at its maximum size, then an idle server in the pool will be used and a new session created for it. If the server originally belonged to a different connection class, the current session will be destroyed and the server will migrate to the new class and get a newly created session. If there are no pooled servers available, the connection request waits for one to become available. This allows the database to continue without becoming overloaded. The connection class should be set to the same value for each instance of PHP running the same application where sharing of pooled connections is desired. If no connection class is specified, each web server process will have a unique, system generated class name, limiting sharing of connections to each process. If DRCP is used but session sharing is not desirable under any condition, use oci_connect() or oci_new_connect() which recreate the session each time. Although session data may be reused by subsequent persistent connections, transactions do not span connections across scripts. Uncommitted data will be rolled back at the end of a PHP script. Using DRCP in PHP Using DRCP with PHP applications involves the following steps: 1) Configuring and enabling the pool 2) Configuring PHP 3) Deploying the application PHP applications deployed as Apache modules, FastCGI, CGI and standalone applications can benefit from DRCP. PHP applications deployed as Apache modules or with FastCGI gain most, since they remain connected to the connection broker over multiple script executions and can also take advantage of other optimizations, such as statement caching. Configuring and Enabling the Pool Every instance of Oracle Database 11g uses a single, default connection pool. User defined pools are currently not supported. The default pool can be configured and administered by a DBA using the DBMS_CONNECTION_POOL package: SQL execute dbms_connection_pool.configure_pool( pool_name = 'SYS_DEFAULT_CONNECTION_POOL', minsize = 4, maxsize = 40, incrsize = 2, session_cached_cursors = 20, inactivity_timeout = 300, max_think_time = 600, max_use_session = , max_lifetime_session = 86400); PHP Scalability and High Availability Page 11 Alternatively the method dbms_connection_pool.alter_param() can be used to set a single parameter: SQL execute dbms_connection_pool.alter_param( pool_name = 'SYS_DEFAULT_CONNECTION_POOL', param_name = 'MAX_THINK_TIME', param_value = '1200'); There is a dbms_connection_pool.restore_defaults() procedure to reset all values. When DRCP is used with RAC, each database instance has its own connection broker and pool of servers. Each pool has the identical configuration. For example all pools will start with minsize server processes. A single dbms_connection_pool command will alter the pool of each instance at the same time. The po
Recommended
View more...
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks
SAVE OUR EARTH

We need your sign to support Project to invent "SMART AND CONTROLLABLE REFLECTIVE BALLOONS" to cover the Sun and Save Our Earth.

More details...

Sign Now!

We are very appreciated for your Prompt Action!

x