Transcript www.ukoug.org

MySQL Web Reference Architectures
Best Practices for Innovating on the Web
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.
2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
 Requirements for Web Innovation
Program
Agenda
 Reference Architectures
– Sizing & Topologies
– Enabling Technology
– Best Practices
 Hardware Sizing
 Operational Best Practices
 Resources
3
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
2.4BN USERS
$1.7TR GLOBAL CONTRIBUTION
70+ NEW DOMAINS EVERY 60 SECONDS
2x DATA GROWTH EVERY 14
1BN+ USERS
20M APPS PER DAY
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
4
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
85% HANDSETS SHIPPED WITH A
BROWSER
Infrastructure Requirements
 Agile, open & adaptable
 Simple & repeatable
 Low latency
 Scalable and Available
 Rock-solid security
 Low TCO
5
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL: Powering the Web & the Cloud
6
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
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
7
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
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
8
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Reference Architecture Sizing
Social Network
Small
Medium
Large
Extra Large
Queries/Second
<500
<5,000
10,000+
25,000+
Transactions/Second
<100
<1,000
10,000+
25,000+
Concurrent
Read Users
<100
<5,000
10,000+
25,000+
Concurrent
Write Users
<10
<100
1,000+
2,500+
Database Size
9
Sessions
<2 GB
<10 GB
20+ GB
40+ GB
eCommerce
<2 GB
<50 GB
50+ GB
200+ GB
Analytics (Multi-Structured Data)
<10 GB
<1TB
10+ TB
100+ TB
Content Management (Meta-Data)
<10 GB
<500 GB
1+ TB
2+ TB
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Reference Architectures
10
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Small: Web Reference Architecture
Social Network
Small
Medium
Large
Extra Large
Queries/Second
<500
<5,000
10,000+
25,000+
Transactions/Second
<100
<1,000
10,000+
25,000+
Concurrent
Read Users
<100
<5,000
10,000+
25,000+
Concurrent
Write Users
<10
<100
1,000+
2,500+
Database Size
11
Sessions
<2 GB
<10 GB
20+ GB
40+ GB
eCommerce
<2 GB
<50 GB
50+ GB
200+ GB
Analytics (Multi-Structured Data)
<10 GB
<1TB
10+ TB
100+ TB
Content Management (Meta-Data)
<10 GB
<500 GB
1+ TB
2+ TB
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Small: Web Reference Architecture
MySQL Master
 Single server supporting:
MySQL
Enterprise Monitor
Backup
MySQL Replication
– Users & sessions
– eCommerce
– Content management
 MySQL replication
Analytics
MySQL
Enterprise Backup
– Analytics and Backups
 If traffic volumes increase, scale session
management first on a separate server
Slave 1
Slave 2
Limited Scalability
Recommended to start with the Medium Architecture
12
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL Default Storage Engine
InnoDB
 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
13
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL Enterprise Audit
New!
Policy-based Auditing for MySQL Applications
 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
Adds regulatory compliance to MySQL applications.
HIPAA, Sarbanes-Oxley, PCI, etc.
14
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL Enterprise Audit Flow
3. Joe’s connection, query logged
1. DBA Enables on Server1
2. User Joe Connects and Queries Server1
15
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Server1
MySQL Enterprise Security
External Authentication
 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)
