MySQL für Oracle DBAsEinsatz von MySQL Wo wird MySQL eingesetzt: Facebook – 1 Mia User, 72 M QPS...

of 31/31
www.fromdual.com 1 / 31 MySQL für Oracle DBAs DOAG Webinar 14. Juni 2013 Oli Sennhauser Senior MySQL Consultant, FromDual GmbH [email protected]
  • date post

    24-Jul-2020
  • Category

    Documents

  • view

    1
  • download

    0

Embed Size (px)

Transcript of MySQL für Oracle DBAsEinsatz von MySQL Wo wird MySQL eingesetzt: Facebook – 1 Mia User, 72 M QPS...

  • www.fromdual.com

    1 / 31

    MySQL für Oracle DBAs

    DOAG Webinar 14. Juni 2013

    Oli SennhauserSenior MySQL Consultant, FromDual GmbH

    [email protected]

  • www.fromdual.com

    2 / 31

    Über FromDual GmbH● FromDual bietet neutral und unabhängig:

    ● Beratung für MySQL● Support für MySQL und Galera Cluster● Remote-DBA Dienstleistungen für MySQL● MySQL Schulungen

    ● Oracle Silver Partner (OPN)● Mitglied der SOUG, DOAG, /ch/open

    www.fromdual.com

    http://www.fromdual.com/

  • www.fromdual.com

    3 / 31

    InhaltHA Solutions➢ Read scale-out➢ Replication set-up for HA➢ Active/passive fail-over➢ MySQL Cluster➢ Replication Cluster➢ Storage-Engine-Replication

    MySQL für Oracle DBAs➢ Einsatz von MySQL➢ Installation, Konfiguration, Starten/Stoppen➢ Architektur, Storage Engines➢ InnoDB➢ Monitoring, Logging➢ Backup, Restore, Point-in-Time-Recovery➢ Replikation➢ Hochverfügbarkeit➢ RAC für MySQL

  • www.fromdual.com

    4 / 31

    Einsatz von MySQL● Wo wird MySQL eingesetzt:

    ● Facebook – 1 Mia User, 72 M QPS● Google – Adwords, Mia Umsatz/Jahr

    (M→O→M→F1)● Wikipedia – z. Zt. #6 weltweit● BörseGo – Online Börsenhandel● Playboy – Drupal CMS● EMKA – ERP, 1000 MA● V-Zug – Hybris Webshop● Buch.de – #2 online Buchhändler in D● Kikxxl – Callcenter, 1000 MA● Integrics – VoIP Lösungen, 1000e Anschlüssen● RePower – zig 1000 Windmühlen

  • www.fromdual.com

    5 / 31

    InstallationOracle: OUI (Oracle Universal Installer)

    ● MySQL: Windows: InstallerC:\Program files\mysql\mysqlserver5.6\C:\Program files\mysql\mysqlserver5.6\data

    ● MySQL Linux:● Pakete: *.rpm, *.deb/usr//var/lib/mysql

    ● Binary Tar-Ball: mysql5.7.1linuxx86_64.tar.gz● Quellen → Kompilieren: cmake; make; make install

    ● MySQL Community vs. Enterprise, Drittanbieter

    file:///C:/Programfile:///C:/Program

  • www.fromdual.com

    6 / 31

    MySQL Plattform

    ● „Exotische“ Plattformen führen aus statistischen Gründen eher zu Problemen!

    ● 85.7% Linux● 10.5% Windows● 1.7% Solaris● 1.4% BSD● 0.7% Others

  • www.fromdual.com

    7 / 31

    KonfigurationOracle: $ORACLE_HOME/dbs/init.ora

    ● MySQL:● Windows:C:\Program Files\mysql\mysqlserver5.6\my.ini

    ● Linux:/etc/my.cnf, /etc/mysql/my.cnf, $basedir/my.cnf

    ● !includedir /etc/mysql/conf.d/● defaultsfile, defaultsextrafile

    file:///C:/Program

  • www.fromdual.com

    8 / 31

    Konfigurations-Parameter

    ● my.cnf / my.ini● mysql> SHOW GLOBAL VARIABLES;

    ● 5.1.69: 277● 5.5.31: 317● 5.6.11: 429

    ● mysql> SET GLOBAL variable = value;

    ● Kein Persistieren (spfile)

  • www.fromdual.com

    9 / 31

    Starten / stoppenOracle: sqlplus / as sysdba STARTUP

    ● MySQL Linux:● Alt: shell> /etc/init.d/mysql start|stop|restart● Neu: shell> service mysql start|stop|restart● „von Hand“:

    ● shell> bin/mysqld_safe &● shell> bin/mysqld &● shell> mysqladmin user=root shutdown

    ● MySQL Windows● Windows Service Utility● cmd> net start|stop|restart MySQL

  • www.fromdual.com

    10 / 31

    Tools● Tools:

    ● sqlplus → mysql● srvmgrl → mysqladmin

    ● MySQL Workbench● Admin● Query Browser● ER - Diagramme

    ● Heidi SQL, phpMyAdmin

  • www.fromdual.com

    11 / 31

    Prozess-Architektur

    Oracle: Multi-Prozess ModellShared MemoryPMON, SMON, RECO, DBW0, LGWR, ARC0, ...

    ● MySQL: Multi-Thread Modellmysqld

    ● Angel-Prozessmysqld_safe

    ● Vordergrund- und Hintergrund-Threads:

  • www.fromdual.com

    12 / 31

    MySQL Thread Architektur

    mysql> SELECT thread_id, name AS 'thread_name', type, processlist_user AS user FROM performance_schema.threads;+-----------+----------------------------------------+------------+------+| thread_id | thread_name | type | user |+-----------+----------------------------------------+------------+------+| 1 | thread/sql/main | BACKGROUND | NULL || 2 | thread/innodb/io_ibuf_thread | BACKGROUND | NULL || 3 | thread/innodb/io_log_thread | BACKGROUND | NULL || 4 | thread/innodb/io_read_thread | BACKGROUND | NULL || 11 | thread/innodb/io_write_thread | BACKGROUND | NULL || 14 | thread/innodb/srv_error_monitor_thread | BACKGROUND | NULL || 15 | thread/innodb/srv_lock_timeout_thread | BACKGROUND | NULL || 16 | thread/innodb/srv_monitor_thread | BACKGROUND | NULL || 17 | thread/innodb/srv_master_thread | BACKGROUND | NULL || 18 | thread/innodb/srv_purge_thread | BACKGROUND | NULL || 19 | thread/innodb/page_cleaner_thread | BACKGROUND | NULL || 20 | thread/sql/signal_handler | BACKGROUND | NULL || 22 | thread/sql/one_connection | FOREGROUND | root || 28 | thread/sql/one_connection | FOREGROUND | oli |+-----------+----------------------------------------+------------+------+

    shell> ps -efL | egrep 'mysqld|PID'UID PID PPID LWP C NLWP STIME TTY TIME CMDmysql 3248 1 3248 0 1 16:28 pts/0 00:00:00 /bin/sh bin/mysqld_safemysql 3925 3248 3925 0 23 16:28 pts/0 00:00:00 bin/mysqld ...mysql 3925 3248 4088 0 23 16:31 pts/0 00:00:00 bin/mysqld

  • www.fromdual.com

    13 / 31

    Connections / Connectors● Verbindung

    ● In MySQL billig: oft KEIN Connection-Pooling● 1 Verbindung = 1 Thread → 1 Query → 1 Core● Thread Pool (1000e von Verbindungen)

    ● Connectors:● JDBC/ODBC● PHP, Perl, Python, Ruby, .NET

  • www.fromdual.com

    14 / 31

    User und Schema● User

    ● 'oli'@'localhost' → Unix Socket● 'oli'@'127.0.0.1' → TCP Port● 'oli'@'%' → TCP von überall her

    ● Privilegien● Global: *.*, pro Schema , pro Tabelle, pro Spalte

    ● Schema (= Database)● Unabhängig vom User (→ gehört System)

  • www.fromdual.com

    15 / 31

    Storage Engines

    mysqld

    Application / Client

    ThreadCache

    ConnectionManager

    User Au-thentication

    CommandDispatcherLogging

    Query CacheModule

    QueryCache

    Parser

    Optimizer

    Access Control

    Table Manager

    Table OpenCache (.frm, fh)

    Table DefinitionCache (tbl def.)

    Handler Interface

    MyISAM Memory NDB TokutekInnoDB ...Aria Bright-house Federated-X

  • www.fromdual.com

    16 / 31

    InnoDB (default SE >= 5.5.)● Transaktionen (ACID)● Isolation Level (repeatable-read vs read-committed)● Tabelspaces

    ● System TS = ibdata1● Table TS (innodb_files_per_table = 1)

    ● InnoDB: PK geclusterte Tabellen → IOT● InnoDB Buffer Pool (16k Block Buffer)● REDO Logs: ib_logfile? (5M default)

  • www.fromdual.com

    17 / 31

    Logging● Error Log (~ alert_.log)

    ● /var/log/mysql*

    ● General Query Loggeneral_log = 1

    ● Slow Query Log● slow_query_log = 1

    ● Binary Log (~ archive.log)● log_bin = 1 / binarylog● DML + DDL aller SE

    ● (Transaktions-Log, ib_logfile?, SE abhängig) (~ redo.log)● DML InnoDB

  • www.fromdual.com

    18 / 31

    Logisches Backup

    Oracle: exp/imp (vor Datapump)● MySQL: mysqldump/mysql● Jede Row wird angelangt:

    ● Langsam● Restore SEHR langsam!mysql> mysqldump alldatabases singletransaction masterdata=1 > full_dump.sql

    mysql> mysql 

  • www.fromdual.com

    19 / 31

    Physisches BackupOracle: rman, ALTER TABLESPACE ... BEGIN|END BACKUP;

    ● MySQL:● Snapshot mit LVM, BtreeFS oder ZFS

    ● mylvmbackup● Xtrabackup, MySQL Enterprise Backup (MEB)

    shell> innobackupex /data/backups

    shell> innobackupex applylog /data/backups/20121113/

    shell> innobackupex copyback /data/backups/20121113/

    shell> chown R mysql:mysql /var/lib/mysql

    ● Links:http://www.lenzg.net/mylvmbackup/

    ● http://www.percona.com/doc/percona-xtrabackup

    http://www.lenzg.net/mylvmbackup/

  • www.fromdual.com

    20 / 31

    Binary Log~ Oracle Archive Logs

    ● MySQL Binary-Log:● DDL + DML aller SE (nicht nur InnoDB)!

    ● für:● Replikation● Point-in-Time-Recovery (PiTR)

    ● 3 Varianten:● Statement Based Replication (SBR)

  • www.fromdual.com

    21 / 31

    Point-in-Time-Recovery (PITR)

    Application ApplicationApplication

    mysqld

    binarylog

    writerthread

    bin-log.1 bin-log.2 bin-log.n...

    log_bin = on

    t

    full

    back

    up

    pos/time?

  • www.fromdual.com

    22 / 31

    MySQL Replikation● MySQL Basis-Fuktionalität● Sehr einfach aufzusetzen (5 min)● Streaming-Replication (kein Log Shipping)● Basiert auf MySQL Binary Logs● Braucht:

    ● Unique server_id (Master und Slave) (restart)● Binary Loggin auf Master einschalten (restart)● Replikations-User● Konsistentes Backup + Binary Log Position

  • www.fromdual.com

    23 / 31

    ...

    Master – Slave Replikation

    Application

    Master

    log_bin = onserver_id = 42

    Slave

    ● Wir brauchen:● Binary Log● Server Id● User für die Replikation (auf dem Master)● Konsistentes Backup MIT Binary Log Position

    bin-log.m bin-log.n relay-log.m relay-log.n...

    IO_thread

    SQL_thread

  • www.fromdual.com

    24 / 31

    High-Availability mit Replikation

    Application

    Master

    Slave Backup

    Slave Reporting

    rtw

    Load balancer

    read only

    Slave 1

    Slave 2

    Slave 3 ...

    async!Slave

    M

    VIP

  • www.fromdual.com

    25 / 31

    RAC: Galera Cluster

    App App App

    Load balancing (LB)

    Node 2 Node 3Node 1wsrep

    Galera replicationwsrep wsrep

    rwrw

    Oracle Real Application Cluster (RAC)● MySQL: Galera Cluster

    ● Shared-Nothing Architektur

  • www.fromdual.com

    26 / 31

    Galera Cluster für MySQL

    App App App

    Load balancing (LB)

    Node 2 Node 3Node 1wsrep

    Galera replicationwsrep wsrep

    ● Hardware-Ausfall● Wartungsarbeiten

    ● HW/OS/DB Upgrade● 5x9 HA: 99.999%

  • www.fromdual.com

    27 / 31

    Monitoring● OEM/DBC/Grid-Control/Cloud-Control● MySQL Enterprise Monitor (MEM) €€€● MySQL Performance Monitor (mpm)● mysql> SHOW GLOBAL STATUS;● Nagios / Icinga● Links:

    http://www.mysql.com/products/enterprise/monitor.htmlhttp://www.fromdual.com/mysql-performance-monitorhttp://www.fromdual.com/download#nagios

    http://www.fromdual.com/mysql-performance-monitor

  • www.fromdual.com

    28 / 31

    Performance Tuning● mysql> SHOW GLOBAL STATUS;

    ● PERFORMANCE_SCHEMA● Slow Query Log

    ● slow_query_log = 1● long_query_time = 0.5● shell> mysqldumpslow s t slow.log > profile

    ● Query Execution Plan:mysql> EXPLAIN SELECT * FROM test;

  • www.fromdual.com

    29 / 31

    Stored Programs

    Oracle: PL/SQL, Java● MySQL:

    ● Stored Procedures● Stored Functions● User Defined Functions (UDF): C/C++● Plugin: C/C++

  • www.fromdual.com

    30 / 31

    Volltext-Suche

    Oracle: Kostenpflichtiges Modul?● MySQL: Standardmässig dabei!

    ALTER TABLE test ADD FULLTEXT INDEX (data);SELECT * FROM test WHERE MATCH data AGAINST('DBA');+----+----------------------------------------+| id | data |+----+----------------------------------------+| 1 | Wir suchen zur Zeit einen Support DBA! |+----+----------------------------------------+

  • www.fromdual.com

    31 / 31

    Q & A

    Fragen ?

    Diskussion?

    Wir haben Zeit für ein persönliches Gespräch...

    ● FromDual bietet neutral und unabhängig:● Beratung● Remote-DBA● Support für MySQL, Galera, Percona Server und MariaDB● Schulung

    www.fromdual.com/presentations

    Slide 1Slide 2Slide 3Slide 4Slide 5Slide 6Slide 7Slide 8Slide 9Slide 10Slide 11Slide 12Slide 13Slide 14Slide 15Slide 16Slide 17Slide 18Slide 19Slide 20Slide 21Slide 22Slide 23Slide 24Slide 25Slide 26Slide 27Slide 28Slide 29Slide 30Slide 31