MySQL Technical Update: SCaLE 2012 - SCALE 19x 19x

2y ago
69 Views
2 Downloads
2.27 MB
53 Pages
Last View : Today
Last Download : 2m ago
Upload by : Ellie Forte
Transcription

Insert Picture Here MySQL Technical Update:SCaLE 2012Dave Stokes MySQL Community Manager

Safe Harbor StatementThe following is intended to outline our general productdirection. It is intended for information purposes only,and may not be incorporated into any contract. It is not acommitment to deliver any material, code, orfunctionality, and should not be relied upon in makingpurchasing decisions.The development, release, and timing of any features orfunctionality described for Oracle’s products remains atthe sole discretion of Oracle.2

Agenda Oracle’s Strategy and Investment in MySQL What’s New in MySQL PHP centric stuff Other goodies Q&A3

UNMATCHED INVESTMENTPERFORMANCE UNDREDS OF EXPERTSSTRATEGICWORLD-CLASS SUPPORTNoSQLLINUXMySQL CLUSTERWEBMySQL ENTERPRISE EDITIONLARGEST MySQL ENGINEERING & SUPPORT ORGANIZATION4

Oracle’s Investment in MySQLRapid InnovationMake MySQL a Better MySQL #1 Open Source Database for Web Applications “M” in most complete LAMP stack EmbeddedDevelop, Promote and Support MySQL Improved engineering, consulting and support Leverage 24x7, World-Class Oracle SupportMySQL Community Edition Source and binary releases GPL license5

More Product Releases Than Ever BeforeOracle Drives MySQL Innovation MySQL Enterprise Backup 3.6 MySQL Database 5.5 Oracle VM Template for MySQL MySQL Enterprise Backup 3.5 MySQL Enterprise Monitor 2.3 MySQL Cluster Manager 1.1GA MySQL Database 5.6 MySQL Cluster 7.2DMR*and MySQL Labs!(“early and often”)All GA! MySQL Workbench 5.2GA! MySQL Enterprise Monitor 2.2 MySQL Cluster 7.1 MySQL Cluster Manager 1.0A Better MySQLAll GA!*Development Milestone ReleaseQ2 CY2010Q3 CY2010Q4 CY20106Q1 CY2011Q2-3 CY2011

MySQL: Open Source (GPL)Available to download and use under the GPL: MySQL Database (Community Server) MySQL Cluster MySQL Workbench Community Edition MySQL Utilities (in Python) MySQL Connectors MySQL Proxy Documentation (free to use, not covered under GPL) ForumsSet your ambitions high!7

Industry Leading CustomersWebSaaS, CloudOEM / ISV’sTelecommunicationsRely on MySQL8Enterprise 2.0

Tough Questions performance issues? servers down? expensive, slow queries? security policies, change?Where/When/How to Tune? Developers productive? Replication synch issues?.version of MySQL to run? will systems scale? can I recover? managing Oracle and MySQL databases?9

MySQL Enterprise EditionMost secure, scalable MySQL Database, Online Backup,Development/Monitoring Tools, backed by Oracle PremierLifetime SupportOracle ProductCertificationsOracle PremierSupportMySQL EnterpriseBackupMySQL EnterpriseMonitor/Query AnalyzerMySQL EnterpriseSecurityMySQL EnterpriseScalabilityMySQL EnterpriseHigh AvailabilityMySQL Workbench10

MySQL DatabasePerformance, Reliability, Ease of UseSupport for commondevelopmentlanguages/platformsEfficient multithreaded sessionhandlingFull DML, DDLparsing, costbased optimizer,caching of queriesand result setsFlexible StorageEngine options forapplicationspecific storageneedsFlexible logging and physical storage options11

MySQL DatabaseInnoDB - Transactional by Default Default Storage Engine for MySQL 5.5 and aboveACID-compliant transactions, MVCCRow-level lockingTwo phase commitEfficient indexingFast DDL operationsTable compressionAutomatic crash recoveryReferential integrityOnline backupMore12

MySQL DatabaseSysBench Benchmarks - LinuxMySQL 5.5.6(Default InnoDB)MySQL 5.1.50(InnoDB Plug-in)MySQL 5.1.50(InnoDB built-in)370% performance gainfor MySQL 5.5 over 5.1.50; at scale13Intel Xeon X7460 x86 644 CPU x 6 Cores/CPU2.66 GHz, 32GB RAMFedora 10

MySQL DatabaseSysBench Benchmarks - LinuxMySQL 5.5.6(Default InnoDB)MySQL 5.1.50(InnoDB Plug-in)MySQL 5.1.50(InnoDB built-in)200% performance gainfor MySQL 5.5 over 5.1.50; at scale14Intel Xeon X7460 x86 644 CPU x 6 Cores/CPU2.66 GHz, 32GB RAMFedora 10

