1 Load-Balanced Enterprise Web Applications With PHPPresenter: Jerome Gagner

2 Jerome Gagner , Name IntelligenceWhere I come from What I do Project Background

3 The Problem LAMP environment traditionally confined to one or two servers One server may have to handle all the load Large datasets hamper smaller queries Simply doesn’t scale

4 Too much load! Sessions Account Data Web Pages MySQL Work LoadBatch Processing Reports Nightly s I’m overworked!

5 The Glass Ceiling Queries backing up due to batch processingReports taking forever Running out of MySQL connections Running out of memory Apache calling it quits

6 The Solution Distribute work over multiple serversDefine classes of servers Isolate responsibilities to respective servers Queue and manage taxing processes Provide redundancy in systems Ensure web pages/sessions are handled in an appropriate timeframe All databases (with the exception of the web servers) have the same data

7 Architecture

8 Challenge: Security and RecoveryDatabase Servers on Internal Network and Firewalled Servers are interchangeable Master database server is authoritative!

9 Challenge: Code Management“Read-Only” mode Single code base. Two branches. Code needs to be the same on all servers Code deployment “Smart Code”

10 Smart Code Different classes of queries and serversDatabase class figures out which machines are which.

11 Example Excerpts from sample database class Logic behind smart codeClassifying connections, boxes and query types

12 Example: Classificationsdefine('PEER_SELECT', 0); define('MASTER_SELECT', 1); define('LOCAL_SELECT', 2); define('SLAVE_SELECT', 3); //insert types define('LOCAL_INSERT', 0); define('MASTER_INSERT', 1); define('PEER_INSERT', 2); define('GLOBAL_INSERT', 3); define(‘STANDARD_INSERT’,4); //priorities define('DEFAULT_PRIORITY', 1); define('HIGH_PRIORITY',9); //database server types define('LOCAL',0); //local database servers define('PEER',1); //peer webservers define('MASTER',2); //master database server define('SLAVE',3); //slave database server

13 Example: Storing connections and queriesprivate $destructed=false;     private $connLocal = array();     private $connPeers = array();     private $connMaster = array();     private $connSlave = array();     private $connectedToPeers = false;     private $connectedToMaster = false;     protected $masterBuffer = array();     protected $peerBuffer = array();     protected $allBuffer = array();

14

15 Challenge: Getting ConnectedNow have to maintain and keep track of many connections and types In general, connect to local database only first, then connect programmatically to additional servers as needed Use mysqli_init() and mysqli_real_connect() Set timeouts to a reasonable level Possible routine for completely removing downed servers Store connection objects in connPeers, etc. arrays

16

17 Challenge: Lets Get Some DataDecide with application logic which connections to use for which purposes and how to use them Main datastores on slaves and master Only data commonly accessed (such as sessions and cached information) need to be local Identify where the data needs to come from. Have a fallback system

18 Select Method Specify query Optional select type Optional server aliaspublic function select($query,$serverType = LOCAL_SELECT) Specify query Optional select type Optional server alias

19 Using Select LOCAL_SELECT: Session and cached informationSLAVE_SELECT: Normal Data Retrieval MASTER_SELECT: Unique Ids, final failsafe Depending on design, you can fall back to various servers in emergency situations.

20

21 Inserting Data Local inserts not replicatedGlobal inserts go to everyone Standard inserts go to slaves and master Buffer all inserts except local Connect and insert to other databases once all work is done and actor is presented with the result If appropriate, add ‘DELAYED’ to insert statement

22 Problems with Inserts Autoincrement fields may get out of syncLet master server be resource for unique ids

23 Unique Ids Let master assign unique ids.Include a method just for that

24 Insert Prototype Required query Defaults to LOCAL_INSERTpublic function insert($query,$insertType = LOCAL_INSERT,$priority = DEFAULT_PRIORITY, $alias=null) Required query Defaults to LOCAL_INSERT

25

26 Query buffering Make sure you are connected to peers, slave, and masters Most of the time you want to flush the output buffer before emptying query buffer

27 Database Synchronization“Read-Only” mode is important Master server is the authoritative copy

28 Challenge: Reporting and Batch JobsExecuted on report servers Method of retrieval Method of identifying which server does the job, you don’t want two servers working on the same thing

29 Example: Report Queue Table

30 Wrap it up Summary Questions