A

This is one in a series of articles on how to use the innotop MySQL and InnoDB monitor. In this article I show how innotop can display locks that are causing a transaction to wait.

Pre-requisites

This tutorial is aimed at Linux installations that has the standard development tools already installed. The INSTALL file in the source archives provides good details of the software required (e.g. gunzip, gcc 2.95.2+,make).

Create a separate user for this development work.

su -
useradd mysqldev
su - mysqldev

Get an appropiate Snapshot

You can view recent snapshots at http://downloads.mysql.com/snapshots/mysql-5.1/.

The official statement on snapshots from MySQL AB.
Daily snapshot sources are only published, if they compiled successfully (using the BUILD/compile-pentium-debug-max script) and passed the test suite (using make test). If the source tree snapshot fails to compile or does not pass the test suite, the source tarball will not be published.

At the time of producing …

One of the tackiest things about SQL syntax is that stored procedures use the same delimiter for statements in stored procedures as in other statements, and that the syntax doesn't make it easy to distinguish the scope of the stored procedure. The “solution” (which I'd consider a poor workaround) is the DELIMITER keyword, which changes the character (sequence) that the client recognizes to represent the end-of-statement delimiter. From the manual, a typical stored procedure definition might be:

DELIMITER //

CREATE PROCEDURE simpleproc (OUT param1 INT)
  BEGIN
  SELECT COUNT(*) INTO param1 FROM t;
  END;
//
DELIMITER ;

The problem in BUG#11312 is that we try to replicate this stuff. But the DELIMITER kludge^Wcommand is a client-side thing which never makes it to the …

Just as Jay writes about an upcoming MySQL Barcamp, I’m all stoked to head to the 2006 inagural WordCamp. After that, I’ll be at Apple’s WWDC 2006.

There’s one thing there that interests me greatly is the My Agenda tool - you can pre-plan what you’re going for, have it printed or subscribed to in the webcal (iCal) format.

So, anyone want to catch up in San Francisco? I’ll be around from the 4th right till the 12th (though I leave later in the evening then). Drop me an email or just comment here…

In this article I’ll introduce deadlocks, analyze and explain a real example, and show how to cause and avoid deadlocks.

Depending on the ratio of READs/WRITEs in your database, removing indexes (not adding them!) will give you better performance. Indexes hurt on UPDATE and INSERT, but by how much and when?

CREATE TABLE index_test (id INT NOT NULL PRIMARY KEY auto_increment,
 a char(50), 
 b char(50)) ENGINE=MyISAM;

Insert some dummy rows:

insert into index_test (a,b) values (REPEAT('a', 50), REPEAT('b', 50));
insert into index_test (a,b) SELECT a,b FROM index_test;
insert into index_test (a,b) SELECT a,b FROM index_test;
insert into index_test (a,b) SELECT a,b FROM index_test;
insert into index_test (a,b) SELECT a,b FROM index_test;
insert into index_test (a,b) SELECT a,b FROM index_test;
insert into index_test (a,b) SELECT a,b FROM index_test;
insert into index_test (a,b) SELECT a,b FROM index_test;
insert into index_test (a,b) SELECT a,b FROM index_test;
insert into index_test (a,b) SELECT a,b FROM …

If you care about archiving best performance in your application using MySQL you should learn about prepared statements. These do not neccesary provide performance beneft but they may, they also have other benefits.

As a quick introduction - before MySQL 4.1 there were only textual statements and textual protocol for data transfer - query was sent as text and result returned back as text. For example number 123 would be sent as string "123". Such protocol had serious performance implication - queries had to be parsed fully each time, all return values had to be converted to the strings on server side and back on the client side, which is pretty expensive especially for certain data types. Furthermore BLOBs require escaping as not all characters could be used in textual protocol, which not only consumed time but also required extra memory consumption both on server and client.

So in MySQL 4.1 Prepared statement came. Do not mix these …

Marten Mickos was kind enough to ping me about my post on whether open source companies can exceed the $1 billion speed limit our (alleged) business models impose. With his permission, I've posted in some of his thoughts below, which are cogent and a bit more thoughtful than my off-the-cuff tirade. :-)

(Stephen O'Grady, btw, now has a great response to my (and others') critique of his original premise (that we're not going to see an open source company hit the $1 billion in sales mark any time soon). You should read it.)

Marten writes:

If you read " …

Looks like things continue without me at MySQL

From Stefan, the MySQL docs lead:

We’ve completely reworked the Connector/MXJ and Connector/J sections of the MySQL Reference Manual.

MySQL provides connectivity for client applications developed in the Java programming language via a JDBC driver, which is called MySQL Connector/J. It’s a JDBC-3.0 Type 4 driver, which means that is pure Java, implements version 3.0 of the JDBC specification, and communicates directly with the MySQL server using the MySQL protocol. Find the overhauled documentation here:

- MySQL 5.1: http://dev.mysql.com/doc/refman/5.1/en/connector-j.html
- MySQL 5.0: http://dev.mysql.com/doc/refman/5.0/en/connector-j.html
- MySQL 4.1: …