A special extended edition of Tech Messages for 2010-04-15 through 2010-04-24:
Some of the utilities we were adding to Maatkit really did not belong there. Yes, this included some of the functionality in the now-retired mk-audit tool. We really learned a lesson about what it’s possible to support, design, spec, code, and test in a single tool.
I’ve moved those tools to a new project, Aspersa. Some folks are revolting and calling it Asparagus, because apparently that’s easier to say. Aspersa is the name of the common garden snail, which turns out to be a fascinating creature. It is also slow. Draw your own conclusions.
This project is more of a home for simple scripts and snippets — a simple place I can grab all the little utilities I use to make my life easy. There is a “summary” tool that largely replaces mk-audit’s functionality outside the database, and I plan to add a …
[Read more]
At work we have been looking for tools to monitor MySQL and at
the same time provide as much diagnosis information as possible
upfront when an alarm is triggered. After looking around at
different options, I decided to test MONyog from Webyog, the makers of
the better known SQLyog. Before we go on, the customary
disclaimer: This review reflects my own opinion and in
no way represents any decision that my current employer may or
may not make in regards of this product.
First ImpressionYou know what they say about the first
impression, and in this where MONyog started with the right foot.
Since it is an agent-less system, it only requires to install the
RPM or untar the tarball in the server where you're going to run
the monitor and launch the daemon to get started. How much faster
or simpler can it be? But in order to start monitoring a server
you need to do some …
We have an early draft of a new tool available for you to use and test. It uses heuristics to find problems in SQL. Please use it and give feedback! Here’s how:
$ wget http://www.maatkit.org/trunk/mk-query-advisor
$ perl mk-query-advisor /var/log/mysql/slow.log
By default it consumes a log file in MySQL’s slow query log format, but it can also parse the general-log format, and if you have any other kind of log you can feed it through mk-query-digest to transform the log into something it can recognize. Tell me if you’d be able to spot the mixture of join styles and use of leading % wildcards in the following query without help:
# Query ID 0x643E813A9ABDA151 at byte 2001701
# CRIT JOI.001 Mixing comma and ANSI joins.
# WARN ARG.001 Argument with leading wildcard.
SELECT `rhubarb_series_title`.`id`, `rhubarb_series_title`.`series_id`, …I've been working in a fully distributed work environment for almost 8 years now (I joined MySQL AB in April, 2002). Therefore I've been reading Toni Schneider's blog post about the "5 reasons why your company should be distributed" with great interest – he raised several points that I fully agree with and which I covered in my talks about "Working for a virtual company - how we do it at MySQL" at last year's next09 conference (slides, video) and at …
[Read more]
I'm happy to announce that my talk
"Making MySQL administration a breeze - a look into
a MySQL DBA's toolchest" has been accepted for this year's
edition of the MySQL Conference & Expo in Santa Clara, which will
take place on April 12-15, 2010. The session is currently
scheduled for Wednesday 14th, 10:50 in Ballroom E.
My plan is to provide an overview over the most popular utilities and applications that a MySQL DBA should be aware of to make his life easier. The focus will be on Linux/Unix applications available under opensource licenses that ease tasks related to user administration, setting up and administering replication setups, performing backups and security audits.
Of course I will cover the usual …
[Read more]
My proposal has been accepted, yay!
I'll be speaking on a topic that I feel passionate about:
MySQL Server Diagnostics Beyond Monitoring.
MySQL has limitations when it comes to monitoring and diagnosing
as it has been widely documented in several blogs.
My goal is to share my experience from the last few years and,
hopefully, learn from what others have done. If you have a
pressing issue, feel free to comment on this blog and I'll do my
best to include the case in my talk and/or post a reply if the
time allows.
I will also be discussing my future plans on sarsql. I've
been silent about this utility mostly because I've been
implementing it actively at work. I'll post a road map shortly
based on my latest experience.
I'm excited about meeting many old friends (and most now fellow
MySQL alumni) …
Maatkit does more than just MySQL. I’ve just committed a new version of mk-query-digest, a powerful log analysis tool, with support for Posgtres logs, in both syslog and stderr format. I’m hoping that people will give this a spin in the real world. I have lots of test cases, but that’s never enough; I’m looking for people to crunch their logs and let me know if anything breaks.
A brief tutorial:
# Get it
$ wget http://www.maatkit.org/trunk/mk-query-digest
# Run it
$ perl mk-query-digest --type pglog /path/to/log/file
# Learn about it (search for the string "pglog")
$ perldoc mk-query-digest
I’m going to close comments on this blog post so I don’t get bug reports in the comments. If you have feedback, please post it to the Maatkit mailing list, or …
[Read more]
iostat is one of the most important tools for
measuring disk performance, which of course is very relevant for
database administrators, whether your chosen database is
Postgres, MySQL, Oracle, or anything else that runs on GNU/Linux.
Have you ever wondered where statistics like await (average wait
for the request to complete) come from? If you look at the disk
statistics the Linux kernel makes available through files such as
/proc/diskstats, you won’t see await there. How does iostat
compute await? For that matter, how does it compute the average
queue size, service time, and utilization? This blog post will
show you how that’s computed.
First, let’s look at the fields in /proc/diskstats. The order and location varies between kernels, but the following applies to 2.6 kernels. For reads and writes, the file contains the number of …
[Read more]This is part 3 of my Tyrant extra's, part 1 focused on durability, part 2 focused on the perceived performance wall.
#3. Tokyo Cabinet Can have only a single writer thread, bottlenecking performance
When writing an application using Tokyo Cabinet only one connection can be opened as a “writer” while the rest are readers. Tyrant allows for multiple “writes” to be sent in from multiple applications but it still single threads them when writing out to disk. If you run several threads all just inserting into Tyrant your will see tyrant hit 100% Cpu on 1 core, and your writes will start to peter out quickly.
In my tests when I was not disk bound (FS Cache writes) I was able to complete 4Million inserts in a little over 91 seconds using 8 threads. I actually averaged 43896.98 inserts per second during my 8 thread test. Moving to 10 threads doing the same 4Million inserts I …
[Read more]