1 / 53

Optimizing Security, Performance and Availability MySQL Enterprise Edition

Optimizing Security, Performance and Availability MySQL Enterprise Edition. Mick Xu Oracle University Principal Instructor .

yves
Download Presentation

Optimizing Security, Performance and Availability MySQL Enterprise Edition

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Optimizing Security, Performance and Availability MySQL Enterprise Edition Mick XuOracle University Principal Instructor

  2. 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. Program Agenda • Oracle’s Investment in MySQL • From Nice to Have to Mission Critical • MySQL Enterprise Edition • Oracle Product Integrations/Certifications • Learn more • Questions?

  4. Industry Leaders Rely on MySQL OEM & ISVs Web & Enterprise Cloud Confidential - Oracle Internal Use Only

  5. ORACLE DRIVES MySQL INNOVATION MAKING MySQL BETTER EVERY DAY DELIVERING MORE BETTER PRODUCTS FASTER

  6. Hundreds of MySQL development engineers, support experts and consultants, including original architects of the server, InnoDB and MySQL Cluster Investing and growing the team BY FAR, THE LARGEST DEVELOPMENT TEAM IN MySQL’S HISTORY

  7. MySQL Cluster 7.2 MySQL Cluster Manager 1.4 MySQL Utilities 1.2 MySQL Migration Wizard MySQL Enterprise Backup 3.8 MySQL Enterprise Audit MySQL Database 5.6 MySQL Cluster 7.3 DMR MySQL Database 5.7 DMR MySQL Applier for Hadoop (Labs) Driving MySQL Innovation MySQL Enterprise Backup 3.7 Oracle VM Template for MySQL Enterprise Edition MySQL Enterprise Oracle Certifications MySQL Windows Installer MySQL Enterprise Security MySQL Enterprise Scalability MySQL Enterprise Monitor 2.2 MySQL Cluster 7.1 MySQL Cluster Manager 1.0 MySQL Workbench 5.2 MySQL Database 5.5 MySQL Enterprise Backup 3.5 MySQL Enterprise Monitor 2.3 MySQL Cluster Manager 1.1 All GA! All GA! MySQL Database 5.6 DMR* MySQL Cluster 7.2 DMR MySQL Labs! (“early and often”) All GA! Available Now! 2010 2012-13 2011

  8. MySQL 5.6: Best Release Ever!

  9. MySQL 5.6: Scalability MySQL5.6 • Oracle Linux 6 Intel(R) Xeon(R) E7540 x86_64 MySQL leveraging: • 48 of 96 available CPU threads • 2 GHz, 512GB RAM • Users can fully utilize latest generations of hardware and OS • Scales as data volumes and users grow

  10. MySQL5.6: Scalability MySQL5.6 • Oracle Linux 6 Intel(R) Xeon(R) E7540 x86_64 MySQL leveraging: • 48 of 96 available CPU threads • 2 GHz, 512GB RAM • Users can fully utilize latest generations of hardware and OS • Scales as data volumes and users grow

  11. MySQL 5.6 SysBench Benchmarks MySQL5.6 MySQL5.5 • Oracle Linux 6 Intel(R) Xeon(R) E7540 x86_64 MySQL leveraging: • 48 of 96 available CPU threads • 2 GHz, 512GB RAM Up to 151% Performance Gain

  12. MySQL 5.6 SysBench Benchmarks MySQL5.6 MySQL5.5 • Oracle Linux 6 Intel(R) Xeon(R) E7540 x86_64 MySQL leveraging: • 48 of 96 available CPU threads • 2 GHz, 512GB RAM Up to 234% Performance Gain

  13. From Nice to Have to Mission-Critical (and all points between)

  14. Custom Applications Mission Critical Over Time Mission-Critical Example Works so well that access is extended to Executive Management and Accounting System added as second data source Data Mart has entire Salesforce, Exec team, leaders of Finance, Marketing and Service departments as users (200+ people) and three back ends, Orders, Accounting & CRM Sales dept can’t get accurate reports from IT. Builds Data Mart to query Order System to serve Sales Management with sales data reports Users/Dependence Inflection point when “nice to have” becomes mission-critical nice to have Day 1 1 year 6 months

  15. What’s At Risk? Mission-Critical Revenue and Productivity “We need help!” Spend money, save time, mitigate risk “I’ll hire more DBAs!” Spend more time, save less money, more risk Users/Dependence 1 minute of downtime = Lost $ “I’ll fix it myself” Spend time, save money, some risk nice to have Day 1 6 months 1 year

  16. And then, tough questions… …performance issues? …servers down? …expensive, slow queries? …security policies, change? Where/When/How to Tune? …Developers productive? ..version of MySQL to run? …Replication synch issues? …will systems scale? …can I recover? …managing Oracle andMySQL databases?

  17. 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

  18. 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 Easily implemented via MySQL 5.5 Audit API MySQL 5.5.28 and higher New! MySQL Enterprise Audit Policy-based Auditing for MySQL Applications Adds regulatory compliance to MySQL applications. HIPAA, Sarbanes-Oxley, PCI, etc.

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

  20. 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) Pluggable Authentication API MySQL Enterprise Security External Authentication Integrates MySQL with existing security infrastructures and SOPs.

  21. 2.2BNUSERS 630MWEB SITES 1 BUSERS 552MDAILY ACTIVE 40% DATA GROWTH PER YEAR 72 HOURSUPLOADED EVERY MINUTE $1TR BY 2013 $700BN IN 2011 400m TWEETS PER DAY 5.9BN MOBILE SUBS IN 2011 OVER 1.2 BILLION iOS & ANDROID APPSIN 2012 85% HANDSETS SHIPPED WITH A BROWSER

  22. MySQL default thread-handling – excellent performance, can limit scalability as user connections grow MySQL Thread Pool improves sustained performance/scale as user connections grow Thread Pool API MySQL Enterprise Scalability MySQL Thread Pool Ensures better, sustained performance as user loads continue to grow.

  23. Default Thread Handling External Clients Default Thread Handling Connection Execution Threads Connections /statements assigned to Threads for life Internal Clients • Connections assigned to 1 thread for the life of the connection, same thread used for all statements • No prioritization of threads, statement executions • Many concurrent connections = many concurrent execution threads to consume server memory, limit scalability

  24. With Thread Pool Enabled External Clients Thread Pool Connection Execution Threads Thread Group 1 Threads 1 - 4096 Thread Group 1 Thread Group 2 Thread Group 2 Threads 4097 - 8193 Thread Group N Thread Group N Threads 8194 - N Internal Clients • Thread Pool contains configurable number of thread groups (default = 16), each manages up to 4096 re-usable threads • Each connection assigned to thread group via round robin • Threads are prioritized, statements queued to limit concurrent executions, load on server, improve scalability as connections grow

  25. With Thread Pool Enabled MySQL Enterprise Edition With Thread Pool MySQL Community Server Without Thread Pool MySQL 5.5.16Oracle Linux 6.1, Unbreakable Kernel 2.6.322 sockets, 24 cores, 2 X 12-coreIntel(R) Xeon(R) X5670 2.93GHz CPUs72GB DDR3 RAM2 X LSI SCSI Disk (MR9261-8i) (597GB) 20x Better Scalability with Thread Pool

  26. With Thread Pool Enabled MySQL Enterprise Edition With Thread Pool MySQL Community Server Without Thread Pool MySQL 5.5.16Oracle Linux 6.1, Unbreakable Kernel 2.6.322 sockets, 24 cores, 2 X 12-coreIntel(R) Xeon(R) X5670 2.93GHz CPUs72GB DDR3 RAM2 X LSI SCSI Disk (MR9261-8i) (597GB) 3x Better Scalability with Thread Pool

  27. MySQL Enterprise Monitor • Web-based, global view of MySQL/Cluster applications (on-premise 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 A Virtual MySQL DBA Assistant!

  28. Administration Upgrade Security Custom Monitors and Advises on Optimal Start up and Run time Configuration • Monitors and Advises on Unplanned Security Changes/Loopholes Monitors and Advises on Bugs/Upgrades that affect current installation Built by DBA to Enforce Organization specific best practices Memory Usage Replication Schema Backup Performance Monitors and advises on optimal memory/cache settings Monitors and Advises on Backup/Recovery processes Monitors and Advises on Unplanned Schema Change Monitors and Advises on Optimal Performance Variable Settings Monitors and Advises on Master/Slave Latency MySQL Cluster Monitors and Advises on status/ performance of MySQL Cluster Data Nodes Automated Advisors and Alerts 10 Advisors, 160+ Rules, 60+ MySQL, OS specific Graphs Saves time writing, deploying, versioning, maintaining custom scripts. Visualize problems and tuning opportunities you cannot find yourself.

  29. 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.

  30. Query Execution Drill Downs Example query exec with variable substitution Trace query exec back to source code Full exec EXPLAIN

  31. Automated Replication Monitor • Auto-detects, groups/maintains Master/Slave topologies • Consolidated, real time status/synch check • Notifications on Synch Issues • Proactive vs reactive Saves you time monitoring and collecting replication status/synch data from MySQL command line.

  32. Online 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 Certified with Oracle Secure Backup, NetBackup, Tivoli, others 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.

  33. New!Database Migration Wizard for SQL Server, Sybase, PostgreSQL MySQL Workbench Database Design • Visual Design, modeling • Forward/Reverse Engineer • Schema validation, Schema doc SQL Development • SQL Editor - Color Syntax Highlighting • Objects - Import/Export, Browse/Edit Database Administration • Status, Configuration, Start/Stop • Users, Security, Sessions • Import/Export Dump Files Saves you time developing and managing your MySQLapplications.

  34. MySQL Enterprise High Availability Oracle VM Template for MySQL Enterprise Edition • Oracle Linux • Oracle VM • Oracle VM Manager • Oracle Cluster File System 2 (OCFS2) • MySQL Database 5.5 (Enterprise Edition) • Pre-Installed & Pre-Configured • Full Integration & QA Testing • Single Point of Support Automatic Fault Detection & Recovery Secure Live Migration (SSL) Oracle VM Server Pool Oracle VM Servers Oracle VM Oracle VM ocfs2 http://mysql.com/why-mysql/white-papers/mysql_wp_oracle-vm-template-for-mee.php iSCSI / FC-AL Oracle VM Manager

  35. New! MySQL Enterprise High Availability Oracle Linux + DRBD Stack • 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 • Based on distributed storage, not a SAN • Synchronous replication eliminates risk of data loss • Open source, mature & proven

  36. Native Windows HA clustering with MySQL Failures & Planned Maintenance Quorum (3rd vote), data (InnoDB + schema) & binaries (optional) stored in shared storage (iSCSI & FCAL) Loss of service = couple of seconds + InnoDB recovery time App sees temporary loss of connection and reconnects on same IP address Replication slave recovers automatically Cluster managed through MS Failover Cluster Management snap-in GUI No new software/scripts required MySQL Enterprise High Availability Windows Server Failover Clustering Download the Guide: http://bit.ly/w3rSoP ** Technical support for Windows Server Failover Clustering must be sourced from Microsoft.

  37. Kernel based heartbeat and monitoring SPARC and x86. Solaris Virtualization-aware MySQL agent included with Oracle Solaris Cluster Learn more: http://www.oracle.com/technetwork/server-storage/solaris-cluster/overview/index.html New! MySQL Enterprise High Availability Oracle Solaris Clustering

  38. MySQL Enterprise Edition Enables you to manage your Oracle and MySQL databases with Oracle tools/solutions you are already using. 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 Workbench MySQL Enterprise High Availability

  39. Oracle Fusion MiddleWare WebLogic Server Database Adapter for Oracle SOA Suite ** Oracle Business Process Management ** Oracle Virtual Directory Oracle Data Integrator Oracle Enterprise Performance Management ** Oracle Identity Analytics Open SSO STS, Open SSO Fedlet All ship with MySQL 5.x JDBC driver ** MySQL as a Metadata Repository option (in progress) MySQL Enterprise Oracle Certifications Completed

  40. MySQL Enterprise Oracle CertificationsCompleted • Oracle Linux • Oracle VM • Oracle VM Template for MySQLEnterprise Edition • Oracle GoldenGate • Oracle Secure Backup • Oracle Database Firewall • MyOracle Online Support

  41. MySQL Enterprise Oracle CertificationsIn Progress • Oracle Fusion MiddleWare • WebCenter Suite • Enterprise Content Management • Oracle Business Intelligence Suite • Oracle Clusterware • Oracle Audit Vault • Oracle Enterprise Manager • And More…

  42. Largest Team of MySQL Experts Backed by MySQL Developers Forward Compatible Hot Fixes MySQL Maintenance Releases Direct Access to MySQL Support Engineers Support in 29 Languages 24/7/365 Unlimited Incidents Knowledge Base MySQL Consultative Support Oracle Premier Lifetime Support for MySQL Rely on the Experts Only From Oracle "The rep that assisted me was simply outstanding. He immediately recognized the cause of my problem and provided the resolution.” mysql.com/support/quotes

  43. Learn More • mysql.com • MySQLProducts and Editions • TCO calculator • Customer use cases and success stories • dev.mysql.com • Downloads, Documentation • Forums • PlanetMySQL • eDelivery.oracle.com • Download and evaluate all MySQL products

  44. San Francisco, September 21-23 Additional Day of Tutorials Oracle.com/mysqlconnect • Keynotes • Conferences Sessions • Birds-of-a-feather sessions • Tutorials • Hands-on Labs • Exhibition Hall • Demo Pods • Receptions Graphic Section Divider Register Now!

  45. “With an ever-expanding portfolio of tools and applications, Oracle University is strongly oriented toward the individual learner.” 2013 leader inIT EducationAccording toIndustry Analyst Firm IDC “Of the vendors evaluated, Oracle is most adept at describing the value of training at multiple parts of the technology deployment life cycle.” “This decentralized control [of the curricula development] helps maximize the relevance of the training offering to the elements that are most useful to end users.”

  46. One of the World’s Largest Training Organizations Worldwide classrooms: 200+ in 89 countries Courses: 2000+ Languages supported: 28 Classes per week: 600+, 90+ Virtual Classes On Demand Courses: 100 and growing Students trained per year:400,000+ Global education partnerships:500+ Oracle certified professionals: 1.6+ million

  47. Training & Certification for the Entire Stack ROLE-BASED TRAINING Administrators Developers Business Users Architects Implementers WHEN AND HOW YOU NEED IT In-Class Training On Demand Private Live Virtual Class VALIDATED BY INDUSTRY-RECOGNIZED CERTIFICATIONS Oracle Certified Professional Oracle Certified Expert Oracle Certified Associate Oracle Certified Master

  48. More than 2000 Courses Across 50+ Products

  49. 160+ Certification Credentials Available

More Related