Solving the C20K Problem: PHP Performance and Scalability Kuassi - - PowerPoint PPT Presentation

Solving the C20K Problem: PHP Performance and Scalability

Kuassi Mensah, Group Product Manager Oracle Corporation

The C20K Problem

Genesis: the C10K Problem

• Circa 2003: “How to write a server which can handle

10.000 parallel connections”

• http://www.kegel.com/c10k.html

The C20K Problem

• Can a single commodity database server handle 20K

simultaneous PHP users?

…

Solving C20K with Built-In Database Mechanisms in PHP

• Database Resident Connection Pool
• Query Change Notification
• Client-side Query Result Cache
• Scaling with Stored Procedures
• Database Built-in Partitioning
• Scaling Very Complex Queries
• Scaling Queries with Advanced Data Compression
• Database Failover
• Case Study: Community Connect

<Insert Picture Here>

Database Resident Connection Pool

PHP Lacks Proper Connection Pool

Current choices:

• 2. Persistent Connections
• Connection not automatically closed at

script completion

• Fast for subsequent connections but

holds resources when application idle ⇒ Over-allocation – Waste of system resources

• Non Persistent Connections
• Connection closed at script completion

⇒ High connect times ⇒ Unnecessary connect/disconnect CPU load

Database Resident Connection Pool

Connection ( DB handle)

Oracle Net

Dedicated servers Session (User Handle) Session (User Handle) Session (User Handle) Session (User Handle) Session (User Handle)

Connection Broker

• Pool of dedicated servers
• (1) Server allocated/locked on Appl. Connect
• (2) Direct server access after handoff
• (3) Server released on Appl. ConnClose .
• No man-in-the-middle, low latency

1 2

• Pools a set of dedicated servers on each database

instance

• Designed primarily for process systems (PHP)

1/ Just Change the Connect String

2/ Zero code change: change TNS alias

• Currently in OCI, C/C++, PHP (OCI8), Python

Database Resident Connection Pool

<?php $c = oci_pconnect("phpweb", "phpweb", "//localhost/orcl:pooled"); $s = oci_parse($c, 'select * from employees');

• ci_execute($s);
• ci_fetch_all($s, $res);

var_dump($res); ?>

Idle Server

DRCP in Action – PHP Connection

Connection Broker Pooled Database Servers

PGA memory

. . .

1

• c

i _ p c

• n

n e c t ( )

• ci_pconnect()

35k 35k

PHP PHP

Idle Server Session memory Busy Server

2

Apache Processes

3

Connection Broker

PHP

Pooled Database Servers

PHP

Busy Server PGA memory

4

Session memory

• ci_close()
• ci_close()

. . .

DRCP in Action – Closing Connection

Idle Server

Apache Processes

35k 35k

Pooled Database Servers

Idle Server PGA memory

. . .

DRCP in Action – After Close

Session memory Idle Server

5

Apache Processes

PHP PHP

Connection Broker socket connection socket connection

35k 35k

Configuring and Starting DRCP

• Configure the Pool (Optional)

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 => 500000, max_lifetime_session => 86400);

• Start the pool

SQL> execute dbms_connection_pool.start_pool();

C20K, Yes We Did! PHP DRCP Benchmark

• PHP script
• connect, query, disconnect, sleep 1 second
• Database Server
• Dual CPU Intel P4/Xeon -- 3.00GHz - 2GB RAM
• 32bit Red Hat Enterprise Linux 4
• DRCP
• 100 pooled servers, one connection broker
• 0.5% nb users simultaneously active
• PHP Hosts
• 3 machines similar to Db Server
• Apache
• PHP DRCP Whitepaper: http://tinyurl.com/554cz4

C20K, Yes We Did! PHP DRCP Benchmark - Throughput

C20K, Yes We Did! PHP DRCP Benchmark - Memory

<Insert Picture Here>

Query Change Notification

Built-in Query Change Notification

<?php … 4.Invalidate cache 5.repopulate cache … ?> Custom cache

3.Automatic Notification (Java or PL/SQL database job

as noificaion handler)

• 1. Register

the query

