MySQL Web Reference Architectures Best Practices for Innovating on the Web

1. MySQL Web Reference ArchitecturesBest Practices for Innovating on the Web

2. Safe Harbour Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle.

3. Requirements for Web Innovation • Reference Architectures • Sizing & Topologies • Enabling Technology • Best Practices • Hardware Sizing • Operational Best Practices • Resources ProgramAgenda

4. 2.4BNUSERS $1.7TR GLOBAL CONTRIBUTION 70+ NEW DOMAINS EVERY 60 SECONDS 1BN+USERS 20M APPS PER DAY 2x DATA GROWTH EVERY 14 MONTHS 72 HOURS UPLOADED EVERY MINUTE 350m TWEETS PER DAY 875k TPM DURING US PRESIDENTIAL ELECTION $1TR BY 2014 $700BN IN 2011 6.7BN MOBILE SUBS IN 2012 1.2 BILLION iOS & ANDROID APPS DOWNLOADED PER WEEK 85% HANDSETS SHIPPED WITH A BROWSER

5. Infrastructure Requirements • Agile, open & adaptable • Simple & repeatable • Low latency • Scalable and Available • Rock-solid security • Low TCO

6. MySQL: Powering the Web & the Cloud

7. MySQL Web Reference Architectures • Best practices for innovating in web & mobile services • Fast Time to Market • Open, Agile and Highly Adaptable • Reduced Cost, Risk & Complexity • On-Premise or in the Cloud

8. Reference Architectures: Design Patterns • On-Line Retail • Small, Medium & Large: Database load & size • User Authentication & Session Management • Content Management • eCommerce • Analytics & Big Data Integration • Social Networking • Extra Large • Recommended hardware • Operational Best Practices

9. Reference Architecture Sizing

10. Reference Architectures

11. Small: Web Reference Architecture

12. Small: Web Reference Architecture • Single server supporting: • Users & sessions • eCommerce • Content management • MySQL replication • Analytics and Backups • If traffic volumes increase, scale session management first on a separate server Limited Scalability Recommended to start with the Medium Architecture

13. MySQL Default Storage EngineInnoDB • Data integrity: Fully ACID-compliant, crash-safe, Foreign Keys • High concurrency: Row level locking, MVCC • Scale up: 48+ threads, Scale-Out: MySQL replication • Flexible: On-Line DDL, Full-text search, SQL & NoSQL APIs, Backup and restore buffer pool • Instrumented & Monitored: Performance_Schema, MySQL Enterprise Monitor

14. MySQL Audit API– enables users to add auditing MySQL Enterprise Audit provides ready-made policy auditing Out-of-the-box logging of connections, logins, query activity across all or specific MySQL servers User defined policies, filtering and log rotation Dynamically enabled, disabled: no server restart XML-based audit stream per Oracle audit specification New! MySQL Enterprise Audit Policy-based Auditing for MySQL Applications Adds regulatory compliance to MySQL applications. HIPAA, Sarbanes-Oxley, PCI, etc.

15. 2. User Joe Connects and Queries Server1 MySQL Enterprise Audit Flow 3. Joe’s connection, query logged Server1 1. DBA Enables on Server1

16. MySQL Authentication API – enables addition of user authentication MySQL Enterprise Security provides ready-made authentication modules PAM (Pluggable Authentication Modules) Access external authentication methods Standard interface (Unix, LDAP, Kerberos, others) proxied and non-proxied users Windows Access native Windows services Authenticate users already logged into Windows (Windows Active Directory) MySQL Enterprise Security External Authentication Integrates MySQL with existing security infrastructures and SOPs.

17. Online, high performance backup for InnoDB (scriptable interface) Full, Incremental, Partial Backups (with compression) Point in Time, Full, Partial Recovery options Enterprise Advisor Monitoring and Alerts on Backup Operations Metadata on status, progress, history Unlimited Database Size Cross-Platform Windows, Linux, Unix MySQL Enterprise Backup mysqlbackup MySQL Database Files MEB Backup Files Ensures quick, online backup and recovery of your on premise and Cloud based MySQL applications.

18. Medium: Web Reference Architecture

19. Medium: Web Reference Architecture

20. Best Practices - OverviewMedium Web Reference Architecture • Server ratio: 10 application servers to each MySQL Server • More for PHP applications, fewer for Java • Add more slaves as the application tier scales • Caching layer deployed in session & content management components • Memcached or Redis are common • Reads fulfilled from cache, relieving load on the source database servers • NoSQL Memcached APIs can enable compression of caching and data layers

