1. Beyond PHP : It's not (just) about the codeWim Godden Cu.be Solutions

2. Who am I ? Wim Godden (@wimgtr) Founder of Cu.be Solutions (http://cu.be) Open Source developer since 1997 Developer of OpenX Zend Certified Engineer Zend Framework Certified Engineer MySQL Certified Developer Speaker at PHP and Open Source conferences 3. Cu.be Solutions ? Open source consultancy PHP-centered High-speed redundant network (BGP, OSPF, VRRP) High scalability development Nginx + extensions MySQL ClusterProjects :mostly IT & Telecom companies lots of public-facing apps/sites 4. Who are you ? Developers ? Anyone setup a MySQL master-slave ? Anyone setup a site/app on separate web and database server ? How much traffic between them ? 5. The topic Things we take for granted Famous last words : "It should work just fine" Works fine today might fail tomorrow Most common mistakes PHP code PHP ecosystem How-to & How-NOT-to 6. It starts with... code !First up : database 7. Database queries complexity SELECT DISTINCT n.nid, n.uid, n.title, n.type, e.event_start, e.event_start AS event_start_orig, e.event_end, e.event_end AS event_end_orig, e.timezone, e.has_time, e.has_end_date, tz.offset AS offset, tz.offset_dst AS offset_dst, tz.dst_region, tz.is_dst, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND AS event_start_utc, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND AS event_end_utc, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_start_user, e.event_end - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_end_user, e.event_start - INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_start_site, e.event_end INTERVAL IF(tz.is_dst, tz.offset_dst, tz.offset) HOUR_SECOND + INTERVAL 0 SECOND AS event_end_site, tz.name as timezone_name FROM node n INNER JOIN event e ON n.nid = e.nid INNER JOIN event_timezones tz ON tz.timezone = e.timezone INNER JOIN node_access na ON na.nid = n.nid LEFT JOIN domain_access da ON n.nid = da.nid LEFT JOIN node i18n ON n.tnid > 0 AND n.tnid = i18n.tnid AND i18n.language = 'en' WHERE (na.grant_view >= 1 AND ((na.gid = 0 AND na.realm = 'all'))) AND ((da.realm = "domain_id" AND da.gid = 4) OR (da.realm = "domain_site" AND da.gid = 0)) AND (n.language ='en' OR n.language ='' OR n.language IS NULL OR n.language = 'is' AND i18n.nid IS NULL) AND ( n.status = 1 AND ((e.event_start >= '2010-01-31 00:00:00' AND e.event_start = '2010-01-31 00:00:00' AND e.event_end = '2010-02-01 00:00:00' AND event_start = '2010-02-01 00:00:00' AND event_end filterByState('SC') ->find(); foreach ($customers as $customer) { $contacts = ContactsQuery::create() ->filterByCustomerid($customer->getId()) ->find(); foreach ($contacts as $contact) { doSomestuffWith($contact); } } 18. Joins$contacts = mysql_query(" select contacts.* from customer join contact on contact.customerid = customer.id where state = 'SC' "); while ($contact = mysql_fetch_array($contacts)) { doSomeStuffWith($contact); }or the ORM equivalent 19. Better... 10001 1 query Sadly : people still produce code with query loops Usually :Growth not anticipated Internal app Public app 20. The origins of this talk Customers : Projects we built Projects we didn't build, but got pulled into Fixes Changes Infrastructure migration15 years of 'how to cause mayhem with a few lines of code' 21. Client X Jobs search site Monitor job views : Daily hitsWeekly hits Monthly hits Which user saw which job 22. Client X Originally : when user viewed job details Now : when job is in search result Search for 'php' 50 jobs = 50 jobs to be updated 50 updates for shown_today 50 updates for shown_week 50 updates for shown_month 50 inserts for shown_user 23. Client X : the code foreach ($jobs as $job) { $db->query(" insert into shown_today( jobId, number ) values( " . $job['id'] . ", 1 ) on duplicate key update number = number + 1 "); $db->query(" insert into shown_week( jobId, number ) values( " . $job['id'] . ", 1 ) on duplicate key update number = number + 1 ");$db->query(" insert into shown_month( jobId, number ) values( " . $job['id'] . ", 1 ) on duplicate key update number = number + 1 "); $db->query(" insert into shown_user( jobId, userId, when ) values ( " . $job['id'] . ", " . $user['id'] . ", now() ) "); } 24. Client X : the graph 25. Client X : the numbers 600-1000 updates/sec (peaks up to 1600) 400-1000 updates/sec (peaks up to 2600) 16 core machine 26. Client X : panic ! Mail : "MySQL slave is more than 5 minutes behind master" We set it up who did they blame ? Wait a second ! 27. Client X : what's causing those peaks ? 28. Client X : possible cause ? Code changes ? According to developers : noneAction : turn on general log, analyze with pt-query-digest 50+-fold increase in queries Developers : 'Oops we did make a change'After 3 days : 2,5 days behind Every hour : 50 min extra lag 29. Client X : But why is the slave lagging ?File : master-bin-xxxx.log um g d ad n lo e Bi thrMasterpSlave I/O threadFile : master-bin-xxxx.log Sl av th e S re Q ad LSlave 30. Client X : Master 31. Client X : Slave 32. Client X : fix ? foreach ($jobs as $job) { $db->query(" insert into shown_today( jobId, number ) values( " . $job['id'] . ", 1 ) on duplicate key update number = number + 1 "); $db->query(" insert into shown_week( jobId, number ) values( " . $job['id'] . ", 1 ) on duplicate key update number = number + 1 ");$db->query(" insert into shown_month( jobId, number ) values( " . $job['id'] . ", 1 ) on duplicate key update number = number + 1 "); $db->query(" insert into shown_user( jobId, userId, when ) values ( " . $job['id'] . ", " . $user['id'] . ", now() ) "); } 33. Client X : the code change $todayQuery = " insert into shown_today( jobId, number ) values "; foreach ($jobs as $job) { $todayQuery .= "(" . $job['id'] . ", 1),"; } $todayQuery = substr($todayQuery, -1); $todayQuery .= " ) on duplicate key update number = number + 1 "; $db->query($todayQuery);Result : insert into shown_today values (5, 1), (8, 1), (12, 1), (18, 1), ... Careful : max_allowed_packet ! 34. Client X : the chosen solution $db->autocommit(false); foreach ($jobs as $job) { $db->query(" insert into shown_today( jobId, number ) values( " . $job['id'] . ", 1 ) on duplicate key update number = number + 1 "); $db->query(" insert into shown_week( jobId, number ) values( " . $job['id'] . ", 1 ) on duplicate key update number = number + 1 ");$db->query(" insert into shown_month( jobId, number ) values( " . $job['id'] . ", 1 ) on duplicate key update number = number + 1 "); $db->query(" insert into shown_user( jobId, userId, when ) values ( " . $job['id'] . ", " . $user['id'] . ", now() ) "); } $db->commit(); 35. Client X : conclusion For loops are bad (we already knew that) Add master/slave and it gets much worse Use transactions : it will provide huge performance increase Result : slave caught up 5 days later 36. Database Network Customer Y Top 10 site in Belgium Growing rapidly At peak traffic :Unexplicable latency on database Load on webservers : minimal Load on database servers : acceptable 37. Client Y : the network 38. Client Y : the network60GB700GB700GB 39. Client Y : network overload Cause : Drupal hooks retrieving data that was not needed Only load data you actually need Don't know at the start ? Use lazy loading Caching : Same story Memcached/Redis are fast But : data still needs to cross the network 40. Network trouble : more than just traffic Customer Z 150.000 visits/dayNews ticker : XML feed from other site (owned by same customer) Cached for 15 min 41. Customer Z fetching the feedif (filectime(APP_DIR . '/tmp/ScrambledSiteName.xml') < time() - 900) { unlink(APP_DIR . '/tmp/ScrambledSiteName.xml'); file_put_contents( APP_DIR . '/tmp/ScrambledSiteName.xml', file_get_contents('http://www.scrambledsitename.be/xml/feed.xml') ); } $xmlfeed = ParseXmlFeed(APP_DIR . '/tmp/ScrambledSiteName.xml');What's wrong with this code ? 42. Customer Z no feed without the sourceFeed source 43. Customer Z no feed without the sourceFeed source 44. Customer Z : timeout default_socket_timeout : 60 sec by default Each visitor : 60 sec wait time People keep hitting refresh more load More active connections more load Apache hits maximum connections entire site down 45. Customer Z : timeout fix$context = stream_context_create( array( 'http' => array( 'timeout' => 5 ) ) ); if (filectime(APP_DIR . '/tmp/ScrambledSiteName.xml') < time() - 900) { unlink(APP_DIR . '/tmp/ScrambledSiteName.xml'); file_put_contents( APP_DIR . '/tmp/ScrambledSiteName.xml', file_get_contents('http://www.scrambledsitename.be/xml/feed.xml', false, $context) ); } $xmlfeed = ParseXmlFeed(APP_DIR . '/tmp/ScrambledSiteName.xml'); 46. Customer Z : don't delete from cache$context = stream_context_create( array( 'http' => array( 'timeout' => 5 ) ) ); if (filectime(APP_DIR . '/tmp/ScrambledSiteName.xml') < time() - 900) { unlink(APP_DIR . '/tmp/ScrambledSiteName.xml'); file_put_contents( APP_DIR . '/tmp/ScrambledSiteName.xml', file_get_contents('http://www.scrambledsitename.be/xml/feed.xml', false, $context) ); } $xmlfeed = ParseXmlFeed(APP_DIR . '/tmp/ScrambledSiteName.xml'); 47. Network resources Use timeouts for all : fopen curl SOAP Data source trusted ? setup a webservice let them push updates when their feed changes less load on data source no timeout issuesAdd logging early detection 48. Logging Logging = good Logging in PHP using fopen bad idea : locking issues Use file_put_contents($filename, $data, FILE_APPEND) For Firefox : FirePHP (add-on for Firebug) Debug logging = bad on production Watch your logs ! Don't log on slow disks I/O bottlenecks 49. File system : I/O bottlenecks Causes : Excessive writes (database updates, logfiles, swapping, ) Excessive reads (non-indexed database queries, swapping, small file system cache, )How to detect ? topCpu(s):0.2%us,iostatavg-cpu:%user 0.10Device: sda sdb dm-0 dm-13.0%sy,0.0%ni, 61.4%id, 35.5%wa,%nice %system %iowait 0.00 0.96 53.70 tps 120.40 2.10 4.20 0.00Blk_read/s 0.00 0.00 0.00 0.00%steal 0.00Blk_wrtn/s 123289.60 4378.10 36.80 0.000.0%hi,0.0%si,0.0%st%idle 45.24 Blk_read 0 0 0 0Blk_wrtn 616448 18215 184 0See iowait ? Stop worrying about php, fix the I/O problem ! 50. File system Worst of all : NFS PHP files lstat calls Templates same Sessions locking issues corrupt data store sessions in database, Memcached, Redis, ... 51. Much more than codeXML feedUserNetwork WebserverDB server 52. Look beyond PHP ! 53. Questions ? 54. Questions ? 55. Contact Twitter Web Slides E-mail@wimgtr http://techblog.wimgodden.be http://www.slideshare.net/wimg wim.godden@cu.bePlease... Rate my talk : http://joind.in/9763 56. Thanks ! Please... Rate my talk : http://joind.in/9763