Problem to solve: Be notified when changes in the database invalidates an existing query result set

2.Upon Change (DMLImpacting the result set) Callout

<Insert Picture Here>

Client-Side Query Result Cache

query = "select name, address, phone, acctbal from custumer, nation where c_nationkey= n_nationkey; key = md5($query); If (serval=$memcache->get($key) { res = oci_execute($query) ser = serialize($res); memcache->set($key, $ser); } res = unserialize($serval);

PHP Database MemCached

MemCached

Problems

• Cache Invalidation
• Additional

Memcached Servers

PHP Database

Built-in Client Query Result Cache

$query = "select /*+ RESULT_CACHE */ name, address, phone, acctbal from customer, nation where c_nationkey=n_nationkey;

• May be Faster than Memcached !
• No additional cache server(s) but process level cache
• Automatic Cache Invalidation
• Transparent OCI, PHP, Ruby, ODP.Net, ODBC, JDBC

Configuring Client Query Result Cache

1/ Configure the Cache

Database Server Configuration (init.ora)

client_result_cache_size=200M client_result_cache_lag=5000

Client Configuration (sqlnet.ora)

OCI_QUERY_CACHE_SIZE=200M OCI_QUERY_CACHE_MAXROWS=20

2/ Caching the Result Set

Using Hints select /*+ result_cache */ * from employees

Alternatively, at Table level

alter table emp result_cache (mode force);

Client Query Result Cache

0% 100% 200% 300% 400% 500% 600% 700% 800% 900% 10 100 1000

5-8 x Faster

<Insert Picture Here>

Scaling with Stored Procedures

Java

JDBC Calls

SQL

Stored Procedures instead of Multiple Unique Statements

PHP

Calls Unique Statements

SQL

Stored Procedures (PL/SQL or Java) Faster, up to 10X!

PHP

Stored Procedure Call

PHP Bulk Insert

function do_transactional_insert($conn, $array) { $s = oci_parse($conn, 'insert into ptab (pdata) values (:bv)');

• ci_bind_by_name($s, ':bv', $v, 20, SQLT_CHR);

foreach ($array as $v) $r = oci_execute($s, OCI_DEFAULT);

• ci_commit($con);

}

Elapsed time: 8 millisec

PHP Bulk Insert with Stored Procedure

function do_bulk_insert($conn, $array) { $s = oci_parse($conn, 'begin mypkg.myproc(:c1); end;');

• ci_bind_array_by_name($s, ":c1", $array,

count($array), -1, SQLT_CHR);

• ci_execute($s);

}

Elapsed time: 2 millisec (4X speed up; may vary!)

PL/SQL Stored Proc. (Bulk insert)

create or replace package mypkg as type arrtype is table of varchar2(20) index by pls_integer; procedure myproc(p1 in arrtype); end mypkg; create or replace package body mypkg as procedure myproc(p1 in arrtype) is begin forall i in indices of p1 insert into ptab values (p1(i)); end myproc; end mypkg;

Using Java Stored Procedures in PHP

• Secure Credit-Card Processing

using JSSE

• Custom Alert applications that

monitor business data

• Sending emails with attachment

from within the database

• Produce PDF files from Result

Set

• Execute external OS commands

and external procedures

• Implement Md5 CRC
• Publish Repository Content to

Portal

• Portable Logistic Applications
• Implement Parsers for various File

Formats (txt, zip, xml, binary)

• Implement Image Transformation and

Format Conversion (GIF, PNG, JPEG, etc)

• Implement database-resident Content

Management System

• HTTP Call-Out
• JDBC Call-Out
• RMI Call-Out to SAP
• Web Services Call-Out
• Messaging across Tiers
• RESTful Database Web Services*
• Db Resident Lucene*

* http://marceloochoa.blogspot.com/

<Insert Picture Here>

Database Built-in Partitioning

Built-in Partitioning

Orders Inventory Back Orders Line Items Pick Lists Orders Orders Orders Jan Feb Mar

<Insert Picture Here>

Scaling Very Complex SQL Queries

Problem to Solve: Query Sales and Quantity by Year, Department, Class and Country The SQL Query

