The last few days I’ve been cleaning up indexes. This entails removing duplicate, unused or redundant indexes. There were quite a lot of indexes, so it was hard work. I knew why this was important to do: as the table changes, indexes need to be kept updated, less indexes means less updates and less wasted resources. But I wanted to have a real number to attach to the theory. How much benefit is there really?

Fixing a query by finding the right index feels great, as if by magic, things are blazing fast again. But indexes, like everything in life, needs balancing. As things inevitably change, some indexes will be left abandoned and unused. What happens then? Well, the database engine must keep them updated regardless, that’s wasted I/O. They also take up space. Pruning indexes is the life of any dba. Unused Indexes in MySQL 🔗Fortunately in MySQL has are two great tools: the performance_schema, which collects all kind of metrics and the sys schema which offers easier-to-read views.

This is my second part of the sysbench guide, I’ll cover here some basic benchmarks for databases. Feel free to check my first part for an introduction. Connecting to the DB 🔗First things first, we’ll need a test database. We’ll stick to the default “sbtest”, so go ahead and create it. I’ll wait. Now sysbench needs the connection parameters. The actual values depend on the target. Sysbench supports MySQL and postgreSQL.

Why, When, How? 🔗Benchmark shows the difference between should and does. Coupled with monitoring it’s a great tool to identify bottlenecks. As to when: whenever we can, as long as the system is production. A good benchmark will stress the server so we don’t want users affected. Right after provisioning a server, during a maintenance window, before and after any major changes, these are all good opportunities to take advantage of a few precious minutes of calm to run some tests.

There are loads of ways to benchmark MySQL. mysqlslap was, I believe, one of the first official tools for this. It’s bundled with the client tools for MySQL and MariaDB, so you probably already have it installed. In this post I’ll cover the basics and the auto generate sql mode, be sure to read the second part about other ways to use it. How Does It Work? 🔗mysqlslap uses the same connection parameters as the normal mysql client, so --host --port --socket --username --password all work (but strangely the --defaults-file doesn’t).

This is the second part of my mysqlslap guide, you can read the first part mysqlslap uses the same connection parameters as the normal mysql client, so --host --port --socket --username --password work. In the examples below the parameters will be omitted for brevity’s sake. The Query Mode 🔗Query mode allow us to benchmark ad-hoc queries. It’s a quick way to troubleshoot a troublesome query. In its simplest form we pass the query:

sar is part of the sysstat project, a suite of programs for system monitoring. With sar, we can log system activity, build performance reports and even make plots. sar is available on Linux, Solaris, AIX and HP-UX. We can choose to run sar automatically, as an unattended script. Or interactively and get system metrics on the spot. Unattended Mode 🔗With a simple cron entry we can log system activity. If possible, as root, otherwise some data may be missing.

A getting started guide for vmstat, iostat, mpstat and pidstat. vmstat 🔗Once I figured out how vmstat works, I found that I almost could no longer live without it, it’s that good. Fortunately it’s available everywhere, so I don’t. There are many versions around, both for Linux and Unix. vmstat does a little bit of everything: memory, processes, I/O, swap, disks, CPU. The basic syntax is: vmstat [options] <interval> <count> vmstat prints a new update every interval seconds, stopping after count lines.

Hey! Did I say welcome already? Glad you’re here. So, about blogging: three things moved me to start doing it. Number one, I’ve been turning in essays during my Portuguese classes, every week. Right now I’m on vacation and feeling the writing bug. Number two, learning. For me, experience and explaining are the best roads to learning. We all get experience, living and working, everyday. Sometimes we can choose what or where, sometimes not.

I’ve collected a lot of random thoughts and notes about writing over the last few months, which I’ve been meaning to turn into a full-sized post. Today I realized it’ll never fit on a single post, so I’ll just do it as a series. Let’s start with the beginning. I spent so much time doing things of little value. If you can read, you can write. It doesn’t take a university degree to be able to write.