MySQL DatabaseSysBench Benchmarks - WindowsMySQL 5.5.6(Default InnoDB)MySQL 5.1.50(InnoDB Plug-in)MySQL 5.1.50(InnoDB built-in)540% performance gainfor MySQL 5.5 over 5.1.50; at scale15Intel x86 644 CPU x 2 Cores/CPU3.166 GHz, 8GB RAMWindows Server 2008

MySQL DatabaseSysBench Benchmarks - WindowsMySQL 5.5.6(Default InnoDB)MySQL 5.1.50(InnoDB Plug-in)MySQL 5.1.50(InnoDB built-in)1560% performance gainfor MySQL 5.5 over 5.1.50; at scale16Intel x86 644 CPU x 2 Cores/CPU3.166 GHz, 8GB RAMWindows Server 2008

MySQL DatabaseHigh Availability with MySQL Replication17Copyright 2011 Oracle Corporation17

MySQL DatabaseReplication InternalsMySQL MasterMySQL SlavemysqldmysqldupdatesselectsupdatesI/O ThreadReplicationrelaybinlogSQL Threaddataindex &binlogsbinlog18data

MySQL DatabaseReplication Enables ScalabilityApplicationLoad BalancerReadsWrites & ReadsReadsMySQL Replication Write to one master Read from many slaves, easily add more as needed Perfect for read/write intensive apps19

MySQL 5.6: A Better MySQLNew! MySQL 5.6 builds on MySQL 5.5 by improving: Optimizer for better Performance, ScalabilityPerformance Schema for better instrumentationInnoDB for better transactional throughputReplication for higher availability, data integrity“NotOnlySQL” options for better flexibilityTry it now:dev.mysql.com/downloads/mysql20

MySQL Enterprise SecurityMySQL External AuthenticationNew! PAM (Pluggable Authentication Modules) Access external authentication methods Standard interface (Unix, LDAP, others) proxied and non-proxied users Windows Access native Windows services Authenticate users already logged intoWindows (Windows Active Directory) Pluggable Authentication APIIntegrates MySQL with existing security infrastructures and SOPs.21

Windows AuthenticationExampleConnect as1. MyDomain/joe logs intoenvironment withWindows login, passwordAppConnectorConnect as3. MyDomain/joe logs intoapplication, applicationconnects to MySQL withConnecteduser win joe.WindowsAuthConnect as2. MyDomain\joe isauthenticated onWindows OSConnect as4. Tokens are checked, win joeuser is authenticated usingWindows Authenticatelogin, password withMySQL specific privsCREATE USER win joeIDENTIFIED WITH authentication windowsAS ‘joe';22Win OS users,groups, etc

PAM AuthenticationExample1. Joe logs in usingapplication username/password.AppConnector2. Connector sends the username/password to theMySQL server.ConnectedPAMAuthentication3. User name/password sentto the PAM library, yes/noanswer from PAM libraryreturned to client.CREATE USER joeIDENTIFIED WITH 'authentication pam'AS ‘mysql';234. PAM library verifies credentials(using e.g. LDAP or Kerberosetc) and returns yes/no answerfor delivery to client.PAM libraryand variousbackends

MySQL Enterprise ScalabilityMySQL Thread PoolNew! MySQL default thread-handling – excellentperformance, can limit scalability as connections grow MySQL Thread Pool improves sustainedperformance/scale as user connections grow Thread Pool API24

Default Connection PoolDefaultConnection PoolConnection 1Connection tion 1 Execution ThreadConnection 2 Execution Threadto Threadsfor life Connections assigned to 1 thread for the life of theconnection Same thread used for all connection statement executions(single threaded)25

With Thread Pool EnabledThread Pool (and threadConnection 1 Execution Threadsgroups)Connection 1Connection 1Threads 1 - 4096Connection 2Connection 2 Execution ThreadsConnection 2Threads 4097 - 8193Users/ClientsConnection NThreads 8194 - N 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 robinEnsures better, sustained performance as user loads continue togrow.26

MySQL Enterprise EditionMySQL Enterprise EditionWith Thread PoolMySQL Community ServerWithout Thread Pool20x Better Scalability with Thread Pool27MySQL 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)

MySQL Enterprise EditionMySQL Enterprise EditionWith Thread PoolMySQL Community ServerWithout Thread Pool3x Better Scalability with Thread Pool28MySQL 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)

