1 / 47

MySQL Administration and Monitoring

MySQL Administration and Monitoring. Mark Leith Senior Software Development Manager @ Oracle. Program Agenda. Basic Command Line Tools Internal Schemas Backup / Recovery GUI Tools / Scripts Monitoring. Basic Command Line Tools. Standard MySQL Client Programs.

mollie-sargent
Download Presentation

MySQL Administration and Monitoring

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

More Related