solving the c20k problem: php performance and scalability
DESCRIPTION
TRANSCRIPT
![Page 1: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/1.jpg)
Solving the C20K Problem: PHP Performance and ScalabilityKuassi Mensah, Group Product Manager Oracle Corporation
![Page 2: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/2.jpg)
The C20K Problem
![Page 3: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/3.jpg)
Genesis: the C10K Problem
• Circa 2003: “How to write a server which can handle 10.000 parallel connections”
• http://www.kegel.com/c10k.html
![Page 4: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/4.jpg)
The C20K Problem
• Can a single commodity database server handle 20K simultaneous PHP users?
…
![Page 5: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/5.jpg)
Solving C20K with BuiltIn Database Mechanisms in PHP
• Database Resident Connection Pool• Query Change Notification• Clientside Query Result Cache• Scaling with Stored Procedures• Database Builtin Partitioning• Scaling Very Complex Queries• Scaling Queries with Advanced Data Compression• Database Failover• Case Study: Community Connect
![Page 6: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/6.jpg)
<Insert Picture Here>
Database Resident Connection Pool
![Page 7: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/7.jpg)
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 ⇒ Overallocation – Waste of system resources
• Non Persistent Connections• Connection closed at script completion⇒ High connect times ⇒ Unnecessary connect/disconnect CPU load
![Page 8: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/8.jpg)
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 maninthemiddle, low latency
1
2
![Page 9: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/9.jpg)
• 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');oci_execute($s);oci_fetch_all($s, $res);var_dump($res);?>
![Page 10: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/10.jpg)
Idle Server
DRCP in Action – PHP Connection
ConnectionBroker
Pooled Database Servers
PGA memory
.
.
.
1
oci_pconnect()
oci_pconnect()35k
35k
PHP
PHP
Idle Server
Session memory
Busy Server
2Apache Processes
3
![Page 11: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/11.jpg)
ConnectionBroker
PHP
Pooled Database Servers
PHP
Busy Server
PGA memory
4
Session memory
oci_close()
oci_close()
.
.
.
DRCP in Action – Closing Connection
Idle Server
Apache Processes
35k
35k
![Page 12: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/12.jpg)
Pooled Database Servers
Idle Server
PGA memory
.
.
.
DRCP in Action – After Close
Session memory
Idle Server
5Apache Processes
PHP
PHP
ConnectionBrokersocket
connection
socket connection
35k
35k
![Page 13: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/13.jpg)
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 poolSQL> execute dbms_connection_pool.start_pool();
![Page 14: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/14.jpg)
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
![Page 15: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/15.jpg)
C20K, Yes We Did!PHP DRCP Benchmark Throughput
![Page 16: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/16.jpg)
C20K, Yes We Did!PHP DRCP Benchmark Memory
![Page 17: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/17.jpg)
<Insert Picture Here>
Query Change Notification
![Page 18: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/18.jpg)
Builtin Query Change Notification
<?php…4.Invalidate cache5.repopulate cache…?>
Custom cache3.Automatic Notification (Java or PL/SQL database job as noificaion handler)
1. Registerthe 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
![Page 19: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/19.jpg)
<Insert Picture Here>
ClientSide Query Result Cache
![Page 20: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/20.jpg)
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
![Page 21: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/21.jpg)
PHP
Database
Builtin 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
![Page 22: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/22.jpg)
Configuring Client Query Result Cache
1/ Configure the Cache Database Server Configuration (init.ora)
client_result_cache_size=200Mclient_result_cache_lag=5000
Client Configuration (sqlnet.ora)
OCI_QUERY_CACHE_SIZE=200MOCI_QUERY_CACHE_MAXROWS=20
2/ Caching the Result SetUsing Hintsselect /*+ result_cache */ * from employees
Alternatively, at Table level alter table emp result_cache (mode force);
![Page 23: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/23.jpg)
Client Query Result Cache
0%
100%
200%
300%
400%
500%
600%
700%
800%
900%
10 100 1000
58 x Faster
![Page 24: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/24.jpg)
<Insert Picture Here>
Scaling with Stored Procedures
![Page 25: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/25.jpg)
JavaJDBCCalls
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 ProcedureCall
![Page 26: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/26.jpg)
PHP Bulk Insert
function do_transactional_insert($conn, $array)
{
$s = oci_parse($conn,
'insert into ptab (pdata) values (:bv)');
oci_bind_by_name($s, ':bv', $v, 20, SQLT_CHR);
foreach ($array as $v)
$r = oci_execute($s, OCI_DEFAULT);
oci_commit($con);
}
Elapsed time: 8 millisec
![Page 27: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/27.jpg)
PHP Bulk Insert with Stored Procedure
function do_bulk_insert($conn, $array)
{
$s = oci_parse($conn,
'begin mypkg.myproc(:c1); end;');
oci_bind_array_by_name($s, ":c1", $array,
count($array), -1, SQLT_CHR);
oci_execute($s);
}
Elapsed time: 2 millisec (4X speed up; may vary!)
![Page 28: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/28.jpg)
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;
![Page 29: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/29.jpg)
Using Java Stored Procedures in PHP
• Secure CreditCard 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 databaseresident Content Management System
• HTTP CallOut• JDBC CallOut• RMI CallOut to SAP• Web Services CallOut• Messaging across Tiers• RESTful Database Web Services*• Db Resident Lucene*
* http://marceloochoa.blogspot.com/
![Page 30: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/30.jpg)
<Insert Picture Here>
Database Builtin Partitioning
![Page 31: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/31.jpg)
Builtin Partitioning
OrdersInventory
Back Orders
LineItems
PickLists
Orders
Orders
Orders
Jan
Feb
Mar
![Page 32: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/32.jpg)
<Insert Picture Here>
Scaling Very Complex SQL Queries
![Page 33: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/33.jpg)
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
![Page 34: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/34.jpg)
Builtin OLAP Engine
PHP
![Page 35: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/35.jpg)
Cube Organized Materialized ViewsTransparent to SQL Queries
Materialized Views
Region Date
Product Channel
SQL Query
QueryRewrite
AutomaticRefresh
OLAP Cube
![Page 36: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/36.jpg)
<Insert Picture Here>
Scaling with Advanced Data Compression
![Page 37: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/37.jpg)
Scaling w. Advanced Data Compression Going Green
0
500
1000
1500
2000
2500Storage
Reduction
MB
More than 70% (up to 4X) Storage Savings
Table Scan Performance
Tim
e (s
econ
ds)
0
0.1
0.2
0.3
0.4
DML Performance
0
10
20
30
40
Tim
e (s
econ
ds)2.5 x Faster
Less than 3% Overhead
No Compression
Compression
![Page 38: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/38.jpg)
Database Failover
![Page 39: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/39.jpg)
Oracle Database 11g RAC Shared Disk Architecture
![Page 40: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/40.jpg)
• Pub/Sub Event Notification (AQ based)
• High Availability feature for PHP with RAC or Data Guardwithphysicalstandby
• 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
![Page 41: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/41.jpg)
Database Failover – Application View
Invalid Connections Valid Connections
Fast Connection Failover
Inst x Inst y
![Page 42: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/42.jpg)
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 eventsoci8.events = On
7.Application to Reconnect
![Page 43: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/43.jpg)
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);
![Page 44: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/44.jpg)
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 instanceswww.oracle.com/technology/tech/php/pdf/phpscalabilityhatwp.pdf
![Page 45: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/45.jpg)
<Insert Picture Here>
Case Study
Community Connect Inc.
![Page 46: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/46.jpg)
Xtreme ScalabilityWeb 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
![Page 47: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/47.jpg)
Oracle Technology Network PHP Developer Center
• Free• Articles• Install guides• Underground PHP
and Oracle Manual • Online forum• PHP RPMs• Oracle JDeveloper
10g PHP extension
otn.oracle.com/php
![Page 48: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/48.jpg)
Oracle Resources
• Free Oracle Techology Network (OTN)PHP Developer Center
otn.oracle.com/php • Underground PHP and Oracle Manual• Whitepapers, Articles, FAQs, links to blogs, JDeveloper
PHP Extension, PHP RPMs• Information
[email protected]@oracle.com
• SQL and PL/SQL Questionsasktom.oracle.com
• ISVs and hardware vendors oraclepartnernetwork.oracle.com
![Page 49: Solving the C20K Problem: PHP Performance and Scalability](https://reader030.vdocuments.us/reader030/viewer/2022020115/548532bbb47959140d8b4e23/html5/thumbnails/49.jpg)
49
Q & AThank You