MySQL Enterprise High AvailabilityOVM Template for MySQL Oracle Linux with the UnbreakableEnterprise KernelOracle VM Oracle VM Oracle VM ManagerAutomatic FaultDetection &RecoveryOracle VMSecure LiveMigration (SSL) Oracle Cluster File System 2 (OCFS2) MySQL Database (Enterprise Edition)New!Oracle VMManager Pre-Installed & Pre-ConfiguredOracle VM Server PoolOracle VM Servers Full Integration & QA Testingocfs2 Single Point of Support**** Technical support for Oracle Linux and Oracle Virtual Machine requiresUnbreakable Linux Network subscription.29SAN / iSCSI

MySQL Enterprise High AvailabilityProtecting Against Planned/Unplanned DowntimeVMVMVMVMVMBrowserVM Server PoolXVMVMVM Server PoolSSL MigrationHA Auto RestartmOracle VM ManagermOracle VM ServersOracle VM ServersPlanned Events Maintenance orupgrades Secure Live Migration Zero interruptionVMFailuresSAN, iSCSISAN, iSCSIPool Masters assureSecure Migration orHA restarts completein the event of aManager outage30 Server, VM ordatabase failure HA Auto-restart inpool Automatic failuredetection & recovery

MySQL Enterprise High AvailabilityWindows Server Failover Clustering Windows Server 2008 R2, MySQL 5.5** Quorum (3rd vote), data (InnoDB schema)& binaries (optional) stored in sharedstorage (testing iSCSI & FCAL) Failover:SlaveApp Planned maintenance MySQL service failureAppAppVirtual IP Connectivity, Host failures Loss of service couple of seconds InnoDB recovery time App sees temporary loss of connection andreconnects on same IP address Replication slave recovers automatically Cluster managed through MS FailoverCluster Management snap-in GUI No new software/scripts requiredVoteData** Technical support for Windows Server Failover Clustering must be sourced from Microsoft.31BinNew!

MySQL Enterprise Backup Online Backup for InnoDBFull, Incremental, Partial Backups (scriptable interface)CompressionPoint in Time, Full, Partial Recovery optionsMetadata on status, progress, historymysqlbackupUnlimited Database SizeCross-Platform Windows, Linux, UnixMEB BackupFiles Certified with Oracle Secure BackupMySQLDatabase FilesEnsures quick, online backup and recovery of your MySQL apps.32

MySQL Enterprise Monitor Global view of MySQLenvironment Automated, rules-basedmonitoring and alerts (SMTP,SNMP enabled) Query capture, monitoring,analysis and tuning, correlatedwith Monitor graphs Visual monitoring of “hot”applications and servers Real-time Replication Monitorwith auto-discovery of masterslave topologies Integrated with MySQL SupportA Virtual MySQL Tuning Assistant!33

MySQL Expert AdvisorsMySQL Cluster Monitors and Advises onstatus/ performance ofMySQL Cluster DataNodes.Administration Monitors and Advises onOptimal ConfigurationCustomPerformance Monitors and Adviseson OptimalPerformance VariableSettingsReplication Built by DBA toEnforce Organizationspecific best practices. Monitors and Advises onMaster/Slave Latency.UpgradeSecurity Monitors and Advises onBugs/Upgrades thataffect current installation Monitors and Adviseson Unplanned SecurityChanges/LoopholesMemory UsageSchema Monitors and Adviseson Unplanned SchemaChange Monitors and adviseson optimalmemory/cache settings140 Rules, 40 MySQL, OS specific GraphsSave you time writing, deploying, versioning, maintaining customscripts. Find problems and tuning opportunities you cannot find yourself.34

MySQL Query Analyzer Centralized monitoring of querieswithout Slow Query Log, SHOWPROCESSLIST; Enabled via MySQL Connectors Aggregated view of queryexecution counts, time, and rows Visual “grab and go” correlationwith Monitor graphs Traces query executions back tosource codeSaves you time parsing atomicexecutions from logs. Findsproblems you cannot find yourself.35

Query Execution Drill DownsExample query execwith variablesubstitutionTrace query execback to source codeFull exec EXPLAIN36

Automated Replication Monitor Auto-detects, groups/maintainsMaster/Slave topologies Consolidated, real timestatus/synch check Notifications on Synch Issues Proactive vs reactiveSaves you time monitoring andcollecting replication status/synchdata from MySQL command line.37

MySQL Enterprise MonitorIntegrated with My Oracle Support Collect MySQL diagnostics and upload to MySQL Support Track MOS SRs from Enterprise Dashboard38

