Download
slide1 n.
Skip this Video
Loading SlideShow in 5 Seconds..
MySQL Administration and Monitoring PowerPoint Presentation
Download Presentation
MySQL Administration and Monitoring

MySQL Administration and Monitoring

121 Views Download Presentation
Download Presentation

MySQL Administration and Monitoring

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. MySQL Administration andMonitoring Mark LeithSenior Software Development Manager @ Oracle

  2. Program Agenda • Basic Command Line Tools • Internal Schemas • Backup / Recovery • GUI Tools / Scripts • Monitoring

  3. Basic Command Line Tools

  4. Standard MySQL Client Programs http://dev.mysql.com/doc/refman/5.5/en/programs-client.html

  5. Standard MySQL Client Programs Options http://dev.mysql.com/doc/refman/5.5/en/command-line-options.html

  6. MySQL Startup Programs http://dev.mysql.com/doc/refman/5.6/en/programs-server.html

  7. Internal Schemas / Scripts

  8. mysql • The main system schema (a kind of Data Dictionary) • All user and privilege configuration • Stored programs (Procedures, Functions, Events) • Time Zone information • Also in 5.6 • InnoDB Table Index Statistics • Replication State http://dev.mysql.com/doc/refman/5.6/en/grant-table-structure.html

  9. INFORMATION_SCHEMA • ANSI SQL (SQL:2003) – general access to database metadata • Tables, indexes, constraints, views, triggers, routines, privileges etc. • Also contains various extensions within MySQL • Many of these are runtime metadata • Processlist, system / status variables, InnoDB runtime data • System Views (not real views, not real tables, generated on the fly) http://dev.mysql.com/doc/refman/5.6/en/information-schema.html

  10. Performance Schema • MySQL's “Wait Interface” • Instruments various wait events • File IO / Table IO / Network IO • Mutexes (Semaphores) / R/W Locks / Conditions • Table Locks • Statements and Stages (connection states of execution) http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html

  11. Performance Schema – Raw Wait Event mysql> select * from performance_schema.events_waits_history limit 1\G *************************** 1. row *************************** THREAD_ID: 3 EVENT_ID: 11 END_EVENT_ID: 11 EVENT_NAME: wait/io/file/innodb/innodb_log_file SOURCE: os0file.cc:5542 TIMER_START: 2543370511043700 TIMER_END: 2543370535624100 TIMER_WAIT: 24580400 SPINS: NULL ...

  12. Performance Schema – Raw Wait Event Cont.. mysql> select * from performance_schema.events_waits_history limit 1\G *************************** 1. row *************************** ... OBJECT_SCHEMA: NULL OBJECT_NAME: /Users/mark/sb/msb_5_7_2/data/ib_logfile0 INDEX_NAME: NULL OBJECT_TYPE: FILE OBJECT_INSTANCE_BEGIN: 4771328832 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL OPERATION: write NUMBER_OF_BYTES: 512 FLAGS: NULL

  13. Performance Schema – Raw Stage Event mysql> select * from performance_schema.events_stages_history_long limit 1\G *************************** 1. row *************************** THREAD_ID: 25 EVENT_ID: 5199518 END_EVENT_ID: 5199536 EVENT_NAME: stage/sql/System lock SOURCE: lock.cc:304 TIMER_START: 280557566125986000 TIMER_END: 280557566190403000 TIMER_WAIT: 64417000 NESTING_EVENT_ID: 5199509 NESTING_EVENT_TYPE: STATEMENT

  14. Performance Schema – Raw Statement Event mysql> select * from performance_schema.events_statements_history_long limit 1\G *************************** 1. row *************************** THREAD_ID: 24 EVENT_ID: 3923 END_EVENT_ID: 4044 EVENT_NAME: statement/sql/insert_select SOURCE: mysqld.cc:931 TIMER_START: 251016737474892000 TIMER_END: 251016738730372000 TIMER_WAIT: 1255480000 LOCK_TIME: 573000000 ...

  15. Performance Schema – Raw Statement Event mysql> select * from performance_schema.events_statements_history_long limit 1\G *************************** 1. row *************************** ... SQL_TEXT: insert into t2 select * from t1 DIGEST: e6f8db8a3f557ffbb2bf6a7b237cd897 DIGEST_TEXT: INSERT INTO `t2` SELECT * FROM `t1` CURRENT_SCHEMA: test OBJECT_TYPE: NULL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: NULL ...

  16. Performance Schema – Raw Statement Event mysql> select * from performance_schema.events_statements_history_long limit 1\G *************************** 1. row *************************** ... MYSQL_ERRNO: 0 RETURNED_SQLSTATE: 00000 MESSAGE_TEXT: Records: 5 Duplicates: 0 Warnings: 0 ERRORS: 0 WARNINGS: 0 ROWS_AFFECTED: 5 ROWS_SENT: 0 ROWS_EXAMINED: 5 ...

  17. Performance Schema – Raw Statement Event mysql> select * from performance_schema.events_statements_history_long limit 1\G *************************** 1. row *************************** ... CREATED_TMP_DISK_TABLES: 0 CREATED_TMP_TABLES: 0 SELECT_FULL_JOIN: 0 SELECT_FULL_RANGE_JOIN: 0 SELECT_RANGE: 0 SELECT_RANGE_CHECK: 0 SELECT_SCAN: 1 ...

  18. Performance Schema – Raw Statement Event mysql> select * from performance_schema.events_statements_history_long limit 1\G *************************** 1. row *************************** ... SORT_MERGE_PASSES: 0 SORT_RANGE: 0 SORT_ROWS: 0 SORT_SCAN: 0 NO_INDEX_USED: 1 NO_GOOD_INDEX_USED: 0 NESTING_EVENT_ID: NULL NESTING_EVENT_TYPE: NULL

  19. Backup / Recovery

  20. MySQL Backup Tools • Cold Backup (Offline, Physical) • Simple file copy when instance down • Warm Backup (Locking, Logical) • mysqldump • Standby Copy (Hot swappable) • MySQL Replication • Hot Backup (Online, Physical) • MySQL Enterprise Backup, mysqlbackup

  21. MySQL Backup Tools – Cold Backup • Who wants to do this anyway..? Moving on....

  22. MySQL Backup Tools – Warm Backup • Advantages for mysqldump • Good for small databases / tables • Logical, so more flexible and portable • Disadvantages for mysqldump • Single thread for dump and restore, slow for larger instances • Not online, requires a transaction (for InnoDB) or global lock (for all other non-transactional tables) • Not incremental (needs binary logs as well, complex recovery)

  23. MySQL Backup Tools – Hot Standby • Advantages for MySQL Replication • Rolling snapshot • Very quick failover • Standby can be used for other backup types • Disadvantages for MySQL Replication • Only latest point in time (can use delayed replication with 5.6) • Not incremental (needs binary logs as well, complex recovery) • Can't archive (offsite, though can have an offsite standby)

  24. MySQL Backup Tools – Hot Backup • Advantages for MySQL Enterprise Backup • Physical Backup, so very quick (especially restore) • Ties in natively with Oracle Secure Backup to Tape (SBT) • Multi-Thread for Performance • Incremental Backups available • Compression Built-In • Disadvantages for MySQL Enterprise Backup • No logical option (good to have a periodic mysqldump too)

  25. GUI Tools / Scripts

  26. MySQL Workbench • General purpose DBA / Developer Tool • MySQL Instance Administration • SQL Development • Data Modeling • Commercial Extensions • Audit Log Inspection • Hot Backup

  27. MySQL Workbench – Instance Overview

  28. MySQL Workbench – Current Connections

  29. MySQL Workbench – User Management

  30. MySQL Workbench – Config Management

  31. MySQL Workbench – Modeling

  32. MySQL Workbench – Online Backup

  33. Monitoring

  34. MySQL Enterprise Monitor • Distributed Monitoring for MySQL Environments • Intelligent analysis of key metrics / configuration • Historical reporting • Query Analysis

  35. MEM – Overview Dashboard

  36. MEM - Advisors

  37. MEM – Overview Dashboard

  38. MEM - Advisors

  39. MEM - Events

  40. MEM – Event Details

  41. MEM – Timeseries Graphs

  42. MEM – Query Analysis Overview

  43. MEM – Query Analysis Query Details

  44. Questions?