Maatkit’s mk-loadavg tool is a helpful way to gather information about infrequent conditions on your database server (or any other server, really). We wrote it at Percona to help with those repeated cases of things like “every two weeks, my database stops processing queries for 30 seconds, but it’s not locked up and during this time there is nothing happening.” That’s pretty much impossible to catch in action, and these conditions can take months to resolve without the aid of good tools.

In this blog post I’ll illustrate a very simple usage of mk-loadavg to help in solving a much smaller problem: find out what is happening on the database server during periods of CPU spikes that happen every so often.

First, set everything up.

1. Start a screen session: …

Yesterday I attended CPOSC 2009. The conference was great. It was very well run, and I liked the sessions. I would definitely attend this conference again, and will recommend that Percona sponsor it next year. I attended the following talks:

• Stop Worrying and Start Monitoring with Nagios (Andrew Libby)
• DRBD, Network Raid, High Availability and General Awesomeness (Brian Gorka)
• MySQL Performance Tuning for non-DBAs (Tom Clark)
• Wonderful Desktop Tricks, and Aesthetics (Seth Jerome)
• Jump Start Django: The Web Framework for Perfectionists with Deadlines (Rob Yates)
• Watching and Manipulating Your Network Traffic (Josiah Ritchie)

And then of course I gave my own talk on Maatkit ( …

I had trouble today on a client site using my MySQL power tools Maatkit and Statpack.

$ ~/scripts/statpack.py --files=mysql.status.091015.080001.txt,mysql.status.091015.090001.txt
Traceback (most recent call last):
  File "/home/rbradfor/scripts/statpack.py", line 563, in ?
    main()
  File "/home/rbradfor/scripts/statpack.py", line 527, in main
    locale.setlocale(locale.LC_NUMERIC, '')
  File "/usr/lib64/python2.4/locale.py", line 381, in setlocale
    return _setlocale(category, locale)
locale.Error: unsupported locale setting

$ cat /var/log/slow-query.log | ./mk-query-digest
perl: warning: Setting locale failed.
perl: warning: Please check that your locale settings:
        LANGUAGE = (unset),
        LC_ALL = (unset),
        LANG = "e_US"
    are supported and installed on your system.
perl: warning: Falling back to the standard …

Q: What SQL is running on your MySQL database server now?
A: The bane of pain for MySQL DBA’s when there is no official MySQL instrumentation that is dynamic and fine grained sufficiently to solve this problem at the SQL interface.

While hybrid solutions exist, the lack of dynamic and real-time are the issues. There is however great work being done by Baron and others on Maatkit mk-query-digest and packet sniffing the MySQL TCP packets.

$ sudo tcpdump -i eth0 port 3306 -s 65535  -x -n -q -tttt | ./mk-query-digest --type tcpdump
tcpdump: verbose output suppressed, use -v or -vv for full protocol decode
listening on eth0, link-type EN10MB (Ethernet), capture size 65535 bytes
# Caught SIGINT.
5444 packets captured
8254 packets received by filter
2809 packets dropped by kernel
# 2.1s user time, 40ms system time, 22.23M rss, 57.60M vsz
# …

I’ll be attending and presenting at the 2009 Central Pennsylvania Open-Source Conference. My session is on Maatkit. I see Tom Clark has a session on MySQL performance! I hope to see you there — I’ve really become a fan of these regional conferences.

By the way, I’ve also created a speaker badge by adapting a wallpaper someone else made — you can find it on the sidebar of my blog if you’re also a speaker.

Related posts:

1. Speaking about Maatkit at CPOSC I’m
2. Speaking at EdUI Conference 2009 I’m

3. …

We often encounter customers who have partitioned their applications among a number of databases within the same instance of MySQL (think application service providers who have a separate database per customer organization ... or wordpress-mu type of apps). For example, take the following single MySQL instance with multiple (identical) databases:

SHOW DATABASES;
+----------+
| Database |
+----------+
| db1      |
| db2      |
| db3      |
| db4      |
| mysql    |
+----------+

Separating the data in this manner is a great setup for being able to scale by simply migrating a subset of the databases to a different physical host when the existing host begins to get overloaded. But MySQL doesn't allow us to examine statistics on a per-database basis.

Enter Maatkit.

There is an often-ignored gem in Maatkit's mk-query-digest, and that is the --group-by argument. This can …

We all knew that we are risking with MMM. Risking, and placing availability as a more important like consistency.  But non of us can risk loosing data forever but we show using it, regarding to our conversations think:  "I can fix my data later on, but I can’t turn back time and prevent the downtime. (Pascal Hofmann@xaprb.com)".

As I wrote before about staying online, now let me write about how to stay consistent.

We all know, mmm is not like a key of salvation, but its getting close to it . While MySQL doesn't support multi-master-slave environments from it's source code, we will sleep badly wondering on the safety of our precious databases.

But its not just about MMM, a few days ago we ran in to a well known InnoDB "feature". Its about the auto increment counter determination on restart. InnoDB try to count the next auto increment value on MySQL restart what can screw up things in the replication as in your …

A short time ago in a galaxy nearby, Domas Mituzas wrote about contention profiling with GDB stack traces. Mark Callaghan found the technique useful, and contributed an awk script (in the comments) to aggregate stack traces and identify which things are blocking most threads. I’ve used it myself a time or five. But I’ve found myself wanting it to be fancier, for various reasons. So I wrote a little utility that can aggregate and pretty-print backtraces. It can handle unresolved symbols, and aggregate by only the first N lines of the stack trace. Here’s an example of a mysqld instance that’s really, really frozen up:

bt-aggregate -4 samples/backtrace.txt | head -n12
2396 threads with the following stack trace:
        #0  0x00000035e7c0a4b6 in …

I’m going to present on Maatkit at the CPOSC conference in central Pennsylvania on Saturday, October 17th 2009. I’ll give an overview of the toolkit, which is no longer an easy task in a single session. I see a number of other interesting sessions have been accepted. It looks like it’ll be a good conference.

Related posts:

1. Speaking at EdUI Conference 2009 I’m
2. Learn about Maatkit at the MySQL Conference I’m
3. Presentation uploaded …

You probably know that it’s possible to set configuration variables to log queries that don’t use indexes to the slow query log in MySQL. This is a good way to find tables that might need indexes.

But what if the slow query log isn’t enabled and you are using (or consulting on) MySQL 5.0 or earlier, where it can’t be enabled on the fly unless you’re using a patched server such as Percona’s enhanced builds? You can still capture these queries.

The key is knowing what it really means for a query to “not use an index.” There are two conditions that trigger this — not using an index at all, or not using a “good” index. Both of these set a bit. If either bit is set, the query is captured by the filter and logged. Both of these bits also …