Pythian Group

This is an issue that keeps rearing its ugly head over and over again, and since it greatly affects performance, it is most important that DBAs of any DMBS running on Linux come to grips with it. So I decided to do some research and try different settings on my notebook. Here are my findings.

What can you find on the web?

A Wikipedia search for the word swappiness will come up empty (any volunteers out there want to write an article?). A Google search will show some pretty old material—the best article I found is from 2004: Linux: Tuning Swappiness. This article includes a detailed discussion with some interesting remarks by Andrew Morton, a Linux kernel maintainer.

So, what is swappiness?

Read the rest of this entry . . .

One very helpful use of the technique Sheeri described in Remote connections without leaving the mysql shell is making sure that replication is working properly.

According to the MySQL Reference Manual’s section on SHOW SLAVE STATUS Syntax, it shows information corresponding to the slave thread in the slave server. When replication is broken, however, or not working properly due to network issues between master and slave, this information may not be accurate. This has improved over recent releases, but it’s still not perfect.

The question, then, is: how to be 100% sure (or as close as you can get to 100%) that replication is running fine? The answer, as offered by Sheeri: use CONNECT.

Example

1. Log into the slave using the mysql client and issue SHOW SLAVE STATUS:

   Read the rest of this entry . . .

This post is more of a personal note than most on the Pythian blog, but over the holidays, I couldn’t help thinking about my turbulent last year.

First, MySQL

In 2008, I worked at three different companies. It was about a year ago (January 15^th will be a year) I was at MySQL AB’s first company-wide meeting in a few years with more than 400 of my colleagues, when all of a sudden we get the announcement: “Sun acquired MySQL for 1 billion dollars”. Many thought it was a joke, just in time to watch Jonathan Schwartz come up live on video to greet us. Talk about an intense way of starting a year! For some reason, nobody thought that toasting with a shot of vodka before 9:00am was the oddest thing to happen that morning.

Working for MySQL was, most likely, the wildest ride I will ever have. True Open Source spirit (no matter what the rest of the world says), start-up mentality, and growth equal to one order of magnitude while I was there (a little more than six years). Being in the Sales team, I was in the front lines watching the evolution from the customer portfolio point-of-view, and it was amazing. I owe MySQL and its people my 100% commitment to Open Source.

Read the rest of this entry . . .

Last week I had to confront one of those situations where you can’t really tell what is going on with a piece of software, and the final conclusion would sound completely crazy if postulated as the initial hypothesis. The regular MySQL commands and utilities fall short in these cases, so I had to resort to the three tools reviewed in this article.

The problem we were diagnosing was this: at some point in time, a number of queries that use to take less than one minute to execute, started to take between five to 15 minutes. We needed to get an insight into what was going on inside the MySQL server.

MySQL Tuner

At some point in a long diagnosis process, MySQL’s SHOW [GLOBAL] VARIABLES and SHOW [GLOBAL] STATUS are nothing more than a long list of numbers. Going through a team mate’s notes on another issue, I came across MySQL Tuner. This is an extremely simple tool that takes the information from the SHOW statements mentioned above and combines it in a useful way. Sample run:

Read the rest of this entry . . .

Martin Brown’s blog shows a pretty good way of navigating the MySQL Reference Manual. It’s worth noting, however, that finding the different topics has been a lot easier since mysql.com started using a Google appliance for its search.

I use the documentation all the time and have been doing so for years (I won’t claim that I can remember +2000 pages worth of ever-changing content). A few years back, I stopped using the search box on dev.mysql.com because the result sets were enormous, with lots of unrelated references. My technique was to do a Google site search:

For replication use the expression: replication site:http://dev.mysql.com/doc/refman/5.0/en/index.html

The result set was smaller and I would find what I was looking for relatively easily, usually within the first page.

Since the documentation team implemented the Alphabetical Index, it has succeeded the Google search as my favorite way to get the information I needed. Things are easy to find and never more than a couple of URLs away.

Read the rest of this entry . . .

Over the last few weeks I’ve been looking at several customers’ slow query logs, and I found in many of them an odd type of query. These are SELECT statements that contain an IN clause that includes dozens, sometimes hundreds of values. These statements often end in the slow query log. I’m not sure if these queries are this way by design or if they are generated by a specific database development tool.

I did some tests in one of my own databases, one with only around 10K rows in its largest table. The database corresponds to the Amarok media player. For example, I queried for songs by B. B. King (spelled “BB King”, “B.B. King”, etc. or with other artists: “B. B. King & Eric Clapton”).

The first query used a JOIN and an IN clause with all the spellings in my db; the second used the same JOIN and WHERE ... name LIKE "BB%" OR name LIKE "B.%". Both had the same execution plan, and both retrieved the same number of results. In MySQL version 4.1 there were some enhancements to the optimizer for treating these massive IN clauses, which means that for smaller databases, this is expected.

With bigger databases and more complex queries, things are different. Read the rest of this entry . . .

The Maatkit toolkit is a real blessing for the MySQL DBA. And while its documentation is pretty good, in some cases it’s necessary to read carefully a second and third time to make sure you are not missing an important piece of information. In this article I will comment on mk-table-chksum and mk-table-sync. My comments are mostly aimed at those DBAs who are considering using these utilities with medium or larger-sized databases.

–replicate

This option allows you to store the checksum results on the master, in a table that will get replicated to the slaves. Although it might seem like overhead for a simple check, it really simplifies your life, especially when used in combination with mk-table-sync. I always use it, for the following reasons:

• You only need to run mk-table-chksum on the master.
• A simple query will tell you the slave status.
• When used with --chunksize, it divides the checking and synching into manageable portions.
• It’s the best way to get consistent checks between master and slaves.

I always define the table as an InnoDB table to improve concurrency and avoid deadlocks (see the tool documentation for details). This table can be created on any database. Just make sure that it is going to be replicated. Here’s an example of the table definition (straight from the documentation) with the InnoDB specification at the end:

Read the rest of this entry . . .

A few days back I read the Workbench Team’s blog and was curious about the printing capabilities of MySQL Workbench Community edition. As we already know by now, it only allows you to print a single page. I needed to review a customer query which had several tables and some complicated relationships, so I decided to take Workbench Community for a spin (I already knew the Standard edition from my previous job) and tested the following steps:

1. imported the database after doing a mysqldump -d (Workbench Community can’t connect to the database to get the schema directly).
2. had Workbench rearrange the diagram.
3. followed the Workbench Team’s blog instructions to spread it among more pages, since it ended really cramped.
4. created a PDF file, which was barely readable on paper.

Here’s where the heavy testing started. Besides the PDF file I also created an SVG and an EPS. All of these are scalable. My thinking was that if I imported these files into the right tools, I should be able to get a bigger printout.

Read the rest of this entry . . .