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:imported the database after doing a "mysqldump -d" (Workbench Community can't connect to the database to get the schema directly).had Workbench rearrange the diagram.followed the Workbench Team's blog instructions to spread it among more pages, since it ended really cramped.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  []

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.htmlThe 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.  []

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 TunerAt 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  []

MySQL Full Text Search LimitationsSuppose you have a MyISAM table containing a column with a full text index. This table starts to grow to a significant size (millions of rows) and gets updated fairly frequently. Chances are that you'll start to see some bottlenecks when accessing this table, since without row level locking, the reading and writing operations will be blocking each other.A solution that many people would suggest right away is to use the master for writes and a slave for reads, but this only masks the problem, and it won't take long before enough read traffic on the slave starts causing slave lags.Why Sphinx?

I just filed a very annoying bug when trying to compile with plugin engines using the 5.1.xx source tarball.DescriptionI am trying to test SphinxSE as a plugin instead of getting it statically linked and came across an annoying bug. When using the configure "--with-plugins" option only once, the engine is statically linked. When using it twice, the first engine is created as a plugin, and the 2nd one is linked statically. Here are a couple of examples:

This tip is to overcome a type of expensive query that I see all the time. In doing this, I uncovered a nasty bug in MySQL v5.1.ScenarioI've seen queries like these several times:SELECT COUNT(*) FROM sample WHERE status = n;Normally, this is OK, but if you have hundreds of millions of rows, only a handful of possible statuses and execute this query dozens of times per minute on an active InnoDB table, it quickly becomes a bottleneck.One SuggestionEstablis

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.--replicateThis 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:\tYou only need to run "mk-table-chksum" on the master.\tA simple query will tell you the slave status.\tWhen used with "--chunksize", it divides the checking  []

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  []

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\tLog into the slave using the mysql client and issue "SHOW SLAVE STATUS:

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?

Lenz Grimmer recently wrote two blogs about password security on MySQL. Both are worth reading in detail. You'll find them in Basic MySQL Security: Providing passwords on the command line and More on MySQL password security. Although I wrote a comment on the latter one, there is one point I thought was worth its own blog.