SELECT SUM(s.quantity) AS quantity, SUM(s.sales) AS sales, t.calendar_year_name, p.department_name, c.class_name, cu.country_name FROM times t, products p, channels c, customers cu, sales_fact s WHERE p.item_key = s.product AND s.day_key = t.day_key AND s.channel = c.channel_key AND s.customer = cu.customer_key GROUP BY p.department_name, t.calendar_year_name, c.class_name, cu.country_name;

Scaling Very Complex SQL Queries

Built-in OLAP Engine

PHP

Cube Organized Materialized Views Transparent to SQL Queries

Materialized Views

Region Date Product Channel SQL Query Query Rewrite Automatic Refresh OLAP Cube

<Insert Picture Here>

Scaling with Advanced Data Compression

Scaling w. Advanced Data Compression Going Green

Storage Reduction

MB More than 70% (up to 4X) Storage Savings

Table Scan Performance

Time (seconds)

DML Performance

Time (seconds) 2.5 x Faster Less than 3% Overhead

No Compression C

• m

p r e s s i

• n

Database Failover

Oracle Database 11g RAC Shared Disk Architecture

• Pub/Sub Event Notification (AQ based)
• High Availability feature for PHP with RAC or Data-

Guard-with-physical-standby

• When DB node or network fails
• Database generates FAN events
• PHP error returned without TCP timeout delay
• PHP application reconnect to surviving instance
• OCI8 1.3.1 Beta supports FAN

Fast Application Notification of Events

Database Failover – Application View

Invalid Connections Valid Connections

Fast Connection Failover

Inst x Inst y

Configuring Fast Application Notification for PHP

• High Availability feature with RAC or DG
• Usable with or without DRCP
• Available from Oracle 10gR2
• OCI8 1.3 supports FAN

5.Tell DB to broadcast FAN Events

SQL> execute dbms_service.modify_service(service_name =>'SALES', aq_ha_notifications =>TRUE);

6.Subscribe to FAN events

Example: Configure PHP for OCI8 to listen for FAN events

• ci8.events = On

7.Application to Re-connect

Database Failover in PHP

When DB node or network fails

• Database generates FAN events
• Oracle error returned without TCP timeout delay
• PHP application is not blocked for TCP timeout – it can

immediately reconnect to surviving DB instance

$conn = doConnect(); $err = doSomeWork($conn); if (isConnectionError($err)) { // reconnect, find what was committed, and retry $conn = doConnect(); $err = checkApplicationStateAndContinueWork($conn); } if ($err) handleError($err);

C(N*20)K Using DRCP and RAC

• Scale your database horizontally to N*C20K with N

RAC nodes!!

• DRCP starts on all RAC instances
• Same pool limits apply to each individual RAC

instance

• min, max
• number of brokers
• max connections per broker
• DRCP connections benefit from TNS Listener

connection load balancing across RAC instances

www.oracle.com/technology/tech/php/pdf/php-scalability-ha-twp.pdf

<Insert Picture Here>

Case Study Community Connect Inc.

Xtreme Scalability Web Scale PHP Deployment

BlackPlanet.com

• 19+ million users
• 500+ millions pages views

per month

• 100+ web servers
• 10+ databases (incl. RAC)
• ~ 50,000 conc. Users
• Case Studied by Gartner

http://tinyurl.com/dg6rxm

Oracle Technology Network PHP Developer Center

• Free
• Articles
• Install guides
• Underground PHP

and Oracle Manual

• Online forum
• PHP RPMs
• Oracle JDeveloper

10g PHP extension

• tn.oracle.com/php

Oracle Resources

• Free Oracle Techology Network (OTN)

PHP Developer Center

• tn.oracle.com/php
• Underground PHP and Oracle Manual
• Whitepapers, Articles, FAQs, links to blogs, JDeveloper

PHP Extension, PHP RPMs

• Information

kuassi.mensah@oracle.com christopher.jones@oracle.com

• SQL and PL/SQL Questions

asktom.oracle.com

• ISVs and hardware vendors
• raclepartnernetwork.oracle.com

49

Q & A

Thank You