Integrates MySQL with existing security infrastructures and SOPs.
16
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL Enterprise Backup







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
mysqlbackup
Unlimited Database Size
Cross-Platform
MySQL
MEB Backup
- Windows, Linux, Unix
Files
Database Files
Ensures quick, online backup and recovery of your on premise and Cloud
based MySQL applications.
17
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Medium: Web Reference Architecture
Social Network
Small
Medium
Large
Extra Large
Queries/Second
<500
<5,000
10,000+
25,000+
Transactions/Second
<100
<1,000
10,000+
25,000+
Concurrent
Read Users
<100
<5,000
10,000+
25,000+
Concurrent
Write Users
<10
<100
1,000+
2,500+
Database Size
18
Sessions
<2 GB
<10 GB
20+ GB
40+ GB
eCommerce
<2 GB
<50 GB
50+ GB
200+ GB
Analytics (Multi-Structured Data)
<10 GB
<1TB
10+ TB
100+ TB
Content Management (Meta-Data)
<10 GB
<500 GB
1+ TB
2+ TB
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Medium: Web Reference Architecture
19
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Best Practices - Overview
Medium 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
20
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Best Practices – Content Management
Medium 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
21
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
XOR
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
 NoSQL Memcached API for InnoDB can be used to scale session management
– eCommerce HA
 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
22
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL HA
Solutions
23
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
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
24
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
High Availability & Scalability
MySQL 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
25
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL 5.6: Evolving Replication
PERFORMANCE
• Multi-Threaded Slaves
• Binary Log Group Commit
• Optimized Row-Based Replication
FAILOVER & RECOVERY
• Global Transaction Identifiers
• Replication Failover & Admin Utilities
• Crash Safe Slaves & Binlogs
DATA INTEGRITY
• Replication Event Checksums
DEV/OPS AGILITY
• Replication Utilities
• Time-Delayed Replication
• Remote Binlog Backup
• Informational Log Events
• Server UUIDs
26
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
VIP
Clients
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
27
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
• MySQL Certified & Supported
Solutions
•
•
•
Oracle VM Template
Windows Failover Clustering
Oracle Solaris Cluster
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
Copyright
Oracle
andOracle
/ or itsand/or
affiliates.
All rights
reserved.
September
29th, 2012
28 © 2012,
Copyright
© 2013,
its affiliates.
All rights
reserved.
|
MySQL Enterprise Scalability
MySQL Thread Pool
 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