MySQL Workbench SEDatabase Design Visual Design, modeling Forward/Reverse Engineer Schema validation, Schema docSQL Development SQL Editor - Color SyntaxHighlighting Objects - Import/Export, Browse/EditScripting & Plug-in SupportUI Designed to match VS 2010Saves you time developing andmanaging your MySQL apps. Connections - Wizard, SSH TunnelDatabase Administration Status, Configuration, Start/Stop Users, Security, Sessions Import/Export Dump Files39

MySQL Enterprise EditionNew!Enables you to manage your Oracle and MySQL databases withOracle tools/solutions you are already using.Oracle ProductCertificationsMySQL EnterpriseBackupOracle PremierSupportMySQL EnterpriseMonitor/Query AnalyzerMySQL EnterpriseSecurityMySQL EnterpriseScalabilityMySQL EnterpriseHigh AvailabilityMySQL Workbench40

MySQL Enterprise Oracle Certifications WebLogic ServerDatabase Adapter for Oracle SOA Suite **Oracle Business Process Management **Oracle Virtual DirectoryOracle Data IntegratorOracle Enterprise Performance Management **Oracle Identity AnalyticsOpen SSO STS, Open SSO Fedlet All ship with MySQL 5.x JDBC driver ** MySQL as a Metadata Repository option (in progress)41New!

MySQL Enterprise Oracle Certifications Oracle Linux Oracle VM Oracle VM Template forMySQL EE Oracle GoldenGate Oracle Secure Backup My Oracle Online SupportEnables you to manage your Oracle andMySQL databases with Oracle tools/solutionsyou are already using.42New!

Oracle Premier Support for MySQL 24 X 7 Problem ResolutionServices Unlimited Support Incidents Knowledge Base Maintenance Releases, Bugfixes, Patches, Updates MySQL Consultative Support Staffed by experienced,seasoned MySQL Engineers43

Tough Questions, Real Solutions performance issues? servers down? expensive, slow queries? security policies, change? MySQL Enterprise Scalability MySQL Enterprise Monitor, Advisors, Query Analyzer MySQL Consultative Support MySQL Enterprise Monitor, Query Analyzer Oracle Premier 7x24 Support MySQL Enterprise Monitor, Advisors MySQL Enterprise High Availability MySQL External Authentication MySQL Enterprise Monitor, Security AdvisorWhere/When/How to Tune? Developers productive? MySQL Enterprise Monitor, Advisors, Query Analyzer MySQL Consultative Support MySQL Workbench SE MySQL Enterprise Monitor, Query Analyzer Replication synch issues?.version of MySQL to run? MySQL Enterprise Monitor, Replication Monitor Oracle Premier 7x24 Support Oracle Premier 7x24 Support will systems scale? can I recover? MySQL Enterprise Scalability MySQL Enterprise Backup MySQL Enterprise Monitor, Advisors, Query Analyzer MySQL Consultative Support managing Oracle and MySQL databases? Oracle Product Certifications44

And Now for the esoteric stuff45

Mysqlnd - MySQL native driver for PHP Non-blocking, asynchronous queries 150 performance statistics Since 5.3 used for Windows, default as of 5.4Free PECL drop-in solutions for all PHP MySQL extensions: mysqlnd ms - replication and load balancing support *new* mysqlnd qc - client-side query cache (TTL) mysqlnd uh - swiss-armyknife to hack mysqlnd with PHPBetter licensing, easier to build and distribute46

47

MySQL 5.6 – Milestone releasesBinlog API, Binlog GroupCommit, GlobalTransactions Ids,InnoDB Features,InnoDB FTS,InnoDB/Memachced,Performance SchemaCluster 7.2 withMemcachedThese binaries were created by MySQLtesting servers.They are provided solely for testingpurposes, to try the latest bug fixesand generally to keep up with thedevelopment.Please, DO NOT USE THESEBINARIES IN PRODUCTION.Instead, install them on a spareserver.If you are looking for production readybinaries, please visit MySQLDownloads.MySQL Software is provided underthe GPL License48

Optimizer Tracing – Beyond EXPLAINSET optimizer trace "enabled on";SELECT Name FROM City WHEREID 999;SELECT trace into dumpfile '/tmp/foo'FROMINFORMATION SCHEMA.OPTIMIZER TRACE;Provides details ofoptimizer steps in aJSON format. You canthen tune49

Additional Resourcesmysql.com MySQL Products and EditionsTCO calculatorWhite PapersCustomer use cases and success storiesdev.mysql.com Downloads very.com Download and evaluate all MySQL products50

