of 36 /36
<Insert Picture Here> MySQL professionell betreiben Mario Beck October, 2010

MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Embed Size (px)

Citation preview

Page 1: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

<Insert Picture Here>

MySQL professionell betreibenMario BeckOctober, 2010

Page 2: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

MySQL Kunden

MySQL ist die treibende Kraft für das Web

Enterprise 2.0TelekommunikationOn Demand, SaaS, Hosting

Web / Web 2.0 OEM / ISV's

Page 3: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Puma

AnwendungOnline Shop; Verwaltung von Produkt- und Kundendaten sowie Warenkörben

HauptvorteilGeringe Kosten, einfache Installation und Nutzung

Warum MySQL? “MySQL Enterprise mit dem MySQL

Enterprise Monitor und den umfangreichen Dienstleistungen ist für uns eine optimale Lösung zum Betrieb und Ausbau unseres Online-Shops.“

Christian BurgerIT Manager

Puma

Page 4: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Cashpoint

AnwendungPortal für Online-Wetten und -Spiele auf MySQL Cluster & MySQL Enterprise

HauptvorteilHöhere Performanz und Skalierbarkeit. Zuvor eingesetzte Sybase Datenbank genügte den Anforderungen nicht mehr.

Warum MySQL? “Neben einer jährlichen Einsparung von

250.000 Euro für Lizenzen konnte Cashpoint 150.000 Euro an Server- und 90.000 Euro an Personalkosten einsparen.“

Thomas SchlinkSystemadministrator

Cashpoint Agentur & IT Service GmbH

Page 5: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

RZ der Finanzverw. Nordrhein-Westfalen

AnwendungSteuer- und Finanzwanwendungen, derzeit über 260 MySQL Server im Einsatz

HauptvorteilNiedrige Kosten, einfache Nutzung, hohe Zuverlässigkeit

Warum MySQL? “Mit MySQL Enterprise können wir

kosteneffizient neue Anwendungen entwickeln, betreiben sowie alle relevanten Datenbankfunktionen übersichtlich und sehr einfach überwachen.“

Guido KutasiDezernent Datenbanken

RZ der Finanzverw. Nordrhein-Westfalen

Page 6: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Security

Performance

Availability

• Maximum Performance per Server

• Low Ressource Consumption

• Scaling Options

• Stable Software

• HA Options for different Needs

• Find Problems BEFORE they occur

• Correct Code

• Only Authorized Access

• Up-to-date Security Fixes

MySQL – Professional Requirements

Page 7: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

What Pros are dreaming of...

Open-source serverwith pluggable APIs

Proactive Monitoring

BestPractices

7x24Support

OnlineBackup

KnowledgableConsultants

Easy QueryAnalysis

EasyDevTools

Page 8: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Revenue and Productivity

Applications/Users/Customers

• 1 minute downtime = Lost $• DBA/Dev time on strategy/projects

- eCommerce Times

“I’ll fix it myself”Spend time, save

money

“I’ll hire more DBAs!”

Spend more time, save less

money

Outage = Loss

“We need help!”Spend money,

save time

What is at risk?

Page 9: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

What Pros are dreaming of...

Open-source serverwith pluggable APIs

Proactive Monitoring

BestPractices

7x24Support

OnlineBackup

KnowledgableConsultants

Easy QueryAnalysis

EasyDevTools

Page 10: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

What Pros are dreaming of...

Open-source serverwith pluggable APIs

Proactive Monitoring

BestPractices

OnlineBackup

KnowledgableConsultants

Easy QueryAnalysis

EasyDevTools

Premium Support

Page 11: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

• Self Help Support Service• Online Knowledge Base

• Problem Resolution Service• Seasoned Support Team• 24 X 7 email/phone access

• Consultative Support Services• Remote Troubleshooting Service• Configuration and Tuning Services• Customer Code Review Services

Production Support from Oracle

Page 12: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Premium Support

What Pros are dreaming of...

Open-source serverwith pluggable APIs

Proactive Monitoring

BestPractices

OnlineBackup

Easy QueryAnalysis

EasyDevTools

Professional Service

Page 13: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

• MySQL High Availability Solutions• MySQL Scale-Out, HA and

Replication Jumpstart• MySQL Architecture & Design• MySQL Cluster Jumpstart• MySQL Project

Implementations/Migrations

MySQL Professional Services

Page 14: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Professional Service

Premium Support

What Pros are dreaming of...

Open-source serverwith pluggable APIs

Proactive Monitoring

OnlineBackup

Easy QueryAnalysis

EasyDevTools

Knowledge Base

Page 15: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

MySQL Knowledge Base

• Written and reviewed by MySQL experts• Best practices, How-to’s, Performance Tuning, ...• Fully categorized with easy search capabilities • Integrated with Monitoring and Advisory Service

Failing over a Slave SQL Node to another SQL Master node

Heartbeat Configuration

DRBD Configuration

Can I use LVM on top of DRBD?

Advanced MySQL Replication Techniques

How can a slave be set to not replicate DROP and DELETE statements?

How to restore a Cluster Slave using its own backups.

How do I check if Master and Slave are in sync?

How do I configure a slave if the master is running and I do not want to stop it?

How do I replicate a cluster?

How do I convert a CSV table to another table type?

How can MS Access be used as a front-end for MySQL?

How do I Migrate Data From Microsoft Access to MySQL?

How do I import data into MySQL from a simple text file?

Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

How can I add to an existing InnoDB data file?

How can I determine how much disk space an InnoDB table uses?

How do I tune the MyISAM key buffer and InnoDB buffer pool in MySQL?