29

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
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
With Thread Pool Enabled
MySQL 5.6 Sysbench OLTP Read/Write
Transactions Per Second
12000
10000
MySQL Enterprise Edition
With Thread Pool
8000
6000
4000
2000
MySQL Community Server
0
1
4
16
32
64
128
256
512
1024
2048
4096
8192
Simultaneous Database Connections
60x Better Scalability with Thread Pool
30
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Without Thread Pool
MySQL 5.6.11
Oracle Linux 6.3, Unbreakable Kernel 2.6.32
4 sockets, 24 cores, 48 Threads
Intel(R) Xeon® E7540 2GHz CPUs
512GB DDR RAM
Large: Web Reference Architecture
Social Network
Small
Medium
Large
Extra Large
Queries/Second
<500
<5,000
10,000+
25,000+
Transactions/Second
<100
<1,000
10,000+
25,000+
Concurrent
Read Users
<100
<5,000
10,000+
25,000+
Concurrent
Write Users
<10
<100
1,000+
2,500+
Database Size
31
Sessions
<2 GB
<10 GB
20+ GB
40+ GB
eCommerce
<2 GB
<50 GB
50+ GB
200+ GB
Analytics (Multi-Structured Data)
<10 GB
<1TB
10+ TB
100+ TB
Content Management (Meta-Data)
<10 GB
<500 GB
1+ TB
2+ TB
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Large: Web Reference Architecture
Session Management
eCommerce
Content Management
Caching / Application Servers
MySQL Servers
F3
F2
F4
MySQL Cluster Data Nodes
F1
F3
F1
F3
MySQL
Enterprise Backup
32
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Slave N
F2
F4
F2
F4
Slave 6
Slave 7
Slave 8 Slave 9 Slave 10
Slave 1
Slave 2
Slave 3 Slave 4
MySQL Cluster Data Nodes
Analytics
MySQL
Enterprise Monitor
MySQL Master
Node Group 2
Node 3
F4
Node 3
F2
Node Group 1
Node 4
F1
Node 3
F3
Node 4
Node 3
Node 4
F1
Node Group 2
Node 4
MySQL Servers
Node Group 1
Hadoop
Cluster
MySQL Master
Slave 1 Slave 2 Slave 3
Slave 5
XOR
Distributed
Storage
MySQL Cluster: Overview
HIGH SCALE,
READS + WRITES
99.999%
AVAILABILITY
REAL-TIME
SQL + NoSQL
LOW TCO
33
• Auto-Sharding, Multi-Master
• ACID Compliant, OLTP + Real-Time Analytics
• Shared nothing, no Single Point of Failure
• Self Healing + On-Line Operations
• In-Memory Optimization + Disk-Data
• Predictable Low-Latency, Bounded Access Time
• Key/Value + Complex, Relational Queries
• SQL + Memcached + JavaScript + Java + JPA + HTTP/REST & C++
• Open Source + Commercial Editions
• Commodity hardware + Management, Monitoring Tools
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL Cluster Architecture
Clients
Application Layer
Management
Data Layer
MySQL Cluster Data Nodes
34
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL & Hadoop Use-Case in Web Architecture
Users
Browsing
Profile,
Purchase
History
Recommendations
Recommendations
Social media updates
Preferences
Brands “Liked”
Web Logs:
Pages Viewed
Comments Posted
Telephony Stream
35
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL in the Big Data Lifecycle
DECIDE
ACQUIRE
BI Solutions
Applier
ANALYZE
36
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
ORGANIZE
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
37
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
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
38
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Extra Large: Social Network Reference Architecture
Social Network
Small
Medium
Large
Extra Large
Queries/Second
<500
<5,000
10,000+
25,000+
Transactions/Second
<100
<1,000
10,000+
25,000+
Concurrent
Read Users
<100
<5,000
10,000+
25,000+
Concurrent
Write Users
<10
<100
1,000+
2,500+
Database Size
39
Sessions
<2 GB
<10 GB
20+ GB
40+ GB
eCommerce
<2 GB
<50 GB
50+ GB
200+ GB
Analytics (Multi-Structured Data)
<10 GB
<1TB
10+ TB
100+ TB
Content Management (Meta-Data)
<10 GB
<500 GB
1+ TB
2+ TB
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Extra Large: Social Network
Central Databases
App /Caching
Servers
Shards
Customers
1% – 33%
Shard Catalog
Hadoop
Cluster
MySQL Master
MySQL Servers
F3
F1
F3
Node 3
F1
Node Group 2
Node 4
Node 4
Node 3
Node Group 1
Slave 1 Slave 2 Slave 3
F2
F4
Slave 1
Slave 2
Slave N
Analytics
Customers
34% – 66%
F2
F4
MySQL Cluster Data Nodes
Authentication
Slave 1
Slave 2
Slave N
MySQL Servers
MySQL
Enterprise Monitor
Customers
67% – 100%
F3
F1
F3
Node 3
F1
Node Group 2
Node 4
Node 4
Node 3
Node Group 1
F2
F4
F4
MySQL Cluster Data Nodes
40
MySQL
Enterprise Backup
F2
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Slave 1
Slave 2
Slave N
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
41
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Master
Slave
Reads
Clients
Sharding Implementation
Writes
Partitioning Logic
1
2
3
4
5
Shards
Slaves
42
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Recommended Hardware
43
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
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.
44
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
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.
45
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
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
46
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL Cluster Hardware Selection:
Disk Subsystem for Checkpointing
Entry-Level
Mid-Range
LCP
REDOLOG
1 x SATA 7200 RPM
• For read-mostly
• No redundancy
(but other data node is
the mirror)
LCP
REDOLOG
High-End
LCP / REDOLOG
1 x SAS 10K RPM or SSD
4 x SAS 10-15K RPM or SSDs
• Heavy duty (many MB/s)
• Heavy duty (many MB/s)
• No redundancy
(but other data node is
the mirror)
• Disk redundancy (RAID1+0),
hot swap
• REDO, LCP, BACKUP – written sequentually in small chunks (256KB)
• If possible, use Odirect = 1
47
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL Cluster Hardware Selection:
Disk Data Storage
Recommended Minimum
LCP
REDOLOG
UNDOLOG
TABLESPACE
2 x SAS 10K RPM
or 2 x SSD
High-End Recommendation
UNDOLOG
(REDO LOG)
TABLESPACE 1
TABLESPACE 2
(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
48
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Operational Best Practices
49
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL Enterprise Edition
Highest Levels of Security, Performance and Availability
Oracle Premier
Lifetime Support
MySQL Enterprise
Security
MySQL Enterprise
Audit
MySQL Enterprise
Scalability
MySQL Enterprise
High Availability
50
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Oracle Product
Certifications/Integrations
MySQL Enterprise
Monitor/Query Analyzer
MySQL Enterprise
Backup
MySQL Workbench
MySQL Enterprise Monitor
 Web-based, global view of
MySQL/Cluster applications (onpremise and Cloud deployments)
 Automated, rules-based monitoring
and alerts (SMTP, SNMP enabled)
 Query capture, monitoring, analysis
and tuning, correlated with Monitor
graphs
 Real-time Replication Monitor with
auto-discovery of master-slave
topologies
 Integrated with Oracle Support
51
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
A Virtual MySQL DBA Assistant!
Automated Advisors and Alerts
MySQL Cluster
Monitors and Advises on
status/ performance of
MySQL Cluster Data Nodes
Custom
Administration
Monitors and Advises on
Optimal Start up and
Run time Configuration
Performance
Monitors and Advises on
Optimal Performance
Variable Settings
Replication
Built by DBA to Enforce
Organization specific
best practices
Monitors and Advises on
Master/Slave Latency
Security
Upgrade
Monitors and Advises on
Unplanned Security
Changes/Loopholes
Monitors and Advises on
Bugs/Upgrades that affect
current installation
Backup
Monitors and Advises on
Backup/Recovery
processes
Schema
Monitors and Advises on
Unplanned Schema
Change
Memory Usage
Monitors and advises on
optimal memory/cache
settings
10 Advisors, 160+ Rules, 60+ MySQL, OS specific Graphs
Saves time writing, deploying, versioning, maintaining custom scripts.
52
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL Query Analyzer
 Centralized monitoring of queries
without Slow Query Log, SHOW
PROCESSLIST;
 Aggregated view of query
execution counts, time, and rows
 Visual “grab and go” correlation
with Monitor graphs
 Enabled via Connectors (PHP,
JDBC, .Net) or MySQL Proxy
Saves you time parsing atomic
executions from logs. Finds problems
you cannot find yourself.
53
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
MySQL Cluster Manager
Enhancing DevOps Agility, Reducing Downtime
Automated
Management
• Start / Stop node or
whole cluster
• On-Line Scaling
• On-Line Reconfiguration
• On-Line Upgrades
• On-Line Backup &
Restore
54
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Self-Healing
• Node monitoring
• Auto-recovery
extended to SQL +
mgmt nodes
HA Operations
• Cluster-wide
configuration
consistency
• Persistent
configurations
• HA Agents
Reference Architecture - Summary
 Designed as a springboard to
innovating on the web
 Based on insight from most
successful web properties
 Best practices & repeatable
technologies for Scale & HA
55
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
Next Steps
 MySQL Web Reference Architectures Whitepaper
http://www.mysql.com/why-mysql/white-papers/mysql_wp_high-availability_webrefarchs.php
 MySQL 5.6: Developer and DBA Guide
http://www.mysql.com/why-mysql/white-papers/whats-new-mysql-5-6/
 Engage MySQL Consulting
56
Copyright © 2013, Oracle and/or its affiliates. All rights reserved. |
<Insert Picture Here>
MySQL Web Reference Architectures
Building Massively Scalable Web Infrastructure