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.
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.
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.
I’ve heard of people replicating MySQL from coast to coast. I’d love to hear if anyone attending @lfnw has longer replication runs. Happy to share some examples at my talk on Sat. http://linuxfestnorthwest.org/sessions/mysql-performance-and-availability
This session covers topics in MySQL high availability and performance, centered mostly on your interests and questions.
Your Questions? Your Interests?
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
- 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 Availability before high performance
- 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
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
- materialized views
Full Text Search
- RDBMS != Full Text Search
- Lucene, Solar
- 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.