PlanetMysql.ru — информация о СУБД MySQL

The Maatkit toolkit for MySQL has a lot of functionality that’s common across the tools. It’s not a good idea to document this in each tool’s man page, of course. So there is an overall maatkit man page. It explains concepts such as configuration file syntax. This and all the other Maatkit man pages are online.

Related posts:

1. How PostgreSQL protects against partial page writes and data corruption
2. Writing a book about Maatkit
3. Learn about Maatkit at the MySQL Conference
4. Maatkit version 1417 released
5. Maatkit version 4334 released

The Maatkit tools provide a suite of additional MySQL commands. There is one command I use constantly and that is mk-query-digest.

Unfortunately the documentation does leave a lot to be desired for usability. While throughout, it is a man page and not a user guide. Several of us have discussed writing better documentation however it’s always a matter of time. I have however learned a number of tips and I’d like to share them in smaller digests.

The first is showing additional display. Maatkit works on truncating per line output to a reasonable length of 73 characters?

One of those lines is the list of hosts that connected to MySQL for a query, for example.

# Hosts                  4 192.168.40... (2), 192.168.40... (2)... 2 more
# Hosts                  3 99.99.245.14 (12), 999.106.206.167 (6)... 1 more

The problem is I want to know what that 1 more is so I can gather a complete list of IP addresses that connect to this server. You do that with the –show-all=host argument.

Without

$ cat external.tcpdump | ./mk-query-digest --type tcpdump | grep Hosts | uniq -c
#
      1 # Hosts                  3 99.99.245.14 (12), 999.106.206.167 (6)... 1 more
      1 # Hosts                  1 99.99.139.140

With

$ cat external.tcpdump | ./mk-query-digest --type tcpdump --show-all=host | grep Hosts | uniq -c
      1 # Hosts                  3 99.99.245.14 (12), 999.106.206.167 (6), 99.99.139.140 (2)
      1 # Hosts                  1 99.99.139.140

You can apply the same principle to the Users as well with –show-all=user

$ cat external.tcpdump | ./mk-query-digest --type tcpdump  --show-all=user | grep Users | uniq -c
      1 # Users                  2 xxx (13), phpmysqlmo... (5)
     49 # Users                  1  xxx

The problem is a still gett a truncation of the name ‘phpmysqlmo…’ That’s the one thing I’m trying to uncover, because that IP and usernme are not valid permissions for this system.

While I use this tcpdump command for MySQL query analysis with mk-query-digest, I found recently that it didn’t work on FreeBSD

$ tcpdump -i bge0 port 3306 -s 65535 -x -n -q -tttt -c 5
tcpdump: syntax error

It left me perplexed and reading the man page seemed to indicate my options were valid. I tried a few variances just to be sure without success.

$ tcpdump -i bge0 -c 5 port 3306 -x
tcpdump: syntax error
$ tcpdump -i bge0 -c 5 port 3306 -q
tcpdump: syntax error
$ tcpdump -i bge0 -c 5 port 3306 -tttt
tcpdump: syntax error

The solution was actually quite simple in the end, it had nothing to do with the commands, it had everything to do with the order of them. Placing port as the last option solved the problem.

$ tcpdump -i bge0 -s 65535 -x -n -q -tttt -c 5  port 3306

$ uname -a
FreeBSD db4.example.com 6.3-RELEASE-p3 FreeBSD 6.3-RELEASE-p3 #0: Wed Jul 16 05:13:50 EDT 200

I’m surprised, and flattered, to see that people still use, write and recommend mysqlsla, mysqlreport and–most surprisingly–mysqlsniffer. In truth, however, I consider all the original Hack MySQL tools as retired. Maatkit consumes the majority of my development time and provides better replacements for all the Hack MySQL tools. The mk tools are better because–most importantly–they’re tested, their code is more robust, and they benefit from the collected knowledge and experience of the community’s top minds (whereas the Hack MySQL tools are brain-children of only my knowledge and experience circa several years ago).

Thus I created a new tools page where I list and briefly profile free, open-source MySQL tools. As the intro paragraph states, MySQL Forge does this, too, but imho the forge is a dense jungle in which it is difficult to discern the useful bits from the less-than-useful bits. My tools page is meant to 1) inform people that the Hack MySQL tools are retired, 2) list replacements for them, and 3) give people new to the MySQL universe a quick, simple list of tools they’ll probably want to become familiar with.

Kind thanks to all who used, wrote about, contributed to and recommend the Hack MySQL tools over the years.

Daniel changed mk-query-digest to use much less memory. It parsed and aggregated a 2GB MySQL slow query log file in a few dozen megabytes of memory for me yesterday. Thanks to Facebook for sponsoring this work.

Related posts:

1. mk-query-digest now supports Postgres logs Maatkit do
2. Learn about mk-query-digest at PgEast 2010 I’ll
3. Slides from my session on mk-query-digest at PgEast 2010 After two

Related posts brought to you by Yet Another Related Posts Plugin.

If you’re new to the MySQL DBA role, you’ll be excited to learn about the Maatkit toolset.  It provides a whole host of valuable functionality and fills many of the DBAs day-to-day needs.

DatabaseJournal – Wonders of Maatkit

I spent a little time yesterday doing some things with the “summary” tool from Aspersa. I added support for summarizing status and configuration of Adaptec and LSI MegaRAID controllers. I also figured out how to write a test suite for Bash scripts, so most major parts of the tool are fully tested now. I learned a lot more sed and awk this weekend.

