Overview The Skinny

Database Proxies provide a single entry point into MySQL for the calling client applications.

Proxies are wonderful tools to handle various situations like a master role switch to another node for maintenance, or for transparency with read and write connections.

However, when the time comes to perform the switch action, all of the calling clients have been funneled through the proxy, so identification of the calling host from the database itself becomes difficult.

The Problem What is going on?

Let’s illustrate how not knowing the source of a client connection can be an issue for the database administrator…

In the following diagram, three client applications connect to a Tungsten Cluster via the Connector proxy:

…

Disclaimer: I’m not specially an expert of benchmarking, this post is to compare different options. All comments and advices are welcome.

I’m not telling you anything new, the show processlist command is a fantastic command line tool for instant check.
But what about monitor your databases with this command embedded in a tool?

Just have a look at this graph:

With 5K queries per seconds, how much will be hidden with a show processlist executed every seconds? Probably a lot.
So, I wanted to test which alternatives could be efficient to retrieve all the queries during a time lapse.

Test procedure and configuration

I used sysbench 0.5 (with oltp.lua) to make my tests on different configurations and …

Your MySQL server is overloaded. You see hundreds of running queries in the SHOW PROCESSLIST taking many seconds to run, or can’t connect at all because all connections slots are busy. If you have worked with MySQL long enough you surely have seen it, probably more than once. This is what I would call “MySQL server overload” – having more work than the server can possibly handle. At this point I see people often jumping to the conclusion that something went wrong with MySQL and focus all their effort on this belief. This is also often how we see questions framed when they are filed with our Support or to Emergency Consulting.

In fact there are two very distinct causes for such a situation – and to find the resolution most effectively you need to understand what …

When InnoDB appears hung, I know the natural reaction is to check SHOW ENGINE INNODB STATUS.

In fact, it’s the first thing I check when InnoDB tables are involved.

However, I just want to iterate how valuable SHOW FULL PROCESSLIST and/or mysqladmin debug outputs can be even when it seems mysqld is hung on on InnoDB table.

Two recent cases I’ve encountered illustrate why.

Case #1:

MySQL appeared hung on the following simple, single-row INSERT:

---TRANSACTION 0 2035648699, ACTIVE 76629 sec, process no 9047,
OS thread id 3069426592, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
...
INSERT INTO test (id, parent, text) VALUES (180370, 70122, 'test table')

At least that’s what it seemed per the INNODB STATUS, but unfortunately, there wasn’t any further information to go on.

The next time it occurred, SHOW FULL PROCESSLIST was captured at the time.

…

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=1
*** (1) WAITING FOR THIS LOCK TO BE …

Yes, it has been a while since I last posted, so we'll see how this year goes...Just the other evening I came across something that I should have done much sooner. Run SHOW PROCESSLIST every second without hitting "ENTER" repetitively.The Setup1) Create a temporary user with a non-sensitive password on localhost with SUPER privs. Yes, this crazy, but I did say *temporary*, right? mysql> GRANT

Since MySQL Proxy 0.7.0 is soon to be released, I thought another brief tutorial would be helpful. Today we ran across a good use case, and so I wanted to pass this along. If you’ve not used Proxy yet, this is a great opportunity to get your feet wet with it and some Lua.

When queries are routed though MySQL Proxy from various servers, the MySQL Server only sees that the query came from the machine Proxy is running on.

So when you are viewing output of SHOW PROCESSLIST, you have no way of telling what server a particular query originated from.

However, this could be very useful information to have, especially to determine which server a particular long-running query is coming from at that particular moment in time.

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db …

In two words: online operations. In a paragraph: Forget partitioning, row-based replication and events. The big reasons most people are going to salivate over 5.1, and probably start plans to upgrade now, are the online operations:

• online ALTER TABLE for column rename, column default value change, and adding values to the end of an ENUM/SET
• Online, table-based logging. No more need to restart your server to enable or change the general or slow query logs. You can have the standard file-based output or choose a table format…which you can query.

(more…)