MySQL

Quick thot on MySQL

Had a good question in my MySQL talk about how to monitor performance, and of course I suggested turning on the MySQL slow query log. What I neglected to mention, however, was mytop. This little utility is found in the rpmforge repo and it’s like a top(1) for your mysql queries.

The basis for it is the SHOW PROCESSLIST command, of course. Mytop collects, sorts and colors the output and adds the system summary of key-hits-ratio, query count, etc at the top.

LinuxFest Northwest 2011: MySQL Overview

These are the slides I’ll be presenting with for my MySQL Overview talk. Last year I called it the “MySQL Buffet”…and I prolly have more slides up here than time window.

Slides:2011-mysql-buffet

You’ll find a surprise slide, “boring” in there. And this is pretty much my take on the Oracle acquisition of Sun thus of MySQL AB. Any we trolls of why “MySQL Sucks” aside, are you really all that interested about what features and bug fixes Oracle management approves of…in an overview talk. My take: the griping, if you want to dig, is in the askmonty.org wiki. There they are discussing how they are trying to keep feature parity with the MySQL features and fixes.

MySQL and it’s sisters are not about to disappear, esp not with how promising Drizzle and Maria look, and that there are commercial consulting services like Percona that could resell services around both Drizzle and Maria.

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.