Using Replication-Aware Connections with MySQL and Connector/J

Should I use Connector/NET or Connector/ODBC to develop my application?

Understanding and using connection pooling

...

Page 16: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Knowledge Base

Professional Service

Premium Support

What Pros are dreaming of...

Open-source serverwith pluggable APIs

Proactive Monitoring

OnlineBackup

Easy QueryAnalysis

Workbench SE

Page 17: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

MySQL Workbench – Home Screen

Page 18: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

MySQL Workbench – Home Screen

MySQL Workbench

für Einsteiger

16:00, Raum Krakau

Page 19: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Workbench SE

Knowledge Base

Professional Service

Premium Support

What Pros are dreaming of...

Open-source serverwith pluggable APIs

Proactive Monitoring

Easy QueryAnalysis

Enterprise Backup

Page 20: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

MySQL Enterprise Backup

Formerly “InnoDB Hot Backup”Online, non-locking backup & recovery

– Tables, Indexes– Server, database, or object-level

Logical and physical backupsFull or incremental backupsPoint-in-time recoveryCompressed backupsAlso provides backup & recovery for MyISAMCross-Platform (Windows, Linux, Unix)

Page 21: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Enterprise Backup

Workbench SE

Knowledge Base

Professional Service

Premium Support

What Pros are dreaming of...

Open-source serverwith pluggable APIs

Easy QueryAnalysis

Enterprise Monitor

Page 22: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

• Single, consolidated view into entire MySQL environment

• Automated, rules-based monitoring and alerts (SMTP, SNMP enabled)

• Query capture, monitoring, analysis and tuning, correlated with Monitor graphs

• Visual monitoring of “hot” applications and servers

• Real-time Replication Monitor with auto-discovery of master-slave topologies

• Integrated with MyOracle Support

A Virtual MySQL DBA Assistant!

MySQL Enterprise Monitor

Page 23: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

MEM Deployment DiagramMySQL Enterprise Monitor Deployment

Page 24: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Monitoring with Rules

Page 25: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

MySQL Expert Advisors

Save you time writing, deploying, versioning, maintaining custom scripts. Visualize problems and tuning opportunities you cannot find yourself.

140+ Rules, 40+ Graphs

Administration• Monitors and Advises on

Optimal Configuration

Security• Monitors and Advises

on Unplanned Security Changes/Loopholes

Upgrade• Monitors and Advises on

Bugs/Upgrades that affect current installation

Replication

• Monitors and Advises on Master/Slave Latency.

Memory Usage

• Monitors and advises on optimal memory/cache settings

Schema

• Monitors and Advises on Unplanned Schema Change

Performance

• Monitors and Advises on Optimal Performance Variable Settings

• Built by DBA to Enforce Organization specific best practices.

Custom

MySQL Cluster• Monitors and Advises on

status/ performance of MySQL Cluster Data Nodes.

Page 26: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Saves you time monitoring and collecting replication status/synch data from MySQL command line.

• Auto-detects, groups/maintains Master/Slave topologies• Consolidated, real time

status/synch check• Notifications on Synch

Issues

Automated Replication Monitor

Page 27: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Graphs

- 40+ graphs immediately visualize system behaviour- MySQL and OS data available

Page 28: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Integration with MyOracle Support

MySQL Enterprise Monitor • Collect MySQL diagnostics and upload to MOS SR

• Track SRs from Enterprise Dashboard

• Saves DBA time

• collecting data for Support team, tracking status

Page 29: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Enterprise Monitor

Enterprise Backup

Workbench SE

Knowledge Base

Professional Service

Premium Support

What Pros are dreaming of...

Open-source serverwith pluggable APIs

Query Analyzer

Page 30: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

• Centralized monitoring of Queries across all servers• No reliance on Slow Query

Logs, SHOW PROCESSLIST;, VMSTAT, etc.• Aggregated view of query

execution counts, time, and rows• Saves time parsing atomic

executions for total query expense• Visual “grab and go”

correlation with Monitor graphs

MySQL Query Analyzer

Page 31: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Architecture

3. MySQL Connector

4. Connector Plugin

Page 32: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Query Execution Drill Downs Example query exec

with variable substitution

Trace query exec back to source code

Full exec EXPLAIN

Page 33: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Relief for Administrators

Easy browser UI and Integration in SNMP frameworks

• quick overview, easy to use, same tools as always

140+ pre-defined rules enforce best-practice administration• No more waste of time to develop monitoring, adapt to new version, …

Regular rule updates to monitor new features• Fast adoption of new features without much effort

Comrehensive documentation• Assistance for junior MySQL admins

Query Analyzer• Easier to spot application problems

Support integration• Save time in handling support issues, faster solution, better uptime

Page 34: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

What Pros are dreaming of...

Open-source serverwith pluggable APIs Premium Support

Professional Service

Knowledge Base

Enterprise Backup

Enterprise Monitor

Workbench SE

Query Analyzer

Page 35: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

Oracle + MySQL Customers

• Product Integration

• Oracle GoldenGate (Complete!)• Oracle Enterprise Linux + Oracle VM (CY 2011)• Oracle Secure Backup (CY 2011)• Oracle Audit Vault (CY 2011)• Oracle Enterprise Manager (CY 2011)

• Support

• Leverage 24x7, World-Class Oracle Support• MyOracle Support

Page 36: MySQL Enterprise - October, 2010 · October, 2010. MySQL Kunden MySQL ... Revenue and Productivity ... Debugging InnoDB Locks using the new InnoDB Plugin’s Information Schema Tables

[email protected]

Thank You!