MySQL Embedded Online ForumTuesday, January 31, 2012 9:00AM PST - 12:00 PM PSThttp://bit.ly/w1bOwXThink You Know MySQL? Get the Facts.MySQL is even better than you think. According to the experts, it’s easier to use and it’s more capablethan most ISVs / OEMs realize, and by taking a few tried-and-tested steps before shipping your MySQLembedded products, you can lower your product’s costs and increase its performance.Join our expert-created and delivered MySQL Embedded Online Forum to learn: Why MySQL is a great embedded database for startups as well as the largest software, hardware, and appliancevendors in the world, and how its features ensure costs remain low throughout an application's life cycle. MySQL installation options that require minimal or zero end user effort and how to easily build them into yourapplication How to secure MySQL embedded in applications, appliances, and devices Tips to simplify your integration with MySQL using the most popular MySQL Connectors and guidance on selectingthe settings that will maximize your application’s performance Why MySQL delivers excellent performance and how to go beyond the default settings to optimize MySQL’s queries,reporting, and search capabilitiesAgenda:It Just Works! The Beauty of MySQL as an Embedded DatabaseBuilding MySQL Embedded for Simple Installation and SecurityCraig Sylvester , Principal MySQL Sales Engineer, OracleGetting the Most Out of MySQL Connectors for Better Performance and Streamlined IntegrationMatt Lord, Senior Principal MySQL Support Engineer, OracleBetter than Great: MySQL Embedded Performance and ReportingAlexander Rubin, MySQL Technical Consulting Manager, OracleCopyright Oracle Corporation 201251

MySQL Tech Tour EventSanta Clara, CAThursday, February 9, 2012http://www.oracle.com/go/?&Src 7314534&Act 247&pcode WWMK11042736MPP125El Segundo, CAWednesday, February 8, 2012http://www.oracle.com/go/?&Src 7314534&Act 249&pcode WWMK11042736MPP127Raleigh, NCThursday, February 2, 2012http://www.oracle.com/go/?&Src 7314534&Act 248&pcode WWMK11042736MPP126

Insert Picture Here Thanks for attending!David.Stokes@Oracle.com

Lifetime Support Oracle Premier Support Oracle Product Certifications MySQL Enterprise High Availability MySQL Enterprise Security MySQL Enterprise Scalability MySQL Enterprise Backup MySQL Enterprise Monitor/Query Analyzer MySQL Workbench MySQL Enterprise Edition. 11 MySQL Database

Related Documents:

MySQL for Excel is a 32-bit add-in for Microsoft Excel, which you can install and run on Microsoft Windows. MySQL for Excel is not compatible with Linux or macOS. MySQL for Excel can interact with MySQL Workbench to simplify the management of MySQL connections when both MySQL client tools are installed.

Configure MySQL Download the latest version of MySQL www.dev.mysql.com The default Apple install has errors Supposed to be fixed in 10.4.4 update Install Package, Startup Item, and Preference Pane Reboot server MySQL Administrator GUI application to edit MySQL users and settings Cocoa MySQL GUI application to edit .

Modul Pembelajaran Praktek Basis Data (MySQL) 2012 BAB I MENGENAL MySQL 1.1Mengenal MySQL MySQL adalah Sebuah program database server yang mampu menerima dan mengirimkan datanya sangat cepat, multi user serta menggunakan peintah dasar SQL ( Structured Query Language ). MySQL merupakan dua bentuk lisensi, yaitu FreeSoftware dan Shareware.

Various forms of mysQL exist, such as oracle mysQL, mariadb, Percona server for mysQL, Galera cluster for mysQL, and Galera cluster for mariadb. oracle mysQL community edition is a freely downloadable version. commercial

MySQL PHP Syntax MySQL works very well in combination of various programming languages like PERL, C, C , JAVA and PHP. Out of these languages, PHP is the most popular one because of its web application development capabilities. PHP provides various functions to access MySQL database and to manipulate data records inside MySQL database.

MySQL Performance Tuning &KDSWHU 10 MySQL settings to tune DIWHULQVWDOODWLRQ Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.

MySQL Tutorial MySQL is the most popular Open Source Relational SQL database management system. MySQL is one of the best RDBMS being used for developing web-based software applications. This tutorial will give you quick start with MySQL and make you comfortable with MySQL programming.

Andreas M unch and Endre S uli Mathematical Institute, University of Oxford Andrew Wiles Building, Radcli e Observatory Quarter, Woodstock Road Oxford OX2 6GG, UK Barbara Wagner Weierstrass Institute Mohrenstraˇe 39 10117 Berlin, Germany and Technische Universit at Berlin, Institute of Mathematics Straˇe des 17. Juni 136 10623 Berlin, Germany (Communicated by Thomas P. Witelski) Abstract .