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 Dedicated servers Connection Connection ( DB handle) Broker Session Session Session (User Handle) Session 1 (User Handle) Session (User Handle) (User Handle) (User Handle) Oracle Net 2 • 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

Database Resident Connection Pool • Pools a set of dedicated servers on each database instance • Designed primarily for process systems (PHP) 1/ Just Change the Connect String <?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); ?> 2/ Zero code change: change TNS alias • Currently in OCI, C/C++, PHP (OCI8), Python

DRCP in Action – PHP Connection Pooled Database Servers 3 Busy Server Idle Server PHP PGA memory Session memory o c i _ p c o n n e c t ( ) 35k Idle Server 1 2 Apache Processes . . 35k . oci_pconnect() Connection PHP Broker

DRCP in Action – Closing Connection Pooled Database Servers Busy Server oci_close() PHP PGA memory Connection Session memory Broker Idle Server 35k 4 Apache Processes . . PHP 35k . oci_close()

DRCP in Action – After Close Pooled Database Servers Idle Server PHP PGA memory Connection Session memory socket Broker connection Idle Server 35k 5 Apache Processes . . PHP 35k . socket connection

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 Problem to solve: Be notified when changes in the database invalidates an existing query result set 2.Upon Change (DML Impacting <?php the result set) … Callout 4.Invalidate cache 5.repopulate cache … ?> 1. Register the query 3.Automatic Custom cache Notification ( Java or PL/SQL database job as noificaion handler)

<Insert Picture Here> Client-Side Query Result Cache

MemCached 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); Problems memcache->set($key, $ser); • Cache Invalidation } • Additional res = unserialize($serval); Memcached Servers MemCached PHP Database

Built-in Client Query Result Cache $query = "select /*+ RESULT_CACHE */ name, address, phone, acctbal from customer, nation where c_nationkey=n_nationkey; PHP Database • 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 5-8 x Faster 900% 800% 700% 600% 500% 400% 300% 200% 100% 0% 10 100 1000

<Insert Picture Here> Scaling with Stored Procedures

Stored Procedures instead of Multiple Unique Statements Stored Procedures (PL/SQL or Java) Unique Statements PHP PHP Stored Procedure Call Calls Java JDBC Calls SQL SQL Faster, up to 10X!

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

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!)

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 • Implement Parsers for various File using JSSE Formats (txt, zip, xml, binary) • Custom Alert applications that • Implement Image Transformation and monitor business data Format Conversion (GIF, PNG, • Sending emails with attachment JPEG, etc) from within the database • Implement database-resident Content • Produce PDF files from Result Management System Set • HTTP Call-Out • Execute external OS commands • JDBC Call-Out and external procedures • Implement Md5 CRC • RMI Call-Out to SAP • Publish Repository Content to • Web Services Call-Out Portal • Messaging across Tiers • Portable Logistic Applications • RESTful Database Web Services* • Db Resident Lucene* * http://marceloochoa.blogspot.com/

<Insert Picture Here> Database Built-in Partitioning

Built-in Partitioning Orders Line Inventory Items Orders Jan Orders Feb Back Pick Orders Orders Lists Mar

<Insert Picture Here> Scaling Very Complex SQL Queries

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;

Built-in OLAP Engine PHP

Cube Organized Materialized Views Transparent to SQL Queries Materialized Views SQL Query Region Date Query Rewrite Product Channel Automatic OLAP Cube Refresh

<Insert Picture Here> Scaling with Advanced Data Compression

Scaling w. Advanced Data Compression Going Green Storage 2500 Reduction More than 70% (up to 4X) 2000 C o m p r e s s i o n Storage Savings 1500 MB No Compression 1000 500 0 Table Scan Performance DML Performance Less than 3% Overhead 0.4 40 2.5 x Faster (seconds) 0.3 (seconds) 30 Time Time 0.2 20 0.1 10 0 0

Database Failover

Oracle Database 11 g RAC Shared Disk Architecture