Some time ago I get brand new IBM POWER6 server as the replacement for "old" P5 used to host Oracle database. Because we planed to use advanced virtualization with VIOS + LPAR/DLPAR I conceived the idea to use one spare partition for MySQL tests. Because I had no past experience with it and there is not much documentation all around the web, I tried to set-up system and database traditional way. The first problem I hit was memory allocation and I think it is the best place share my remarks. Let's start from the beginning..

For any reason, you decided to run MySQL database on AIX 5L operating system. You compiled it successfully, configured and.. unluckily database didn't start due to memory allocation problem?

Basically, for some historical reasons AIX OS will allow your application to allocate maximum 256MB of memory per process by default. To use more, you have to use "Large Address Space" memory model so AIX will split …

When running InnoDB you are able to dig into the engine internals, look at various gauges and counters, see past deadlocks and the list of all open transactions. This is in your reach with one simple command -- SHOW ENGINE InnoDB STATUS. On most occasions it works beautifully. The problems appear when you have a large spike in number of connections to MySQL, which often happens when several transactions kill the database performance resulting in very long execution times for even simplest queries, or a huge deadlock.

In such rare cases SHOW ENGINE InnoDB STATUS often fails to provide the necessary information. The reason is that its output is limited to 64000 bytes, so a long list of transactions or a large deadlock dump may easily exhaust the limit. MySQL in such situation truncates the output so it fits the required size and obviously this is not good since you may lose some valuable information from your sight. …

We have written before about the importance of using numeric types as keys, but maybe you've inherited a schema that you can't change or have chosen string types as keys for a specific reason. Either way, the character sets used on joined columns can have a significant impact on the performance of your queries.

Take the following example, using the InnoDB storage engine:

PLAIN TEXT SQL:

1. CREATE TABLE `t1` (
2. `char_id` char(6) NOT NULL,
3. `v` varchar(128) NOT NULL,
4. PRIMARY KEY (`char_id`)
5. ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
6.  
7. CREATE TABLE `t2` (
8. `id` int UNSIGNED NOT NULL AUTO_INCREMENT,
…

The trouble with slave lag is that you often can't see it coming. Especially if the slave's load is pretty uniform, a slave that's at 90% of its capacity to keep up with the master can be indistinguishable from one that's at 5% of its capacity.

So how can you tell when your slave is nearing its capacity to keep up with the master? Here are three ways:

One: watch for spikes of lag. If you have Cacti (and these Cacti templates for MySQL) you can see this in the graphs. If the graphs start to get a little bumpy, you can assume that the iceberg is floating higher and higher in the water, so to speak. (Hopefully that's not too strange a metaphor.) As the slave's routine work gets closer and closer to its capacity, you'll see these spikes get bigger and "wider". The front-side of the spike will always be less than a 45-degree angle in ordinary operation[1] but …

A paginated display is one of the top optimization scenarios we see in the real world. Search results pages, leaderboards, and most-popular lists are good examples. You know the design pattern: display 20 results in some most-relevant order. Show a "next" and "previous" link. And usually, show how many items are in the whole list and how many pages of results there are.

Rendering such a display can consume more resources than the entire rest of the site!

As an example, I'm looking at slow log analysis results (with our microslow patches, set to log all queries) for one client; the slow log contains 6300 seconds' worth of queries, and the two main queries for the paginated display consumed 2850 and 380 seconds, respectively.

Why is it so expensive? I typically see queries like this:

PLAIN TEXT …

When optimizing queries for customers, the first thing I do with a slow query is figure out what it's trying to do. You can't fully optimize a query unless you know how to consider alternative ways to write it, and you can't do that unless you know what the query "means." I frequently run into a situation where I'm forced to stop and ask the developers what they were trying to do with COUNT(). This is database-agnostic, not related to MySQL.

The problem is when the COUNT() contains a column name, like this:

PLAIN TEXT SQL:

1. SELECT count(col1) FROM TABLE;

If you know your SQL well, you know COUNT() has two meanings. 1) count the number of rows 2) count the number of values. Sometimes, but not always, these are the same thing. COUNT(*) always counts the number of rows in the result. If …

There are many times when I needed to find out my outgoing (or external) IP for the servers which are behind load balancers or firewalls.  I used to just login to another external server from the server in question and find out by looking at “who” what my external ip is.  Even though it works and I am so used to it, today I decided to figure out a more graceful way of finding my outgoing ip.  As most of us already know, whatismyip.com is the quickest way to find out your outgoing ip from the browser.  So I decided to use the same way on the servers.  So I issued a wget:

wget http://www.whatismyip.org

Well that does the trick.  But being lazy as I am, I did not want to have to cat the output file to find out the ip (plus there is no point of creating extra files and doing extra work to remove them).  …

Recently, I have worked on client's project and I had to read MySQL's documentation very often. Caught myself on the fact that I perform the same operation: type query like 'site:dev.mysql.com WORD' in Google. Finally, I have found search plugin MySQL50DOC (http://www.searchplugins.net/pluginlist.aspx?q=mysql&mode=title) for Firefox which makes search pretty simple.

Now since I reinstalled Vista 32bit version and its up and running, lets go through what works what don’t.

1. Daemon Tools (check)
2. Microsoft Office (check)
3. Trillian (check)
4. VMWare Server (CHECK!)
5. Diskeeper 10 (Nope, apparently I have to go download an update for Vista, postponed)

Ok now the fun part starts. How to get everything working the way I want to.

I need to start my Fedora virtual machine so I can get some development/testing done. And a wall I hit. It says that I don’t have permissions to open the file. I am logged in as a user which belongs to Administrators group. So why do I not have permission? Well let me copy it to desktop and see if that works. Voila! it does! Ok.. I will let this one go since I have already wasted my whole weekend getting Vista going.

OK.. so lets change my hosts file so I can point some domain names to …

Every programmer loves to optimize, even when we know we shouldn't. To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.

I should point out that using the hints incorrectly will most likley cause your queries to perform worse, so be sure that it actually makes sense to use them before you go nuts. This means use EXPLAIN and read the documentation on each hint before using.

It's also a good idea to enclose the hints within a SQL comment, for example SELECT /*! SQL_NO_CACHE */ columns FROM table. This can help to make your application a bit more portable.

Let's take a look at some MySQL Optimization Hints:

SQL_NO_CACHE

The SQL_NO_CACHE hint turns off MySQL's …