21. Best Practices – Content ManagementMedium Web Reference Architecture • Each slave can handle around 5,000 concurrent users • Each master handles 20 slaves • More slaves are often used in larger environments • MySQL Replication for high availability & scale-out • Meta data of content assets managed by MySQL • Distributed File System / CDN / Cloud (i.e. S3) for physical storage of assets on • High quality SAN (redundancy for HA) • Across commodity nodes XOR

22. Best Practices – Sessions & eCommerce Medium Web Reference Architecture • Session Management & eCommerce • Session data maintained for up to 1 hour in a dedicated partition, rolling partitions used to delete aged data • NoSQLMemcached API for InnoDB can be used to scale session management • eCommerceHA • Semi-Synchronous replication or clustering (DRBD, shared storage, etc.) • If web traffic grows, move Session Management to MySQL Cluster • Persist session data for real-time personalization of user experience • 99.999% availability and in-memory data management can reduce need for DRBD & caching • Data is captured in Analytics Database, increasingly replicated to Big Data system for analysis

23. MySQL HASolutions

24. MySQL Replication • Duplicates database from a “master” to a “slave” • Redundant copies of the data provide foundation for High Availability • Scale out by distributing queries across the replication cluster Web / App Servers Writes & Reads Reads Master Slaves

25. High Availability & ScalabilityMySQL Replication • Scale-out within and across data centers • Self-healing and crash-safe • Multiple topologies supported • Master/Slave, Cascading, Circular • Asynchronous as default with Semi-Synchronous as an option • Replication utilities for fast provisioning • Monitoring & best practices with MySQL Enterprise Monitor Relay Log

26. MySQL 5.6: Evolving Replication

27. Shared-Disk Clustering for HA • Stricter data durability, integrity constraints • Shared storage persists commits across instances • Clustering software manages data access • Auto-failover of applications and database • Deploy with MySQL replication for slave scale-out • MySQL Certified & Supported Solutions • Oracle VM Template • Windows Failover Clustering • Oracle Solaris Cluster VIP Clients

28. Shared Nothing Clustering for HA • DRBD + Clustering • Based on distributed storage, not a SAN • Synchronous replication eliminates risk of data loss • Open source, mature & proven • Certified and fully supported by Oracle • DRBD integrated into Oracle Linux Unbreakable Enterprise Kernel R2 • Pacemaker and Corosync for clustering / failover • Updates to stack via ULN channel

29. MySQL default thread-handling – excellent performance, can limit scalability as user connections grow Connections assigned to 1 thread for the life of the connection, same thread used for all statements Thread Pool API, enables users to build their own thread pool MySQL Thread Pool improves sustained performance/scale as user connections grow Thread Pool contains configurable number of thread groups, each manages up to 4096 re-usable threads Threads are prioritized, statements queued to limit concurrent executions, load on server, improve scalability as connections grow MySQL Enterprise Scalability MySQL Thread Pool

30. With Thread Pool Enabled MySQL Enterprise Edition With Thread Pool MySQL Community Server Without Thread Pool MySQL 5.6.11 Oracle Linux 6.3, Unbreakable Kernel 2.6.324 sockets, 24 cores, 48 ThreadsIntel(R) Xeon® E7540 2GHz CPUs512GB DDR RAM 60x Better Scalability with Thread Pool

31. Large: Web Reference Architecture

32. Large: Web Reference Architecture

33. MySQL Cluster: Overview HIGH SCALE, READS + WRITES 99.999% AVAILABILITY LOW TCO SQL + NoSQL REAL-TIME • Auto-Sharding, Multi-Master • ACID Compliant, OLTP + Real-Time Analytics • Shared nothing, no Single Point of Failure • Self Healing + On-Line Operations • Open Source + Commercial Editions • Commodity hardware + Management, Monitoring Tools • Key/Value + Complex, Relational Queries • SQL + Memcached + JavaScript + Java + JPA + HTTP/REST & C++ • In-Memory Optimization + Disk-Data • Predictable Low-Latency, Bounded Access Time

34. MySQL Cluster Architecture Clients Application Layer Management Data Layer MySQL Cluster Data Nodes

35. MySQL & Hadoop Use-Case in Web Architecture Users Browsing Profile, Purchase History Recommendations Recommendations Web Logs: Pages Viewed Comments Posted Social media updates Preferences Brands “Liked” Telephony Stream

36. MySQL in the Big Data Lifecycle ACQUIRE DECIDE BI Solutions Applier ANALYZE ORGANIZE

