- I’ve done dutiful DBA work in the past to identify and remove what are commonly called duplicate indexes. That is, those indexes that look like (a) and (a,b). The thought is that a query will utilize an index as easily on (a) as on (a,b), and removing (a) will save storage cost and write performance. I’ve had the experience, though, of removing (a) and seeing performance tank. Why do we sometimes want to keep duplicate indexes?
- I keep finding new chances to use gh-ost online schema change tool, and thinking I might find a case it won’t work. Today I tested it on a TokuDB setup. Worked fine. This makes for an uninteresting (but satisfying) blog post. “Tried new gh-ost scenario; worked fine.”
I started the week working on my stressout database stress testing tool (with real traffic).
The goal for this weekend was to feed the generated lua scripts to sysbench. But what is this? There is a new sysbench!
I called it Little Dutch Boy for obvious reasons. He used his finger to stop a flood? Get it? OK, it amused me. More importantly, it allowed the ops team to not get woken up repeatedly.
- I often joke that if I was stuck on a desert island, the book I’d want to have along is the MySQL Reference Manual. There’s an endless source of amusement within the 4400+ pages. I recently found myself stuck in the West Virginia mountains with no quick source of internet, and discovered several bits of new trivia. Here’s just one item: Adding the –i-am-a-dummy flag to your mysql client (or within a configuration file, of course) keeps you from making updates or deletes on a whole table without a “where” clause.
Quite excited about this new MySQL ecosystem conference, this one specifically about the primary fork of MySQL, MariaDB. (Or you may think of it as the original MySQL, pre-Oracle fork.) Equally excited it’s on the East Coast.
I had the pleasure of attending a presentation by Yoshinori Matsunobu at the NYC MySQL Meetup, and learning about the MyRocks engine optimized for space and writes.
It’s been a busy month for file vulnerabilities. Thanks to Dawid Golunski at legalhackers.com for giving us all the opportunity to tighten security in our MySQL, MariaDB, and Percona Server instances.
- Disk space issues are common, and they’re often difficult to solve quickly. One way to recover some space is by enabling InnoDB compression. First, of course, you want to make sure you’ve covered alternative solutions. Can you archive data? Do partitioning/sharding? These generally involve application changes and can take longer. You may need to first do conversion to InnoDB. While compression is available for MyISAM via myisampack, and this can be useful for some use cases (for example, if you are rotating out tables on a monthly basis), it makes the tables read-only, so generally you will want to first convert MyISAM tables to InnoDB.
- The pt-online-schema-change tool has been a workhorse for years, allowing MySQL DBAs to alter tables with minimal impact to application workload, and before MySQL had native online alter capabilities. Although consistently grateful for the tool’s existence, I never liked the messiness of having to use and clean up triggers, and most DBAs have a horror story or two to tell about using online alters. When Github’s online schema transmogrifer, or gh-ost was released, then, I jumped on the chance to test it out.