There is really only one way to get status of Adaptec controllers (/usr/StorMan/arcconf), but the LSI controllers can be queried through multiple tools. I added support for MegaCli64, as long as it’s located in the usual place at /opt/MegaRAID/MegaCli/MegaCli64. I am looking for feedback and/or help on supporting other methods of getting status from the LSI controllers, such as megarc and omreport. If you can contribute sample output from these tools, please attach them as a file to a new issue report on the project’s issue tracker. (Don’t paste them as text, please — formatting and whitespace will get mangled. Tabs and spaces need to be preserved.)

I am slowly gaining insight into how best to write a similar summary tool for MySQL servers. The goals of this tool are very specific — including things like diff’able output. I’m figuring out what went wrong with Maatkit’s mk-audit tool and how to go about it differently.

Related posts:

1. mk-query-digest now supports Postgres logs Maatkit do
2. New Maatkit tool to compute index usage In a coupl
3. Try mk-query-advisor, a new Maatkit tool We have an

Related posts brought to you by Yet Another Related Posts Plugin.

I was just reading up on the syntax for index hints in MySQL, and noticed this:

An index_name value need not be a full index name. It can be an unambiguous prefix of an index name. If a prefix is ambiguous, an error occurs.

I actually prefer not to have extra “syntactic sugar” features such as this. It helps avoid bugs and unexpected behavior. Even if I don’t use it intentionally, I can get bitten by it, if someone adds another index whose name has the same prefix as one that I already use:

mysql> create table t(a int primary key);
mysql> select * from t force index(PRIMAR);
Empty set (0.00 sec)

mysql> alter table t add key PRIMARY_2(a);

mysql> select * from t force index(PRIMAR);
ERROR 1176 (HY000): Key 'PRIMAR' doesn't exist in table 't'

I actually considered adding support for prefixes of command-line options to Maatkit, once upon a time. This way you’d be able to say --rep instead of spelling out the full option name; some of the options are very long-winded. This is pretty standard behavior, and even the MySQL command-line tools let you do it. But I came to my senses quickly when I realized that this would never let us rest easy about backwards and forwards compatibility. Even if it weren’t a potential problem, I think there are more important things to work on in Maatkit.

Related posts:

1. A simple way to make birthday queries easier and faster It’s
2. A simple and effective way to protest DRM If you don
3. New Maatkit tool to compute index usage In a coupl

Related posts brought to you by Yet Another Related Posts Plugin.

In a couple of recent consulting cases, I needed a tool to analyze how a log of queries accesses indexes and tables in the database, specifically, to find out which indexes are not used. I initially hacked together something similar to Daniel Nichter’s mysqlidxchk, but using the framework provided by Maatkit, which gave me a pretty good start right out of the box. This was useful in the very tight time constraints I was under, but was not a complete solution. Alas, I could not use anything like Percona’s enhancements for finding unused indexes.

So, in response to another consultant’s customer request (and sponsorship — thank you!) I spent more time actually writing a real tool in the Maatkit style, with full tests and all the rest of the usual goodies. The resulting tool finds all indexes in a server, EXPLAINs the log of queries against the server, and reports which indexes were never selected by EXPLAIN.

Such a tool invites many interesting questions beyond “which indexes are not used.” Here are a few samples:

• Which queries have several possible execution plans?
• Which indexes are chosen instead of other indexes?
• Which queries have many variations? Only one variation?
• Which indexes are considered as alternates for other indexes?

I plan to add functionality for these kinds of questions in the future. But for right now, there’s a start on this tool in Subversion trunk, under mk-index-usage. I am interested in feedback on it; what doesn’t it handle correctly? What else could it do for you? Please post your questions and suggestions to the Maatkit mailing list, or report an issue on the Maatkit project’s issue tracker.

It’s kind of nice to be writing a single-purpose tool again. Many of the Maatkit tools are extremely complex at this point, some with more than 50 command-line options. This one doesn’t have any options at all, besides the standard ones to connect to a MySQL server.

Related posts:

1. Analyze and optimize memcached usage with Maatkit Ryan poste
2. Try mk-query-advisor, a new Maatkit tool We have an
3. Seeking input for a new tool to verify MySQL upgrades I’ve

Related posts brought to you by Yet Another Related Posts Plugin.

The May release of Maatkit included a new feature in mk-query-digest. This allows you to process queries in many pieces, write out intermediate results, and then combine the pieces in a separate step. Maybe it’s not exactly map-reduce, but it makes a good headline.

The purpose is to enable query analysis across an arbitrarily large group of servers. Process queries on all of them, ship the results to a central place, and then combine them together. Pre-processing the results has some nice benefits, such as reduced bandwidth requirements, speeding up processing by doing it in parallel, and reducing the workload on the central aggregator. One Percona customer with many MySQL instances is trying this out.

The --save-results option on mk-query-digest saves the digested results to a file, stopping just before the final stages of the query event pipeline. There is a tool in Subversion trunk, tentatively called mk-merge-mqd-results, which reads these saved files, aggregates them together, and then finishes the process of computing statistics and making a report.

Related posts:

1. Try mk-query-advisor, a new Maatkit tool We have an
2. Analyze and optimize memcached usage with Maatkit Ryan poste
3. Speaking about Maatkit at CPOSC I’m

Related posts brought to you by Yet Another Related Posts Plugin.