MySQL For Linux Sysadmins
•
2 likes•1,610 views
This document provides an overview of MySQL for Linux system administrators. It discusses MySQL architecture including storage engines, memory usage, the MySQL server process, and InnoDB transaction processing. It also covers topics like backups and replication, and the agenda includes performance and capacity planning. The goal is to help system administrators understand and manage MySQL databases.
Report
Share
More Related Content
What's hot
What's hot (20)
Viewers also liked
Viewers also liked (19)
Similar to MySQL For Linux Sysadmins
Similar to MySQL For Linux Sysadmins (20)
More from Morgan Tocker
More from Morgan Tocker (11)
Recently uploaded
Recently uploaded (20)
MySQL For Linux Sysadmins
- 2. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | MySQL For Linux Sysadmins Morgan Tocker MySQL Community Manager August, 2014
- 3. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Safe Harbor Statement The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle’s products remains at the sole discretion of Oracle. Oracle Confidential – Internal/Restricted/Highly Restricted 3
- 4. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 4 Program Agenda Introduction MySQL Architecture Backup & Replication Performance & Capacity Planning 1 2 3 4
- 5. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | The Career Path to DBA • Start as a Developer • Start as a Sysadmin • Train to be a DBA 5 Usually either #1 or #2. I started as a Developer }
- 6. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Non Exhaustive List of Duties • Backup • Replication • Patching & Upgrades • Performance and Capacity Planning • Coaching & Mentoring • Fighting Fires 6
- 7. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 7 Program Agenda Introduction MySQL Architecture Backup & Replication Performance & Capacity Planning 1 2 3 4 2 1
- 8. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | About MySQL • Version numbers are misleading: • 5.5 to 5.6 was the biggest upgrade in history • 5.6.xx • Micro-‐release approximately every 2 months. • Fixes bugs and security vulnerabilities. 8
- 9. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | About MySQL (cont.) • 5.7.xx • Current focus of development. • Release cycle is 2-‐3 years. • 5.5 and 5.6 are under active support. 9
- 10. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 10
- 11. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Storage Engines in MySQL • InnoDB (default) • MyISAM (previous default) • NDB Cluster • Other 11
- 12. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Memory Usage • Databases like to allocate large amounts of memory for caches • Better to run with smaller caches than swap • Kind of works as “double buffering” of operating system caches 12
- 13. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted MySQL Server • Single process of mysqld • Supervisor process is mysqld_safe • Each new MySQL connection consumes a thread • With the server maintaining a small thread cache 13
- 14. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 14 Transaction System StorageCaching SYS_TABLES ibdata1 space0 Page Cache A.ibd B.ibd C.ibd IBUF_HEADER IBUF_TREE TRX_SYS FIRST_RSEG DICT_HDR Data Dict. SYS_COLUMNS SYS_INDEXES SYS_FIELDS Block 1 (64 pages) Block 2 (64 pages) iblogfile0 iblogfile1 iblogfile2 Tables with file_per_tableDoublewrite Buffer BufferPool Data Dictionary Cache Adaptive Hash Indexes Buffer Pool LRU Additional Mem Pool Log Buffer LogGroup Buffer Pool Flush List In Memory On Diskhttps://github.com/jeremycole/innodb_diagrams
- 15. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 15 InnoDBTransaction System StorageCaching SYS_TABLES ibdata1 space0 Page Cache A.ibd B.ibd C.ibd IBUF_HEADER IBUF_TREE TRX_SYS FIRST_RSEG DICT_HDR Data Dict. SYS_COLUMNS SYS_INDEXES SYS_FIELDS Block 1 (64 pages) Block 2 (64 pages) iblogfile0 iblogfile1 iblogfile2 Tables with file_per_tableDoublewrite Buffer BufferPool Data Dictionary Cache Adaptive Hash Indexes Buffer Pool LRU Additional Mem Pool Log Buffer LogGroup Buffer Pool Flush List SELECT * FROM a WHERE id = 10; mysqld Not Found Query -‐ Non Cached
- 16. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 16 InnoDBTransaction System StorageCaching SYS_TABLES ibdata1 space0 Page Cache A.ibd B.ibd C.ibd IBUF_HEADER IBUF_TREE TRX_SYS FIRST_RSEG DICT_HDR Data Dict. SYS_COLUMNS SYS_INDEXES SYS_FIELDS Block 1 (64 pages) Block 2 (64 pages) iblogfile0 iblogfile1 iblogfile2 Tables with file_per_tableDoublewrite Buffer BufferPool Data Dictionary Cache Adaptive Hash Indexes Buffer Pool LRU Additional Mem Pool Log Buffer LogGroup Buffer Pool Flush List SELECT * FROM a WHERE id = 10; mysqld Query -‐ Cached
- 17. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 17 Update Query in a Transaction (simplified) InnoDB Transaction System StorageCaching SYS_TABLES ibdata1 space0 Page Cache A.ibd B.ibd C.ibd IBUF_HEADER IBUF_TREE TRX_SYS FIRST_RSEG DICT_HDR Data Dict. SYS_COLUMNS SYS_INDEXES SYS_FIELDS Block 1 (64 pages) Block 2 (64 pages) iblogfile0 iblogfile1 iblogfile2 Tables with file_per_tableDoublewrite Buffer BufferPool Data Dictionary Cache Adaptive Hash Indexes Buffer Pool LRU Additional Mem Pool Log Buffer LogGroup Buffer Pool Flush List UPDATE a SET col1 = ‘new’ WHERE id = 10; mysqld commit;
- 18. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Log Files • Provide recovery. • Only written to in regular operation. • Read only required if there is a crash. • Are rewritten over-‐and-‐over again. • Think of it like a tank tread. 18
- 19. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 19 Program Agenda Introduction MySQL Architecture Backup & Replication Performance & Capacity Planning 1 2 3 4 1 3
- 20. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Backup (Theory) • Cold • Warm • Hot 20
- 21. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Backup Consistency • Must represent a single point in time 21
- 22. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 22 customerid name 1 Acorn Enterprises 2 First National Squirrel Bank 3 Squirrels 5000 Inc invoiceid customerid ... 1 1 # Acorn Enterprises 2 1 # Acorn Enterprises 3 3 # Squirrels 5000 Inc customer invoice itemid invoiceid Description Amount 1 1 1 hrs Professional Services $10 2 2 Acorn Parts $12.50 3 2 5 hrs Professional Services $50 4 3 Delivery Fee $17.50 5 3 Replacement Acorn $20 invoice_item
- 23. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 23 customerid name 1 Acorn Enterprises 2 First National Squirrel Bank 3 Squirrels 5000 Inc invoiceid customerid ... 1 1 # Acorn Enterprises 2 1 # Acorn Enterprises 3 3 # Squirrels 5000 Inc customer invoice itemid invoiceid Description Amount 1 1 1 hrs Professional Services $10 2 2 Acorn Parts $12.50 3 2 5 hrs Professional Services $50 4 3 Delivery Fee $17.50 5 3 Replacement Acorn $20 invoice_item
- 24. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 24 customerid name 1 Acorn Enterprises 2 First National Squirrel Bank 3 Squirrels 5000 Inc invoiceid customerid ... 1 1 # Acorn Enterprises 2 1 # Acorn Enterprises 3 3 # Squirrels 5000 Inc customer invoice itemid invoiceid Description Amount 1 1 1 hrs Professional Services $10 2 2 Acorn Parts $12.50 3 2 5 hrs Professional Services $50 4 3 Delivery Fee $17.50 5 3 Replacement Acorn $20 invoice_item
- 25. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 25 customerid name 1 Acorn Enterprises 2 First National Squirrel Bank 3 Squirrels 5000 Inc 4 Savvy Squirrel Global invoiceid customerid ... 1 1 # Acorn Enterprises 2 1 # Acorn Enterprises 3 3 # Squirrels 5000 Inc 4 4 # Savvy Squirrels customer invoice itemid invoiceid Description Amount 1 1 1 hrs Professional Services $10 2 2 Acorn Parts $12.50 3 2 5 hrs Professional Services $50 4 3 Delivery Fee $17.50 5 3 Replacement Acorn $20 6 4 Premium Acorn $999 invoice_item
- 26. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 26 customerid name 1 Acorn Enterprises 2 First National Squirrel Bank 3 Squirrels 5000 Inc 4 Savvy Squirrel Global invoiceid customerid ... 1 1 # Acorn Enterprises 2 1 # Acorn Enterprises 3 3 # Squirrels 5000 Inc 4 4 # Savvy Squirrels customer invoice itemid invoiceid Description Amount 1 1 1 hrs Professional Services $10 2 2 Acorn Parts $12.50 3 2 5 hrs Professional Services $50 4 3 Delivery Fee $17.50 5 3 Replacement Acorn $20 6 4 Premium Acorn $999 invoice_item
- 27. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 27 customerid name 1 Acorn Enterprises 2 First National Squirrel Bank 3 Squirrels 5000 Inc 4 Savvy Squirrel Global invoiceid customerid ... 1 1 # Acorn Enterprises 2 1 # Acorn Enterprises 3 3 # Squirrels 5000 Inc 4 4 # Savvy Squirrels customer invoice itemid invoiceid Description Amount 1 1 1 hrs Professional Services $10 2 2 Acorn Parts $12.50 3 2 5 hrs Professional Services $50 4 3 Delivery Fee $17.50 5 3 Replacement Acorn $20 6 4 Premium Acorn $999 invoice_item
- 28. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | 28 customerid name 1 Acorn Enterprises 2 First National Squirrel Bank 3 Squirrels 5000 Inc 4 Savvy Squirrel Global invoiceid customerid ... 1 1 # Acorn Enterprises 2 1 # Acorn Enterprises 3 3 # Squirrels 5000 Inc 4 4 # Savvy Squirrels customer invoice itemid invoiceid Description Amount 1 1 1 hrs Professional Services $10 2 2 Acorn Parts $12.50 3 2 5 hrs Professional Services $50 4 3 Delivery Fee $17.50 5 3 Replacement Acorn $20 6 4 Premium Acorn $999 invoice_item
- 29. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Backup Methods • mysqldump • Is warm by default. Can safely be set hot if all InnoDB. • Logical backup. Slow to recover. • Snapshot Based -‐ i.e. LVM snapshot. • Consistent + Hot • May have higher impact during snapshot phase. • Best used with a script like mylvmbackup. 29
- 30. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Backup Methods (cont.) • MySQL Enterprise Backup • Commercial Tool • Hot with InnoDB. Warm for MyISAM phase • Low Impact 30
- 31. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | How to Decide? • MTTR of logical backups is much higher. • Most failures are accidents (either by DBA or user). • “I accidentally updated the phone number of the wrong customer!” 31
- 32. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Replication • Built in to MySQL • Easy to setup and get running • Uses same port 3306 • Most deployments will use Replication 32
- 33. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Replication Options • Row Format • Statement Based (default) • Row Based • Global Transaction IDs • Semi-‐Synchronous • Time delayed 33
- 34. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 34 Program Agenda Introduction MySQL Architecture Backup & Replication Performance & Capacity Planning 1 2 3 4 1 4
- 35. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Quality of Service • Often better to let one too demanding user go unserviced than all users go unserviced • Statement Timeout coming to MySQL 5.7. 35
- 36. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Quality of Service (cont.) • Replication Slaves, Hadoop very useful. • Important to Optimize for Response Time (user facing) before sub optimizing for background tasks. 36
- 37. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Configuration Most important settings • innodb_buffer_pool_size -‐ 50-‐80% of memory • innodb_log_file_size -‐ up to 4G quite safe 37
- 38. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Configuration Also Useful • innodb_flush_method • innodb_flush_neighbors • innodb_io_capacity and innodb_lru_scan_depth • Explained here: • http://www.tocker.ca/2013/09/17/what-‐to-‐tune-‐in-‐ mysql-‐56-‐after-‐installation.html 38
- 39. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted How much can you expect from config? Versus other optimizations • Optimizing Queries is critical to performance • SQL is hard • Easy to write • Hard to sight check for performance 39
- 40. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted How to find slow queries? • Pro-‐active: • Design or review all schema and queries. • Re-‐active: • Find worst offenders and suggest improvements to development staff. 40
- 41. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Realistic Compromise • Query and index usage changes over time • You have to have tools in place to find slow SQL • We release MySQL Enterprise Monitor for this purpose 41
- 42. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Hardware Choices • Frequently memory is under-‐provisioned • http://rhaas.blogspot.ca/2014/08/memory-‐ matters.html (PostgreSQL) • http://www.tocker.ca/2013/05/10/twice-‐as-‐much-‐ ram-‐does-‐not-‐equal-‐twice-‐as-‐fast.html (Me; MySQL) 42
- 43. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Hardware Choices (cont.) • SSD transition is real • But still better to buy more memory first • Database need to write for persistence. Heavier IO requirements than any other tier 43
- 44. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted Hardware Choices (cont.) • More CPUs can mean more consistent performance • Some level of scale up + scale out is best: • http://www.tocker.ca/2014/04/22/five-‐reasons-‐why-‐ vertical-‐scalability-‐matters.html 44
- 45. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Non Exhaustive List of Duties • Backup • Replication • Patching & Upgrades • Performance and Capacity Planning • Coaching & Mentoring • Fighting Fires 45
- 46. Copyright © 2014 Oracle and/or its affiliates. All rights reserved. | Oracle Confidential – Internal/Restricted/Highly Restricted 46