37. New: MySQL Applier for Hadoop • Real-time streaming of events from MySQL to Hadoop • Supports move towards “Speed of Thought” analytics • Connects to the binary log, writes events to HDFS via libhdfs library • Each database table mapped to a Hive data warehouse directory • Enables eco-system of Hadoop tools to integrate with MySQL data • See dev.mysql.com for articles • Available for download now • labs.mysql.com

38. NoSQL Interfaces for MySQL • 10x faster read/write access to InnoDB or MySQL Cluster • Bypasses SQL parsing • Fast look-ups and data ingestion • Maintain ACID guarantees • Maintain SQL for complex queries • Combine with On-Line DDL for schema evolution

39. Extra Large: Social Network Reference Architecture

40. Extra Large: Social Network

41. Best Practices Social Networking Reference Architecture • Introduces Sharding • Implemented at the application layer for scaling very high volume of writes • Data divided into smaller sets, distributed across low-cost hardware • Shards based on Hash of a single column – ie. User ID • “Functional” sharding also an option • Sharding only needed in a small percentage of workloads • MySQL scale-up, coupled with replication will get users a long way! • Most Web and mobile workloads are still read-intensive, ie record is read before updates applied • MySQL Cluster supports Auto-Sharding + Cross-Shard JOINs

42. Sharding Implementation Master Clients Slave Reads Writes Partitioning Logic 1 2 3 4 5 Shards Slaves

43. Recommended Hardware

44. The Perfect MySQL Server • Up to 64 x86-64 bit CPU threads (MySQL 5.6 and above). • Recommended RAM at least equal to or larger than “hottest” (most regularly accessed) data set. • Linux, Oracle Solaris or Microsoft Windows operating systems. • Minimum of 4 x SSDs or HDDs. 8 – 16 drives will increase performance for I/O intensive applications. • Hardware RAID with battery-backed cache. • RAID 10 recommended. RAID 5 is suitable if the workload is read-intensive. • 2 x Network Interface Cards and 2 x Power Supply Units for redundancy.

45. MySQL Cluster Hardware Selection - SQL Layer • 4 - 24 x86-64 bit CPU threads • Minimum 4GB of RAM • Memory not as critical at this layer • Requirements influenced by connections and buffers. • 2 x Network Interface Cards & 2 x PSUs • Linux, Solaris or Windows operating systems.

46. MySQL Cluster Hardware Selection - Data Layer • Up to 64 x86-64 bit CPU threads • Use high frequency: enables faster processing of messages • Calculating RAM per Data Node (in-memory database) • Database Size * # Replicas * 1.25 / # data node • 50GB database * 2 replicas * 1.25 / 2 data nodes = 64GB of RAM • Non-indexed columns can be stored on disk, reduces RAM requirements • 2 x NICs and 2 x PSUs • Deep-dive into network best-practices in Ref Archs Guide

47. MySQL Cluster Hardware Selection: Disk Subsystem for Checkpointing Entry-Level Mid-Range High-End LCPREDOLOG LCPREDOLOG LCP / REDOLOG • 1 x SATA 7200 RPM • For read-mostly • No redundancy (but other data node isthe mirror) • 1 x SAS 10K RPM or SSD • Heavy duty (many MB/s) • No redundancy (but other data node is the mirror) • 4 x SAS 10-15K RPM or SSDs • Heavy duty (many MB/s) • Disk redundancy (RAID1+0), hot swap • REDO, LCP, BACKUP – written sequentually in small chunks (256KB) • If possible, use Odirect = 1

48. MySQL Cluster Hardware Selection: Disk Data Storage Recommended Minimum High-End Recommendation LCPREDOLOGUNDOLOG UNDOLOG (REDO LOG) (REDO LOG) TABLESPACE 1 TABLESPACE TABLESPACE 2 2 x SAS 10K RPM or 2 x SSD (REDO LOG / UNDO LOG) LCP 4 x SAS 10-15K RPM or SSD • Use High-End Recommendation for heavy read / write workloads • (1000's of 10KB records per sec) of data (i.e. Content Delivery platforms) • Having TABLESPACE on separate disk is good for read performance • Enable WRITE_CACHE on devices

49. Operational Best Practices

50. MySQL Enterprise Edition Highest Levels of Security, Performance and Availability Oracle Premier Lifetime Support Oracle Product Certifications/Integrations MySQL Enterprise Security MySQL Enterprise Monitor/Query Analyzer MySQL Enterprise Audit MySQL Enterprise Backup MySQL Enterprise Scalability MySQL Enterprise High Availability MySQL Workbench