LinuxFest Northwest 2010: MySQL Buffet

MySQL Buffet

This session covers topics in MySQL high availability and performance, centered mostly on your interests and questions.

Your Questions? Your Interests?

Jed Reynolds is not employed by MySQL but has been using it in a HA capacity since 2004

MySQL is a large topic and we can do a thumbnail overview followed by specific topics you’re interested in. I’ve listed the topics below in what I believe are the most important first –if you haven’t mastered backups, you have no business doing InnoDB tuning.

Backups and Recovery

  • mysql dump,
  • replication! do dumps from a pooled out replica
  • LVM snapshots
  • snapshot load issues
  • snapshot recovery: flush w/ read lock, InnoDB recovery
  • InnoDB Hot Backup
  • Maatkit replication checking correction, use cautiously

High Availability

  • replication: snapshot v. restore, LVM, InnoDB
  • load balancing: internal to application or external to application?
  • health criteria for pool-in/out
  • (replication lag, response time, disk free, system load, thread count)
  • ???? Multi-Master v. NDB?
  • Single master, multi mater, and fail over

High Performance

  • High Availability before high performance
  • mysqlperformanceblog
  • monitoring: slow queries, system load, swap, disk io, concurrent connections, replication lag
  • identifying “table pressure” and “update pressure” (large joins, inadequate indexes, bad queries)
  • indexes, multi-column indexes
  • EXPLAIN *show example, geoip data?*
  • query caching and when to not use it (large, infrequent results, frequently updated tables)
  • query caching, memcached, application level, or mysql write-thru
  • table engines suit different purposes

More Detailed Tunings can be found on the mysql performance blog. And softwareprojects.com.

Table Engines Notes

  • MyISAM, Memory, Merge, InnoDB, BDB (tx, fk)
  • Archive (fast inserts, compression, lacks indexes)
  • CSV text files
  • Black Hole
  • Federated, NDB
  • :-( Falcon – transactional, orphaned?
  • ALTER TABLE t ENGINE=foo
  • – 3rd party –
  • NitroEDB – security log management
  • BrightHouse – infobright, warehousing
  • DB2 (IBM)
  • Kickfire: column based db appliance with compression, FPGA processor, CentOS

MariaDB

This is the myontyprogram fork of mysql, and it includes other engines (PBXT) by default,  thread pools and XtraDB instead of InnoDB (same format).

Other Tricky Features

  • triggers
  • views
  • materialized views
  • memcached

Full Text Search

  • RDBMS != Full Text Search
  • Sphinx
  • Lucene, Solar

Security

  • grants
  • ssl
  • think layers: start outside your application and work your way thru it into your backup storage policies.

Don’t Forget to Backup!

Who Am I?

Jed Reynolds has been an IT pro since 1996. He recently completed his first year of car-free commuting–traveling about 2500 miles on his mountain bike, rain or shine. He also loves his Pentax K10D.

Advertisements