1 / 16

Optimizing MySQL: Landscape, Engines, High Availability, and Performance Insights

Join Jed Reynolds as he explores the intricate world of MySQL, focusing on its landscape, various engines like InnoDB and MyISAM, and effective replication techniques. Learn about high availability options, performance enhancements, and the nuances between MySQL and other RDBMS such as Oracle and PostgreSQL. This session highlights user tools, community editions, and commercial variants, along with practical maintenance tips for optimizing performance. Ready your questions on the board to dive into this comprehensive overview.

kat
Télécharger la présentation

Optimizing MySQL: Landscape, Engines, High Availability, and Performance Insights

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 Overview • Jed Reynolds • Write Your Questions on the Board! • Landscape, Engines, HA, Performance • Questions

  2. Overview • Landscape • MySQL Engines • Replication • High Availability • High Performance • Questions

  3. RDBMS Landscape • Smaller: SQLite, FireBird, BerkeleyDB • MySQL, PostgeSQL, SQL Server • MySQL Enterprise, NDB • Larger: Oracle Enterprise, DB2, other OLTP

  4. Oracle v MySQL? • Boring

  5. MySQL Landscape • Various Editions: • Community,Standard, Standard, Enterprise • User Tools: • Workbench, Query Analyzer, Monitor • Commercial Variants • Percona, Kickfire • Sister projects: • MariaDB, askmonty.org • Drizzle, drizzle.org

  6. MySQL Engines • InnoDB (v: XtraDB, PBXT, Aria) • ACID, Triggers, Transactions, MVCC • MyISAM • Non ACID, fast • NDB – in memory, cluster • BerkeleyDB - keystore • Memory • Archive • Federated

  7. MySQL Replication • Built in, simple, multi-master • Log shipping • Single threaded • Capture log position and snapshot • Ignore some tables • Cross-engine

  8. High Availability • No Backup: No Replication: No HA • You Want Montioring • Backup your logs and data • N+1, N+2 sized pools • Health criteria • Single v. Multi master, failover • Table Maintenance, Partitioning • Failure containment (when do you shard?)

  9. High Performance • Avoid the database • Is a DB correct tool? • Pregenerated results? • Application caching? • Results caching, memcache

  10. Multiple Data Stores? • Your RDBMS doesn't need to do it all • NoSQL is vague • Document, key store, or graph • http://en.wikipedia.org/wiki/NoSQL_(concept) • Full Text indexes: Sphinx, Lucene, Solr

  11. 2: Avoid your disk • Judcial query caching • Order by + group by = filesort • Avoid table scans, use indexes • Multi Column Indexes

  12. 3:Concurrency • Table locking • Separate servers/engines per task • Replication • Partitioning • Sharding

  13. 4:Disk thruput • Raid • Partitioning • Sharding

  14. 5:Maintenance • Contain maintenance impact • Practice in your dev env • Alters, Indexing • Optimize Table • Repair Table

  15. Questions, Sources • Does Luke Skywalker use Oracle or CouchBase? • Askmonty.org • Drizzle.org • Percona.com • mysqlperformanceblog.com • www.xaprb.com/blog/ • Maatkit

  16. Thank You • @jed_reynolds • jed@bitratchet.com • Blog.bitratchet.com • Sunday: Penguin Bike Ride After Talks!

More Related